SQL Server 2016 – Groupes de disponibilité distribués

Publié le 27 septembre 2017 par Christophe

Depuis SQL Server 2012 il set possible de créer des groupes de disponibilité, un mix plutôt intéressant enter le failover cluster (un nom réseau et une adresse IP gérée par le Cluster Windows, tout comme la bascule automatique en cas de panne sur le primaire) et le database mirroring apparu avec SQL Server 2005 qui permet de s’affranchir de point de défaillance unique que constitue le stockage dans un failover cluster. Ajouter un peu de log shipping pour autoriser la lecture de données sur un serveur secondaire et vous obtenez les groupes de disponibilités, solution de haute disponibilité, mais de disaster recovery également.

Initialement prévu pour fonctionner avec 1 réplica primaire (pour la lecture écriture) et 4 réplicas secondaires, rapidement s’est imposée l’idée qu’il fallait ajouter des réplicas secondaires, 8 à l’heure actuelle.

Les réplicas secondaires peuvent être accessibles en lecture seule, soit directement, soit au travers du listener qui permet de se connecter au “groupe de bases”. Si l’on spécifie l’application intent à ReadOnly, alors les routing list nous aiguillent vers un réplica secondaire. Depuis SQL Server 2016, il existe également la possibilité de disposer d’un load balancing afin de distribuer la charge sur des réplicas secondaires. Initialement 2 réplicas pouvaient êter choisis opur assurer al haute disponibilité au travers d’un basculement automatique. 3 réplicas sont possibles aujourd’hui. Le basculement automatique n’est autorisé que si le réplica est en mode synchrone, c’set à dire que la commit sur le serveur principal n’est effectif que lorsque les données ont été écrites physiquement dans le journal de transaction du (ou des) réplicas secondaires. cela permet de s’assurer d’une perte de données nulle (RPO = 0). Par conter, cela implique un peu de latence sur les transactions. Si l’on souhaite privilégier la performance, il set possible d’opter pour un mode asynchrone. Dans ca cas, le commit sur le réplica principal n’attend pas l’acquittement des serveur secondaires.

Initialement réservée à l’édition entreprise, SQL Server 2016 a permis une évolution du database mirroring vers les groupes de disponibilité basiques accessibles depuis l’édition standard

Les groupes de disponibilité ont sans conteste possible été une réussite, au vu du nombre d’implémentation que j’ai pu réaliser chez mes clients, tout comme les différentes sessions que j’ai pu animer aux TechDays, Journées SQLServer, SQLSaturday, …

Principalement utilisée pour offrir une stratégie de haute disponibilité, il est tout a fait possible de l’utiliser dans un cadre de disaster recovery avec des réplicas sur un second site ou bien dans Azure. Cela fonctionne parfaitement.

Mais l’affaire se corse un peu lorsque il est nécessaire de disposer d’une résilience totale de site avec du multi maitre, a savoir que chaque site doit pouvoir héberger des bases actives en lecture écriture, même en cas de coupure de lien réseau inter-site. C’est là que les groupes de disponibilité distribués prennent tout leur intérêt.

Voici donc une reproduction d’un cas client mis en œuvre à la fin de l’été. Désolé pour le retard de post, je n’ai pas eu le temps de rédiger l’article plus tôt. Et je tien également à m’excuser auprès de Jonathan Kehayias (SQLskills) qui a publié dans la newsletter de ce mois ci une vidéo sur les Distributed availability Groups. Désolé pour cette malheureuse collision de calendriers.

L’idée principale était de pouvoir disposer de X databases dans un groupe de disponibilité sur le site 1. Sur ce site, un second réplica, synchrone, assurait la haute disponibilité. Deux autres réplicas, sur un site de secours auraient pu assurer la partie disaster recovery. Donc un AG réparti sur les 4 instances SQL aurait pu faire l’affaire. Sauf que sur le second site, il me fallait aussi disposer d’un autre AG, avec d’autres bases. Jusque là, pas de problèmes au niveau de la configuration, le site 1 pouvait également faire office de disaster recovery pour ce second AG.

Malheureusement, en cas de coupure réseau entre les sites, sur ce cluster 4 nœuds, il fallait choisir où poser le dernier vote, communément appelé Quorum. Si le partage réseau (File Share Witness) se trouve sur le site 1, l’AG du site 2 devient indisponible. Nous n’avions pas non plus la possibilité d’utiliser un Quorum Azure, possibilité offerte depuis Windows 2016.

Restait donc une nouveauté de SQL Server 2016 : les groupes de disponibilité distribués. Cerise sur le gâteau, cela permet également de réduire le trafic réseau intersites, car seul le primaire du site 1 envoie les journaux sur le primaire du site 2.

Sur un premier hyperviseur j’ai donc créé 1 VM Active Directory et 2 VMs SQL Server. Je passe volontairement sur le création d’une VM, l’installation de SQL Server en mode StandAlone avec compte managés en compte de service pour le moteur, rien de particulier de ce côté là. Idem pour l’AD, rien de neuf.

image

Sur un second hyperviseur, 3 VMs également. Un AD, qui est donc le second contrôleur de domaine de mon domaine de test.

image

Attention, soyez patient, la réplica intersites entre des contrôleurs de domaine n’est pas immédiate ! Ou alors vous al forcez sur le NTDS …

image

image

Ce qui donne côté DNS :

  • Site1 :
    • DC1-AD : 10.0.1.10
    • DC1-SQL1 : 10.0.1.11
    • DC1-SQL2 : 10.0.1.1
  • Site2 :
    • DC2-AD : 10.0.2.10
    • DC2-SQL3 : 10.0.2.13
    • DC2-SQL4 : 10.0.2.14

image

Etant donné que l’on cherche de la résilience de site, l’étape suivant consiste à créer un Cluster, au sens Windows du terme, sur chaque site.

Un petit script PowerShell permet de réaliser cette opération rapidement. Notez au passage le –NoStorage sur la commande de création du cluster. C’est important. Au même titre que lors d’un ajout de nœud dans la configuration cluster.

Install-WindowsFeature -Name Failover-Clustering -IncludeAllSubFeature -IncludeManagementTools -ComputerName DC1-SQL1
Install-WindowsFeature -Name Failover-Clustering -IncludeAllSubFeature -IncludeManagementTools -ComputerName DC1-SQL2
Install-WindowsFeature -Name Failover-Clustering -IncludeAllSubFeature -IncludeManagementTools -ComputerName DC2-SQL3
Install-WindowsFeature -Name Failover-Clustering -IncludeAllSubFeature -IncludeManagementTools -ComputerName DC2-SQL4


Test-Cluster -Node DC1-SQL1,DC1-SQL2
New-Cluster -Name DC1-WSFC -Node DC1-SQL1,DC1-SQL2 -NoStorage -StaticAddress 10.0.1.20
Get-Cluster  | Set-ClusterQuorum -FileShareWitness "\\DC1-AD1\FSW" 


Test-Cluster -Node DC2-SQL3,DC2-SQL4
New-Cluster -Name DC2-WSFC -Node DC2-SQL3,DC2-SQL4 -NoStorage -StaticAddress 10.0.2.20
Get-Cluster  | Set-ClusterQuorum -FileShareWitness "\\DC2-AD2\FSW"

Une fois terminé nous obtenons 2 clusters, totalement indépendants l’un de l’autre, chacune pouvant supporter des groupes de disponibilités “locaux”.

imageimage

Une fois les cluster “locaux” créés il est possible d’activer la fonctionnalité AlwaysOn Availability Groups sur les instances SQL.

# DC1
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\DC1-SQL1\DEFAULT -Force
Restart-Service -InputObject $(Get-Service -Computer DC1-SQL1 -Name "MSSQLSERVER") -Force

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\DC1-SQL2\DEFAULT -Force
Restart-Service -InputObject $(Get-Service -Computer DC1-SQL2 -Name "MSSQLSERVER") -force

# DC2
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\DC2-SQL3\DEFAULT -Force
Restart-Service -InputObject $(Get-Service -Computer DC2-SQL3 -Name "MSSQLSERVER") -Force

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\DC2-SQL4\DEFAULT -Force
Restart-Service -InputObject $(Get-Service -Computer DC2-SQL4 -Name "MSSQLSERVER") -force

Je passe sur l’ouverture des ports sur les firewall locaux des serveurs (port 5022 par défaut mais c’est tout a fait paramétrable).

L’étape suivante consiste à mettre en place la tuyauterie permettant à tous les instances de dialoguer les une avec les autres. Cela passe par la création de point de terminaison, la création de Logins correspondants aux comptes de service des instances et aux autorisation de connexion sur ces points de terminaison.


# create the endpoints on SQL Server for DBM and AG
$endpoint = New-SqlHadrEndpoint MirroringEndpoint -Port 5022 -Path SQLSERVER:\SQL\DC1-SQL1\DEFAULT
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"

$endpoint = New-SqlHadrEndpoint MirroringEndpoint -Port 5022 -Path SQLSERVER:\SQL\DC1-SQL2\DEFAULT
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started" 

$endpoint = New-SqlHadrEndpoint MirroringEndpoint -Port 5022 -Path SQLSERVER:\SQL\DC2-SQL3\DEFAULT
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"

$endpoint = New-SqlHadrEndpoint MirroringEndpoint -Port 5022 -Path SQLSERVER:\SQL\DC2-SQL4\DEFAULT
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started" 



# create the login and grant the service account on the endpoints
$tSQL = "CREATE LOGIN [demo\DC1-SQL1$] FROM WINDOWS;"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "CREATE LOGIN [demo\DC1-SQL2$] FROM WINDOWS;"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "CREATE LOGIN [demo\DC2-SQL3$] FROM WINDOWS;"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "CREATE LOGIN [demo\DC2-SQL4$] FROM WINDOWS;"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"


$tSQL = "GRANT CONNECT ON ENDPOINT::[MirroringEndpoint] TO [demo\DC1-SQL1$];"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "GRANT CONNECT ON ENDPOINT::[MirroringEndpoint] TO [demo\DC1-SQL2$];"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "GRANT CONNECT ON ENDPOINT::[MirroringEndpoint] TO [demo\DC2-SQL3$];"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "GRANT CONNECT ON ENDPOINT::[MirroringEndpoint] TO [demo\DC2-SQL4$];"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"

Maintenant que les instances sont capables de dialoguer les une avec les autres, il ne reste qu’à créer des groupes de disponibilité sur chaque cluster.

Encore une fois, rien de très compliqué, mais les choses doivent être faites dans l’ordre. Notez dans le script suivant l’utilisation du paramètre SEEDING_MODE = AUTOMATIC. Encore une nouveauté liée à SQL Server 2016 qui permet d’initialiser les bases sur le serveur secondaire d’un AG de manière automatique sans procéder à une restore de la base suivi du journal de transaction. C’est la raison pour laquelle le groupe de disponibilité reçoit la permission CREATE DATABASE .


# AG on site 1
$tSQL = "
CREATE AVAILABILITY GROUP [DC1-AG]
FOR REPLICA ON 
'DC1-SQL1' 
    WITH (   ENDPOINT_URL = 'TCP://DC1-SQL1.demo.local:5022', 
             AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
             FAILOVER_MODE = AUTOMATIC,
             SEEDING_MODE = AUTOMATIC  ),
'DC1-SQL2' 
    WITH (   ENDPOINT_URL = 'TCP://DC1-SQL2.demo.local:5022', 
             AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
             FAILOVER_MODE = AUTOMATIC,
             SEEDING_MODE = AUTOMATIC )
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"

# grant the AG to create a database
$tSQL = "ALTER AVAILABILITY GROUP [DC1-AG] GRANT CREATE ANY DATABASE"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"


# join the seconday node and also grant create database
$tSQL = "
ALTER AVAILABILITY GROUP [DC1-AG] JOIN
ALTER AVAILABILITY GROUP [DC1-AG] GRANT CREATE ANY DATABASE
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL2"

New-SqlAvailabilityGroupListener -Name "DC1-AG-VIP" -StaticIp "10.0.1.30/255.0.0.0" -Path "SQLSERVER:\Sql\DC1-SQL1\DEFAULT\AvailabilityGroups\DC1-AG"

image


# AG on site 2
$tSQL = "
CREATE AVAILABILITY GROUP [DC2-AG]
FOR REPLICA ON 
'DC2-SQL3' 
    WITH (   ENDPOINT_URL = 'TCP://DC2-SQL3.demo.local:5022', 
             AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
             FAILOVER_MODE = AUTOMATIC,
             SEEDING_MODE = AUTOMATIC  ),
'DC2-SQL4' 
    WITH (   ENDPOINT_URL = 'TCP://DC2-SQL4.demo.local:5022', 
             AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
             FAILOVER_MODE = AUTOMATIC,
             SEEDING_MODE = AUTOMATIC )
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"

# grant the AG to create a database
$tSQL = "ALTER AVAILABILITY GROUP [DC2-AG] GRANT CREATE ANY DATABASE"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"


# join the seconday node and also grant create database
$tSQL = "
ALTER AVAILABILITY GROUP [DC2-AG] JOIN
ALTER AVAILABILITY GROUP [DC2-AG] GRANT CREATE ANY DATABASE
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

New-SqlAvailabilityGroupListener -Name "DC2-AG-VIP" -StaticIp "10.0.2.30/255.0.0.0" -Path "SQLSERVER:\Sql\DC2-SQL3\DEFAULT\AvailabilityGroups\DC2-AG"

image

Il ne reste qu’à présent qu’à ajouter une base dans le groupe de disponibilité. L’auto seeding est une avancée majeure pour les groupes de disponibilité mais cela ne dispense pas du prérequis BACKUP DATABASE. Please, ne pas faire de backup vers NUL en production !!!


# add a database into the AG to verify all is working fine
$Database = "DemoDB01"
$tSQL = "
CREATE DATABASE [$Database];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"


# Dummy backup to fake the controls for addinf a DB into an AG
# Do not run on a production environment
$tSQL = "
BACKUP DATABASE [$Database] TO DISK = 'NUL';
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"


$tSQL = "
ALTER AVAILABILITY GROUP [DC1-AG]
ADD DATABASE [$Database];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"

La base se trouve à présent dans le groupe de disponibilité, sur le serveur DC1-SQL1 et DC1-SQL2.

image

Jusque là, rien de nouveau. La partie fun commence maintenant : la création du groupe de disponibilité distribué afin de propager cette base sur les serveurs sur Site 2 (DC2-SQL3 et DC2-SQL4).

La différence entre un AG et un DAG (Distributed Availability Group) est fine … Voyez le DAG comme un AG qui repose sur deux AGs. Autrement dit, on remplace ENDPOINT_URL par LISTENER_URL et on point sur les nom DNS des listeners. That’s all.


# Create DAG
$tSQL = "
CREATE AVAILABILITY GROUP [distributedag]  
   WITH (DISTRIBUTED)   
   AVAILABILITY GROUP ON  
      'DC1-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC1-AG-VIP.demo.local:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'DC2-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC2-AG-VIP.demo.local:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"



# wait for total synchronisation
$tSQL = "
SELECT   ar.replica_server_name
       , ag.name as availabilitygroup_name
       , ag.is_distributed
       , DB_NAME(drs.database_id) As database_name
       , drs.is_primary_replica
       , drs.synchronization_state_desc
       , drs.synchronization_health_desc
       , drs.log_send_queue_size
       , drs.redo_queue_size
       , drs.end_of_log_lsn 
       , drs.last_sent_lsn
       , drs.last_received_lsn
       , drs.last_hardened_lsn
       , drs.last_redone_lsn
       , drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs 
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
inner join sys.availability_replicas ar on ar.replica_id = drs.replica_id

"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1" | Out-GridView

# Join DAG
$tSQL = "
ALTER AVAILABILITY GROUP [distributedag]   
   JOIN   
   AVAILABILITY GROUP ON  
      'DC1-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC1-AG-VIP.demo.local:5022',    
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      ),   
      'DC2-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC2-AG-VIP.demo.local:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,   
         FAILOVER_MODE = MANUAL,   
         SEEDING_MODE = AUTOMATIC   
      );    
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"

 

Ne jouez pas le script d’une traite, soyez patients. Jouez les portions de script les unes après les autres.

La base set maintenant disponible les sur les réplicas du site 2 …

image

 

And voilà ! comme disent les anglo-saxons avec  un mot de français Sourire

Le groupe de disponibilité distribué est à présent opérationnel. Les données vont bien être “recopiées” de manière synchrone entre les réplicas de chaque site, mais de manière asynchrone entre les sites. a solution de disaster recovery est opérationnelle. Et il est tout a fait possible de créer un second DAG, primaire sur le site 2 avec son disaster recovery sur le site 1. Cela correspond à l’architecture demandée : la résilience de site en cas de coupure réseau.

Happy DAG !

Note : La méthode et les scripts vous sont fournis clé en main. Merci de jouer franc-jeu. Si vous les utilisez, ajoutez une ligne de crédit. Rien n’oblige à partager et voir notre travail présenté par des tiers de manière publique dans afficher la source n’est pas vraiment fairplay. A bon entendeur …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM/MCSM SQL Server
Cet article, publié dans PowerShell, SQL Server, Windows, est tagué , , , . Ajoutez ce permalien à vos favoris.

3 commentaires pour SQL Server 2016 – Groupes de disponibilité distribués

  1. Ping : SQL Server 2016–Ajout d’une base dans un groupe de disponibilité distribué | Christophe LAPORTE – Consultant SQL Server

  2. Ping : SQL Server 2016–Failover d’un groupe de disponibilité distribué | Christophe LAPORTE – Consultant SQL Server

  3. Ping : SQL Server 2008 et SQL Server 2008R2 – fin de support étendu | Christophe LAPORTE – Consultant SQL Server

Votre commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Gravatar
Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. ( Déconnexion /  Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. ( Déconnexion /  Changer )

Annuler

Connexion à %s