New feature in the BizTalk Password Manager utility

A couple of years ago I had released a small command line tool to set the passwords of the send ports (primary transport only) and the receive locations.

You can find the latest release of this tool at https://github.com/pellitteris/BizTalkPassword.

This allowed me to create scripts that, in the case of a complete application deployment, set all the necessary passwords reducing manual operations that often can cause errors.

This is just an example that can be extended if necessary. However, it has several features that can be used as is.

In the release I released a few days ago I implemented some new features with the aim of facilitating and therefore speeding up the updating of the scripts.

In particular I added these features:

  • List all the port that have a user account and a password set
  • Generate script to set passwords

To list all the port that have a user account and a password set, is enough type the following command:

Here the result:

The output is made in such a way that it can be easily imported into an Excel spreadsheet for documentation purposes.

To set passwords it is necessary to execute the “set” command. Being a command line tool, it is possible to create a batch file that sets all the necessary usernames/passwords. Keeping the script up to date can be a problem or a rather expensive activity, so I added a couple of features to simplify the already difficult deployment adventure.

In this regard it is possible to execute the “generatescript” command which generates a file with the necessary commands. The command can be executed as is, and in this case the passwords will have a placeholder “######” or can be executed with a json mapping file.

To generate the mapping file, run the following command:

The command generates a file .json like the following:

Now you have to substitute “null” with the password, then you can run the following command:

As a result you will have a “cmd” file with the commands ready to be executed.

Configuring BizTalk 2016 in High Availability Config with SQL Server 2016 Always On

This article tells about our recent experience with an installation of BizTalk Server 2016 in high availability configuration with SQL Server 2016 Always On. It’s just a small experience, but I hope it can be useful for anyone who wants to install a BizTalk infrastructure in high availability on Microsoft Azure or in a virtual environment where cluster failover is not well accepted by system engineers.

There are several articles dealing with this topic and we highly recommend reading them carefully.

In addition, we suggest you to read the article related to Master Secret Server configuration in cluster (https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1).

To design our server infrastructure, we referred to the scenario suggested by the Microsoft article.

Anyway, our implementation is smaller than the examples you can see in the article. This because it often happens to have the need for configurations in high availability but not to have a very high load.

As recommend, we have grouped BizTalk Server databases into the following four SQL Server instances:

The subdivision of the instances is due to the fact that MSDTC on SQL Server 2016 Always-On Availability Group is not able to handle distributed transactions between multiple databases within the same instance. We are all waiting for BizTalk to support the 2017 version of SQL that is able to do that.

In this specific case we don’t need ESB toolkit databases and BAM Analysis and Alert, so our final cluster implementation is illustrated in the figure below.

As you can see, we have configured one Listener for each SQL Server instance.

We do not report the installation and configuration of SQL Server as it is extensively described in the articles we referred to.

SQL Server Installed Features

On each server node and node instance we have installed the following services and features:

  • Database Engine Services
    • SQL Server Replication
    • Full-Text and Semantic Extractions for Search
  • Shared Features
    • Client Tools Connectivity
    • Integration Services

DTC Configuration

In this configuration it is not necessary to configure a DTC instance as a cluster resource because a local DTC instance will be used.

To enable Network DTC Access on the BizTalk Server and the SQL Server follow the step below:

  • In the Start menu, open “dcomcnfg”.
    • Or, open Administrative Tools, and then select Component Services.
    • Or, open Server Manager, select Tools, and then select Component Services.
  • Expand Component Services, expand Computers, expand My Computer, and expand Distributed Transaction Coordinator.
  • Right-click Local DTC, and select Properties.
  • Go to the Security tab, and check the following:
    • Network DTC Access
    • Allow Inbound
    • Allow Outbound
    • No Authentication Required
  • Select OK. If prompted to restart MS DTC, select Yes.

Enable remote access to COM

Follow the instructions in the following article to enable remote access to COM.

https://support.microsoft.com/en-us/help/3182294/0x80004027-error-when-you-try-to-remotely-access-com-object-after-you

Cluster resource check

From the failover cluster management console, enable the prevent back option in each resource group created.

This may be your preferred setting because it allows you to control when the failback occurs. For example, you may want to select Prevent failback if you want to take time to troubleshoot or run diagnostics on the failed node before allowing the node to take ownership of the SQL Server again.

Availability Group Options

Make sure Availability Groups are configured with the Per Database DTC Support option.

Also configure “Readable Secondary” equal to “Yes”. Otherwise the SSO service could generate the following error:

Failed to contact the SSO database: The target database, ‘SSODB’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.
Data Source=***;Integrated Security=SSPI;Initial Catalog=SSODB

Error code: 0x800710D9, Unable to read from or write to the database.

Finally, you must check that “Primary” has been selected in the Backup Preferences tab, in the “Where backup should occur” configuration setting.

SSO Setup

After completing the installation of SQL Server, before proceeding with the installation of BizTalk, you need to install the Enterprise Single Sign-On master secret server.

We will follow this article regarding the installation of the clustered SSO service: https://docs.microsoft.com/en-us/biztalk/core/how-to-cluster-the-master-secret-server1.

We will set up a cluster group that will contain IP address, Network Name and a generic service resource for the SSO service.

Execute the following step on both SQL Server nodes.

Enable only the following components

  • Enterprise Single Sign-On Administration Module
  • Enterprise Single Sign-On Master Secret Server

Execute the following step on first SQL Server node

Execute the following step on second node

To configure SSO in failover cluster follow the below steps:

  1. Restart the Enterprise SSO service on the first node
  2. From the command prompt type the following command:

    “<drive>:\Program Files\Common Files\Enterprise Single Sign-On\ssomanage” -updatedb XMLFile

    Where XMLFile is a file with the following content “<sso><globalInfo><secretServer>[SSO NET NAME CLUSTER RES]</secretServer></globalInfo></sso>”.

  3. Configure manual start type the “Enterprise Single Sign-On Service” service on both servers.
  4. From the failover cluster management console create a Generic Services resource for the SSO service
  5. Restore the master secret on the second cluster node

Configure SSO Database replica

Before to start to configure SSODB database replica you have to execute e full backup of database.

Install BizTalk Server Application Server

Before starting with the BizTalk Server installation, the prerequisites must be installed. Follow the step described in the Microsoft article https://docs.microsoft.com/en-us/biztalk/install-and-config-guides/set-up-and-install-prerequisites-for-biztalk-server-2016.

The article shows a series of mandatory and optional steps. Following what I have done:

  • Enable Network DTC Access
  • Disable Windows Firewall
  • Disable UAC
  • Enable IIS
  • Run 64-bit BAM portal
  • Install Visual C++ redistributable package
  • Install SQL XML 4
  • SQL Server Integration Services

BizTalk Setup

Execute the following step on both BizTalk Servers.

Enable all available components

BizTalk Configuration (First Node)

In general, the configuration of the first BizTalk node creates configuration databases while the configuration of the second node is limited to joining the existing farm.

BizTalk Configuration (Second Node)

Final Database Configuration

Database Replica

Now that all the databases have been created it is necessary to proceed with the configuration of the replica within the different SQL Server availability groups. Below are the steps to configure the BizTalkMgmtDb database.

Repeat these steps for each database created by the configuration procedure.

Linked Server & Job

Linked servers and jobs are automatically created by the configuration procedure, anyway it is better to verify that they have been created on both nodes.

It is also necessary to modify all the steps of each job to include the control that the server running is the primary one. So, include the original step inside the following code:

if (sys.fn_hadr_is_primary_replica(‘<DBNAME>’)
= 1)
begin

    [original step commands]

end;

I suggest to use the validation script for BizTalk Availability Group (https://skastberg.wordpress.com/2017/06/30/validate-your-biztalk-availability-groups/) in order to check the configuration, it is a very useful tool.

I want to thank my colleague Paolo Barbieri who helped me to reason during this little adventure and write this article.

BizTalk CRM Lookup functoid is now ready for CRM Online

In recent years I often integrate Microsoft Dynamics CRM by using BizTalk Server, and as many know, BizTalk Server does not have a native connector for MS CRM.

For this reason, we must work hard to generate canonical schemas, to resolve lookup type fields, Guid type fields and generally to develop maps.

To increase the productivity of my team I have always tried to develop tools or helpers to automate the development of long and repetitive tasks, reducing the possibility of generating errors and standardize approaches to problems.

In recent months, with the cooperation of Nino Crudele and Sandro Pereira we published several functoids that simplify integration with Dynamics CRM and in this article I want to describe the work that has been done to also integrate the CRM Online.

When you integrate MS CRM using BizTalk Server, typically you have at least two problems:

  • Transcode the Lookup and Guid type fields
  • Mapping the input information with the generic schema exposed by CRM

The BizTalk Mapper Extension Utility pack simplifies both tasks by providing specialized functoids.

Today we released a new version where you can find the CRM Lookup functoid with two new features:

  • The ability to define more than one configuration in the SSO. This allows of integrating more than one CRM instance with a single BizTalk Farm
  • Now you can integrate CRM On-Line. CRM Lookup, for performance reasons, performs lookups operation by accessing the CRM database in read only. However, the CRM Online does not expose his database, so we added the ability to do lookups using the native CRM web services that are present in both the online and on-premises versions.

The functoid now takes five mandatory input parameters:

  1. SSO Application Name: the connection parameters are stored inside SSO. Here you have to specify the application name where you decide to store the settings. This allow you to have multiple CRM environments that you can integrate;
  2. CRM Guid Field Name: key field name of which you want to get the value;
  3. CRM Entity Name: CRM entity name on which the resolution will be performed
  4. CRM Input Field Name: imagining having a “code” as input and you want to know the CRM physical key, in this attribute should specify the name of the “code” field.
  5. CRM Input Value: value from source system to resolve


The output of the functoid is a string, Example: 6erg5r-sdrf56-4dfgt5-6ty5r4-456trt 24

Installation

Download and install BizTalk Mapper Extensions UtilityPack from https://btsmapextutilitypack.codeplex.com/.

Download CRM Sdk and register in GAC the following assemblies:

  • Microsoft.Xrm.Sdk.dll (version 5.0.9910.15)

Create a SSO application by using SSO Configuration Application MMC Snap-In available at https://www.microsoft.com/en-us/download/details.aspx?id=14524.

SQL Server Lookup

Create the following SSO configuration parameters

  • CrmConnectionType: “sql”
  • CrmDatabaseConnectionString: specify the CRM database connection string

Open SQL Server management console, connect to CRM database and execute the script under CRMLookup.sql file in project folder.

Local CRM web services lookup

Create the following SSO configuration parameters

CRM Online web services lookup

Create the following SSO configuration parameters

Manage your BizTalk Passwords

During the deployment of BizTalk applications, it is sometimes necessary to set passwords manually. There are other options such as those to prepare a binding file that contains the passwords, but it is not always a quick and easy job.

Then I created a console application that easily sets the password for the send ports and receive locations so that it can create automatic deployment script.

You can get this tool at https://github.com/pellitteris/BizTalkPassword site.

It is just an example that currently manages the adapter FILE, FTP, SFTP, POP3 and WCF *. Of course you can access the source code in order to manage other kind of adapters.

This console is very simple to use, simply specify the port type (if a send port or a receive location), a user and a password.

Following an example of use:

Microsys.EAI.Framework.PasswordManager.exe -set -receive -name:MyReceiveLocation -user:John -password:@Passw0rd1

Microsys.EAI.Framework.PasswordManager.exe -set -send -name:MySendPort -user:John -password:@Passw0rd1

In addition to setting the password, it allows you to view the property of the ports that normally contain credentials (TransportTypeData).

Following are other examples of use:

Microsys.EAI.Framework.PasswordManager.exe -list -application:[application name]

Microsys.EAI.Framework.PasswordManager.exe -get -receive -name:[receive location name]

Microsys.EAI.Framework.PasswordManager.exe -get -send -name:[send port name]

Microsys.EAI.Framework.PasswordManager.exe -set -receive -name:[receive location name] -user:[username] -password:[password]

Microsys.EAI.Framework.PasswordManager.exe -set -send -name:[send port name] -user:[username] -password:[password]

Composite Operations vs User-Defined Table Type from BizTalk Server

Normally, when I integrate SQL Server with BizTalk server, I prefer to use stored procedures. This because I believe that a database within an integration scenario should not expose data models but should be a service that exposes methods.

When you need to insert data sets into a SQL Server through a stored procedure, you can use a composite operation or develop a stored procedure that accept a user-defined table type as a parameter.

For more information about the use of a composite operation, refer to the article “https://pellitterisbiztalkblog.wordpress.com/2013/07/01/insert-master-detail-data-structure-on-sql-server-in-a-single-transaction-using-biztalk-server&#8221;.

Develop a stored procedure that accepts a user-defined table type as a parameter is very simple. Simply create a user-defined table type as shown below.

CREATE TYPE dbo.Lines AS TABLE
(
    lineId INT NOT NULL,
    lineField1 VARCHAR(10) NULL,
    lineField2 VARCHAR(10) NULL,
    PRIMARY KEY (lineId)
);

Then create a stored procedure that uses the type previously created.

CREATE PROCEDURE dbo.BizTalkSample
    @headerId INT,
    @headerField1 VARCHAR(10),
@headerField2 VARCHAR(10),
@lines Lines READONLY
AS
BEGIN
    INSERT INTO sampleHeader
   (
        headerId,
        headerField1,
        headerField2
   )
   VALUES
   (
       @headerId,
       @headerField1,
       @headerField2
   );
    INSERT INTO sampleLines
   (
       lineId,
       headerId,
       lineField1,
       lineField2
   )
   SELECT
       lineId,
       @headerId,
       lineField1,
       lineField2
   FROM
       @lines;
END

At this point, it is sufficient to generate a schema from a BizTalk project.

There are different contexts in which it is preferable to use a method or the other. The main difference is that a composite operation invokes the stored procedure several times while a stored procedure that accepts a user-defined table type as parameter is called only once.

If the need is to send a dataset to SQL Server, the second method can introduce significant advantages in terms of performance. The BizTalk message is a few percent smaller, but the main point is that the number of communications between BizTalk Server and SQL Server is drastically reduced and SQL Server performs the transaction much faster.

I conducted several tests using a 100MB file which contained about 8 million lines. The improvement was amazing. The time has decreased significantly from about 20 minutes using a composite operation in just over three minutes by running a single stored procedure that accept a user-defined table type as a parameter.

BizTalk Business Activity Monitoring “remove-view” issue

When you remove an existing BizTalk Business Activity Monitoring (BAM) view using BM.exe command, you could get the following error message: “XML parsing failed at line 1, column 0: A document must contain exactly one root element”.

This occurs only if the view contains Analysis Services cube objects such as measures and dimensions.

When you deploy a BAM view that contains OLAP objects, BM.exe generates an Integration Services package that is able to process data contained in the database BAMPrimaryImport to update the Analysis Services cube.

The package, however, is not scheduled therefore you must configure a SQL Server job that periodically process BAM activities data.

If this is not done and the process has never been performed, you could have the problem.

To resolve it, you have to manually process the cube and re-execute the remove-view command.

To manually process the cube, open Microsoft SQL Server Management Studio. When “Connect to Server” window appears, select “Analysis Services” as Server type and specify the Analysis Services server name.

Then expand the “Databases” node and choose the BAM OLAP database (in my case BAMAnalysis). Then right-click on the database and select “Process”

Leave all default settings and click on Ok.

Once Process is complete, re-execute the BM.exe command.

How to generate a BizTalk schema for a SAP Web Service

 

I have several clients who own a SAP system, so I often develop integration flows that invoke BAPI exposed as Web Service. However, every time, I lose a lot of time in verifying the SAP configuration that allowed me to download the correct WSDL and generate a XSD schema. So, I decided to document every step, from the access to SAP to check the correct configuration up to the necessary actions on BizTalk side.

 

Configure the SAP logon settings

 

Start the SAP GUI program

 

 

Add new connection entry

 

 

Select “User Specified System”

 

 

There are two ways to access the SAP system, in this document I choose to connect directly to the application server.

Then, specify a connection Description, Application server name, Instance number and System ID.

 

 

Accept default settings and click Next

 

 

Select your language

 

 

At the end you have the configuration ready to use.

 

 

Access to SAP

To access SAP, use an existing connection or one configured previously specifying user and password

 

 

You should get an interface similar to that shown below

 

 

So specify “soamanager” in the command field

 

 

The SOA Management web interface appears. Click on “Web Service Configuration”

 

 

Search the BAPI you want to generate a WSDL

 

 

Select the BAPI and click on “Apply Selection”

 

 

You must be sure that the “WSDL format” section is set to “Standard”. Unlike the wizard of visual studio will not be able to generate correctly the XSD schema and the bindings file.

 

 

Click on “Open WSDL document for selected binding or service”

 

 

The Internet browser appears with the WSDL. Save the file to a folder.

 

 

 

Open the BizTalk Visual Studio project and Add a generated items.

 

 

Select “Consume WCF Service”

 

 

Select “Metadata Files (WSDL and XSD)”

 

 

In the Metadata Files form, add the WSDL file that you had previously saved and then click Next

 

 

Specify a Namespace and click on Import

 

 

That’s all. You have your SAP web services schema ready to use.

 

BizTalk Configuration Reports

Documenting the integration flows is an activity very expensive in terms of time. If we think to the configuration of the receive ports, send ports and how they are linked to the orchestrations we become crazy.

In a context where they develop many BizTalk objects you end up having documents constantly obsolete.

For this reason, I decided to develop a small package of reports that allow having this part of the documentation always up to date. Simply run reports and export them in the required format such as Microsoft Word, PDF or Microsoft Excel.

You can download the installation package at https://github.com/pellitteris/BizTalkConfigReports.

The solution includes a package of reports developed with Microsoft SQL Server Reporting Services and a database to be installed on the same SQL Server instance where the database BizTalkMgmtDb resides. This database contains the views and stored procedures performed by the reports.

Once the installation is completed you have three reports:

 

  • Orchestration Summary
  • Orchestrations
  • Receive Port with Port Subscription

 

 

Orchestration Summary

The report “Summary Orchestration” displays at a high level the relationship between the orchestrations, the receive ports and the send ports.

Each report allows you to select the application, and optionally a search string to filter the objects you want to examine or document.

 

 

Following an example of this report.

 

 

Orchestrations

The report “Orchestrations” adds more details to the previous report.

 

 

Finally the report “Receive Port having Send Send Port or Port Group Subscriptions” that displays all subscriptions to a specific port configured on the send port or on the send port group.

 

 

Now you have the source code of this solution and you can add features or simply customize reports to fit your needs.

Integration Monday – High available BizTalk infrastructure on Azure IaaS

I’m really pleased to announce that on 4 May Massimo Crippa and me will present my work about BizTalk HA on Microsoft Azure to the integration Monday (http://www.eventbrite.com/e/integration-monday-2015-05-04-massimo-salvatore-tickets-16540234286?aff=estw).

I am very honored that Michael Stephenson gave me this opportunity.

I hope to meet his expectations.

It is a pleasure to work with Massimo who was a colleague of mine at Microsys for 10 years, and now is doing a great experience of working and living at Codit.

Testing BizTalk Server Infrastructure in HA on Azure IaaS

After writing the article Configuring BizTalk Server Infrastructure in HA on Azure IaaS, I wanted to conduct some tests to check communication between servers in the BizTalk farm.

In particular, I wanted to be certain that the infrastructure of Distributed Transaction Coordinator (MSDTC) responds correctly even in case of stress.

To generate more transactions as possible, I created a flow that receives in input an xml file with a high number of elements, each of which generates the execution of an instance of an orchestration that performs a transaction on SQL Server.

Following the schema that I used:

 

 

To create multiple instances of a single orchestration I configured the debatching.

 

 

Below is a simple orchestration that transforms the incoming message in a call to a stored procedure that inserts the information into a table.

 

 

The first test was performed by submitting a file with 1000 elements that has generated 1000 instances of orchestration with their distributed transactions.

 

 <ns0:Transactions xmlns:ns0=http://MsDtcTest.MsDtcTestInputFile>

<Transaction rowNumber=0001 rowDescription=Row number 0001 />

<Transaction rowNumber=0002 rowDescription=Row number 0002 />

<Transaction rowNumber=0003 rowDescription=Row number 0003 />

<Transaction rowNumber=0004 rowDescription=Row number 0004 />

 

<Transaction  rowNumber=0998 rowDescription=Row number 0998 />

<Transaction rowNumber=0999 rowDescription=Row number 0999 />

<Transaction rowNumber=1000 rowDescription=Row number 1000 />

</ns0:Transactions>

The second test was performed by submitting a file with 10000 elements that has generated 10000 instances of orchestration with their distributed transactions.

<ns0:Transactions xmlns:ns0=http://MsDtcTest.MsDtcTestInputFile>

<Transaction rowNumber=0001 rowDescription=Row number 0001 />

<Transaction rowNumber=0002 rowDescription=Row number 0002 />

<Transaction rowNumber=0003 rowDescription=Row number 0003 />

<Transaction rowNumber=0004 rowDescription=Row number 0004 />

 

<Transaction rowNumber=9998 rowDescription=Row number 9998 />

<Transaction rowNumber=9999 rowDescription=Row number 9999 />

</ns0:Transactions>

 

To increase the number of active transaction I configured database connection pooling to 250 on the send port that invokes the stored procedure.

Also, I have reduced the latency time to 250 milliseconds for both message and orchestration polling Operations.

 

The host instance used by the flow was configured on both BizTalk nodes. Each BizTalk Server and SQL Server has 7 GB of RAM and 4 cores.

 

Test Results

With 1.000 simultaneous orchestration instances, I am able to generate an average of 28.2 distributed transactions per second and about 313 active distributed transactions.

 

Following some additional details.

 

Counter Average Minimum Maximum Duration (sec)
Active Transaction

313,1

136,0

458,0

29

Committed Transaction / sec.

28,2

0,0

38,0

29

 

With 10.000 simultaneous orchestration instances, I am able to generate an average of 26.7 distributed transactions per second and about 462 active distributed transactions.

 

Following some additional details.

 

Counter Average Minimum Maximum Duration
Active Transaction

462,1

208,0

500,0

5,56

Committed Transaction / sec.

26,7

0,0

75,0

5,56

 

10,000 active instances generate an intensive dehydration activity that cause average execution times quite high.

 

 

Conclusion

The configuration on the TCP layer that has limited the number of ports that RPC can activate on SQL server has not created any side effect. We generated up to 500 active transactions. This limit was established by the configuration of BizTalk server-side connection pooling.

The modest number of transactions is due to the infrastructure of the flow that does not allow a high scalability but that merely stress the throttling system and biztalk infrastructure in general.