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.

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