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.
- 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.
- H: Kerberos is required, and you must provide appropriate SPN's, see the SPN user guide for additional information
- I: Databases in AOAG must have the Full recovery mode. This will put additional pressure on your disk IO. Please review the Why Does the Transaction Log Keep Growing or Run Out of Space? article.
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
- 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
- E: The Logging Service automatically creates new databases according to the following System Parameters
- 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)
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.
D: We are not the only ones who do not recommend the use of BizTalk 2016 and SQL Server Always On Availability Groups Creation of Always on Availability Group for BizTalk 2016 environment using Microsoft SQL Server Management Studio (14.0.17224.0).
E: You must be using BizTalk Server 2016 CU5 or later to get a stable environment (CU5!!!! This proves that BizTalk 2016 was not designed to work with SQL Server Always-On Availability Groups from the ground-up)
Later versions of Microsoft BizTalk Server may very well support this better in future versions (Article date: 2019-03-22)