Controler le Database Mirroring au travers d’un cluster Windows

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 …