1. Comparison
| Failover Cluster Instance (FCI) | Availability Group (AG) | |
|---|---|---|
| Protects | The instance (all databases, logins, jobs) | A group of databases |
| Storage | Shared (SAN, S2D, Azure shared disks, SMB 3) | Each replica has its own copy |
| Data redundancy | One copy of the data; storage is the single point of failure | 2+ copies |
| Failover time | Instance restart on another node (often 30 s–2 min, plus recovery) | Usually seconds |
| Readable secondaries | No | Yes (Enterprise) |
| Instance objects (logins, jobs, linked servers) | Move with the instance automatically | Must be synced yourself (or use contained AGs in 2022+) |
| Standard Edition | 2-node FCI | Basic AG: 1 database per AG, 2 replicas, no reads |
| DR | Combine with an AG or log shipping | Add an async replica in another site |
Rule of thumb: choose an AG when you want data redundancy and fast failover. Choose an FCI when you need instance-level failover, many databases, or have proven shared storage. Both can be combined (an FCI as an AG replica).
2. Build a two-replica AG
Prerequisites: both nodes in a WSFC cluster with a cloud or file share witness, the same SQL Server version and CU, and databases in the FULL recovery model with a full backup taken.
Enable-DbaAgHadr -SqlInstance sql-prd-01, sql-prd-02 -Force
-- on both: endpoint for data movement
CREATE ENDPOINT Hadr_endpoint STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
-- on the primary
CREATE AVAILABILITY GROUP AG_Sales
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON,
CLUSTER_TYPE = WSFC, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0)
FOR DATABASE Sales
REPLICA ON
'SQL-PRD-01' WITH (ENDPOINT_URL = 'TCP://sql-prd-01.corp.local:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC),
'SQL-PRD-02' WITH (ENDPOINT_URL = 'TCP://sql-prd-02.corp.local:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP AG_Sales
ADD LISTENER 'sql-sales' (WITH IP ((N'10.20.1.50', N'255.255.255.0')), PORT = 1433);
-- on the secondary
ALTER AVAILABILITY GROUP AG_Sales JOIN;
ALTER AVAILABILITY GROUP AG_Sales GRANT CREATE ANY DATABASE;
Applications connect to sql-sales with MultiSubnetFailover=True.
3. Keep instance objects in sync
Copy-DbaLogin -Source sql-prd-01 -Destination sql-prd-02 -ExcludeSystemLogins
Copy-DbaAgentJob -Source sql-prd-01 -Destination sql-prd-02
Or use a contained AG (SQL Server 2022+), which carries its own master and msdb for the AG's logins and jobs.
Jobs should check whether they are running on the primary: IF sys.fn_hadr_is_primary_replica('Sales') = 1.
4. Health checks
SELECT ag.name, ar.replica_server_name, drs.database_id,
drs.synchronization_state_desc, drs.log_send_queue_size, drs.redo_queue_size
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 ar.group_id = ag.group_id;
Alert on NOT SYNCHRONIZING, and on large send or redo queues.
5. Test failover
ALTER AVAILABILITY GROUP AG_Sales FAILOVER; -- run on the target secondary (no data loss)
Test it quarterly, along with a full app reconnect test.
Tip
Most AG "outages" are actually the cluster losing quorum. Use a witness, keep an odd number of votes, and don't let a single network switch decide quorum.