GAPTHEGURU

Geek with special skills

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

  1. 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.
    1. Save the file as an .xml file. For example, save the file as SSOCLUSTER.xml.
    2. 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.
    3. 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.
  1. 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.

  1. On the Select Service page of the New Resource Wizard, click to select Enterprise Single Sign-On Service and click Next.
  2. On the Confirmation page click Next.
  3. 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.
  4. 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.
  5. Click the Dependencies tab of the properties dialog box and click Insert.
  6. 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.
  1. 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.
  2. Log on to the second node and at a command prompt, change to the Enterprise SSO installation folder.
  3. 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.
  1. The master secret is stored in the registry at the following location:
  2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ENTSSO\SSOSS
  3. 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.

 

Advertisements

03/12/2012 Posted by | Biztalk, SQL Scripting, Sql Server, SSO, T-SQL | , , , , | 1 Comment

BIZTALK: Database Structure and Jobs

The last week I have implemented a new Biztalk environment with the databases on a SQL 2008 R2 SP1 failover cluster. I’ve found some interesting information about Biztalk design, functionality and tuning that i would like to share with you.

This topic shows the database structure and database jobs for BizTalk Server 2006.

The following figure shows the processes and entities that write to the BizTalk Server databases.

Database write diagram showing the processes and entities that write to the BizTalk Server databases
Processes that write to BizTalk Server databases

BizTalk Server 2006 includes the following SQL Server Agent jobs to assist you in managing the BizTalk Server databases:

Aa561960.note(en-us,BTS.20).gifNote
The names of the jobs change depending on the database names given during configuration. If you have deployed multiple MessageBox databases in your environment, there will be several jobs for each MessageBox.
Warning
In the BizTalk Management (BizTalkMgmtDb) database, there’s a stored procedure named dbo.adm_cleanupmgmtdb. DO NOT RUN THIS STORED PROCEDURE! If you do run this stored procedure, all the entries in the database will be deleted.
Job Description
Backup BizTalk Server (BizTalkMgmtDb) This job performs full database and log backups of the BizTalk Server databases. For more information about configuring and running this job, see Backing Up and Restoring BizTalk Server Databases.
CleanupBTFExpiredEntriesJob_BizTalkMgmtDb This job cleans up expired BizTalk Framework (BTF) entries in the BizTalk Management (BizTalkMgmtDb) database.
DTA Purge and Archive (BizTalkDTADb) This job automatically archives data in the BizTalk Tracking (BizTalkDTADb) database and purges obsolete data. For more information about configuring and running this job, see Archiving and Purging the BizTalk Tracking Database.
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb This job detects when a BizTalk Server host instance (NT service) has stopped and releases all work that was being done by that host instance so that it can be worked on by another host instance.
MessageBox_Message_Cleanup_BizTalkMsgBoxDb This job removes all messages that are no longer being referenced by any subscribers in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables.

Caution
This is an unscheduled job which is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job. Do not manually start this job.
MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb This job manages the reference count logs for messages and determines when a message is no longer referenced by any subscriber.

Aa561960.note(en-us,BTS.20).gifNote
Even thought this SQL Server Agent job is scheduled to run once per minute, the stored procedure that is called by this job contains logic to ensure that the stored procedure runs continually. This is by design behavior and should not be modified.
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb This job removes all message parts that are no longer being referenced by any messages in the BizTalk MessageBox (BizTalkMsgBoxDb) database tables. All messages are made up of one or more message parts, which contain the actual message data.
MessageBox_UpdateStats_BizTalkMsgBoxDb This job manually updates the statistics for the BizTalk MessageBox (BizTalkMsgBoxDb) database.
Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb This job is needed for multiple MessageBox deployments. It asynchronously performs operational actions such as bulk terminate on the master MessageBox after those changes have been applied to the subordinate MessageBox.
PurgeSubscriptionsJob_BizTalkMsgBoxDb This job purges unused subscription predicates from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database.
Rules_Database_Cleanup_BizTalkRuleEngineDb This job automatically purges old audit data from the Rule Engine (BizTalkRuleEngineDb) database every 90 days. This job also purges old history data (deploy/undeploy notifications) from the Rule Engine (BizTalkRuleEngineDb) database every 3 days.
TrackedMessages_Copy_BizTalkMsgBoxDb This job copies the messages bodies of tracked messages from the BizTalk Server MessageBox (BizTalkMsgBoxDb) database to the Tracking (BizTalkDTADb) database.

03/08/2012 Posted by | Biztalk, Sql Server | , | 2 Comments

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.


  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.

 

  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.

03/08/2012 Posted by | Biztalk, Cluster Configuration | , , , , | 2 Comments

Installation of SSO on SQL Failover Cluster

In this post I will tell a story of my experience with installation of SSO on SQL Cluster. Each BizTalk Server has an Enterprise Single Sign-On service (EntSSO.exe). Enterprise Single Sign-On is also referred as SSO or EntSSO. SSO serves two purposes. One is for data encryption, that is, port URI data. And the other is, as what the name indicates, Single Sign-On. Single Sign-On is about credential mapping. BizTalk Server SSO currently supports only Windows-initiated Single Sign-On. That means you can only map Windows users accounts to external application (affiliate application) user accounts. On the inbound side the sender is authenticated with Windows; on the outbound side, BizTalk Server automatically authenticates with the affiliate applications using the preconfigured credential mapping. Single Sign-On is a useful feature in business-to-business (B2B) scenarios.

Note: However the encryption function is mandatory for a BizTalk system. Single Sign-On for credential mapping can be solved with other tools like Oracle Wallet.

In addition to the SSO services running on each of the BizTalk Servers, there is a master secret server. The master secret server is a server with the SSO service running on it. The master secret server can be one of the SSO services running on one of the BizTalk Servers, or a dedicated master secret server.

It is the same executable called EntSSO.exe, but with an additional sub component responsible for maintain and supply the master secret key to the SSO services on the other BizTalk Servers. The other SSO services running on the BizTalk Servers check every 30 seconds to see whether the master secret has changed. If it has changed, they read it securely; otherwise, they continue to use the master secret they already have cached in memory.

Considering there is only one master secret server in your entire environment and the dependency of BizTalk Server, it is recommended that you use an active-passive cluster configuration for the master secret server. Because the master secret server doesn’t consume a lot of resources, it is very common to use the SQL Server failover cluster for clustering the master secret server.

On the first server cluster node where you run BizTalk Server Configuration, you choose to create a new SSO system. That makes the cluster node as the master secret server. The host name of the master secret server is the host name of the physical cluster node. The master secret key is automatically generated on that node. On the other cluster node, you choose to join the existing SSO system. To cluster the master secret server, you need to change the master secret server from the first cluster node host name to the virtual SQL Server failover cluster network name (NameSQL1), and create a SSO generic service cluster resource. At the end, you restore the master secret key to the other cluster nodes. So, when the cluster fails over to another node, that node has the master secret. These steps can be done using the domain admin account (usually a network senior administrator will perform these steps with this account i.e. as example I will name the account InstallBizTalk).

Clustering the master secret server service is a complicated process. You might find it confusing when and where you need to perform a step, and the order of the steps. Here are some general rules:

· You must install and configure SSO on each of the cluster nodes. When you create the new SSO system on the first cluster node, this node can be either an active cluster node or a passive cluster node.

· After you successfully installed and configured SSO on all of the cluster nodes, you must update the master secret server host name from a physical cluster node host name to the virtual cluster network name, and you must change the rename from an active node.

· After the master secret server host name is changed, you must restart the SSO service on the active node to refresh the cache by taking the SSO cluster resource offline and then online.

· You must create an SSO cluster resource before restoring the master secret key on the other cluster nodes.

· Before you restore the master secret key on a cluster node, you must make it the active node first.

Steps involved to successfully install and configure SSO on cluster will be outlined here.

There are several SSO user groups. Two of them are required when configuring the master secret server. SSO Administrators have the highest level user rights in the SSO system; and SSO Affiliate Administrators defines the affiliate applications that the SSO system contains.

To create a domain group account for the SQL Server service groups

1. If you haven’t already logged on or if you are logged on with a different credential, log on to Cluster Node A using domain admin account.

2. Click Start, and then click Run.

3. In the Run dialog box, enter dsa.msc, and then click OK.

4. From Active Directory Users and Computers, if the YourDomain domain is not already expanded, click the plus sign (+) to expand the YourDomain.com domain.

5. In the left pane, right-click Users, point to New, and then click Group.

6. From New Object – Group, enter the following values, and then click OK.

Name Value
Group name SSO Administrators
Group scope Global
Group type Security

7. Repeat steps 5 to 6 to create one more group:

Name Value
Group name SSO Affiliate Administrators
Group scope Global
Group type Security

To create a domain user account for the SSO Service

1. (continue from the previous procedure)

2. In the left pane, right-click Users, point to New, and then click User.

3. From New Object – User, enter the following values, and then click Next.

Name Value
First name SSO
Last name Service
User logon name SSOService

4. Enter or select the following values, and then click Next.

Name Value
Password TBD
Confirm password TBD
User must change password at next logon (clear)
User cannot change password (select)
Password never expires (select)
Account is disabled (clear)

5. Click Finish.

Both YourDomain\SSOSerivce and domain admin account need to be members of the YourDomain\SSO Administrators group. It is designated for installing and configuring the BizTalk Server system.

To make YourDomain\SSOService and domain admin account members of SSO Administrators

1. (continue from the previous procedure)

2. In the left pane, highlight Users.

3. In the right pane, right-click SSO Service, point to All Tasks, and then click Add to a group.

4. From Select Group, enter or select the following values, and then click OK.

Name Value
Select this object type Group or Built-in security principal
From this location YourDomain
Enter the object name to select SSO administrators

5. To acknowledge that the account was created, click OK.

6. Repeat steps 3 to 5 to add domain admin account into the same group.

Granting YourDomain\SSO Administrators Full Control on Cluster Node A

You need to grant YourDomain\SSOService or YourDomain\SSO Administrators with the full control privilege on the cluster administrator.

To grant YourDomain\SSO Administrators full control on the cluster

1. If you haven’t already logged on or if you are logged on with a different credential, log on to Cluster Node A as YourDomain\IInstallBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. From Cluster Administrator, in the left pane, right-click CLUSTER NODE A, and then click Properties.

4. From CLUSTER NODE A Properties, click the Security tab, and then click Add.

5. From Select Users, Computers, or Groups, enter the following values, and then click OK.

Name Value
Select this object type Group or Built-in security principal
From this location YourDomain.com
Enter the object name to select SSO administrators

6. Verify the Allow box is selected, and then click OK.

Installing the SSO Components on Cluster Node A

With the accounts and permissions configured in the last step, you can now install the master secret server. BizTalk Server is not cluster aware as SQL Server is. You will need to install SSO on each of the cluster nodes. You will also need to create the SSO cluster resource manually.

BizTalk Server installation process has two parts. In this step, you will install the components. And the next step is configuring the master secret server.

To install the SSO components on Cluster Node A and Cluster Node B

1. If you haven’t logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

2. Run setup.exe to install BizTalk Server 2006 R2.

3. On the Start page, click Install Microsoft BizTalk Server 2006 R2 on this computer.

4. On the Customer Information page, enter information in the User name box, the Organization box, and the Product key box, and then click Next.

5. On the License Agreement page, read the license agreement, select yes, I accept the terms of the license agreement, and then click Next.

6. On the Component Installation page, clear all the check boxes, select Enterprise Single Sign-On Administration Module and Enterprise Single Sing-On Master Secret Server from the Additional Software group, and then click Next.

clip_image002

7. On the Summary page, click Install.

8. On the Installation Completed page, clear Launch BizTalk Server Configuration check box, and then click Finish.

Installing the SSO Components on Cluster Node B

Repeat the same steps to install the SSO components on Cluster Node B.

Configuring the Master Secret Server on Cluster Node A

Configuring the master secret server has three parts, creating SSO database, assigning SSO service account, and backing-up the master secret. Notice there are two options, create a new SSO system, and join an existing SSO system. On the first cluster nodes, you must choose to create a new SSO system. When you create a new SSO system, you must specify the database server name, and the database name. But you don’t need to specify the master secret server host name. The current host name, becomes the default master secret server. Later, you must change the master secret server from the physical cluster node host name to the virtual cluster host name, YourVirtualServerName.

It doesn’t matter whether Cluster Node A is the active node or a passive node when you go through this procedure.

To configure the master secret server on Cluster Node A

1. If you haven’t logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

2. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Configuration.

3. On the Microsoft BizTalk Server 2006 Configuration page, choose Custom Configuration, enter the following values, and then click Configure.

Name Value
Database server name Database Name Cluster
User name YourDomain\SSOService
Password TBD

4. in the left pane, click Enterprise SSO.

5. In the right pane, enter or select the following values:

Name Value
Enable Enterprise Single Sign-On on this computer (checked)
Create a new SSO system (selected)
SSO Database: Server Name Database Name Cluster
SSO Database: Database Name SSODB
Enterprise Single Sign-On Service: Account YourDomain\SSOService
SSO Administrator(s): Windows Group YourDomain\SSO Administrators
SSO Affiliate Administrators(s): Windows Group YourDomain\SSO Affiliate Administrators

image

6. In the left pane, click Enterprise SSO Secret Backup. The Enterprise SSO secret is very critical. You must back it up to a file. It is a good practice to burn the key into a CD and store the CD in a safe place.

7. In the right pane, enter the following values:

Name Value
Secret back password TBD
Confirm password TBD
Password reminder TBD
Backup file location C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSecret.bak)

8. Click Apply Configuration.

9. On the Summary page, to apply the configuration, click Next.

10. Verify that the Configuration Result is Success, and then click Finish.

11. Close Microsoft BizTalk Server 2006 Configuration.

1.1.4 Configuring SSO on Cluster Node B

On the second node, you choose to join the existing SSO system. When joining the existing SSO system, it shares the SSO database of the existing SSO system.

To configure the master secret server on Cluster Node B

1. If you haven’t logged on, log on to Cluster Node B as YourDomain\InstallBizTalk.

2. Click Start, point to All Programs, point to Microsoft BizTalk Server 2006, and then click BizTalk Server Configuration.

3. On the Microsoft BizTalk Server 2006 Configuration page, choose Custom Configuration, enter the following values, and then click Configure.

Name Value
Database server name ServerName Database Cluster
User name YourDomainSSOService
Password TBD

4. In the left pane, click Enterprise SSO.

5. In the right pane, enter or select the following values:

Name Value
Enable Enterprise Single Sign-On on this computer (checked)
Join an existing SSO system (selected)
Server Name ServerName Database Cluster
Database Name SSODB
Account YourDomain\SSOService

6. Click Apply Configuration.

7. On the Summary page, to apply the configuration, click Next.

8. Verify that the Configuration Result is Success, and then click Finish.

9. Close Microsoft BizTalk Server 2006 Configuration.

1.1.5 Updating the Master Secret Server Host Name

When SSO was configured on the first cluster node, it created a new SSO system. It used the host name of the physical cluster node as the master secret server host name that is Cluster Node A. You must change it to the server cluster virtual name, which is YourVirtualClusterName. This procedure must be carried out from the active cluster node. All it does is to update the master secret server field in the SSO database.

To configure the master secret server on Cluster Node B

1. If you haven’t already logged on, log on to Cluster Node A as YourDomain\InstallBizTalk.

<sso>
  <globalInfo>
    <secretServer> ServerName Database Cluster</secretServer>
  </globalInfo>
</sso>

2. Open a notepad.exe, create a file with the following content, and then save it as “C:\Program Files\Common Files\Enterprise Single Sign-On\SSOCluster.xml” (with the double quotes). The content is case sensitive.

clip_image002[5]

3. Open a command prompt, and then change directory to the C:\Program Files\ Common Files\Enterprise Single Sign-On\ folder.

4. From the command prompt, execute the following command:

ssomanage -updatedb SSOCluster.xml

5. Verify the master secrete server name has been changed to  as shown below:

C:\Program Files\Common Files\Enterprise Single Sign-On>ssomanage -updatedb ssocluster.xml
Using SSO server on this computer
 
Updated SSO global information with the following values -
 
SSO secret server name                  : ServerName Database Cluster
SSO Admin account name                  : NOT CHANGED
SSO Affiliate Admin account name        : NOT CHANGE

image

Creating SSO Cluster Resource

BizTalk Server is not cluster aware. So you must manually create the master secret server cluster resource. You can either create a dedicated virtual server (cluster group) for the SSO cluster resource, or use an existing cluster group. The instructions provided use the SQL Server Cluster Group. If you create a dedicated cluster group, you also need to create a network name cluster resource depended by the SSO cluster resource.

To Create SSO cluster resource

1. If you haven’t already logged on, log on to Cluster Node A as YourDomain\InstBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. In the left pane, expand CLUSTER NODE A, expand Groups, and then expand SQL Server Cluster Group. If you get a prompt before it opens Cluster Administrator, choose Open Existing Cluster, and point it to CLUSTER NODE A.

4. Right-click SQL Server Cluster Group, click New, and then click Resource.

5. From New Resource, enter or select the following values, and then click Next.

clip_image002[9]

6. From Possible Owners, verify that CLUSTER NODE A and CLUSTER NODE B are in the Possible owners list, and then click Next.

7. From Dependencies, select SQL Network Name (Cluster Node A) and click Add. And then click Next.

8. From Generic Service Parameters, type or select the following values, and then click Next:

clip_image002[11]

9. From Registry Replication, click Finish.

clip_image001Note
Do not configure any registry keys for replication in the Registry Replication dialog box. Replication of registry keys is not a requirement when creating a SSO cluster resource and, in fact, may cause problems when failover of this cluster resource is attempted.

10. In the details pane, right-click ENTSSO, and click Bring Online. Verify that the state is changed to Online.

Restoring the Master Secret on Cluster Node B

Before restoring the master secret on Cluster Node B, you must make Cluster Node B as the active cluster node, and restart the cluster resource by taking the cluster resource offline and then online.

To make Cluster Node B the active cluster node

1. Log on to Cluster Node B as RBW-NL\InstBizTalk.

2. Click Start, point to All Programs, point to Administrative Tools, and then click Cluster Administrator.

3. From Cluster Administrator, expand CLUSTER NODE A in the left pane, expand Groups, and then expand SQL Server Cluster Group. In the details pane, the Owner column of the cluster resources shows the active cluster node

4. If Cluster Node B is not the active cluster node, in the left pane right-click SQL Server Cluster Group, and then click Move Group. Wait until all the cluster resources are online.

5. In the details pane, right-click ENTSSO, and then click Take Offline. Wait until all the cluster resources are offline.

6. In the details pane, right-click ENTSSO, and then click Bring Online. Wait until all the cluster resources are online.

To restore the master secret on the second cluster node

1. Copy the master secret backup file, C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSecret.bak, on Cluster Node A to the same folder on Cluster Node B. SSOSecret.bak is how you named the file when you configured the master secret server on Cluster Node A.

2. Open a command prompt, and then change the directory to C:\Program Files\Common Files\Enterprise Single Sign-On.

3. Type and execute the following command in the command prompt:

ssoconfig -restoresecret SSOSecret.bak

image

Through following this procedure you will be successful in deploying SSO on SQLCluster and configure SSO. My experience is that in following this procedure with an senior administrator inside an organization works the best. This procedure us done with BizTalk Server 2006 R2, but is also suitable for BizTalk Server 2009.

02/28/2012 Posted by | Active Directory, Biztalk, Cluster Configuration, Sql Server, SSO, Windows Server | , , , | 1 Comment

How to maintain and troubleshoot BizTalk Server databases

Microsoft BizTalk Server databases and the health of the databases are very important for a successful BizTalk Server messaging environment. This article discusses important things to consider when you work with BizTalk Server databases. These considerations include the following:

  • You must disable the Auto Update Statistics and Auto Create Statistics Microsoft SQL Server options.
  • You must set the Max Degree of Parallelism  property correctly.
  • Determine when you can rebuild BizTalk Server indexes.
  • Locking, deadlocking, or blocking may occur.
  • You may experience issues with large databases or tables.
  • BizTalk SQL Server Agent jobs
  • Service instances may be suspended.
  • You may experience SQL Server and BizTalk Server performance issues.
  • You should follow best practices in  BizTalk Server.

Known issues

You must disable the Auto Update Statistics and Auto Create Statistics options

You must disable the Auto Create Statistics and Auto Update Statisticsoptions on the BizTalkMsgBoxDb database. To determine whether these settings are disabled, execute the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics'

You should set the CurrentSetting setting to off. If this setting is set to on, turn it off by executing the following stored procedures in SQL Server:

exec sp_dboption 'BizTalkMsgBoxDB', 'auto create statistics', 'off'
exec sp_dboption 'BizTalkMsgBoxDB', 'auto update statistics', 'off'

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server
912262 The auto update statistics option, the auto create statistics option, and the Parallelism setting are turned off in the SQL Server database instance that hosts the BizTalk Server BizTalkMsgBoxDB database

You must set the Max Degree of Parallelism property correctly

On the computer that is running SQL Server and hosting the BizTalkMsgBoxDb database, set the Max Degree of Parallelism run_value and config_value properties to a value of 1. To determine the Max Degree of Parallelism setting, execute the following stored procedure against the Master database in SQL Server:

exec sp_configure 'max degree of parallelism'

If the run_value and config_value properties are not set to a value of 1, execute the following stored procedure in SQL Server to set them to 1:

exec sp_configure 'max degree of parallelism', '1'
reconfigure with override

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

899000 The Parallelism setting for the instance of SQL Server when you configure BizTalk Server
917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

Determine when you can rebuild BizTalk Server indexes

Most BizTalk Server indexes are clustered (index ID: 1). You can use the DBCC SHOWCONTIG SQL Server statement to display fragmentation information for the BizTalk Server tables.
The BizTalk Server indexes are GUID-based. Therefore, fragmentation typically occurs. If the Scan Density value that is returned by the DBCC SHOWCONTIG statement is less than 30 percent, the BizTalk Server indexes can be rebuilt during downtime.
Many BizTalk Server tables contain columns that use DataType definitions. Online indexing cannot be performed in these columns. Therefore, you should never rebuild the BizTalk Server indexes while BizTalk Server processes data.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

For more information about how to analyze the DBCC SHOWCONTIG statement output, visit the following Microsoft website:

Locking, deadlocking, or blocking may occur

Typically, locks and blocks occur in a BizTalk Server environment. However, these locks or blocks do not remain for an extended time. Therefore, blocking and deadlocking indicate a potential problem.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

917845 You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

You may experience issues with large databases or tables

I have seen that when the BizTalkMsgBoxDb database is larger than 5GB, performance problems can occur. Ideally, the BizTalkMsgBoxDb database should not be holding any data. The BizTalkMsgBoxDb database should be considered a buffer until the data is processed or moved to the BizTalkDTADb database.
An environment that uses a powerful SQL Server at the back end and many long-running orchestrations may have a BizTalkMsgBoxDb database that is larger than 5 GB. A high-volume environment that uses no long-running orchestrations should have a BizTalkMsgBoxDb database that is much smaller than 5 GB.
The BizTalkDTADb database does not have a set size. However, if performance decreases, the database is probably too large. Typically, 15 GB to 20 GB is considered too large. When you have large BizTalk Server databases, you may experience the following issues:

  • The BizTalkMsgBoxDb database continues to grow. However, both the log file and the data size remain large.
  • BizTalk Server takes a longer time than usual to process even a simple message flow scenario.
  • Health and Activity Tracking (HAT) queries take a longer time than usual and may time out.
  • The database log file is never truncated.
  • The BizTalk SQL Server Agent jobs run slower than usual.
  • Some tables are significantly larger or have too many rows compared to the usual table size.

Databases can become large for various reasons. These reasons may include the following:

  • BizTalk SQL Server Agent jobs are  not running
  • Large number of suspended instances
  • Disk failures
  • Tracking
  • Throttling
  • SQL Server performance
  • Network latency

Make sure that you know what is expected in your environment to determine whether a data issue is occurring.
By default, tracking is enabled on the default host. BizTalk requires that the Allow Host Tracking option be checked on a single host. When tracking is enabled, the Tracking Data Decode Service (TDDS) moves the tracking event data from the BizTalkMsgBoxDb database to the BizTalkDTADb database. If the tracking host is stopped, TDDS does not move the data to the BizTalkDTADb database and the TrackingData_x_x tables in the BizTalkMsgBoxDb database will grow.
We recommend that you dedicate one host to tracking. To allow for TDDS to maintain new tracking events in high-volume scenarios, create multiple instances of a single tracking host. No more than one tracking host should exist.
There can be too many rows in a table. There is no set number of rows that are too many. Additionally, this number of rows varies by what kind of data is stored in the table. For example, a dta_DebugTrace table that has more than 1 million rows probably has too many rows. A HostNameQ_Suspended table that has more than 200,000 rows probably has too many rows.

Use the correct BizTalk SQL Server Agent jobs

The BizTalk SQL Server Agent jobs are important for managing the BizTalk Server databases and for maintaining high performance.
The Backup BizTalk Server SQL Server Agent job is the only supported method to back up the BizTalk Server databases. This job requires all BizTalk Server databases use a Full Recovery Model. You should configure this job for a healthy BizTalk Server environment. The SQL Server methods can be used to back up the BizTalk Server databases only if SQL Server Agent is stopped and if all BizTalk Server host instances are stopped.
The MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, the SQL Server Agent job history never displays a successful completion. If a failure occurs, the job restarts within one minute and continues to run infinitely. Therefore, you can safely ignore the failure. Additionally, the job history can be cleared. You should only be concerned if the job history reports that this job constantly fails and restarts.
The MessageBox_Message_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is the only BizTalk Server job that should not be enabled because it is started by the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job.
The DTA Purge and Archive SQL Server Agent job helps maintain the BizTalkDTADb database by purging and archiving tracked messages. This job reads every row in the table and compares the time stamp to determine whether the record should be removed.
All BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job should be running successfully.
For more information about all the BizTalk Server SQL Server Agent jobs, click the following article number to view the article in the Microsoft Knowledge Base:

919776 Description of the SQL Server Agent jobs in BizTalk Server

Service instances may be suspended

Service instances can be suspended (resumable) or suspended (not resumable). These service instances may be Messaging, Orchestration, or Port.
These service instances can make the BizTalkMsgBoxDb database grow unnecessarily and can be terminated. The following table lists what method can be used, depending on the BizTalk version:

Group Hub HAT Terminate.vbs Terminator Tool
BizTalk Server 2010 Yes No Yes Yes
BizTalk Server 2009 Yes No Yes Yes
BizTalk Server 2006 R2 Yes Yes Yes Yes
BizTalk Server 2006 Yes Yes Yes Yes
BizTalk Server 2004 No Yes Yes Yes

For more information about the Terminate.vbs script, visit the following MSDN website:

Caching instances do not appear in the Group Hub page, and you cannot suspend or terminate them. This restriction is a common cause of table growth. To prevent new zombie messages for the cache service instances in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 936536. This issue is fixed in BizTalk Server 2006 R2 and later versions.
Note A zombie message is a message that was routed but not consumed.
For more information, click the following article number to view the article in the Mirosoft Knowledge Base:

936536 FIX: You experience performance issues with BizTalk Server 2006 and throttling messages are logged in the performance log file

When a BizTalk Server host instance terminates, caching instances may not be removed. To resolve this behavior in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 944426. In BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1. This issue is fixed in BizTalk Server 2009 and later versions.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

974563  List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1
944426 FIX: Orphaned cache instances may be built in the Instances and Hosts Queue tables of the BizTalkMsgBoxDb database in BizTalk Server 2006 and in BizTalk Server 2006 R2
Another common issue is that Routing Failure Reports (RFRs) may build up in the BizTalkHostQ and BizTalkHostQ_Suspended tables. The RFRs are not removed, and this behavior may cause the BizTalkMsgBoxDb database to grow. To address this issue in BizTalk Server 2006, install the hotfix in Microsoft Knowledge Base article 941690. This issue is fixed in BizTalk Server 2006 R2 and later versions.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

941690 FIX: Routing failure reports are not removed from the <BizTalkHostName>Q_Suspended table on a BizTalk Server 2006 server

You may experience SQL Server and BizTalk Server performance issues

BizTalk Server makes hundreds of short, very quick transactions to SQL Server within a minute. If the SQL Server cannot sustain this activity, BizTalk Server may experience performance issues. In Performance Monitor, monitor the Avg. Disk sec/Read, Avg. Disk sec/Transfer and Avg. Disk sec/Write Performance Monitor counters in the PhysicalDisk performance object. The optimal value is less than 10 ms (milliseconds). A value of 20 ms or larger is considered poor performance.
For more information about SQL Server performance, visit the following Microsoft website:

For more information about BizTalk Server 2004 database high availability, visit the following MSDN website:

For more information about BizTalk Server 2006 database high availability, visit the following MSDN website:

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

298475 How to troubleshoot SQL Server performance issues
271509 How to monitor blocking in SQL Server 2005 and in SQL Server 2000

Best Practices in BizTalk Server

Start SQL Server Agent on the SQL Server. When the SQL Server Agent is stopped, the built-in BizTalk SQL Server Agent jobs that are responsible for database maintenance cannot run. This behavior causes database growth, and this growth may cause performance issues. BizTalk Server database maintenance has greatly improved in BizTalk Server 2004 Service Pack 2 (SP2) and later versions.
Put the SQL Server LDF and MDF files on separate drives. When the LDF and MDF files for the BizTalkMsgBoxDb and BizTalkDTADb databases are on the same drive, disk contention can occur.
If you do not benefit from message body tracking, do not enable this feature. However, it is a good idea to enable message body tracking while you develop and troubleshoot a solution. If you do this, make sure that you disable message body tracking when you are finished. When message body tracking is enabled, the BizTalk Server databases grow. If there is a business need that requires enabling message body tracking, confirm that the TrackedMessages_Copy_BizTalkMsgBoxDb and DTA Purge and Archive SQL Server Agent jobs are running successfully.
Typically, smaller transaction logs cause better performance. To keep the transaction logs smaller, configure the Backup BizTalk ServerSQL Server Agent job to run more frequently. For more information about BizTalk Server optimization, visit the following MSDN website:

The sp_ForceFullBackup stored procedure in the BizTalkMgmtDb database can also be used to help perform an ad-hoc full backup of the data and log files. The stored procedure updates the adm_ForceFullBackup table with a value 1. The next time the Backup BizTalk Server job runs, a full database backup set is created.
The BizTalk Server Best Practices Analyzer (BPA) can be used to evaluate an existing BizTalk Server deployment. The BPA performs numerous database-related checks. For more information about the BPA, visit the following Microsoft website:

Troubleshooting

The best troubleshooting steps for the BizTalk Server SQL Server databases depend on the kind of database issue, such as blocking or deadlocking. To troubleshoot a BizTalk Server database issue, follow these steps.

Step 1: Enable and run all required BizTalk SQL Server Agent jobs

All the BizTalk SQL Server Agent jobs except the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb job should be enabled and running successfully. Do not disable any other job.
If a failure occurs, use the View History option in SQL Server to view the error information, and then troubleshoot the failure accordingly. Remember that the MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb SQL Server Agent job runs infinitely. Therefore, you should only be concerned if the job history reports that the job constantly fails and restarts.

Step 2: Use the MsgBoxViewer tool

Collect MsgBoxViewer data while you reproduce an issue.
The MsgBoxViewer tool is useful for troubleshooting because it provides an HTML report that has detailed information about table sizes and the row count. The report can also help determine whether BizTalk Server is throttling. Additionally, the tool provides a snapshot of the BizTalk Server databases and the BizTalk Server configuration.
For more information about how to download the MsgBoxViewer tool, visit the following Microsoft website:

For more information about throttling in BizTalk Server, visit the following MSDN website:

When BizTalk Server is running slower than usual, run the MsgBoxViewer tool, and then review the generated HTML report for any problems. The Summary section lists warnings in yellow and potential problems in red.
Additionally, you can use the MsgBoxViewer tool output to determine which tables are the largest and have the most records. The following table lists the BizTalk Server tables that typically grow the largest. You can use this data to determine where a potential problem may exist.

Table Description
HostNameQ_Suspended This table contains a reference to messages in the Spool table that are associated with suspended instances for the particular host.  This table is in the BizTalkMsgBoxDb database.
HostNameQ This table contains a reference to messages in the Spool table that are associated with the particular host and are not suspended. This table is in the BizTalkMsgBoxDb database.
Spool
Parts
Fragments
These tables store actual message data in the BizTalkMsgBoxDb database.
Instances This table stores all instances and their current status in the BizTalkMsgBoxDb database.
TrackingData_0_x These four tables store the Business Activity Monitoring (BAM) tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BAMPrimaryImport database.
TrackingData_1_x These four tables store the tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BizTalkDTADB database.
Tracking_Fragmentsx Tracking_Partsx Tracking_Spoolx Two of each of these tables are in the BizTalkMsgBoxDb and BizTalkDTADb databases. One is online, and the other is offline.
In BizTalk Server 2004 SP2 and in later versions, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job moves tracked message bodies directly to these tables in the BizTalkDTADb database.
In BizTalk Server 2004 Service Pack 1 (SP1) and in earlier versions of BizTalk Server 2004, the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Server Agent job copies tracked message bodies into these tables in the BizTalkMsgBoxDb database. The TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job purges the offline tables and makes the tables online while the job also takes the online tables offline.
dta_ServiceInstances This table stores tracked events for service instances in the BizTalkDTADb database. If this table is large, the BizTalkDTADb database is probably large.
dta_DebugTrace This table stores the Orchestration debugger events in the BizTalkDTADb database.
dta_MessageInOutEvents This table stores tracked event messages in the BizTalkDTADb database. These tracked event messages include message context information.
dta_ServiceInstanceExceptions This table stores error information for any suspended service instance in the BizTalkDTADb database.

Consider the following scenarios.

HostNameQ_Suspended tables

If the HostNameQ_Suspended tables have many records, the tables could be valid suspended instances that appear in Group Hub or in HAT. These instances can be terminated. If these instances do not appear in Group Hub or in HAT, the instances are probably caching instances or orphaned routing failure reports. When suspended instances are terminated, the items in this table and their associated rows in the Spool and Instances tables are cleaned up.
In this scenario, handle the suspended instances by resuming them or terminating them. The BizTalk Terminator tool can also be used.

HostNameQ tables

If the HostNameQ tables have many of records, the following kinds of instances may exist:

  • Ready-to-run instances
  • Active instances
  • Dehydrated instances

BizTalk Server needs time to “catch up” and process the instances. This table can grow when the incoming rate of processing outpaces the outgoing rate of processing. This scenario can occur when another problem occurs, such as a large BizTalkDTADb database or SQL Server disk delays.

Spool, Parts, and Fragments tables

If the Spool, Parts, and Fragments tables have many records, many messages are currently active, dehydrated, or suspended. Depending on the size, the number of parts, and the fragmentation settings in these tables, a single message may spawn all these tables.  Each message has exactly one row in the Spool table and at least one row in the Parts table.

Instances table

The BizTalk Administrator should not allow for many suspended instances to remain in the Instances table. Dehydrated instances should only remain if the business logic requires long-running orchestrations. Remember that one service instance can be associated with many messages in the Spool table.

TrackingData_x_x tables

If the TrackingData_x_x tables are large, the Tracking host (TDDS) is not running or is not running successfully. If the tracking host instance is running, review the event logs and the TDDS_FailedTrackingData table in the BizTalkDTADb database for error information. If BizTalk is throttling with a state of 6 (large database), these tables can also be truncated by using the BizTalk Terminator tool.
If there is a large gap between the sequence numbers in the BizTalkMsgBoxDb TrackingData_x_x tables and the BAMPrimaryImport or BizTalkDTADb TDDS_StreamStatus tables, then TDDS may not move the data from the BizTalkMsgBoxDb database. To correct this, use the BizTalk Terminator tool to purge these tables and reset the sequence number.
On BizTalk Server 2006 R2, install BizTalk 2006 R2 Service Pack 1 to address a known issue with the tracking data. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

969870   FIX: The tracking data is not moved as expected from the BizTalkMsgBoxDb database to the BizTalkDTADb database in BizTalk Server 2006 R2
974563  List of Microsoft BizTalk Server hotfixes that are included in BizTalk Server 2006 R2 Service Pack 1
Tracking_Spool1 or Tracking_Spool2 tables

If the Tracking_Spool1 or Tracking_Spool2 tables become large in BizTalk Server 2004 SP1 and in earlier versions of BizTalk Server 2004, confirm that the TrackingSpool_Cleanup_BizTalkMsgBoxDb SQL Server Agent job is enabled and running.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:

907661 The Tracking_Spool1 or Tracking_Spool2 tables in the BiztalkMsgBoxDb database become very large in BizTalk Server 2004

For more information about a Database Maintenance SDK sample, visit the following MSDN website:

dta_DebugTrace table and dta_MessageInOutEvents

The dta_DebugTrace table is populated when Shape start and end is enabled on an orchestration. If the dta_DebugTrace table has many records, these orchestration debugging events are being used or were being used. If orchestration debugging is not required for regular operations, clear the check box for the Shape start and end option in the orchestration properties.
The dta_MessageInOutEvents table is populated when Message send and receive is enabled on orchestrations and/or pipelines. If these tracking events are not needed, clear the check box for this option in the orchestration and/or pipeline properties.
If these trace events are disabled or if a backlog exists in the BizTalkMsgBoxDb database, these tables may continue to grow because TDDS continues to move this data into these tables.
By default, global tracking is enabled. If global tracking is not necessary, it can be disabled. For more information, visit the following Microsoft website:

If the dta_DebugTrace table and/or the dta_messageInOutEvents table in the BizTalkDTADb database are too large, you can truncate the tables manually after you stop the tracking host. The BizTalk Terminator tool also provides this functionality.
In BizTalk Server 2004, the dtav_FindMessageFacts view in the BizTalkDTADb database prevents the dta_MessageInOutEvents table from truncating. To work around this behavior, follow these steps:

  1. Stop the tracking host and the DTA Purge and Archive job.
  2. If you want to truncate the dta_messageInOutEvents table, save and then delete the dtav_FindMessageFacts view. To do this, follow these steps:
    1. In SQL Server, access the dtav_FindMessageFacts view in the BizTalkDTADb database.
    2. Right-click the dtav_FindMessageFacts view, click All Tasks, and then click Generate SQL Script. When the Generate SQL Scripts dialog box opens, make no changes, and then click OK.
    3. Name the file dtav_FindMessageFacts.sql, and then click Save.
    4. Right-click the dtav_FindMessageFacts view,  and then click Delete. Click Drop All.

You can now truncate the table(s). If you truncate the dta_messageInOutEvents table, you must also truncate the dta_url table. The dta_url table only exists in BizTalk Server 2004.
When you are finished, follow these steps to re-create the dtav_FindMessageFacts view:

  1. Open a new query in SQL Server.
  2. In the Available Databases list, select the BizTalkDTADb database.
  3. Execute your saved dtav_FindMessageFacts.sql script. This will re-create the view in the BizTalkDTADb database.

Restart the tracking host and the DTA Purge and Archive job.

For more information about tracking database sizing guidelines, visit the following MSDN website:

dta_ServiceInstanceExceptions table

The dta_ServiceInstanceExceptions table typically becomes large in an environment that regularly has suspended instances.

Step 3: Investigate deadlock scenarios

In a deadlock scenario, enable DBCC tracing on the SQL Server so that the deadlock information is written to the SQLERROR log.
In SQL Server 2005 and later versions, execute the following statement:

DBCC TRACEON (1222,-1)

In SQL Server 2000, execute the following statement:

DBCC TRACEON (1204)

Additionally,  use the PSSDiag utility to collect data on the Lock:Deadlock event and theLock:Deadlock Chain event.
The BizTalkMsgBoxDB database is a high-volume and high-transaction Online Transaction Processing (OLTP) database.  Some deadlocking is expected, and this deadlocking is handled internally by the BizTalk Server engine. When this behavior occurs, no errors are listed in the error logs. When you investigate a deadlock scenario, the deadlock that you are investigating in the output must be correlated with a deadlock error in the event logs.
For more information about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:

830232 PSSDIAG data collection utility

Step 4: Look for blocked processes

Use Activity Monitor in SQL Server to obtain the server process identifier (SPID) of a locking system process. Then, run SQL Profiler to determine the SQL statement that is executing in the locking SPID.
To troubleshoot a locking and blocking issue in SQL Server, use the PSSDiag for SQL utility to capture all the Transact-SQL events that have the blocking script enabled.
In SQL Server 2005 and later versions, you can specify the blocked process threshold setting to determine which SPID or SPIDs are blocking longer than the threshold that you specify.
For more information about PSSDiag for SQL, click the following article number to view the article in the Microsoft Knowledge Base:

830232 PSSDIAG data collection utility

For more information about the blocked process threshold, visit the following MSDN website:

Step 5: Install the Latest BizTalk Server Service Pack and Cumulative Update

BizTalk Server 2006 R2 and later versions have moved to a Cumulative Update (CU) model. The cumulative updates will contain the latest hot fixes. BizTalk Server 2006 R2 Service Pack 1 is also available:

BizTalk Server 2004 SP1 has no built-in purging and archiving functionality for the BizTalkDTADb database. This functionality is included with BizTalk Server 2004 SP2. Depending on the size of the BizTalkDTADb database, installing BizTalk Server 2004 SP2 may take hours because the Setup program purges the BizTalkDTADb database.
For information about the known issues when you install BizTalk Server 2004 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

940519 Known issues in BizTalk Server 2004 Service Pack 2 that are not documented in the ReadmeSP2.htm file

Delete all the data

If the databases are too large or if the preferred method is to delete all data, all the data can be deleted.
CautionDo not use this method in any environment where the data is business critical or if the data is needed.

BizTalkMsgBoxDb Database Purging Steps

To delete all data in the BizTalkMsgBoxDb database, you can use the BizTalk Terminator tool. Otherwise, follow these steps.
NoteThis action deletes all messages. Be extremely cautious if you follow these steps in a production environment.

  1. Back up all BizTalk Server databases. Remember, the BizTalkMgmtDb.dbo.sp_ForceFullBackup stored procedure can be used to force a full backup of the data and log files. Execute this stored procedure, and then execute the Backup BizTalk Server SQL Agent job.
  2. Copy the Msgbox_cleanup_logic.sql script from Drive:\Program Files\Microsoft BizTalk 200x\schema to the SQL Server.
  3. Execute this SQL script against the BizTalkMsgBoxDb database to update the bts_CleanupMsgbox stored procedure.
  4. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
  5. Execute the bts_CleanupMsgbox stored procedure on all the BizTalkMsgBoxDb databases.
  6. Restart all host instances and BizTalk Server services.

For information about a known issue with the bts_CleanupMsgbox stored procedure in BizTalk Server 2006, click the following article number to view the article in the Microsoft Knowledge Base:

924715 FIX: Message data is not deleted from the tracking database after you run the bts_CleanupMsgbox stored procedure in a BizTalk Server 2006 test environment

BizTalkDTADb database purging options

To delete all data from the BizTalkDTADb database, you can use the the BizTalk Terminator tool. Otherwise, use one of the following methods.
NoteBoth methods delete all messages. Method 2 is faster.

  • Method 1:
    1. Back up all BizTalk Server databases.
    2. Execute the dtasp_PurgeAllCompletedTrackingData stored procedure. For more information about the dtasp_PurgeAllCompletedTrackingData stored procedure, visit the following MSDN website:

      Note This action deletes all completed messages.

  • Method 2:
    1. Back up all BizTalk databases.
    2. Execute the dtasp_CleanHMData stored procedure. Only use this option if the BizTalkDTADb database contains many incomplete instances that must be removed.
      To do this, follow these steps:

      1. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
      2. Execute the dtasp_CleanHMData stored procedure on the BizTalkDTADb database.
      3. Restart all hosts and BizTalk Server services.
BizTalk Server 2004-only steps

To delete all data from the BizTalkDTADb database in BizTalk Server 2004, follow these steps.
NoteThis action deletes all completed messages.

  1. Back up all BizTalk Server databases.
  2. Stop all BizTalk hosts, services, and custom isolated adapters. If you use HTTP or the SOAP adapter, restart the IIS services.
  3. Install the hotfix in Microsoft Knowledge Base article 894253. Follow the steps in this Knowledge Base article to run the Bts_tracking_shrinkexistingdatabase.sql script in SQL Server 2000.
    For information about the dtasp_PruneTrackingdatabase stored procedure, click the following article number to view the article in the Microsoft Knowledge Base:

    894253 FIX: The dtasp_PruneTrackingdatabase() stored procedure may take many hours to clean up the DTA database in BizTalk Server 2004
  4. Restart all hosts and BizTalk services.

Note If you must have the tracking data, back up the BizTalkDTADb database, restore the database to another SQL Server, and then purge the original BizTalkDTADb database.
If you need help to analyze the MsgBoxViewer data or the PSSDiag output, contact Microsoft Customer Support Services. For a complete list of Customer Support Services telephone numbers and information about support costs, visit the following Microsoft website:

12/14/2011 Posted by | Sql Server | , , | 1 Comment

   

%d bloggers like this: