Validate your BizTalk Availability Groups

An installation using SQL Server Always On Availability Groups synchronizes the databases between replicas. It is important that the databases in the different replicas are synchronized. Since transactional consistence is a priority for BizTalk Server Synchronous commit is a requirement. SQL Server 2016 introduced support for distributed transactions but only at the database level. BizTalk Server databases must be separated into at least 4 availability groups with databases divided based on how BizTalk uses distributed transactions internally. Other constraints are DTC support enabled, Synchronous commit used for all replicas and backup preference must be set to primary. When we do health checks we collect information and validate the results. To get an overview of the involved availability groups we have a PowerShell script to collect information and do most of the validations.

The script

Run the script AG-Report.ps1. On a BizTalk Server, the script can get group information from Registry, from another server send parameters.

Download the script:

AG-Report-1.1.0  Version 1.1.0 Add functionality to take into account SQL Server 2016 SP2 and BizTalk 2016 CU5 improvements.

AG-Report-1.0.9  Version 1.0.9 fixes an issue when using ports as part of the litener name.

Parameter Comment Required
outputFolder The folder where the file aginfo_yyyyMMddhhss.html will be saved. Yes
mgmtServer The name of BizTalk Management listener. If you don’t provide this parameter management server and DB will be retrieved from “HKLM:\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration” No
mgmtDatabase The name of BizTalk Management database, default “BizTalkMgmtDb” No

The data collection report is saved to a html file.

As always:

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.

The report

Output of the report with some explanations.

AG-Report

Availability group

One row for each availability group.

Column Description
Name Name of the availability group
ListenerName Network name of the availability group
Health Health status of synchronization
IpFromCluster Address information from the cluster
DtcSupport Must be true and tells if the AG is configured to support distributed transactions
BackupPreference Must be primary and tells on which replicas backups are taken.

Replicas

One row for each replica in the availability group

Column Description
ServerName Local servername of the replica
Role Current role in the availability group
Health Health status of synchronization for this replica
Mode Tells how data is synchronized for this replica. Must be synchronous commit
MDOP MDOP setting for this replica
LinkedServers List of linked servers for this replica. This setting should be the same on all replicas in the group but might be different if the instance is used for other purposes. A diff renders a warning.
JobsPrimaryCheckFail Checks if the SQL type steps have the if statement to ensure that the step will only run on the primary replica. OK or a list of jobs failing.
SecondaryRead Behavior of this replica when being in secondary role. ALL means readable.

Databases

One row for each database in this AG and can include non-BizTalk databases.

Column Description
DatabaseName Name of the database
Status Online status of the database
Health Health status of synchronization for this database
IsSuspended Tells if synchronization is suspended for this database. Generally, it should be False
IsJoined Has the database started to synchronize? Should be True

Author: skastberg

Integration consultant working as Premier Field Engineer at Microsoft Sweden. My opinions only, most certainly not necessarily those of my employer.

2 thoughts on “Validate your BizTalk Availability Groups”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s