Bien que prochainement supprimé des fonctionnalités SQL Server, le Database Mirroring reste néanmoins d’actualité dans bien des société. Apparu avec SQL Server 2005 (SP1) la mise en miroir offrait une réponse directement dans SQL Server à des problématiques de haute disponibilité et de disaster recovery, avec comme avantage par rapport à un failover cluster, de supprimer le SPOF des disques partagés.
Le mirroring peut être synchrone ou asynchrone (uniquement en édition entreprise). Il est possible d’opter pour un basculement automatique en utilisant une troisième instance, SQL Server Express par exemple, qui joue le rôle de témoin.
A l’arrivée de SQL Server 2012, le database mirroring a été un peu mis sur la touche car les groupes de disponibilité (AlwaysOn Availability Groups) surclassent le DBM par le groupement de bases, en nombre de réplicas et par l’adoption d’un listener couplé au Cluster Windows permettant ainsi de se passer du paramètre FailoverPartner dans les chaines de connexion, qui permettait, dans le cadre du DBM, à l’application de basculer sur le nouveau serveur primaire en cas de bascule. Mais les groupes de disponibilités sont cantonnés à l’édition entreprise, dont le tarif refroidit quelques peu.
Ces clients se rabattaient donc sur le Database Mirroring pour assurer la HA de leur base de donnée … Mais SQL Server 2016, édition standard, va bouleverser les choses en introduisant les groupes de disponibilité basiques. Globalement, on retrouve les fonctionnalités et limitations du DBM fondues dans la logique des groupes de disponibilités (1 seul réplica, pas plus d’une base dans l’AG, aucun workload sur le réplica secondaire … ).
En attendant de migrer, certains clients continuent de faire fonctionner du DBM. Certes la bascule automatique peut être garantie par une instance témoin, certes il est possible de modifier les chaines de connexion pour inclure le partenaire, mais dans certains cas cela ne suffit pas. Pour certaines applications, il n’est pas possible d’influer sur la chaine de connexion et l’on souhaite pourtant bénéficier de la redirection automatique. Dans d’autres situations, on souhaiterait assurer une bascule automatique sans disposer d’une instance témoin …
Et si l’on confiait cette tâche à un cluster de basculement ? Le WSFC (Windows Server Faliover Cluster) dispose de tous les éléments pour assurer la détection de panne, le point d’accès client (@IP et nom DNS) …
Okay, lets go !
Dans un premier temps, création d’une base de test et mise en miroir de cette base (endpoints, backup, restore, mise en HA). Le compte de service de SQL est identique sur les 2 nœuds.
-- Run once : create endpoints :Connect SQLNode11 CREATE ENDPOINT [Mirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4 ) :Connect SQLNode12 CREATE ENDPOINT [Mirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4 ) :Connect SQLNode11 CREATE DATABASE DemoDBM :Connect SQLNode11 backup database DemoDBM to disk = '\\AD2012\Share\DemoDBM_Mirror.bak' WITH compression,init,format,stats=5; :Connect SQLNode11 backup Log DemoDBM to disk = '\\AD2012\Share\DemoDBM_Mirror.trn' WITH compression,init,format,stats=5; :Connect SQLNode12 restore database DemoDBM from disk = '\\AD2012\Share\DemoDBM_Mirror.bak' WITH norecovery,stats=5; :Connect SQLNode12 restore Log DemoDBM from disk = '\\AD2012\Share\DemoDBM_Mirror.trn' WITH norecovery,stats=5; :Connect SQLNode12 ALTER DATABASE DemoDBM SET PARTNER = 'TCP://SQLNode11.conseilit.local:5022'; GO :Connect SQLNode11 ALTER DATABASE DemoDBM SET PARTNER = 'TCP://SQLNode12.conseilit.local:5022'; GO
On oublie rapidement avec quelle facilité il était possible de créer une session de mise en miroir en quelques lignes de code T-SQL !
Il reste maintenant à piloter le failover du Database Mirroring. Deux cas de figure sont à prendre en compte : une bascule manuelle et une bascule automatique due à une perte du serveur principal. Pour ce faire, je vous propose de créer une procédure stockée qui va permettre de passer les ordres de bascule. J’ai opté pour l’appel à des jobs, mais bien sur c’est facultatif. Notez au passage l’utilisation d’un serveur lié (MyBuddy). Le code suivant est à exécuter sur le serveur principal et le serveur secondaire.
Use [Master] GO CREATE PROCEDURE dbo.WSFC_DBM_OnLine AS BEGIN BEGIN TRY -- Manual failover EXEC [MyBuddy].msdb.dbo.sp_start_job @job_name = 'WSFC DBM - FAILOVER Manual' END TRY BEGIN CATCH -- Buddy is not online, force failover EXEC msdb.dbo.sp_start_job @job_name = 'WSFC DBM - FAILOVER FORCE_SERVICE_ALLOW_DATA_LOSS' END CATCH END USE [msdb] GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'WSFC DBM - FAILOVER FORCE_SERVICE_ALLOW_DATA_LOSS', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Pas de description disponible.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failover', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'ALTER DATABASE [DemoDBM] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;', @database_name=N'master', @flags=0 EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'WSFC DBM - FAILOVER Manual', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Pas de description disponible.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Failover', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' IF EXISTS (SELECT * FROM sys.databases WHERE name = ''DemoDBM'' AND state=0) ALTER DATABASE [DemoDBM] SET PARTNER FAILOVER; ', @database_name=N'master', @flags=0 EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' GO
Le cluster de basculement, ou failover cluster va nous permettre de disposer d’un point d’accès unique, sur le serveur principal ou le serveur secondaire en fonction du nœud owner du groupe de ressource. On résout ainsi la problématique des chaines de connexion. Notre application va pouvoir se connecter indifféremment sur n’importe lequel des serveurs. La problématique réside maintenant dans le fait de basculer la session active du miroir sur le nœud choisi comme principal au niveau du cluster.
C’est là qu’intervient le script générique. Un fichier VBS (je n’ai pas trouvé de possibilité au travers de Powershell) , pourvu de fonctions réagissant aux commandes de Online, Offline, IsAlive, LooksAlive, …
Voici un exemple de script, basique, permettant d’opérer les bascules manuelles et automatiques en cas d’échec de connexion au serveur SQL. :
Function Online( ) sPrincipalInstance = "VNN_DBM_DemoDBM" sTSQL = "EXEC master.dbo.WSFC_DBM_OnLine" Resource.LogInformation "Begin Online() WSFC_DBM_DemoDBM" set objConnection = createobject("adodb.connection") DSNtemp = "driver={SQL Server};" & "server=" & sPrincipalInstance & ";database=master;security=SSPI" On Error Resume next objConnection.Open DSNtemp If err.number <> 0 Then Resource.LogInformation "Cannot open connection to " & sPrincipalInstance Resource.LogInformation err.description Online = false Else Resource.LogInformation "Connected to " & sPrincipalInstance 'Set objRecordset = Server.CreateObject("adodb.recordset") Resource.LogInformation "Starting " & sTSQL objConnection.Execute (sTSQL) If err.number <> 0 Then Resource.LogInformation err.description Resource.LogInformation "Resource WSFC_DBM_DemoDBM failed to bring online" Online = false Else objRecordset.ActiveConnection = nothing set objConnection = nothing Resource.LogInformation "Resource WSFC_DBM_DemoDBM online" Online = true End If End If End Function Function LooksAlive( ) LooksAlive = true End Function Function IsAlive( ) sPrincipalInstance = "VNN_DBM_DemoDBM" sTSQL = "SELECT @@servername" Resource.LogInformation "Begin IsAlive() WSFC_DBM_DemoDBM" set objConnection = createobject("adodb.connection") DSNtemp = "driver={SQL Server};" & "server=" & sPrincipalInstance & ";database=master;security=SSPI" On Error Resume next objConnection.Open DSNtemp If err.number <> 0 Then Resource.LogInformation "Cannot open connection to " & sPrincipalInstance Resource.LogInformation err.description IsAlive = false Else Resource.LogInformation "Connection successfull to " & sPrincipalInstance IsAlive = true End If End Function
Pour raccourcir le délai de failover en cas de problème de connexion sur un serveur, on peut changer les intervalles dans la configuration du cluster . Vous pouvez également mettre le code du IsAlive dans les LooksAlive et approfondir les tests du IsAlive.
Le code powershell suivant permet de créer le cluster, de créer un groupe de ressource avec les ressources et les dépendances.
# adding Windows features Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -ComputerName SQLNode11 Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -ComputerName SQLNode12 Import-Module ServerManager Import-Module FailoverClusters # create the cluster New-Cluster -Name WSFC_DBM -Node SQLNode11,SQLNode12 -NoStorage -StaticAddress 192.168.1.149 -IgnoreNetwork 10.0.0.0/8 # Adding FSW Get-Cluster | Set-ClusterQuorum -FileShareWitness "\\AD2012\FSW" # Check configuration Get-ClusterQuorum | select * Get-Cluster | select Name,WitnessDynamicWeight,DynamicQuorum $nodes = Get-ClusterNode $nodes | Format-Table -property NodeName, State, NodeWeight # Create an empty role Add-ClusterGroup -Name WSFC_DBM_DemoDBM # ensure Resource group is local Move-ClusterGroup -Name WSFC_DBM_DemoDBM -Node SQLNode11 Stop-ClusterGroup -Name WSFC_DBM_DemoDBM # and add a client access point Get-ClusterResourceType Add-ClusterResource -Name VIP_DBM_DemoDBM -ResourceType "IP Address" -Group WSFC_DBM_DemoDBM Get-ClusterResource -Name VIP_DBM_DemoDBM | Set-ClusterParameter -Multiple @{"Network"="Cluster Network 1";"Address"= "192.168.1.148";"SubnetMask"="255.255.255.0"} Add-ClusterResource -Name VNN_DBM_DemoDBM -ResourceType "Network Name" -Group WSFC_DBM_DemoDBM Get-ClusterResource -Name VNN_DBM_DemoDBM | Set-ClusterParameter -Multiple @{“DnsName”=”VNN_DBM_DemoDBM";"Name"= "VNN_DBM_DemoDBM"} Add-ClusterResourceDependency "VNN_DBM_DemoDBM" "VIP_DBM_DemoDBM" # Bring Online to check role failover Start-ClusterGroup WSFC_DBM_DemoDBM Move-ClusterGroup -Name WSFC_DBM_DemoDBM -Node SQLNode12 Move-ClusterGroup -Name WSFC_DBM_DemoDBM -Node SQLNode11 Stop-ClusterGroup WSFC_DBM_DemoDBM # adding VBS script as a resource Add-ClusterResource -Name WSFC_DBM_Script -ResourceType "Generic Script" -Group WSFC_DBM_DemoDBM Get-ClusterResource -Name WSFC_DBM_Script | Set-ClusterParameter -Multiple @{“ScriptFilepath”=”c:\scripts\WSFC_DBM_Script.vbs"} Add-ClusterResourceDependency "WSFC_DBM_Script" "VNN_DBM_DemoDBM" # don't forget to copy the vbs script to c:\scripts folder Start-ClusterGroup WSFC_DBM_DemoDBM
A présent, la solution est opérationnelle …
Vous pouvez procéder à une bascule manuelle au niveau du gestionnaire de cluster, la base va devenir active sur le nœud owner du groupe de ressource ! Vous pouvez également stopper brutalement l’instance active, dès que le cluster détecte l’indisponibilité, il va basculer sur le réplica en attente. Le nom réseau et l’adresse IP étant identiques, si votre application est munie d’une logique de retry de connexion en cas de coupure réseau, elle se reconnectera directement sur l’instance active.
Une fois que le nœud en panne redevient disponible, il suffit de reprendre le mouvement de données, par un ordre ALTER DATABASE xxxx SET PARTNER RESUME, par exemple en modifiant le IsAlive et en faisant appel à une procédure stockée.
Chalenge réussi ! Il est tout a fait faisable, et de manière relativement simple, de piloter un database mirroring au travers d’un rôle du failover cluster. Facile à customiser pour prendre en compte une spécificité ou une contrainte.
En attendant de migrer sur SQL Server 2016 et les groupes de disponibilité basiques, enjoy …
Merci Monsieur Christophe, Cet article est très important pour nous débutants
Si vous avez un article sur la configuration de la mise en miroir sur SQL Server 2012, je suis preneur. Je travaille la dessus et au moment de préparer le serveur source pour le démarrage de la mise en miroir, voilà l’erreur affiché: the remote copy of database has not been rolled forward to a point in time that is encompassed
Bonsoir
La procédure décrite dans cet article est valable pour toutes les versions de SQL Server depuis SQL Server 2005.
Lors de la mise en place du DBM, veillez à faire un backup database et un backup log sur principal. restore database et restore log sur le secondaire en utilisant systématiquement l’option norecovery.
Ensuite, mise en place du DBM.
Durant cet intervalle, il ne faut pas qu’un backup log intervienne sur le principal.