Installation de la fonction de mise en cluster de basculement

1. RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe SQL Admins et ouvrez une session PowerShell.

2. Ajoutez le groupe SQL Admins au groupe local Remote Management Users afin que les utilisateurs de ce groupe puissent exécuter des commandes à distance.

3. Autorisez le port TCP entrant 5022 dans le serveur car ce port est utilisé pour le trafic du groupe de disponibilité. Installez la fonction Failover Clustering, puis redémarrez le serveur:

   $domainnb = "<NB_Domain>"
   $group = $domainnb + "\SQLAdmins"
   Add-LocalGroupMember -Group "Remote Management Users" -Member $group
   New-NetFirewallRule -DisplayName 'SQL-AG-Inbound' -Profile Domain -Direction Inbound -Action Allow -Protocol TCP -LocalPort 5022
   Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools
   Restart-Computer -Force
   

4. Répétez cette opération pour le second serveur SQL.

Créer un WSFC et activer SQL Always On

1. RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe SQL Admins et ouvrez une session PowerShell.

2. Exécutez un test de validation de cluster. Ignorez les avertissements de type "une paire d'interfaces réseau", car cela est normal pour ce déploiement.

3. S'il n'y a pas d'erreurs, ils créent un cluster WSFC avec le nom wsfc01 qui inclut les deux serveurs SQL <hostname1> et <hostname2>. L'option -ManagementPointNetworkType Distributed utilise l'adresse IP de noeud du serveur virtuel, ce qui signifie que l'adressage IP secondaire sur l'interface n'est pas requis. Cette option crée un nom de réseau distribué (DNN), qui achemine le trafic vers la ressource de cluster appropriée.

4. Le quorum de cluster est ensuite configuré pour Node et Disk Majority à l'aide d'un partage de fichiers sur fs01, \\fs01\clusterwitness-wsfc01

   $sqldb01 = "<hostname1>"
   $sqldb02 = "<hostname2>"
   Test-Cluster -Node $sqldb01, $sqldb02
   New-Cluster -Name wsfc01 -Node $sqldb01, $sqldb02 -ManagementPointNetworkType Distributed
   Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\clusterwitness-wsfc01
   Enable-SqlAlwaysOn -ServerInstance sqldb01, sqldb02 -Force
   

Cette activité n'a pas besoin d'être répétée sur le second noeud

Créer un partage de fichiers

La base de données à répliquer doit être sauvegardée et restaurée sur les instances secondaires, un partage de fichiers est nécessaire pour faciliter cette opération. Sur le serveur bastion, créez un répertoire et partagez-le pour pouvoir stocker une sauvegarde de base de données à partir du serveur SQL principal et la restaurer sur le serveur SQL secondaire. Le répertoire et le partage doivent être accessibles par le compte de service SQL car la sauvegarde est effectuée par le compte de service de la base de données.

1. Sur l'hôte bastion, ouvrez une session PowerShell.

   $domainnb = "<NB_Domain>"
   $user = $domainnb + "\sqlsvc"
   New-Item -Path "C:\" -Name "TempShare" -ItemType "directory"
   $ACL=Get-ACL -Path "C:\TempShare"
   $AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule($user,"FullControl","Allow")
   $ACL.SetAccessRule($AccessRule)
   $ACL | Set-Acl -Path "C:\TempShare"
   New-SmbShare -Name "TempShare" -Path "C:\TempShare" -FullAccess $user
   

Créer les noeuds finaux

1. RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe SQL Admins et ouvrez une session PowerShell.

2. Pour participer aux groupes de disponibilité Always On, une instance de serveur requiert son propre noeud final, qui utilise le port TCP 5022 pour envoyer et recevoir du trafic entre les instances de serveur hébergeant des répliques de disponibilité.

3. Les commandes PowerShell suivantes sont utilisées pour configurer ces noeuds finaux, Hadr_endpoint sur les instances SQL par défaut (DEFAULT) sur les serveurs SQL ; <hostname1>`` and <hostname2> et permettent le chiffrement entre les noeuds finaux:

   $sqldb01 = "<hostname1>"
   $sqldb02 = "<hostname2>"
   $domainnb = "<NB_Domain>"
   $user = $domainnb + "\sqlsvc"
   $pathsqldb01 = "SQLSERVER:\SQL\" + $sqldb01 + "\DEFAULT"
   $pathsqldb01 = "SQLSERVER:\SQL\" + $sqldb02 + "\DEFAULT"
   $endpoint1 = New-SqlHadrEndpoint Hadr_endpoint -Port 5022 -Path $pathsqldb01 -Encryption Required -EncryptionAlgorithm Aes -Owner $user
   Set-SqlHadrEndpoint -InputObject $endpoint1 -State "Started"
   $endpoint2 = New-SqlHadrEndpoint Hadr_endpoint -Port 5022 -Path $pathsqldb02 -Encryption Required -EncryptionAlgorithm Aes -Owner $user
   Set-SqlHadrEndpoint -InputObject $endpoint2 -State "Started"
   

Pour accorder des droits de connexion au service de domaine utilisé par les noeuds finaux, les étapes suivantes sont requises. Si cette étape n'est pas effectuée, les ports de noeud final ne démarrent pas et n'apparaissent pas dans une liste netstat -a :

1. Lancez SQL Server Management Studio (SSMS) sur l'hôte de la réplique principale et connectez-vous à la réplique principale.
2. Développez Sécurité, cliquez avec le bouton droit de la souris sur Connexions et sélectionnez Nouvelle connexion.
3. Cliquez sur Rechercher et entrez le compte utilisateur \sqlserver\sqlsvc, puis cliquez sur OK.
4. Cliquez avec le bouton droit de la souris sur la connexion créée et sélectionnez Propriétés.
5. Cliquez sur Securables, puis sur Rechercher.
6. Dans la boîte de dialogue Ajouter des objets, sélectionnez Objets spécifiques et cliquez sur OK.
7. Dans la boîte de dialogue Sélectionner des objets, cliquez sur Types d'objet et sélectionnez Noeuds finaux.
8. Cliquez sur Parcourir pour sélectionner le nom de l'objet.
9. Sélectionnez Hadr_endpoint et cliquez sur OK.
10. Dans le droit d'accès à Hadr_endpoint, accordez explicitement le droit de connexion à cet objet.

Créer une base de données de test

Pour configurer un groupe de disponibilité, une base de données doit être disponible sur le noeud principal, puis une copie de cette base de données doit être disponible sur le noeud secondaire. Cette tâche crée une base de données de test, puis à l'aide d'une opération de sauvegarde et de restauration, via un partage de fichiers, copie la base de données sur le noeud secondaire.

1. RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe SQL Admins et ouvrez une session PowerShell. Il est important de définir le mode de reprise sur Full pour les bases de données utilisées dans les groupes de disponibilité:

   $sql = "
   CREATE DATABASE [TestDatabase]
    CONTAINMENT = NONE
    ON  PRIMARY
   ( NAME = N'TestDatabase', FILENAME = N'D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDatabase.mdf' , SIZE = 1048576KB , FILEGROWTH = 262144KB )
    LOG ON
   ( NAME = N'MyDatabase_log', FILENAME = N'E:\MSSQL15.MSSQLSERVER\MSSQL\Logs\TestDatabase_log.ldf' , SIZE = 524288KB , FILEGROWTH = 131072KB )
   GO
   
   USE [master]
   GO
   ALTER DATABASE [TestDatabase] SET RECOVERY FULL
   GO
   
   ALTER AUTHORIZATION ON DATABASE::[TestDatabase] TO [sa]
   GO "
   Invoke-SqlCmd -ServerInstance sqldb01 -Query $sql
   

2. Préparez la base de données secondaire à l'aide des commandes Backup-SqlDatabase et Restore-SqlDatabase pour créer une sauvegarde de TestDatabase on <hostname1> on TempShare on <file_share_host>, l'instance de serveur SQL qui héberge la réplique principale. Restaurez la sauvegarde dans <hostname2>, qui héberge la réplique secondaire. Le paramètre de restauration NoRecovery doit être utilisé.

   $sqldb01 = "<hostname1>"
   $sqldb02 = "<hostname2>"
   $filesharehost = "<file_share_host>"
   $backupfiledata = "\\" + $filesharehost +"\TempShare\TestDatabase.bak"
   $backupfilelog = "\\" + $filesharehost +"\TempShare\TestDatabase.trn"
   Backup-SqlDatabase -Database "TestDatabase" -ServerInstance $sqldb01 -BackupFile $backupfiledata -CopyOnly
   Backup-SqlDatabase -Database "TestDatabase" -BackupFile $backupfilelog -ServerInstance $sqldb01 -BackupAction Log -CopyOnly
   Restore-SqlDatabase -Database "TestDatabase" -BackupFile $backupfiledata -ServerInstance $sqldb02 -NoRecovery  
   Restore-SqlDatabase -Database "TestDatabase" -BackupFile $backupfilelog -ServerInstance $sqldb02 -RestoreAction Log -NoRecovery
   

La nouvelle base de données secondaire est à l'état RESTORATION. Tant qu'il n'est pas joint au groupe de disponibilité, il n'est pas accessible.

Créer un groupe de disponibilité

Les bases de données ajoutées à un groupe de disponibilité sont appelées bases de données de disponibilité. Lors de l'ajout de bases de données, la base de données doit être une base de données en ligne en lecture-écriture et exister sur l'instance de serveur qui héberge la réplique principale dans WSFC. Une fois ajoutée, la base de données rejoint le groupe de disponibilité en tant que base de données principale et reste disponible pour les clients. Aucune base de données secondaire n'existe tant que les sauvegardes de la base de données principale ne sont pas restaurées sur l'instance de serveur qui deviendra la réplique secondaire. La nouvelle base de données secondaire est à l'état de restauration jusqu'à ce qu'elle soit jointe au groupe de disponibilité. Voir Utiliser le processus de distribution automatique pour initialiser une réplique secondaire pour un groupe de disponibilité Always On si la méthode de sauvegarde et de restauration n'est pas utilisée.

1. RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe SQL Admins et ouvrez une session PowerShell.

2. Pour vous assurer que vous n'obtenez pas d'erreurs de chemin, utilisez Invoke-SQLCmd qui force le chargement de la bibliothèque SQL PowerShell qui est ensuite accessible via l'arborescence de l'unité PowerShell. PowerShell traite les objets de SQL Server de la même manière que les fichiers d'un répertoire. Remplacez <hostname1> par le nom d'hôte du serveur SQL:

   invoke-sqlcmd
   cd SQLSERVER:\SQL\<hostname1>
   

3. Pour créer le groupe de disponibilité, la commande New-SqlAvailabilityReplica avec le paramètre -AsTemplate permet de créer un objet de réplique de disponibilité en mémoire pour chacune des deux répliques de disponibilité à inclure dans le groupe de disponibilité. Ensuite, le groupe de disponibilité est créé à l'aide de la commande New-SqlAvailabilityGroup et en référençant les objets de réplique de disponibilité. AutomatedBackupPreference Primary est utilisé pour indiquer que les sauvegardes doivent toujours être effectuées sur la réplique principale, tandis que -FailureConditionLevel OnCriticalServerErrors indique que la reprise en ligne automatique est déclenchée lorsqu'une erreur de serveur critique se produit. Il est possible d'utiliser l'option -SeedingMode Automatic qui active le processus de distribution direct car cette méthode ne nécessite pas la sauvegarde et la restauration d'une copie de la base de données principale. Pour SQL 2019, le numéro de version est 15.

   Reportez-vous à la documentation New-SqlAvailabilityGroup pour obtenir la description des autres paramètres.

   $sqldb01 = "<hostname1>"
   $sqldb02 = "<hostname2>"
   $sqldb01fqdn = "<fqdn1>"
   $sqldb02fqdn = "<fqdn2>"
   $endpointurl1 "TCP://" + $sqldb01fqdn + ":5022"
   $endpointurl2 "TCP://" + $sqldb02fqdn + ":5022"
   $pathsqldb01 = "SQLSERVER:\SQL\" + $sqldb01 +" \DEFAULT"
   $primaryReplica = New-SqlAvailabilityReplica -Name $sqldb01 -EndpointURL $endpointurl1 -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 15 -AsTemplate  
   $secondaryReplica = New-SqlAvailabilityReplica -Name $sqldb02 -EndpointURL $endpointurl2 -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 15 -AsTemplate
   New-SqlAvailabilityGroup -Name "AG01" -Path $pathsqldb01 -AvailabilityReplica @($primaryReplica,$secondaryReplica) -Database "TestDatabase" -ClusterType WSFC -AutomatedBackupPreference Primary -FailureConditionLevel OnCriticalServerErrors
   

4. Joignez la réplique secondaire au groupe de disponibilité à l'aide des commandes suivantes. La jointure place la base de données secondaire à l'état ONLINE et lance la synchronisation des données avec la base de données principale correspondante. La synchronisation des données est le processus par lequel les modifications apportées à une base de données principale sont reproduites sur une base de données secondaire. La synchronisation des données implique que la base de données principale envoie des enregistrements de journal des transactions à la base de données secondaire.

   $sqldb02 = "<hostname2>"
   $pathsqldb02 = "SQLSERVER:\SQL\" + $sqldb02 + " \DEFAULT"
   Join-SqlAvailabilityGroup -Path $pathsqldb02 -Name "AG01" -ClusterType WSFC
   

5. Démarrez la synchronisation des données en joignant chaque base de données secondaire au groupe de disponibilité:

   $sqldb02 = "<hostname2>"
   $agpathsqldb02 = "SQLSERVER:\SQL\" + $sqldb02 + " \DEFAULT\AvailabilityGroups\AG01"
   Add-SqlAvailabilityDatabase -Path $agpathsqldb02 -Database "TestDatabase"
   

6. Utilisez la commande dir pour vérifier le contenu du nouveau groupe de disponibilité, par exemple dir SQLSERVER:\SQL\sqldb01\DEFAULT\AvailabilityGroups\AG01.

Créer le nom du réseau réparti du groupe de disponibilité

Avec SQL Server sur IBM Cloud VPC, le nom de réseau distribué (DNN) achemine le trafic vers la ressource en cluster appropriée. Le programme d'écoute (DNN) remplace le programme d'écoute de groupe de disponibilité VNN (Virtual Network Name) traditionnel lorsqu'il est utilisé avec les groupes de disponibilité Always On et simplifie le déploiement dans un environnement de cloud.

Les programmes d'écoute DNN sont conçus pour écouter sur un port unique. L'entrée DNS pour le nom du programme d'écoute sera résolue en toutes les adresses IP des répliques du groupe de disponibilité. Etant donné que SQL Server écoute sur le port 1433, le port 1433 ne peut pas être utilisé pour un programme d'écoute DNN.

RDP sur le premier serveur SQL à l'aide d'un utilisateur du compte de groupe Admins SQL et ouvrez une session PowerShell et utilisez les commandes suivantes: pour créer une ressource DNN avec le nom dnnlsnr-6789, configure DNS sur le serveur DNS AD de la ressource DNN, démarre la ressource DNN, ajoute la dépendance de la ressource de groupe de disponibilité à la ressource DNN et enfin réinitialise la ressource de groupe de disponibilité.

$ag = "AG01"
$dns = "dnnlsnr"
$port = "6789"
Add-ClusterResource -Name $port -ResourceType "Distributed Network Name" -Group $ag
Get-ClusterResource -Name $port | Set-ClusterParameter -Name DnsName -Value $dns
Start-ClusterResource -Name $port
$Dep = Get-ClusterResourceDependency -Resource $ag
if ( $Dep.DependencyExpression -match '\s*\((.*)\)\s*' ) {$DepStr = "$($Matches.1) or [$port]"} else {$DepStr = "[$port]"}
Set-ClusterResourceDependency -Resource $ag -Dependency "$DepStr"
Stop-ClusterResource -Name $ag
Start-ClusterResource -Name $ag

Utilisez la commande suivante pour autoriser TCP 6789 via le pare-feu Windows:

New-NetFirewallRule -DisplayName 'SQL-dnnlsnr-6789-Inbound' -Profile Domain -Direction Inbound -Action Allow -Protocol TCP -LocalPort 6789