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.
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.
|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.
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.
Output of the report with some explanations.
One row for each availability group.
|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.|
One row for each replica in the availability group
|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.|
One row for each database in this AG and can include non-BizTalk databases.
|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|