DB-유

Database Mirroring vs Always On Availability Groups

· mssql, high-availability, architecture

Database mirroring has been marked deprecated since SQL Server 2012, yet I still find it running on plenty of 2014 and 2016 instances because it was simple, worked on Standard Edition, and nobody had a reason to touch it. Always On Availability Groups (AGs) are the replacement, and since SQL Server 2016 the Basic Availability Group brings a mirroring-equivalent to Standard Edition. I keep getting asked "can't we just leave the mirror alone?", so here's my comparison on the points that matter operationally, followed by the minimal steps to stand up a Basic AG as a replacement.

How the two compare

Scope. Mirroring protects one database per session with one principal and one mirror. An AG protects a group of databases that fail over together, with one primary and up to eight secondaries in Enterprise Edition (Basic AGs: one database, one secondary, matching mirroring).

Cluster requirement. Mirroring needs no Windows cluster; a witness instance provides automatic failover. AGs require a Windows Server Failover Cluster (WSFC) for quorum and health detection, even though the storage is not shared. This is the biggest operational change: you now manage a cluster, its quorum and its votes. A file share witness replaces the SQL witness role.

Client redirection. Mirroring relies on the Failover Partner= connection string keyword, which clients cache and which does not survive a reconfiguration. AGs use a listener, a virtual network name and IP owned by the cluster, so the connection string is just Server=ShopDB-Listener.

Readable secondaries. A mirror is unusable except via database snapshots. Enterprise AG secondaries can be read directly (ApplicationIntent=ReadOnly with read-only routing) and can take backups. Basic AGs do not allow reads on the secondary, same as mirroring.

Synchronisation modes. Both offer synchronous (zero data loss) and asynchronous modes. AGs let you mix modes across replicas and, from 2017, set REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT.

Transport and encryption. Both use a database mirroring endpoint on TCP 5022 with certificate or Windows authentication. Log stream compression is on by default for asynchronous replicas in AGs.

Monitoring. Mirroring gives you sys.database_mirroring and the Database Mirroring Monitor. AGs have the Always On dashboard in SSMS, sys.dm_hadr_* DMVs, and Extended Events session AlwaysOn_health created automatically.

Standing up a Basic AG

What I start with: two domain-joined servers on SQL Server 2016 or 2017 Standard (same edition and build), Windows Server 2012 R2 or 2016 with Failover Clustering installed, databases in FULL recovery with a current full backup, and TCP 5022 open between the nodes.

Build the WSFC

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node sqlnode1, sqlnode2
New-Cluster -Name SQLCLU01 -Node sqlnode1, sqlnode2 -StaticAddress 10.0.0.60 -NoStorage
Set-ClusterQuorum -FileShareWitness \\fileserver\SQLCLU01-Witness

Read the Test-Cluster report. Warnings about storage are expected with -NoStorage; anything about networking or updates is not. I always use a file share witness on a third machine rather than a node majority with only two nodes.

Enable the Always On feature

On each node, in SQL Server Configuration Manager, open the service properties, Always On High Availability tab, tick Enable Always On Availability Groups, then restart the service. With PowerShell:

Enable-SqlAlwaysOn -ServerInstance sqlnode1 -Force
Enable-SqlAlwaysOn -ServerInstance sqlnode2 -Force

Endpoints

CREATE ENDPOINT Hadr_endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES);
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CORP\sqlservice];

Run on both nodes. If you are migrating from mirroring, the existing mirroring endpoint can be reused as-is.

The AG itself

On the primary:

CREATE AVAILABILITY GROUP ShopDB_AG
WITH (BASIC, AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR DATABASE ShopDB
REPLICA ON
  N'sqlnode1' WITH (ENDPOINT_URL = N'TCP://sqlnode1.corp.local:5022',
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC),
  N'sqlnode2' WITH (ENDPOINT_URL = N'TCP://sqlnode2.corp.local:5022',
      AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC);

On the secondary:

ALTER AVAILABILITY GROUP ShopDB_AG JOIN;
ALTER AVAILABILITY GROUP ShopDB_AG GRANT CREATE ANY DATABASE;

SEEDING_MODE = AUTOMATIC (2016 and later) streams the database over the endpoint, so no manual backup and restore is required, though for very large databases a restore WITH NORECOVERY followed by ALTER DATABASE ShopDB SET HADR AVAILABILITY GROUP = ShopDB_AG is faster and easier to monitor.

Listener

ALTER AVAILABILITY GROUP ShopDB_AG
ADD LISTENER N'ShopDB-LSN' (WITH IP ((N'10.0.0.61', N'255.255.255.0')), PORT = 1433);

The cluster account needs permission to create computer objects in the OU, or pre-stage the ShopDB-LSN computer object in Active Directory.

Is it healthy?

SELECT ag.name, ar.replica_server_name, drs.synchronization_state_desc, drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON drs.group_id = ag.group_id;

Both replicas should be SYNCHRONIZED and HEALTHY. Then test a manual failover from the secondary: ALTER AVAILABILITY GROUP ShopDB_AG FAILOVER; and confirm clients reconnect through the listener.

Errors

"Msg 35250: The connection to the primary replica is not active. The command cannot be processed." The endpoint on one side is not started, the port is blocked, or the service account lacks CONNECT on the endpoint. Check sys.database_mirroring_endpoints and the firewall.

"Msg 41158: Failed to join local availability replica to availability group." Usually the WSFC nodes cannot see each other, or the secondary's Always On feature is not enabled. Verify with SELECT SERVERPROPERTY('IsHadrEnabled');.

Listener creation fails with "The WSFC cluster could not bring the Network Name resource online." Almost always Active Directory permissions on the cluster name object. Grant it Create Computer Objects on the OU.

Basic AG refuses a second database. By design: one database per Basic AG. Create another AG or move to Enterprise Edition.

Mirroring still works, but I wouldn't build anything new on it. For Standard Edition shops a Basic AG is a near-direct replacement.