Mise en place de SQL Server Always On Availability Group

Symptôme

Objectif : mettre en place le failover automatique et un réplica secondaire lisible pour SQL Server avec un Always On Availability Group (AG).

1. Mettez en place un Windows Server Failover Cluster (WSFC)

Always On repose sur un WSFC sous-jacent. Installez la fonctionnalité Failover Clustering sur tous les nœuds et configurez le réseau et le témoin. Créez le cluster avec 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. Activez Always On sur chaque instance

Activez la fonctionnalité Always On Availability Groups avec SQL Server Configuration Manager ou PowerShell. Redémarrez ensuite le service SQL Server. Cela lie l'appartenance au cluster à l'instance.

SQL Server

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

3. Sauvegardez la base et restaurez-la sur le secondaire WITH NORECOVERY

La base à ajouter à l'AG doit être en mode de récupération FULL. Effectuez une sauvegarde complète et une sauvegarde du log sur le primaire ; restaurez-les sur le secondaire WITH NORECOVERY (pour qu'il puisse continuer à recevoir le redo). C'est le seeding manuel ; l'alternative est l'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. Créez les endpoints et définissez l'Availability Group

Créez un endpoint HADR sur le port 5022 pour le trafic redo entre nœuds. Le mode synchrone (SYNCHRONOUS_COMMIT) assure un failover automatique sans perte de données ; le mode asynchrone (ASYNCHRONOUS_COMMIT) est décalé mais performant pour une DR distante.

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. Joignez le secondaire à l'AG et joignez la base

Faites un JOIN de l'AG sur l'instance secondaire, puis ajoutez la base restaurée à l'AG. Cette étape est nécessaire en cas de seeding manuel ; avec l'automatic seeding, elle est gérée sur le primaire via SET(SEEDING_MODE).

SQL Server

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

6. Créez l'Availability Group Listener

Le listener est le nom virtuel et l'IP auxquels se connectent les applications ; après un failover, les connexions sont redirigées automatiquement vers le nouveau primaire. Utilisez ce nom et MultiSubnetFailover=True dans la chaîne de connexion de l'application.

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. Testez le failover et vérifiez l'état de synchronisation

Lorsque la synchronisation est 'HEALTHY', testez un changement de rôle avec un failover planifié. Pour le failover automatique, le mode doit être synchrone et FAILOVER_MODE=AUTOMATIC. Surveillez l'état avec 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;