- 4 minutes to read

About SQL Server Always On Availability Groups

There are many steps and potential issues working with SQL Server Always On Availability Groups. This guide helps you learn about some of them...

First, read the Microsoft SQL Server and Nodinite user guide for Nodinite.

1. General reflections for SQL Server Always On Availability Groups

  • A: New databases are not automatically configured for synchronization you must either fix this by (not synchronized means NOT Always on!)
    • Manual administration
    • Add a SQL Job that detects new databases not yet configured to be synchronized
  • B: DACPAC deployments are not registered on the other nodes
    • You need to manually register/rerun the DACPAC on each node - Additional administration
  • C: Schema can not be changed when the database is synchronized (this means you need to break the Always-on functionality during updates)
  • D: Accounts are not replicated between nodes - Additional administration and potentially a very big problem after a fail-over when functions stop due to missing rights
  • E - Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

The point here is that the protection level is not necessarily better with AOAG (but probably at a higher cost)

  • F: SQL Jobs are are not replicated between nodes - Additional administration and potentially a very big problem after a fail-over when since functionality may be different
  • G: All SQL Jobs must be altered to honour the currently active replica - Additional administration, and potentially a problem, see 1E and Recommendations for BizTalk Logging - Always On
    • If this logic is not implemented, the jobs will fail on the non-active nodes
if (If sys.fn_hadr_backup_is_preferred_replica('databasename') =1)
BEGIN
    -- your SQL Agent job logic
END

Read more about the preferred backup replica here

  • H: Kerberos is required and you must provide appropriate SPN's, see the [SPN][] user guide for additional information

2. DTC

DTC is supposed to no longer be supported with AOAG, still many services within Nodinite and BizTalk still use the DTC. This means you MUST configure your BizTalk Server, Nodinite and SQL Server nodes with the following settings:

  • A: 1 Local DTC on each node (BizTalk, Nodinite, SQL) configured as described here
  • B: 1 clustered DTC for EACH AOAG configured as described here
  • C: 1 clustered DTC for BizTalk Server (if the BizTalk group is configured with multiple nodes) as described here

You will end up with many DTC instances that must share the same configuration

3. Nodinite and SQL Server Always On Availability Groups

  • A: Nodinite can not update the Log Databases or the Configuration Database using DACPAC part of the Install and Update tool due to databases being synchronized, review
  • B: You must use the AOAG cluster name as Server Name
  • C: The Log Database must have the Remote database option checked
    RemoteLogDatabase
  • D: The Logging Service automatically creates new databases according to the following System Parameters
    • SizeToSplitDatabaseOn - The maximum size (in GB) the active Log Database is allowed to reach until a new Nodinite Log Database should be created
    • DaysToSplitDatabaseOn - The number of days since the creation of the active Log databases before a new Log Database is created.
  • E: New databases are not automatically configured for synchronization review 1D
  • F: Nodinite Does not contain any SQL Jobs and therefore does not suffer from SQL Jobs not being replicated (hence not a problem)

4. BizTalk 2016 and SQL Server Always On Availability Groups

Setting up an Enterprise-grade Microsoft BizTalk Server is a very complex operation and unfortunately, most installations are not even supported due to bad configuration even though:

  • A: There are a plethora of guides (with ambiguous or even contradicting recommendations)
  • B: Many consultants interpret the documentation differently...
  • C: The product relies on proven other enterprise products like Windows, SQL Server, IIS, DTC and more

To begin with, the documentation for enabling the use of Microsoft BizTalk Server with SQL Server Always On Availability Groups is quite extended, find out for your self here

To mitigate the situation Samuel Kastberg (Senior premier field engineer (2019)) at Microsoft has written some how-to enable AOAG for BizTalk posts on his blog

Later versions of BizTalk Server, may very well support this better in future versions (this article was written 2019-03-22)


Next Step