- 5 minutes to read

About SQL Server Always On Availability Groups

There are many steps and potential issues using Microsoft SQL Server Always On Availability Groups (AOAG). 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: A new database is not synchronized automatically; You must either fix this by (not synchronized means NOT Always on!):
    • Manual management
    • Add a SQL Job that detects new databases not yet configured to be synchronized
  • B: DACPAC deployments are not automatically registered on other SQL nodes part of the AOAG group
    • 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 do not replicate between nodes - Additional administration and potentially a huge 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 losing a data file, deleting a database, or corruption of the 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 huge problem after a fail-over when since functionality may be different
  • G: You must alter all SQL Jobs to honour the currently active replica - Additional administration, and potentially a problem, see 1E and Recommendations for BizTalk Logging - Always On
    • If you fail to implement this logic; the SQL jobs fails on 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.

2. DTC

DTC is supposed to be no longer supported with AOAG, still many services within Nodinite and BizTalk still use the DTC. This means you should 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: When updating, the Nodinite Install and Update Tool automatically updates the Configuration Database to the latest version.
  • B: The Nodinite Install and Update Tool never update the Log Databases. These must always be updated manually by executing the provided scripts. This problems is mainly due to the problem with lengthy updates; Orchestrating these update operations from the web interface would most often result in time outs.
    • In addition, when using AOAG, the synchronisation must be temporarily removed (manual or scripted). Then you can perform the update, and then re-synchronize the updated databases. There can be many Log Databases to update.
  • C: You must use the AOAG listener name as Server Name

NOTE: Nodinite does not support the use of alias names (DNS redirection)

  • D: The Log Database must have the Remote database option checked
    RemoteLogDatabase
  • E: 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.
  • F: New databases are not automatically configured for synchronisation review 1D
  • G: 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 yourself 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 Microsoft BizTalk Server may very well support this better in future versions (Article date: 2019-03-22)

Next Step