Configuración de SQL Server Always On Availability Group

Síntoma

Objetivo: configurar failover automático y una réplica secundaria legible para SQL Server con un Always On Availability Group (AG).

1. Configure un Windows Server Failover Cluster (WSFC)

Always On se apoya en un WSFC subyacente. Instale la característica Failover Clustering en todos los nodos y configure la red y el testigo (witness). Cree el clúster con PowerShell.

SQL Server

-- PowerShell (the Failover-Clustering feature must be installed on every node):
New-Cluster -Name sqlclu -Node node1,node2 -StaticAddress 10.0.0.50
-- Witness:
Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\quorum

2. Habilite Always On en cada instancia

Habilite la característica Always On Availability Groups con SQL Server Configuration Manager o PowerShell. Después reinicie el servicio de SQL Server. Esto vincula la pertenencia al clúster con la instancia.

SQL Server

-- PowerShell:
Enable-SqlAlwaysOn -ServerInstance node1\SQL01 -Force
Enable-SqlAlwaysOn -ServerInstance node2\SQL01 -Force

3. Haga backup de la base de datos y restáurela en el secundario WITH NORECOVERY

La base de datos que se agregará al AG debe estar en modelo de recuperación FULL. Tome un backup completo y uno de log en el primario; restáurelos en el secundario WITH NORECOVERY (para que pueda seguir recibiendo redo). Esto es seeding manual; la alternativa es el automatic seeding.

SQL Server

-- On the primary:
BACKUP DATABASE app TO DISK='\\fs01\bak\app.bak';
BACKUP LOG app TO DISK='\\fs01\bak\app.trn';
-- On the secondary:
RESTORE DATABASE app FROM DISK='\\fs01\bak\app.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK='\\fs01\bak\app.trn' WITH NORECOVERY;

4. Cree los endpoints y defina el Availability Group

Cree un endpoint HADR en el puerto 5022 para el tráfico de redo entre nodos. El modo síncrono (SYNCHRONOUS_COMMIT) ofrece failover automático sin pérdida de datos; el asíncrono (ASYNCHRONOUS_COMMIT) tiene retraso pero rinde bien para DR remoto.

SQL Server

CREATE ENDPOINT hadr_endpoint STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION=REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP appag
  FOR DATABASE app
  REPLICA ON
    'NODE1\SQL01' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL)),
    'NODE2\SQL01' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL));

5. Una el secundario al AG y una la base de datos

Ejecute JOIN del AG en la instancia secundaria y luego agregue la base de datos restaurada al AG. Este paso es necesario si se usó seeding manual; con automatic seeding se gestiona en el primario mediante SET(SEEDING_MODE).

SQL Server

-- On the secondary:
ALTER AVAILABILITY GROUP appag JOIN;
ALTER DATABASE app SET HADR AVAILABILITY GROUP = appag;

6. Cree el Availability Group Listener

El listener es el nombre y la IP virtuales a los que se conectan las aplicaciones; tras un failover, las conexiones se redirigen automáticamente al nuevo primario. Use este nombre y MultiSubnetFailover=True en la cadena de conexión de la aplicación.

SQL Server

ALTER AVAILABILITY GROUP appag
  ADD LISTENER 'appag-listener' (WITH IP ((N'10.0.0.60', N'255.255.255.0')), PORT=1433);

7. Pruebe el failover y verifique el estado de sincronización

Cuando la sincronización esté 'HEALTHY', pruebe un cambio de rol con un failover planificado. Para el failover automático, el modo debe ser síncrono y FAILOVER_MODE=AUTOMATIC. Monitoree el estado con sys.dm_hadr_availability_replica_states.

SQL Server

SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ag.group_id=ars.group_id;
-- Planned failover (on the node that will become the new primary):
ALTER AVAILABILITY GROUP appag FAILOVER;