BIZTALK: How to Cluster the Master Secret Server

The last few weeks I have been setting up a new Biztalk environment with the Biztalk databases on a MS SQL 2008 R2 SP1 failover cluster. In my last post i showed how to cluster the MSMQ and MSDTC and here is my way to cluster the Master Secret Server. When you cluster the master secret server, the Single Sign-On servers communicate with the active clustered instance of the master secret server. Similarly, the active clustered instance of the master secret server communicates with the SSO database.

To install and configure Enterprise SSO on the cluster nodes (Windows Server 2008)

1. Install BizTalk Server 2010 on each cluster node. In the Component Installation dialog box of the Microsoft BizTalk Server Installation Wizard, select to install the Enterprise Single Sign-On Administration Module and Enterprise Single Sign-On Master Secret Server components. After installation has completed successfully you have the option to run the BizTalk Server 2010 Configuration program but do not do so at this time.
2. Create domain groups with the names SSO Administrators and SSO Affiliate Administrators. To create a clustered instance of the Enterprise SSO service, you must create the SSO Administrators and SSO Affiliate Administrators groups as domain groups.
3. Create or designate a domain account that is a member of the SSO Administrators domain group. The Enterprise SSO service on each node will be configured to log on as this domain account. This account must have the Log on as a service right on each node in the cluster.
4. Add the account that you are using to log on during the configuration process to the domain SSO Administrators group.
5. Start the BizTalk Server 2010 Configuration program. Click Start, point to Programs, point to Microsoft BizTalk Server 2010, and then click BizTalk Server Configuration to display the Microsoft BizTalk Server 2010 Configuration dialog box.
6. Choose the Custom Configuration option and enter the appropriate values for the Database server name, User name and Password fields. After entering these values click the Configure button to continue.
7. Select the Enterprise SSOoption from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and set the following options for the Enterprise SSO feature:
   1. Select the check the box next to Enable Enterprise Single Sign-On on this computer.
   2. Click the option to Create a new SSO system.
   3. Enter the appropriate values under Data stores for Server Name and Database Name.
   4. Verify that the domain account that you created earlier is the account that is associated with the Enterprise SSO service.
   5. Specify the domain SSO Administrators group that you created earlier as the group associated with the SSO Administrator(s) role.
   6. Specify the domain SSO Affiliate Administrators group that you created earlier as the group associated with the SSO Affiliate Administrator(s) role.
8. Select the Enterprise SSO Secret Backup option from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and provide the appropriate parameters for backing up the Enterprise SSO secret. By default the Enterprise SSO secret is backed up to <drive>:\Program Files\Common Files\Enterprise Single Sign-On\SSOxxxx.bak.
9. Click the Apply Configuration option to display the Microsoft BizTalk Server 2010 Configuration Wizard Summary dialog box.
10. Click Next to apply the configuration.
11. Click Finish to close the Microsoft BizTalk Server 2010 Configuration Wizard.
12. Close the Microsoft BizTalk Server 2010 Configuration program.
13. Log on to the passive cluster node and start the BizTalk Server 2010 Configuration program.
14. Choose the Custom Configuration option and enter the same values for the Database server name, User name, and Password fields that you entered when configuring the first cluster node. After entering these values click the Configure button to continue.
15. Select the Enterprise SSOoption from the left pane of the Microsoft BizTalk Server 2010 Configuration dialog box and set the following options for the Enterprise SSO feature:
    1. Check the box next to Enable Enterprise Single Sign-On on this computer.
    2. Click the option to Join an existing SSO system.
    3. Enter the same values for the SSO Database Server Name and Database Name that you entered when configuring the first cluster node.
    4. Enter the same value for the domain account that you entered when configuring the first cluster node.
16. Click the Apply Configuration option to display the Microsoft BizTalk Server 2010 Configuration Wizard Summary dialog box.
17. Click Next to apply the configuration.
18. Click Finish to close the Microsoft BizTalk Server 2010 Configuration Wizard.
19. Close the Microsoft BizTalk Server 2010 Configuration program.

To update the master secret server name in the SSO database

1. Type the following commands from a command prompt on the active cluster node to stop and restart the Enterprise SSO service:

1. net stop entsso

and

net start entsso

3. Change the master secret server name in the SSO database to the cluster name by following these steps:

Note

The cluster name is the name defined for the network name resource that you have created in the cluster group / clustered service or application that will contain the clustered Enterprise SSO service. For example, the name may be BIZTALKCLUSTER.

1. Paste the following code in a text editor:

1. <sso>
2.   <globalInfo>
3.     <secretServer>BIZTALKCLUSTER</secretServer>
4.   </globalInfo>
5. </sso>

Note

BIZTALKCLUSTER is a placeholder for the actual network name resource that is created in the cluster group / clustered service or application.

7. Save the file as an .xml file. For example, save the file as SSOCLUSTER.xml.
8. At a command prompt, change to the Enterprise SSO installation folder. By default, the installation folder is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
9. Type the following command at the command prompt to update the master secret server name in the database:

10.ssomanage -updatedb XMLFile

Note

XMLFile is a placeholder for the name of the .xml file that you saved earlier.

To create the clustered Enterprise SSO resource (Windows Server 2008)

1. If the cluster is not configured with a clustered Distributed Transaction Coordinator (MSDTC) resource then follow the steps in my last post.
2. Click Start, Programs, Administrative Tools, and then Failover Cluster Management to start the Failover Cluster Management program.
3. In the left hand pane, right-click Failover Cluster Management and click Manage a Cluster.
4. On the Select a cluster to manage dialog box, enter the cluster to be managed and click OK.
5. In the left hand pane click to select a clustered service or application that contains an IP Address and Network Name resource.

Note

A clustered Enterprise SSO service does not explicitly require the use of a clustered Physical Disk resource in the same group.

6. Right-click the clustered service or application, point to Add a resource, and click Generic Service to display the New Resource Wizard dialog.

Important

In the Generic Service Parameters dialog box, if you do not click to select the Use Network Name for computer name check box, SSO client computers will generate an error similar to the following when they try to contact this clustered instance of the Enterprise SSO service: Failed to retrieve master secrets. Verify that the master secret server name is correct and that it is available. Secret Server Name: ENTSSO Error Code: 0x800706D9, there are no more endpoints available from the endpoint mapper.

7. On the Select Service page of the New Resource Wizard, click to select Enterprise Single Sign-On Service and click Next.
8. On the Confirmation page click Next.
9. On the Summary page click Finish. A clustered instance of the Enterprise Single Sign-On Service will appear under Other Resources in the center pane of the Failover Cluster Management interface.
10. Right-click the clustered instance of the Enterprise Single Sign-On Service and select Properties to display the Enterprise Single Sign-On Service Properties dialog box.
11. Click the Dependencies tab of the properties dialog box and click Insert.
12. Click the drop down box under Resource, select the Name: resource and click OK.

To restore the master secret on the second cluster node (Windows Server 2008)

1. In Failover Cluster Management, right click the clustered service or application that contains the clustered Enterprise Single Sign-On service and then click Bring this service or application online to start all of the resources in the clustered service or application.
2. Right-click the clustered service or application, point to Move this service or application to another node, and click the second node. This step moves the clustered service or application that contains the clustered Enterprise Single Sign-On service from the first node to the second node.
3. Right-click the clustered Enterprise Single Sign-On service and click Take this service or application offline, then right-click the clustered instance of the Enterprise SSO service and click Bring this service or application online.

Note

If this step is not completed the attempt to restore the master secret may not succeed.

4. Copy the master secret backup file from the first node to the \Enterprise Single Sign-On installation folder on the second node. By default, the installation folder is <drive>:\Program Files\Common Files\Enterprise Single Sign-On.
5. Log on to the second node and at a command prompt, change to the Enterprise SSO installation folder.
6. Type the following command from the command prompt to restore the master secret to the second node:

1. ssoconfig -restoresecret RestoreFile

Note

Replace RestoreFile with the path of and the name of the backup file that contains the master secret.

8. The master secret is stored in the registry at the following location:
9. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ENTSSO\SSOSS
10. Move the clustered service or application that contains the clustered Enterprise Single Sign-On service from this cluster node to other cluster node to ensure failover functionality. Then move the cluster group back to verify fail-back functionality.

 

BIZTALK TIPS: How to Cluster Message Queuing / How to Cluster MSDTC

Cluster support is provided for the BizTalk Server MSMQ adapter by running the MSMQ adapter handlers in a clustered instance of a BizTalk Host. If the BizTalk Server MSMQ adapter handlers are run in a clustered instance of a BizTalk Host, a clustered Message Queuing (MSMQ) resource should also be configured to run in the same cluster group as the clustered BizTalk Host when using the Send adapter or the Receive adapter for BizTalk Server 2006 R2 and earlier. This should be done for the following reasons:

• MSMQ adapter receive handler – The MSMQ adapter receive handler for BizTalk Server 2006 R2 and earlier does not support remote transactional reads; only local transactional reads are supported. The MSMQ adapter receive handler on BizTalk Server 2006 R2 and earlier must run in a host instance that is local to the clustered MSMQ service in order to complete local transactional reads with the MSMQ adapter.
• MSMQ adapter send handler – To ensure the consistency of transactional sends made by the MSMQ adapter, the outgoing queue used by the MSMQ adapter send handler should be highly available, so that if the MSMQ service for the outgoing queue fails, it can be resumed. Configuring a clustered MSMQ resource and the MSMQ adapter send handlers in the same cluster group will ensure that the outgoing queue used by the MSMQ adapter send handler will be highly available. This will mitigate the possibility of message loss in the event that the MSMQ service fails.

Many BizTalk Server operations are performed within the scope of a Microsoft Distributed Transaction Coordinator (MSDTC) transaction.

A clustered MSDTC resource must be available on the Windows Server cluster to provide transaction services for any clustered BizTalk Server components or dependencies. BizTalk Server components or dependencies that can be configured as Windows Server cluster resources include the following:

• BizTalk Host
• Enterprise Single Sign-On (SSO) service
• SQL Server instance
• Message Queuing (MSMQ) service
• Windows File system

Windows Server 2003 only supports running MSDTC on cluster nodes as a clustered resource.

Windows Server 2008 supports running a local DTC on any server node in the failover cluster; even if a default clustered DTC resource is configured.

To configure Message Queuing for high availability (Windows Server 2008)

---

1. To start the Failover Cluster Management program, click Start, Programs, Administrative Tools, and then click Failover Cluster Management.
2. In the left pane, right-click Failover Cluster Management, and then click Manage a Cluster.
3. In the Select a cluster to manage dialog box, enter the cluster to be managed, and then click OK.
4. To start the High Availability Wizard, i the left pane, click to expand the cluster, right-click Services and Applications, and then click Configure a Service or Application.
5. If the Before You Begin page of the High Availability Wizard is displayed, click Next.
6. On the Select Service or Application page, click Message Queuing, and then click Next.
7. On the Client Access Point page, enter a value for Name, enter an available IP address under Address, and then click Next.
8. On the Select Storage page, click a disk resource, and then click Next.
9. On the Confirmation page, click Next.
10. On the Summary page, click Finish.
11. To create a clustered MSDTC resource on the cluster so that there is transaction support for the clustered MSMQ resource, follow this steps:

 

To configure the Distributed Transaction Coordinator (DTC) for high availability (Windows Server 2008)

---

1. To start the Failover Cluster Management program, click Start, Programs, Administrative Tools, and then click Failover Cluster Management.
2. In the left hand pane, right-click Failover Cluster Management, and then click Manage a Cluster.
3. In the Select a cluster to manage dialog box, enter the cluster to be managed, and then click OK.
4. To start the High Availability Wizard, in the left pane click to expand the cluster, right-click Services and Applications, and then click Configure a Service or Application.
5. If the Before You Begin page of the High Availability Wizard is displayed, click Next.
6. On the Select Service or Application page, click Distributed Transaction Coordinator, and then click Next.
7. On the Client Access Point page, enter a value for Name, enter an available IP address under Address, and then click Next.
8. On the Select Storage page, click to select a disk resource and then click Next.
9. On the Confirmation page, click Next.
10. On the Summary page, click Finish.

 

To configure the MSDTC transaction mode as Incoming Caller Authentication Required (Windows Server 2008)

---

1. To open the Component Services management console, click Start, Programs, Administrative Tools, and then click Component Services.
2. Click to expand Component Services, click to expand Computers, click to expand My Computer, click to expand Distributed Transaction Coordinator, click to expand Clustered DTCs, right-click the clustered DTC resource, and then click Properties.
3. Click the Security tab.
4. If network DTC access is not already enabled, click to enable the Network DTC Access option. Network DTC access must be enabled to accommodate transactional support for BizTalk Server.
5. Under Transaction Manager Communication, enable the following options:
   • Allow Inbound
   • Allow Outbound
   • Incoming Caller Authentication Required
6. After changing security settings for the clustered distributed transaction coordinator resource, the resource will be restarted. Click Yes and OK when prompted.
7. Close the Component Services management console.

 

To add an MSMQ resource to an existing cluster group (Windows Server 2003)

---

1. To start the Cluster Administrator program, click Start, point to Programs, point to Administrative Tools, and then click Cluster Administrator.
2. Click to select a cluster group other than the quorum group that contains a Name and Disk resource.
3. On the File menu, point to New, and then click Resource.
4. Enter a value for the Name field of the New Resource dialog box, for example, MSMQ.
5. In the Resource type drop-down list, click Message Queuing, and then click Next.
6. In the Possible Owners dialog box, include each cluster node as a possible owner of the message queuing resource, and then click Next.
7. In the Dependencies dialog box, add a dependency to a network name resource and the disk resource associated with this group, and then click Finish.
8. Click OK in the dialog box that indicates that the resource was created successfully.
9. To create a clustered MSDTC resource on the cluster so that there is transaction support for the clustered MSMQ resource, follow this steps:

 

To add an MSDTC resource to an existing cluster group (Windows Server 2003)

---

1. To start the Cluster Administrator program, click Start, Programs, Administrative Tools, and then click Cluster Administrator.
2. Click to select a cluster group other than the quorum group that contains a Physical Disk, IP Address, and Network Name resource. To create a group with a Physical Disk, IP Address, and Network Name resource if one does not already exist.
3. On the File menu, point to New, and then click Resource.
4. Enter a value for the Name field of the New Resource dialog box, for example, MSDTC.
5. In the Resource type drop-down list, click Distributed Transaction Coordinator, and then click Next.
6. In the Possible Owners dialog box, include each cluster node as a possible owner of the distributed transaction coordinator resource, and then click Next.
7. In the Dependencies dialog box, add a dependency to a network name resource and the disk resource associated with this group, and then click Finish.
8. In the dialog box that indicates that the resource was created successfully, click OK.

 

To configure the MSDTC transaction mode as Incoming Caller Authentication Required (Windows Server 2003)

---

1. To open the Component Services management console, click Start, Programs, Administrative Tools, and then Component Services.
2. Click to expand Component Services, and then click to expand Computer.
3. Right-click My Computer, and then select the Properties menu item to display the My Computer Properties dialog box.
4. Click the MSDTC tab.
5. To display the Security Configuration dialog box, click Security Configuration .
6. If network DTC access is not already enabled, click to enable the Network DTC Access option. Network DTC access must be enabled to accommodate transactional support for BizTalk Server.
7. Under Transaction Manager Communication, enable the following options:
   • Allow Inbound
   • Allow Outbound
   • Incoming Caller Authentication Required
8. Stop and restart the Distributed Transaction Coordinator service.

SSIS and clustering: What you should do instead

Lots of customers ask about configuring SQL Server Integration Services in a failover cluster. I recommend that you DON’T configure SSIS as a cluster resource. There are almost no benefits to doing so, and you can gain many of the benefits that you want by simple configuration changes. By editing the configuration file for the SSIS service on each node of a cluster, you can manage the packages on any node from any other node. For more information, please see the Books Online topic, Configuring Integration Services in a Clustered Environment.

Microsoft Senior Premier Field Engineer Steve Howard provided these additional details about the recommendations that he makes to customers who ask about clustering. Thanks, Steve, for permission to share:

---

I agree that restarting a running package automatically would be neat, but this would be different from other cluster-aware technologies (and other failover technologies) that we have. For example, if a failover happens with SQL Server, the queries do not restart, and other jobs running in Agent do not restart. I suppose it would be possible to write a job to check for jobs that did not complete successfully and restart those jobs, then schedule the that job to run at startup. That sounds feasible, but I have never done that.

What I’m describing is supported out of the box. It is really the same process that you must go through to manage package on a standalone machine with multiple instances (or even just one named instance). I find this question to be the most common question that customers have when I go onsite. Customers usually just do not understand the function of the SSIS service. When I explain it to them, and we work through it together, they are satisfied. I’ll explain here what I go through with customers, and students in the SSIS workshop.

In the installation folder, they will find the configuration file. For SQL 2005, by default, this path is: C:\Program Files\Microsoft SQL Server\90\DTS\Binn and for SQL 2008, this is C:\Program Files\Microsoft SQL Server\100\DTS\Binn. In either case, the name of the file is MsDtsSrvr.ini.xml. When first installed, this file will look like this:

<?xml version=”1.0″ encoding=”utf-8″?>

<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance“>

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

<Folder xsi:type=”SqlServerFolder”>

<Name>MSDB</Name>

<ServerName>.</ServerName>

</Folder>

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

(I just use notepad for this task, but some people prefer to use XML notepad or some XML editor like that.)

In the top level folder, the servername is “.”, which means it will only connect to the default instance on the local machine (local to where the service is running). So when I connect to that SSIS service, I can only see the default instance on the machine where the SSIS service is running. Everything here is relative to where the service is running. (I tell students that it is the center of management for SSIS). I can connect to this machine with Management Studio on any machine, but with this configuration, I will only see the default instance running on the machine where the SSIS service I connected to is running.

If I have multiple instances on this machine, I need to add top-level folders so I can manage all the instances installed on this machine from this SSIS service. (I’ll get to clusters in a moment). Let’s say that I have both a SQL 2005 instance and a SQL 2008 instance on this machine. Then in the SQL 2008 SSIS MsDtsSrvr.ini.xml, I need to set it up to manage these instances. (I cannot manage SQL 2008 instances from SQL 2005 SSIS, so I must configure the SQL 2008 SSIS to be able to manage both from one service.) In that case, I would add the top-level folders with names that let me distinguish among the servers where I am managing packages:

<?xml version=”1.0″ encoding=”utf-8″?>

<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance“>

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>SQL 2008 MSDB</Name>

      <ServerName>.\SQL2K8</ServerName>

    </Folder>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>SQL 2005 MSDB</Name>

      <ServerName>.</ServerName>

    </Folder>

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

So, I have added one folder that is named “SQL 2008 MSDB” and that points to the named instance SQL2k8 on the local machine. The other folder is named “SQL 2005 MSDB” and that points to the default instance on the local machine. When I make this edit, restart the SSIS service so it will read the modified configuration file, then connect to this SSIS instance, I can now see both servers and manage packages on both:

 

So now, I can see running packages on either server, I can import, export, or manually start the packages. But none of this is really necessary to be able to design, install, or run those packages. The Service is just for convenience for managing this.

So now, let’s take this concept to a cluster. For our cluster, let’s have 4 nodes names Node1, Node2, Node3, and Node4. On this, let’s install 4 instances of SQL in 4 separate resource groups. Let’s use the network names net1, net2, net3, and net4, and let’s install instances InstanceA, InstanceB, InstanceC, and InstanceD on those net names respectively so that the full names of our instances will be net1\InstanceA; net2\InstanceB; net3\InstanceC, and net4\InstanceD. Any of the 4 nodes can host any of the instances in our setup.

To be able to manage packages on any of those instances, you are going to have to modify your config file. To be able to manage packages on all 4 instances from any one machine, we would make modifications like I did above so that the config file will now look like this:

<?xml version=”1.0″ encoding=”utf-8″?>

<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance“>

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

<TopLevelFolders>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>InstanceA MSDB</Name>

      <ServerName>net1\InstanceA</ServerName>

    </Folder>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>InstanceB MSDB</Name>

      <ServerName>net2\InstanceB</ServerName>

    </Folder>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>InstanceC MSDB</Name>

      <ServerName>net3\InstanceC</ServerName>

    </Folder>

    <Folder xsi:type=”SqlServerFolder”>

      <Name>InstanceD MSDB</Name>

      <ServerName>net4\InstanceD</ServerName>

    </Folder>

<Folder xsi:type=”FileSystemFolder”>

<Name>File System</Name>

<StorePath>..\Packages</StorePath>

</Folder>

</TopLevelFolders>

</DtsServiceConfiguration>

So now, whatever machine I put that config file onto will see and be able to manage packages on those 4 machines, just as in the screenshot above, I can see the packages and manage them on those two instances. If I put this on node1, then if I connect to node1, I can manage all of them from that machine. But just having it on one node will be a bit of a pain. So, once I have this configured, and I have tested to make sure it will see all the instances where I want to manage packages, I just copy the MsDtsSrvr.ini.xml file into place on node2, node3, and node4 (if I have installed the SSIS service on those nodes). Now, I can connect to SSIS on any of those nodes.

Most DBAs don’t care what the node names are, but they know the network names of their SQL Server instances very well. In that cluster configuration we described, these network names resolve to IP addresses that move with the SQL Server instance when it fails over. So from Management Studio on the DBA’s workstation, he can connect to the SSIS service on net1 and see all 4 instances on his cluster. If it fails over, and he still wants to connect to SSIS to manage packages on any of the 4 nodes on that cluster, he could connect to net1, and it would connect to the SSIS service running on the node where Net1\InstanceA is now hosted, and he will still see the same thing – he doesn’t know or care that he is now connected to the SSIS service on a different node. If he wanted to, he could even specify the cluster name (instead of any of the SQL network names) and still connect to an SSIS service and still see the same set of folders.

In some environments, the DBA has one server that is his/hers where they set up their management tools. The SSIS configuration that we have allows the DBA to be able to configure the XML file on that one machine to see and manage packages on all instances and machines that they manage by connecting to a single SSIS service. He/she just needs to configure the XML file on that one machine.

Where I see confusion/frustration from customers is that they think of Management Studio as the center of their management tools. With SSIS, it is the SSIS service that is the center of the management tools. Customers, before education, think of the SSIS service as running the packages, but this is not the case. The SSIS service is for management. Management Studio gives them a graphical interface into the service, but the center of management for SSIS is the SSIS service.

If I have one complaint about this, it is that we do not really have a front end for customers so that they don’t have to manually edit the XML files. But really, that XML file is so simple that it is not difficult to edit with tools like Notepad or XML Notepad.

And in that situation, what have we gained if we cluster the SSIS service?

---

The preceding information is presented here for exactly what it is: the educated opinion of an experienced Microsoft field engineer.

What many corporate customers are really looking for, presumably, is high availability for ETL processes, especially long-running processes. Except for its support for transactions, and its ability to restart from checkpoints after failure, SSIS out of the box doesn’t currently have a complete answer for HA concerns.