Configuração do SQL Server Always On Availability Group

Sintoma

Objetivo: configurar failover automático e uma réplica secundária legível para o SQL Server com um Always On Availability Group (AG).

1. Configure um Windows Server Failover Cluster (WSFC)

O Always On depende de um WSFC subjacente. Instale o recurso Failover Clustering em todos os nós e configure a rede e a testemunha (witness). Crie o cluster com 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. Ative o Always On em cada instância

Ative o recurso Always On Availability Groups com o SQL Server Configuration Manager ou PowerShell. Depois reinicie o serviço do SQL Server. Isso vincula a participação no cluster à instância.

SQL Server

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

3. Faça backup do banco e restaure-o no secundário WITH NORECOVERY

O banco a ser adicionado ao AG precisa estar no recovery model FULL. Faça um backup completo e um de log no primário; restaure-os no secundário WITH NORECOVERY (para que continue recebendo redo). Isso é seeding manual; a alternativa é o 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. Crie os endpoints e defina o Availability Group

Crie um endpoint HADR na porta 5022 para o tráfego de redo entre os nós. O modo síncrono (SYNCHRONOUS_COMMIT) oferece failover automático sem perda de dados; o assíncrono (ASYNCHRONOUS_COMMIT) tem atraso, mas é performático 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. Junte o secundário ao AG e junte o banco

Faça o JOIN do AG na instância secundária e depois adicione o banco restaurado ao AG. Esta etapa é necessária se foi usado seeding manual; com automatic seeding, é tratada no primário via SET(SEEDING_MODE).

SQL Server

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

6. Crie o Availability Group Listener

O listener é o nome e o IP virtuais aos quais as aplicações se conectam; após um failover, as conexões são redirecionadas automaticamente para o novo primário. Use esse nome e MultiSubnetFailover=True na connection string da aplicação.

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. Teste o failover e verifique o estado de sincronização

Quando a sincronização estiver 'HEALTHY', teste uma troca de papéis com um failover planejado. Para failover automático, o modo deve ser síncrono e FAILOVER_MODE=AUTOMATIC. Monitore o estado com 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;