- 11 minutes to read

SQL Server Always On Availability Groups: High Availability Reality Check

The High Availability Promise vs Operational Reality

Many organizations adopt SQL Server Always On Availability Groups (AOAG) expecting "automatic high availability" with near-zero downtime. The reality is more nuanced—AOAG delivers powerful failover capabilities, but at the cost of significant operational complexity that catches teams off guard:

  • "Automatic" is Misleading - New databases aren't synchronized automatically; you must manually add them to the availability group or script detection logic
  • Schema Changes Break Synchronization - Database updates require breaking AOAG, applying changes, then re-synchronizing—adding complexity to every deployment
  • Accounts Don't Replicate - SQL logins, jobs, and permissions exist only on the primary node, causing failures after failover when secondary nodes lack required accounts
  • Jobs Run on Wrong Nodes - SQL Agent jobs execute on all replicas unless coded with sys.fn_hadr_backup_is_preferred_replica() checks, causing duplicate operations or failures
  • DACPAC Deployments Aren't Replicated - Schema deployment tools must be manually run on each node, defeating the purpose of "automatic" synchronization
  • Additional DTC Instances Required - Each AOAG group needs its own clustered DTC, multiplying configuration complexity (see Transactions MSDTC)
  • Kerberos SPN Requirements - All nodes, cluster names, and listener names need proper Service Principal Names registered for authentication

The Hidden Cost: Teams often discover these limitations during production deployments or after their first unplanned failover, when "secondary" nodes fail to take over cleanly due to missing accounts, jobs, or permissions.

Key Benefits (When Properly Configured)

Despite the complexity, AOAG provides genuine high availability value when teams understand and manage the operational requirements:

  • ✅ Automatic Failover - Minimizes downtime for hardware failures or planned maintenance (when accounts/jobs replicated manually)
  • ✅ Read-Scale Out - Secondary replicas can serve read-only queries, offloading report workloads from production databases (requires Readable secondary: Yes configuration on each replica) Availability replica properties with Readable secondary set to Yes
    Enable Readable Secondary to allow read-only queries on secondary replicas
  • ✅ Geographic Redundancy - Replicas can span data centers for disaster recovery scenarios
  • ✅ Rolling Updates - Patch secondary nodes first, fail over, then patch former primary with minimal downtime
  • ✅ Backup Offloading - Run backups against secondary replicas to reduce I/O impact on primary server

Bottom Line: AOAG is enterprise-grade technology that requires enterprise-grade operational discipline. This guide helps you understand the manual steps, avoid common pitfalls, and maintain stability for mission-critical Nodinite and BizTalk environments.

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.

How Nodinite Uses an Availability Group

The Core Services reach the Configuration Database through the listener; the CLI creates and updates it when you install or update Nodinite. The Logging Service creates every Log Database, starting with the first one, and works on the primary replica by its node name, not the listener. It registers each new Log Database with the primary's node name as its server.

On each run, the Logging Service checks the SQL Server that hosts the Configuration Database. It treats the setup as an availability group when HADR is enabled (SERVERPROPERTY('IsHadrEnabled') = 1) on SQL Server 2016 or later, and then reads the group, the listener, and every replica with its seeding mode.

flowchart LR subgraph CS["fa:fa-server Nodinite Core Services Server"] Core["fa:fa-globe Core Services"] LS["fa:fa-hard-drive Logging Service"] end Listener["fa:fa-signs-post AG Listener"] subgraph AG["fa:fa-server Always On Availability Group"] Primary["fa:fa-database Primary replica
Configuration and Log Databases
FULL recovery"] Secondary["fa:fa-database Secondary replicas"] end Share["fa:fa-folder-open Backup share
AvailabilityGroupBackupPath"] Core -->|"Connection string with listener name"| Listener Listener --> Primary LS -->|"Creates Log Databases on the primary node name"| Primary Primary -->|"Backup .bak and .trn"| Share Share -->|"Restore, manual seeding only"| Secondary Primary -.->|"AG synchronization or automatic seeding"| Secondary style Listener fill:#2678BD style Primary fill:#93d16d style Secondary fill:#3F827B style Share fill:#FFD525 style LS fill:#E47528

Diagram: the Core Services reach the Configuration Database through the listener (set in the SqlServer System Parameter and the connection string), while the Logging Service (orange) creates new Log Databases on the primary replica by its node name. Each new database is backed up to the AvailabilityGroupBackupPath share (yellow), and secondaries with manual seeding restore it from there. The ports between the replicas are on the Firewall page.

How the Logging Service Creates a Log Database in an Availability Group

flowchart TD A["fa:fa-box 1. Deploy the Log Database DACPAC
on the primary, by node name"] --> B["fa:fa-life-ring 2. Set the recovery model to FULL
overrides LogDatabaseRecovery"] B --> C["fa:fa-floppy-disk 3. Back up the database and its log
to AvailabilityGroupBackupPath"] C --> D["fa:fa-plus 4. Add the database to the
availability group on the primary"] D --> E{"Any secondary with
manual seeding?"} E -->|"Yes"| F["fa:fa-rotate 5. Restore database and log
on each secondary, NORECOVERY"] F --> G["fa:fa-link 6. Join each secondary
to the availability group"] E -->|"No"| H["fa:fa-seedling SQL Server seeds
the secondaries"] G --> I["fa:fa-tag 7. Register the DACPAC version
on every replica"] H --> I I --> J["fa:fa-check 8. Verify the database on the primary"] J --> K["fa:fa-user-shield 9. Grant the service users their roles
on the primary; AG sync carries them"] style A fill:#2678BD style B fill:#2678BD style C fill:#2678BD style D fill:#2678BD style E fill:#FFD525 style F fill:#E47528 style G fill:#E47528 style H fill:#3F827B style K fill:#93d16d

Diagram: the steps on the primary (blue), the extra restore and join for secondaries with manual seeding (orange), automatic seeding by SQL Server (teal), and the finished database (green). The recovery model is always FULL, whatever LogDatabaseRecovery says; the backup goes to AvailabilityGroupBackupPath; the service users in LogServiceUsers get the roles in LogAccessRoles. Registering the DACPAC version on every replica keeps Log Database updates working after a failover.

  • The restore on the secondaries starts 2.5 seconds after the database is added to the group, a fixed delay, so the backup files must be readable on the share as soon as the backup finishes.
  • To decide when to create the next Log Database, the Logging Service checks the size on the primary replica through a linked server from the Configuration Database server.
  • The Logging Service never removes a Log Database from the availability group. When you retire one, remove it yourself.

DBA and Installer Prerequisites

Prerequisite Details
AvailabilityGroupBackupPath A UNC share; empty by default. The SQL Server service account on the primary needs write access, and the SQL Server service accounts on every secondary need read access.
Logging Service account A Windows-authenticated login on every replica, with rights to create, back up, and restore databases, run ALTER AVAILABILITY GROUP and ALTER DATABASE … SET HADR, and write the DAC registration in msdb. In practice sysadmin or an equivalent custom role; see SQL Server Sysadmin Requirements.
Linked Servers From the Configuration Database server to the node name of every replica, not only the current primary, so the size checks and stored procedures keep working after a failover.
Automatic seeding Run ALTER AVAILABILITY GROUP [ag] GRANT CREATE ANY DATABASE on each secondary. Without it the secondaries never receive the database; Nodinite doesn't do this for you.
Logins for the service users The logins of the LogServiceUsers must exist on every replica, with matching SIDs for SQL logins, so the database users work after a failover.

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.
    • Nodinite Log Databases are the exception: the Logging Service adds them to the group itself, see How the Logging Service Creates a Log Database.
  • 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 - This is additional administration for you.
    • You need to perform a manual update if you see 0.0.0.0 as the registered DACPAC version.
    • For new Log Databases, the Logging Service registers the DACPAC version on every replica itself.
  • C: Schema cannot be changed when the database is synchronized (you must break Always On during updates)
  • D: Accounts do not replicate between nodes - Additional administration and potentially a massive problem after a failover when functions stop due to missing rights; see the logins in the prerequisites
  • 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 not replicated between nodes - Additional administration and potentially a massive problem after a failover when functionality may be different
  • G: You must alter all SQL Jobs to honor 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 fail on non-active nodes
if (sys.fn_hadr_backup_is_preferred_replica('databasename') = 1)
BEGIN
    -- your SQL Agent job logic
END

Read more about the preferred backup replica in the Microsoft documentation.

  • H: Kerberos is required, and you must provide appropriate SPNs, see the SPN user guide for additional information
  • I: AOAG enabled Databases 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.
    • The Logging Service creates new Nodinite Log Databases in an availability group with FULL, whatever the LogDatabaseRecovery system parameter says

2. DTC

DTC is supposed to be no longer supported with AOAG, many services within Nodinite and BizTalk 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 in the MSDTC documentation
  • B: 1 clustered DTC for EACH AOAG configured as described in the MSDTC documentation
  • C: 1 clustered DTC for BizTalk Server (if the BizTalk group is configured with multiple nodes) as described in the MSDTC documentation

Info

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

3. Nodinite and SQL Server Always On Availability Groups

  • A: When you update Nodinite, the CLI in the generated package (run in elevated PowerShell 7) updates the Configuration Database to the latest version.
  • B: The CLI never updates the Log Databases. You update them with the Update Log Database guide in the Web Client, which generates the scripts to run. This is mainly due to lengthy updates; orchestrating them from the web interface would most often result in timeouts.
    • In addition, when using AOAG, the synchronization 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 the Server Name; in the SqlServer System Parameter, put the listener name in the Server property, and use it in the connection string (with MultiSubnetFailover=True). The Linked Servers go to the node name of every replica, see the prerequisites

    Note

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

  • D: The Log Database must have the Remote database option checked
    Log Database settings with the Remote option checked
    Enable the Remote option for Log Databases in AOAG
  • E: The Logging Service automatically creates new databases, including the first one, according to the following System Parameters
    • LogDatabaseRecovery - The recovery model for new Log Databases; in an availability group the Logging Service always uses FULL
    • 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.
    • AvailabilityGroupBackupPath - The network share for the backup the Logging Service takes of each new Log Database; secondaries with manual seeding restore from it. It starts empty; set it in the Web Client
  • F: The Logging Service adds each new Log Database to the availability group, and restores and joins the secondaries that use manual seeding; see How the Logging Service Creates a Log Database. It never removes a Log Database from the group
  • 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; learn more in Microsoft's BizTalk AOAG documentation

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.

Next Steps