Who this is for: anyone running RDS for SQL Server Multi-AZ who’s assumed it works the same way under the hood as RDS for PostgreSQL or MySQL Multi-AZ, and wants to know what’s actually happening during a failover.
A colleague moving from managing RDS for PostgreSQL to a new SQL Server workload asked me a reasonable question: could we get a Multi-AZ DB cluster with a readable standby on SQL Server, the way we’d set one up on Postgres. The answer surprised him: Multi-AZ DB clusters don’t exist for SQL Server at all, and what RDS gives you instead is one of three completely different underlying replication technologies, chosen automatically based on your SQL Server version, none of which you get to pick directly.
Why This Matters
Multi-AZ DB clusters, the architecture with two readable standbys and sub-35-second failover, are only available for RDS for MySQL and RDS for PostgreSQL. SQL Server, along with Oracle, MariaDB, and Db2, doesn’t get that option; those engines are limited to classic Multi-AZ with a single, non-readable standby. But “classic Multi-AZ” isn’t one consistent mechanism for SQL Server the way it effectively is for other engines. RDS automatically selects between SQL Server Database Mirroring (DBM), Always On Availability Groups (AGs), or block-level replication, depending on which SQL Server version and edition you’re running. That choice matters because it affects real operational behavior, not just an internal implementation detail you can ignore.
Which mechanism you’re actually running
The version and edition cutoffs are specific. SQL Server 2008 R2 through 2016 (excluding a later Enterprise Edition build) and most of 2017 default to Database Mirroring for Multi-AZ. SQL Server 2016 Enterprise Edition, from build 13.00.5216.0.v1 onward, switched to Always On Availability Groups automatically. SQL Server 2017 Standard Edition later gained Always On AG support for Multi-AZ as well. If you’re not sure which one your instance is using, don’t guess from the SQL Server version alone; check directly.
-- Check whether the instance is using Always On Availability GroupsSELECT ag.name AS availability_group_name, ar.replica_server_name, ars.role_descFROM sys.availability_groups agJOIN sys.availability_replicas ar ON ag.group_id = ar.group_idJOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;
If that query returns rows, you’re on Always On AGs. If it returns nothing but Multi-AZ is enabled, you’re most likely on Database Mirroring or block-level replication instead, and it’s worth cross-checking your specific engine version against AWS’s documented cutoffs to know which.
Why this showed up as a real problem for us
We hit a genuinely practical version of this distinction on an Enterprise Edition instance running with in-memory OLTP tables enabled. Database Mirroring and in-memory optimization don’t play well together in some configurations, and AWS’s own guidance notes you may need to disable in-memory optimization before Multi-AZ can be added cleanly on an instance still using Mirroring. We’d assumed enabling Multi-AZ was a pure checkbox change; instead we hit a compatibility wall specifically because of which underlying mechanism our instance’s version had been assigned, not because of anything we’d configured wrong.
Migrating from Mirroring to Always On AGs
If you’re on an older build still using Database Mirroring and want the newer Always On AG behavior (generally the more modern, more capable option where it’s available), AWS provides a documented migration path rather than a simple flag flip.
aws rds modify-db-instance \ --db-instance-identifier sqlserver-primary \ --engine-version 15.00.4380.1.v1 \ --apply-immediately
Upgrading to an engine version where Always On AGs is the default mechanism triggers the underlying migration as part of the version change; it isn’t a setting you toggle independently of the engine version itself. Treat it with the same care as any other engine version upgrade: test it on a non-production instance first, and read AWS’s specific migration notes for your starting version rather than assuming it’s a transparent, zero-impact switch.
Where This Requires Care
You don’t get to directly choose Mirroring versus Always On AGs versus block-level replication. RDS selects based on version and edition, so the lever you actually have is which engine version you’re running, not a direct configuration switch.
If you need a Multi-AZ DB cluster with a readable standby and sub-35-second failover, that architecture simply isn’t available for SQL Server today. If that specific capability matters enough, it’s a reason to evaluate a different engine for a given workload, not something you can configure your way into on SQL Server.
In-memory OLTP features can conflict with Database Mirroring specifically; if you’re on an older build with in-memory optimization enabled, check compatibility before enabling or modifying Multi-AZ rather than assuming it’s a clean toggle.
None of these three mechanisms are surfaced clearly in the RDS console as a named setting; you have to go looking for which one you’re on, as shown above, rather than expecting it to be labeled for you.
Quick Reference
- Multi-AZ DB clusters (two readable standbys, sub-35-second failover) are MySQL and PostgreSQL only; SQL Server always uses classic single-standby Multi-AZ.
- RDS automatically picks Database Mirroring, Always On Availability Groups, or block-level replication for SQL Server Multi-AZ based on version and edition; you don’t configure this directly.
- Check sys.availability_groups and related DMVs to confirm which mechanism your instance is actually using rather than guessing from the SQL Server version alone.
- In-memory OLTP and Database Mirroring can conflict on older builds; check compatibility before enabling Multi-AZ on an instance using in-memory optimization.
- Moving from Mirroring to Always On AGs happens via an engine version upgrade, not an independent setting; treat it like any other major operational change.
My Take
This is one of those details that doesn’t matter at all until the moment it does, usually during an unexpected compatibility error or a colleague’s reasonable question you can’t immediately answer. Knowing which mechanism a given instance is running, and that it’s determined by version rather than a setting, would have saved us the surprise on the in-memory OLTP instance. If you’re running RDS for SQL Server Multi-AZ today and can’t say off the top of your head which of the three mechanisms you’re on, that’s a five-minute query worth running before you need the answer during an incident.
