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]

Advertisements

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.

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.

Configuring BizTalk Server infrastructure in High Availability on Microsoft Azure IaaS

 

The big difficulty of implementing a BizTalk infrastructure in high availability on Microsoft Azure, has always been about both SQL Server and Distributed Transaction Coordinator (MSDTC).

In this article, I will show the results of some tests with the use of a SAN less solution that enables the creation of SQL Server in a failover cluster configuration.

In this context, we can identify two scenarios of HA. The first involves a single cluster where are running both SQL Server and BizTalk Server services.

 

 

The second one consist of a cluster where are running SQL Server and SSO services and two additional servers for BizTalk server services.

 

 

The first scenario is undoubtedly easier to implement, especially because it requires very little configuration on the network layer. However, the second scenario is certainly the most interesting, for that reason will be what I will illustrate.

This document contains instructions for setting up the Windows Server 2012 R2 test lab by deploying five server computers running Windows Server 2012 R2 as Azure virtual machines in order to implement high availability for a Microsoft BizTalk infrastructure on the cloud.

The Windows Server 2012 R2 configuration in Azure test lab consists of the subnet in a cloud-only Azure Virtual Network named BizTalkLab that simulates a simplified intranet.

 

 

Because Microsoft Azure does not have a clustered storage option, I will use the third party solution called DataKeeper Cluster Edition for cluster storage.

To realize this post I have collected the official documentation and several other posts that I have reported in the following.

 

Step 1: Create the Azure Virtual Network

First, you create the BizTalkLab Azure Virtual Network that will host the BizTalkNet subnet of the base configuration. Then, you create an Azure cloud service. Lastly, you configure a storage account that will contain the disks for your virtual machines and extra data disks.

 

Create a virtual network

  1. In the task bar of the Azure Management Portal, click NEW > NETWORK SERVICES > VIRTUAL NETWORK > CUSTOM CREATE.
  2. On the Virtual Network Details page, type BtLabNet or the name of your choice in NAME.
  3. In LOCATION, select the appropriate region.
  4. Click the Next arrow.
  5. On the DNS Servers and VPN Connectivity page, in DNS servers, type BTLABDC1 in Select or enter name, type 10.0.0.4 in IP address, and then click the Next arrow.
  6. On the Virtual Network Address Spaces page, in Subnets, click Subnet-1 and replace the name with BtLabSubnet.
  7. In the STARTING IP column for the BtLabSubnet subnet, select 10.0.0.4.
  8. In the CIDR (ADDRESS COUNT) column for the BtLabSubnet subnet, click /24 (256).
  9. On the Virtual Network Address Spaces page, click add Subnet
  10. Specify BtLabSubnet2 as subnet name
  11. In the STARTING IP column for the BtLabSubnet2 subnet, select 10.0.1.0.
  12. In the CIDR (ADDRESS COUNT) column for the BtLabSubnet2 subnet, click /24 (256).
  13. Click the Complete icon. Wait until the virtual network is created before continuing.

 

 

The second subnet is not essential, but can be used to separate the network traffic related to the replication of the disks rather than internal communications of the cluster service.

 

Create a new cloud service for the BtLabNet virtual network

 

  1. In the task bar of the Azure Management Portal, click NEW > COMPUTE > CLOUD SERVICE > QUICK CREATE.
  2. In URL, type a unique name. For example, you could name it BtLabNet-UniqueSequence, in which UniqueSequence is an abbreviation of your organization.
  3. In REGION OR AFFINITY GROUP, select the same region as your BtLabNet virtual network.

 

 

  1. Click the CREATE CLOUD SERVICE complete icon. Wait until the cloud service is created before continuing.

 

Create a new storage account

  1. In the task bar of the Azure Management Portal, click NEW > DATA SERVICES > STORAGE > QUICK CREATE.
  2. In URL, type a unique name.
  3. In REGION OR AFFINITY GROUP, select the same region as your BtLabNet virtual network.

 

 

  1. Click the CREATE STORAGE ACCOUNT complete icon. Wait until the storage account is created before continuing.

 

Step 2: Configure Domain and Domain Controller

 

Following the procedure to create BTLABDC1 server.

BTLABDC1 provides the following services:

  • A domain controller for the btlab.pellitterisbiztalkblog.com Active Directory Domain Services (AD DS) domain.
  • A DNS server for the virtual machines of the BtLabNet virtual network.

 

BTLABDC1 configuration consists of the following:

  • Create an Azure Virtual Machine for BTLABDC1.
  • Add a data disk.
  • Configure BTLABDC1 as a domain controller and DNS server.
  • Create a user account in Active Directory.

 

Create an Azure Virtual Machine

 

  1. In the task bar of the Azure Management Portal, click NEW > COMPUTE > VIRTUAL MACHINE > FROM GALLERY.
  2. On the Choose an Image page, click Windows Server 2012 R2 Datacenter, and then click the Next arrow.
  3. On the Virtual machine configuration page:
  • In VIRTUAL MACHINE NAME, type BTLABDC1.
  • Select a size.
  • In NEW USER NAME, type the name of a local administrator account. You will use this account when initially connecting and as an alternate set of credentials when BTLABDC1 becomes a domain controller. Choose a name that is not easy to guess.
  • In NEW PASSWORD and CONFIRM, type a strong password for the local administrator account.
  • Record the local administrator account name and password in a secured location.
  1. Click the Next arrow.
  2. On the second Virtual machine configuration page:
  • In CLOUD SERVICE, select the cloud service that you created in “Step 1: Create the Azure Virtual Network.”
  • In REGION/AFFINITY GROUP/VIRTUAL NETWORK, select BtLabNet.
  • In STORAGE ACCOUNT, select the storage account that you created in “Step 1: Create the Azure Virtual Network.”
  • In AVAILABILITY SET specify Create an availability set and specify its name.
  • In ENDPOINTS, in the PowerShell row, delete the default port number in the PUBLIC PORT column.

 

 

  1. Click the Next arrow.
  2. On the third Virtual machine configuration page, click the Complete icon. Wait until Azure creates the virtual machine.
  3. On the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the BTLABDC1 virtual machine.
  4. On the command bar, click Attach, and then select Attach Empty Disk. The Attach Empty Disk dialog box appears. The Storage Location and File Name boxes contain automatically generated names that do not need to be altered.
  5. In the Size box, type 20.
  6. Leave the Host Cache Preference set to the default value of NONE.
  7. Click the Complete icon to attach the empty data disk. Wait until the status is Running before continuing (a few minutes).
  8. In the task bar, click CONNECT.
  9. When prompted to open BTLABDC1.rdp, click Open.
  10. When prompted with a Remote Desktop Connection message box, click Connect.
  11. When prompted for credentials, use the following:
  • Name: BTLABDC1\[Local administrator account name] (from step 3)
  • Password: [Local administrator account password] (from step 3)
  1. When prompted with a Remote Desktop Connection message box referring to certificates, click Yes.

 

Add a data disk

Add an extra data disk as a new volume with the drive letter F:.

  1. In the left pane of Server Manager, click File and Storage Services, and then click Disks.
  2. In the contents pane, in the DISKS group, click disk 2 (with the Partition set to Unknown).
  3. Click Tasks, and then click New Volume.
  4. On the Before you begin page of the New Volume Wizard, click Next.
  5. On the Select the server and disk, click Disk 2, and then click Next. When prompted, click OK.
  6. On the Specify the size of the volume page, click Next.
  7. On the Assign to a drive letter or folder page, click Next.
  8. On the Select file system settings page, click Next.
  9. On the Confirm selections page, click Create.
  10. When complete, click Close.

 

Configure BTLABDC1 as a domain controller and DNS server

To configure BTLABDC1 as a domain controller and DNS server for the corp.pellitterisbiztalkblog.com domain, run the following commands at a Windows PowerShell command prompt:

 

Install-WindowsFeature AD-Domain-Services -IncludeManagementTools

Install-ADDSForest -DomainName btlab.pellitterisbiztalkblog.com -DatabasePath “F:\NTDS” -SysvolPath “F:\SYSVOL” -LogPath “F:\Logs”

 

Note that you will be prompted to supply a Directory Services Restore Mode (DSRM) password and to restart BTLABDC1.

 

High Availability Considerations

This document shows an installation of a test environment. In a production environment, to ensure high availability of services you need to add a second domain controller.

 

Prepare User and Groups

 

Sample Script

 

New-ADOrganizationalUnit -Name BizTalk -Path “dc=btlab,dc=pellitterisbiztalkblog,dc=com”
 

New-ADUser -Name DataKeeperService -SamAccountName “DataKeeperService” -UserPrincipalName “DataKeeperService@btlab.pellitterisbiztalkblog.com” -AccountPassword (Read-Host -AsSecureString “AccountPassword”) -CannotChangePassword $true -Enabled $true -PasswordNeverExpires $true -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com”

New-ADUser -Name BizTalkService -SamAccountName “BizTalkService” -UserPrincipalName “BizTalkService@btlab.pellitterisbiztalkblog.com” -AccountPassword (Read-Host -AsSecureString “AccountPassword”) -CannotChangePassword $true -Enabled $true -PasswordNeverExpires $true -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com”

New-ADUser -Name SqlService -SamAccountName “SqlService” -UserPrincipalName “SqlService@btlab.pellitterisbiztalkblog.com” -AccountPassword (Read-Host -AsSecureString “AccountPassword”) -CannotChangePassword $true -Enabled $true -PasswordNeverExpires $true -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com”

 

New-ADGroup -Name “SSO Administrators” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

New-ADGroup -Name “SSO Affiliate Administrators” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

New-ADGroup -Name “BizTalk Administrators” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

New-ADGroup -Name “BizTalk Operators” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

New-ADGroup -Name “BizTalk Application Users” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

New-ADGroup -Name “BizTalk Isolated Application Users” -Path “ou=BizTalk,dc=btlab,dc=pellitterisbiztalkblog,dc=com” -groupScope Global

Add-ADGroupMember -Identity “SSO Administrators” -Members “BizTalkService”

Add-ADGroupMember -Identity “SSO Administrators” -Members “pellitteris”

 

Add-ADGroupMember -Identity “SSO Affiliate Administrators” -Members “BizTalkService”

Add-ADGroupMember -Identity “SSO Affiliate Administrators” -Members “pellitteris”

 

Add-ADGroupMember -Identity “BizTalk Administrators” -Members “BizTalkService”

Add-ADGroupMember -Identity “BizTalk Administrators” -Members “pellitteris”

 

Add-ADGroupMember -Identity “BizTalk Operators” -Members “BizTalkService”

Add-ADGroupMember -Identity “BizTalk Operators” -Members “pellitteris”

 

Add-ADGroupMember -Identity “BizTalk Application Users” -Members “BizTalkService”

Add-ADGroupMember -Identity “BizTalk Application Users” -Members “pellitteris”

 

Add-ADGroupMember -Identity “BizTalk Isolated Application Users” -Members “BizTalkService”

Add-ADGroupMember -Identity “BizTalk Isolated Application Users” -Members “pellitteris”

 

Step 3: Configure two SQL Server nodes

 

To create SQL Server nodes with two NICs you have to use PowerShell interface. To do that, download the latest version of the azure SDK available at the following link http://azure.microsoft.com/en-us/documentation/articles/install-configure-powershell/

 

Run the following commands to create the first SQL Server node:

 

Add-AzureAccount
Set-AzureSubscription –SubscriptionName “Visual Studio Ultimate with MSDN” -CurrentStorageAccount “btlabstorage” 

$image = Get-AzureVMImage -ImageName “a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-201412.01-en.us-127GB.vhd”

$vm = New-AzureVMConfig -Name “BTLABSQL1” -InstanceSize “Large” -Image $image.ImageName –AvailabilitySetName “btlabavail” 

Add-AzureProvisioningConfig –VM $vm -Windows -AdminUserName “[USERNAME]” -Password “[PASSWORD]”

Set-AzureSubnet -SubnetNames “BtLabSubnet” -VM $vm

Set-AzureStaticVNetIP -IPAddress “10.0.0.5” -VM $vm

Add-AzureNetworkInterfaceConfig -Name “NIC2” -SubnetName “BtLabSubnet2” -StaticVNetIPAddress “10.0.1.5” -VM $vm 

New-AzureVM -ServiceName “BtLabNet-pellitteris” –VNetName “BtLabNet” –VM $vm

 

Then follow the step below:

 

  1. On the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the BTLABSQL1 virtual machine.
  2. On the command bar, click Attach, and then select Attach Empty Disk. The Attach Empty Disk dialog box appears. The Storage Location and File Name boxes contain automatically generated names that do not need to be altered.
  3. In the Size box, type 10.
  4. Leave the Host Cache Preference set to the default value of NONE.
  5. Click the Complete icon to attach the empty data disk. Wait until the status is Running before continuing (a few minutes).
  6. In the task bar, click CONNECT.
  7. When prompted to open BTLABDC1.rdp, click Open.
  8. When prompted with a Remote Desktop Connection message box, click Connect.
  9. When prompted for credentials, use the following:
  • Name: BTLABSQL1\[Local administrator account name]
  • Password: [Local administrator account password]
  1. When prompted with a Remote Desktop Connection message box referring to certificates, click Yes.

 

Add a data disk

 

Add an extra data disk as a new volume with the drive letter F:.

  1. In the left pane of Server Manager, click File and Storage Services, and then click Disks.
  2. In the contents pane, in the DISKS group, click disk 2 (with the Partition set to Unknown).
  3. Click Tasks, and then click New Volume.
  4. On the Before you begin page of the New Volume Wizard, click Next.
  5. On the Select the server and disk, click Disk 2, and then click Next. When prompted, click OK.
  6. On the Specify the size of the volume page, click Next.
  7. On the Assign to a drive letter or folder page, click Next.
  8. On the Select file system settings page, click Next.
  9. On the Confirm selections page, click Create.
  10. When complete, click Close.

 

Configuring Network

Assign static IP to the network interfaces and disable Firewall or configure it to open SQL Server TCP port, SSO TCP port and RPC (for both SSO and MSDTC services).

 

Join SQL Server node to Active Directory Domain

 

  1. In the left pane of Server Manager, click Local Server.
  2. In the contents pane, in the PROPERTIES group, click WORKGROUP and then Change
  3. Select Domain and specify btlab.pellitterisbiztalkblog.com
  4. Specify the administrator credentials and confirm.
  5. Click OK on Welcome to domain dialog box and then restart the machine.

 

Configuring second node

 

Repeat this step for the second SQL Server node.

 

Step 4: Create Cluster

 

To install the Failover Clustering feature

 

  1. Start Server Manager.
  2. On the Manage menu, click Add Roles and Features.
  3. On the Before you begin page, click Next.
  4. On the Select installation type page, click Role-based or feature-based installation, and then click Next.
  5. On the Select destination server page, click the server where you want to install the feature, and then click Next.
  6. On the Select server roles page, click Next.
  7. On the Select features page, select the Failover Clustering check box.
  8. To install the failover cluster management tools, click Add Features, and then click Next.
  9. On the Confirm installation selections page, click Install.
  10. When the installation is completed, click Close.
  11. Repeat this procedure on every server that you want to add as a failover cluster node.

 

To run cluster validation tests

 

  1. On a computer that has the Failover Cluster Management Tools installed from the Remote Server Administration Tools, or on a server where you installed the Failover Clustering feature, start Failover Cluster Manager. To do this on a server, start Server Manager, and then on the Tools menu, click Failover Cluster Manager.
  2. In the Failover Cluster Manager pane, under Management, click Validate Configuration.
  3. On the Before You Begin page, click Next.
  4. On the Select Servers or a Cluster page, in the Enter name box, enter the NetBIOS name or the fully qualified domain name of a server that you plan to add as a failover cluster node, and then click Add. Repeat this step for each server that you want to add. To add multiple servers at the same time, separate the names by a comma or by a semicolon. For example, enter the names in the format server1.contoso.com, server2.contoso.com. When you are finished, click Next.
  5. On the Testing Options page, click Run all tests (recommended), and then click Next.
  6. On the Confirmation page, click Next.

 

The Validating page displays the status of the running tests.

 

  1. The test should succeed with some warnings. Deselect Create the cluster now using the validated nodes and then click Finish.

 

To create the failover cluster

 

  1. Start Server Manager.
  2. On the Tools menu, click Failover Cluster Manager.
  3. In the Failover Cluster Manager pane, under Management, click Create Cluster.

 

The Create Cluster Wizard opens.

 

  1. On the Before You Begin page, click Next.
  2. In the Select Servers page appears, in the Enter name box, enter the NetBIOS name or the fully qualified domain name of the first on SQL Server nodes, and then click Add. When you are finished, click Next.

 

Note: We will start by creating a single node cluster, this allow you to make the necessary adjustment to the cluster name resource before we add the second node to the cluster.

 

  1. Select No, to skip the validation test and then click Next.
  2. On the Access Point for Administering the Cluster page specify the cluster NetBIOS name and then click Next.

 

 

 

  1. On the Confirmation page, review the settings and click Next to create the failover cluster.
  2. On the Summary page, confirm that the failover cluster was successfully created and click Finish.

 

Once the cluster wizard creation process completes, you may notice that the cluster name resource fails to go online, this is normal.

If it happens, follow steps 10, 11 and 12, otherwise ignore them.

 

 

  1. Right click on IP Address resource and select Properties.
  2. On IP Address properties page select Static IP Address and specify the higher and of the subnet range.

 

 

  1. Confirm the change and try to bring on line the resource.

 

 

To add second SQL Server node

 

  1. On a computer that has the Failover Cluster Management Tools installed from the Remote Server Administration Tools, or on a server where you installed the Failover Clustering feature, start Failover Cluster Manager. To do this on a server, start Server Manager, and then on the Tools menu, click Failover Cluster Manager.
  2. In the Failover Cluster Manager click Add Node.

 

 

  1. On the Before You Begin page, click Next.
  2. In the Select Servers page appears, in the Enter name box, enter the NetBIOS name or the fully qualified domain name of the second SQL Server nodeBTLABSQL2, and then click Add. When you are finished, click Next.
  3. On the Confirmation page, review the settings and click Next.
  4. On the Summary page click Finish.

 

Create a network file share for witness configuration

 

Connect to BTLABDC1 virtual machine.

  1. On the virtual machines page of the Azure Management Portal, click Running in the STATUS column for the BTLABDC1 virtual machine.
  2. In the task bar, click CONNECT.
  3. When prompted to open BTLABDC1.rdp, click Open.
  4. When prompted with a Remote Desktop Connection message box, click Connect.
  5. When prompted for credentials, use the following:
  • Name: BTLAB\[Local administrator account name] (from step 3 of the “Create an Azure Virtual Machine for BTLABDC1” procedure)
  • Password: [Local administrator account password] (from step 3 of the “Create an Azure Virtual Machine for BTLABDC1” procedure)
  1. When prompted by a Remote Desktop Connection message box referring to certificates, click Yes.
  2. In the left pane of Server Manager, click File and Storage Services, and then click Shares.
  3. In the contents pane, in the SHARES group click Tasks, and then click New Share.
  4. In the New Share Wizard leave the proposed profile and click Next.
  5. Select Type a custom path and browse for create a folder to share then click Next.

 

 

  1. Specify a share name and click Next.
  2. Deselect all settings and Click Next.
  3. On permission page select Customize permissions.
  4. Select the principal BTLAB\BTLABCLUSTER$ and provide Modify Permission

 

 

Confirm the permission and complete the wizard.

 

Configure cluster quorum

 

On a computer that has the Failover Cluster Management Tools installed from the Remote Server Administration Tools, or on a server where you installed the Failover Clustering feature, start Failover Cluster Manager. To do this on a server, start Server Manager, and then on the Tools menu, click Failover Cluster Manager.

 

  1. In the Failover Cluster Manager right click the cluster node, select More Actions and then Configure Cluster Quorum Settings.

 

 

  1. On the Before You Begin page, click Next.
  2. Select “Select the quorum witness” and click Next.
  3. Select Configure a file share witness and click Next
  4. Specify the share path “\\BTLABDC1\SqlWitness” and click Next
  5. On the Confirmation page review all settings and click Next
  6. On the Summary page click Finish

 

Step 5: Create Replicated Volume Cluster Resource with DataKeeper Cluster Edition

 

Before starting SIOS setup, you must install the .NET Framework 3.5.

 

To install the .NET Framework 3.5 feature

 

  1. Start Server Manager.
  2. On the Manage menu, click Add Roles and Features.
  3. On the Before you begin page, click Next.
  4. On the Select installation type page, click Role-based or feature-based installation, and then click Next.
  5. On the Select destination server page, click the server where you want to install the feature, and then click Next.
  6. On the Select server roles page, click Next.
  7. On the Select features page, select the .NET Framework 3.5 Feature check box and then click Next.
  8. On the Confirm installation selections page, click Install.
  9. When the installation is completed, click Close.
  10. Repeat this procedure on every server that you want to add as a failover cluster node.

 

Other Prerequisites

 

  1. Create a domain account for DataKeeper service account
  2. Make this account member of local Administrators account of both SQL Server nodes.

 

To install DataKeeper cluster edition

In this lab I used a 14 day trial license that is generally available for testing upon request.

 

 

On the welcome page Click Next

 

 

On the License Agreement click Yes

 

 

Leave the selection and click Next

 

 

Choose the destination and click Next

 

 

Confirm the changes

 

 

Select Domain or Server account and click Next

 

 

Specify the domain service user and click Next

 

 

 

Click on Install License File to choose the License provided with demo setup and click Exit

 

 

Click Finish to confirm the restart.

Repeat the procedure on the second node.

 

After the reboot, connect to the first SQL Server node, run the DataKeeper UI and complete the steps below.

 

 

Click on Connect to Server and specify the first node

 

 

Make same thing with the second node

 

 

Then, create a job specifying name and description.

 

 

If you have prepared a server with two interfaces you can specify the second subnet to implement the disk replica.

 

 

Choose the network interface for the second node

 

 

Then specify Synchronous and click Done

 

 

Confirm to create disk cluster resource

 

 

Then exit. From the Failover Cluster Manager you can see the disk resource.

 

 

Step 6: Install SQL Server 2014

 

Following the screenshot that I used to install SQL Server 2014 cluster nodes.

 

 

 

 

 

 

 

 

 

 

 

 

Repeat the previous steps on the second node. Then follow the step below.

 

 

 

 

 

 

 

 

 

 

 

 

 

To cluster Distributed Transaction Coordinator (MSDTC)

 

From Failover Cluster Manager select SQL Server Role and click Add Resource on the right panel.

 

 

 

 

You can now bring on line DTC cluster resource.

From Administrative Tools, open the Component Services to configure MSDTC cluster resource security settings.

 

 

Step 7: Configure client access

 

Create an Internal Load Balancer

Once the cluster is configured, you will need to create the internal load balancer (ILB) which will be used for all client access. Clients that connect to SQL Server, Enterprise SSO and MSDTC will need to connect to the ILB instead of connecting directly to the cluster IP address.

The following commands can take even a few hours.

 

$ip=”10.0.0.250″ # IP address you want your Internal Load Balancer to use, this should be the same address as your SQL Server Cluster IP Address

$svc=”BtLabNet-pellitteris” # The name of your cloud service

 

$vmname1=”BTLABSQL1″ #The name of the VM that is your first cluster node

$vmname2=”BTLABSQL2″ #The name of the VM that is your second cluster node

 

$prot=”tcp”

$probeport=59999

 

$ilbname=”BTLABSQLCLU” #this is the name your clients connect to, it should coincide with you SQL cluster Name Resource

$subnetname=”BtLabSubnet” #the name of the Azure subnet where you want the internal load balancer to live

 

Add-AzureAccount

 

Set-AzureSubscription –SubscriptionName “Visual Studio Ultimate with MSDN” -CurrentStorageAccount “btlabstorage”

 

# Add Internal Load Balancer to the service

Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ilbname -SubnetName $subnetname -ServiceName $svc –StaticVNetIPAddress $IP

 

# Add load balanced endpoint

# SQL Server Port

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name “BTLABSQL1-SQL1433” -LBSetName “BtLabSql1433Ilb” -Protocol $prot -LocalPort 1433 -PublicPort 1433 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name “BTLABSQL1-SQL1433” -LBSetName “BtLabSql1433Ilb” -Protocol $prot -LocalPort 1433 -PublicPort 1433 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

 

# RPC

 

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name “BTLABSQL1-RPC135” -LBSetName “BtLabRpc135Ilb” -Protocol $prot -LocalPort $locport -PublicPort $pubport -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name “BTLABSQL2-RPC135” -LBSetName “BtLabRpc135Ilb” -Protocol $prot -LocalPort $locport -PublicPort $pubport -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

 

# RPC Dynamic Port

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name “BTLABSQL1-RPC5000” -LBSetName “BtLabRpc5000Ilb” -Protocol $prot -LocalPort 5000 -PublicPort 5000 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name “BTLABSQL2-RPC5000” -LBSetName “BtLabRpc5000Ilb” -Protocol $prot -LocalPort 5000 -PublicPort 5000 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name “BTLABSQL1-RPC5001” -LBSetName “BtLabRpc5001Ilb” -Protocol $prot -LocalPort 5001 -PublicPort 5001 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name “BTLABSQL2-RPC5001” -LBSetName “BtLabRpc5001Ilb” -Protocol $prot -LocalPort 5001 -PublicPort 5001 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

 

[DO THE SAME UP TO TCP PORT 5065]

 

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name “BTLABSQL1-RPC5065” -LBSetName “BtLabRpc5065Ilb” -Protocol $prot -LocalPort 5065 -PublicPort 5065 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name “BTLABSQL2-RPC5065” -LBSetName “BtLabRpc5065Ilb” -Protocol $prot -LocalPort 5065 -PublicPort 5065 -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

 

Update the Client Listener

 

Once the internal load balancer is created you will need to run a Powershell script on first SQL node to update the SQL Server Cluster IP address. The script references the Cluster Network name and the IP Resource Name. The pictures below show you were to find both of these names in Failover Cluster Manager.

 


 


 

The script below should be run on one of the cluster nodes. Make sure to launch Powershell ISE using Run as Administrator.

 


 

# This script should be run on the primary cluster node after the internal load balancer is created
# Define variables

 

$ClusterNetworkName = “Cluster Network 1” # the cluster network name

$IPResourceName = “SQL IP Address 1 (BTLABSQLCLU)” # the IP Address resource name

$CloudServiceIP = “10.0.0.250” # IP address of your Internal Load Balancer

 

Import-Module FailoverClusters

 

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{“Address”=”$CloudServiceIP”;”ProbePort”=”59999″;SubnetMask=”255.255.255.255″;”Network”=”$ClusterNetworkName”;”OverrideAddressMatch”=1;”EnableDhcp”=0}

 

You will need to bring your cluster resource offline once and then bring it online.

 

Configure MSDTC to Use a Specific Port

 

  1. Start Component Services MMC, right click My Computer and select Properties
  2. Select the Default Protocols tab
  3. Click Properties button
  4. Click Add
  5. Type in the port range that is above the port MSDTC will use. In this case, I will use ports 5000-5065.

 

 

  1. Click OK back to My Computer properties window and click OK

 

Note: The number of specified ports for dynamic RPC is not casual but is due to the limitation of maximum number of configurable endpoints of ILB that at this moment is 150. So you can have, 65 dynamic RPC endpoints for the first node, 65 endpoint for the second node and two additional tcp ports endpoint for SQL Server (1433) and RPC (135) for both servers.

 

  1. Start Regedt32.exe
  2. Locate HKEY_LOCAL_MACHINE\Cluster\Resources\{Unique_DTC_ResourceID_GUID}\MSDTCPRIVATE\MSDTC
  3. Right click the MSDTC key, select New and DWord (32-bit) Value
  4. Type ServerTcpPort for the key name
  5. Right click ServerTcpPort key and select Modify
  6. Change radio button to Decimal and type 5021 in the value data, click OK.

 

Note: Leave free the TCP ports between 5000 and 5020 for SSO (for additional information see the article https://msdn.microsoft.com/en-us/library/aa559472.aspx).

 

Restart the MSDTC taking the MSDTC Resource offline/online in Failover Cluster Manager.

 

To confirm MSDTC is using the correct port:

  1. Open an Administrative command prompt and run Netstat –ano to get the port and the Process Identifier (PID)
  2. Start Task Manager and select Details tab
  3. Find MSDTC.exe and get the PID
  4. Review the output for the PID to show it is MSDTC

 

Apply the same configuration to the second node.

 

Step 8: Cluster the Master Secret Server

 

From this time, the installation is pretty standard so I will not go into details because I think it was enough documented.

To cluster enterprise SSO follow the article at this link https://msdn.microsoft.com/en-us/library/aa561823.aspx.

After the installation, you need to set “Use Network Name for computer name” setting on cluster resource property and make the resource dependent from SQL Server cluster resource.

Following some relevant step of my configuration.

 

 

 

 

 

I have completed the configuration of the second node after a failover, so to work always on the active node.

 

 

 

 

Step 9: Install BizTalk nodes

 

  1. In the task bar of the Azure Management Portal, click NEW > COMPUTE > VIRTUAL MACHINE > FROM GALLERY.
  2. On the Choose an Image page, click BizTalk Server 2013 R2 Eenterprise, and then click the Next arrow.
  3. On the Virtual machine configuration page:
  • In VIRTUAL MACHINE NAME, type BTLABBT1.
  • Select a size.
  • In NEW USER NAME, type the name of a local administrator account.
  • In NEW PASSWORD and CONFIRM, type a strong password for the local administrator account.
  • Record the local administrator account name and password in a secured location.
  1. Click the Next arrow.
  2. On the second Virtual machine configuration page:
  • In CLOUD SERVICE, select the cloud service that you created in “Step 1: Create the Azure Virtual Network.”
  • In REGION/AFFINITY GROUP/VIRTUAL NETWORK, select BtLabNet.
  • In STORAGE ACCOUNT, select the storage account that you created in “Step 1: Create the Azure Virtual Network.”
  • In AVAILABILITY SET specify Create an availability set and specify its name.
  • In ENDPOINTS, in the PowerShell row, delete the default port number in the PUBLIC PORT column.

 

 

 

Join BizTalk Server to Active Directory Domain

 

  1. In the left pane of Server Manager, click Local Server.
  2. In the contents pane, in the PROPERTIES group, click WORKGROUP and then Change
  3. Select Domain and specify btlab.pellitterisbiztalkblog.com
  4. Specify the administrator credentials and confirm.
  5. Click OK on Welcome to domain dialog box and then restart the machine.

 

Installing and configuring BizTalk Server

 

Following some relevant picture about my BizTalk configuration.

 

 

 

 

 

 

 

Configuring second node

 

Repeat this step for the second BizTalk server.

Following some relevant step of the configuration I applied.

 

 

 

 

 

 

That’s all folks.

 

 

Considerations

 

The experience that I did opens discussion points. I have done some tests trying to stress much as possible the communication with SQL Server and the MSDTC. During my tests I hadn’t particular problems, however, the limited number of available dynamic RPC ports, lead me to think that a limit, this infrastructure has it.

The scenario 1, exposed at the beginning of this article, is definitely easier and does not have this kind of problem, however, limits the performance as it concentrates all services in a single host with no ability to scale.

 

New version of BizTalk Deployment Utility (BizTalk Diff) is now available

Some weeks ago, I published the first version of a tool that can help you during the deployment activities.

You can find this tool at https://github.com/pellitteris/BizTalkDiff address.

These days I have made available an updated version with the following features:

 

·          BizTalk environments comparison;

·          You can copy specific objects between environments such as send ports, send port groups, receive ports and receive locations;

·          It is possible to view all the ports in which are configured explicit credentials.

 

When you starts the application, the welcome form appears.

 

clip_image002

 

Once closed, you access the main form.

 

clip_image004

 

You have to specify the connection string to the BizTalk databases and start information collection.

 

clip_image005

 

Now you are able to navigate all the BizTalk objects.

 

clip_image007

 

From the left panel you can select a specific object and choose one of available features.

 

clip_image008

 

Compare

 

When you choose to compare the environments, the application highlights the difference between the BizTalk systems in terms of existence of objects.

It is also possible to compare specified attributes of objects that exist on both systems.

 

clip_image010

 

Once the comparison is complete, the tool highlights the differences.

 

clip_image012

 

Attribute Details

You can also view the main attributes of several kind of objects.

clip_image014

 

You can view the attribute value more in detail by double click the grid cell. This is much useful if you have values containing xml data

 

clip_image016

 

It is possible to view the list of send ports and receive locations in which are specified explicit credentials

 

clip_image018

 

 

How-To align specific ports between BizTalk environments

Almost always when you develop an application based on Microsoft BizTalk you configure several ports.

 

When will be necessary to deploy the application in the production system may be necessary to create that ports. Sometimes, export and import bindings is not always possible and recreate the ports manually can be costly and error prone.

 

So, I created a simple tool that allows you to align two systems based on BizTalk Server by copying specific ports from a source system to a target system.

 

clip_image002

 

With this application you can check the existence of applications, receive ports, receive locations, send ports and send port groups, and decide to create specific objects on the target system.

 

This tool does not update the ports on the target system but merely creates them if they do not exist.

 

It is possible to download a sample version at https://github.com/pellitteris/BizTalkDiff.

 

Save and restore BizTalk BAM activities

The BAM is a very useful tool that my clients appreciate more and more. The most interesting aspect is that it is a framework on which you can build reports and dashboards using Analysis Services, the OLTP database or web services.

When you implements a sets of very complex activities and views, may become difficult to update the structure of activities adding or removing columns. Update the schema of the activities often requires its redefinition that causes the loss of data already present.

With my team we have created a set of stored procedures based on SQL Server in order to save and restore the data.

Below I have reported some examples.

 

Database Creation:

 

CREATEDATABASE[BAMPrimaryImportUtilities]

GO

 

 

Procedure that saves the data:

 

USE[BAMPrimaryImportUtilities]

GO

 

CREATEPROCEDURE[dbo].[SaveActivity]

       @activityNameNVARCHAR(128)

AS

BEGIN

 

       SETNOCOUNTON;

 

       DECLARE@resultTABLE (actionPartVARCHAR(200),valuePartVARCHAR(200));

       DECLARE@sqlStatementNVARCHAR(2000);

 

       IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[bam_’+@activityName+‘_Completed]’)ANDtypein(N’U’))

       BEGIN

             SET@sqlStatement=‘DROP TABLE [dbo].[bam_’+@activityName+‘_Completed]’

 

             EXECUTEsp_executesql@sqlStatement;

       END

 

       IFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[bam_’+@activityName+‘_CompletedRelationships]’)ANDtypein(N’U’))

       BEGIN

             SET@sqlStatement=‘DROP TABLE [dbo].[bam_’+@activityName+‘_CompletedRelationships]’

 

             EXECUTEsp_executesql@sqlStatement;

       END

 

       SET@sqlStatement=

                    SELECT

                           *

                    INTO

                           dbo.[bam_’+@activityName+‘_Completed]

                    FROM

                           BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_Completed: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SET@sqlStatement=

                    SELECT

                           *

                    INTO

                           dbo.[bam_’+@activityName+‘_CompletedRelationships]

                    FROM

                           BAMPrimaryImport.dbo.[bam_’+@activityName+‘_CompletedRelationships]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_CompletedRelationships: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SELECT*FROM@result;

 

END

 

 

 

Procedure that restores the data

CREATEPROCEDURE[dbo].[RestoreActivity]

       @activityNameNVARCHAR(128)

AS

BEGIN

 

       SETNOCOUNTON;

 

       DECLARE@resultTABLE (actionPartVARCHAR(200),valuePartVARCHAR(200));

       DECLARE@columnListVARCHAR(4000)=;

       DECLARE@columnVARCHAR(256);

       DECLARE@sqlStatementNVARCHAR(4000);

      

       — Column List

       DECLAREcurColumnListCURSORFOR

             SELECT

                    PrimaryImportInfo.COLUMN_NAME

                   

             FROM

                    BAMPrimaryImport.INFORMATION_SCHEMA.COLUMNSPrimaryImportInfo

                   

                    INNERJOINBAMPrimaryImportUtilities.INFORMATION_SCHEMA.COLUMNSSavedInfo

                    ONPrimaryImportInfo.TABLE_NAME=SavedInfo.TABLE_NAME

                    ANDPrimaryImportInfo.COLUMN_NAME=SavedInfo.COLUMN_NAME

             WHERE

                    PrimaryImportInfo.TABLE_NAME=‘bam_’+@activityName+‘_Completed’

 

             ORDERBY

                    PrimaryImportInfo.ORDINAL_POSITION;

                   

       OPENcurColumnList;

       FETCHNEXTFROMcurColumnListINTO@column;

      

       WHILE@@FETCH_STATUS= 0

       BEGIN

             SET@columnList=@columnList+‘[‘+@column+‘],’+CHAR(13)

            

             FETCHNEXTFROMcurColumnListINTO@column;

       END

      

       CLOSEcurColumnList;

       DEALLOCATEcurColumnList;

      

       SET@columnList=LEFT(@columnList,LEN(@columnList)2)+CHAR(13);

      

       — Insert Statement

       SET@sqlStatement=‘SET IDENTITY_INSERT BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed] ON;

                                       

                                        INSERT INTO BAMPrimaryImport.dbo.[bam_’+@activityName+‘_Completed]

                                        (‘+@columnList+‘)

                                        SELECT

                                        +@columnList+

                                        FROM

                                               BAMPrimaryImportUtilities.dbo.[bam_’+@activityName+‘_Completed];’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_Completed: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SET@sqlStatement=‘INSERT INTO BAMPrimaryImport.dbo.[bam_’+@activityName+‘_CompletedRelationships]

                                        SELECT

                                               *

                                        FROM

                                               BAMPrimaryImportUtilities.dbo.[bam_’+@activityName+‘_CompletedRelationships]’;

                                              

       EXECUTEsp_executesql@sqlStatement;

       INSERTINTO@result(actionPart,valuePart)VALUES (‘bam_’+@activityName+‘_CompletedRelationships: ‘,CONVERT(VARCHAR,@@ROWCOUNT));

 

       SELECT*FROM@result;

 

END

 

 

 

 

The previous procedures represent a starting point. You can enrich them, working on views, for example, you can save all the activities of a view by accessing the table “BAMPrimaryImport.dbo.bam_Metadata_ActivityViews”.

In addition, you can automate the deployment by implementing a console application that calls the stored procedure.

Something like that:

 

clip_image002[4]