Miroirs de bases SQL Server 2008 R2 avec témoin, guide pratique

Logo

Introduction

Le "mirroring" (ou miroir) est une fonctionnalité qui, depuis SQL Server 2005, maintient une base de données "standby" inactive à des fins de DR (Disaster/Recovery) et/ou de bascule automatique en cas d’échec (automatic failover).

Le fonctionnement et les commandes permettant d’implémenter la solution n’ont pas changé en version 2008 R2.

La présente note complète cependant celle publiée en 2009 ( SQLPAC - Miroirs de bases SQL Server 2005 (mirroring), guide pratique) en mettant en place une solution de miroir avec bascule automatique , utilisant une troisième instance dite 'Witness' ou témoin.

À noter de nombreuses alertes de performances dans la documentation relatives à l’utilisation de ce témoin, c’est pourquoi le monitoring de la latence est abordé à la fin de cet article.

L’implémentation est réalisée en utilisant les variables de scripts, fonctionnalité de l’utilitaire SQLCMD introduite avec SQL Server 2005.

Schema mirroring

Comme la réplication transactionnelle ou la stratégie 'Log Shipping', le mirroring duplique les mouvements de données en utilisant les journaux de transactions. Le miroir diffère des 2 autres méthodes citées dans sa façon de dupliquer à la volée les blocs Tlog des journaux de transactions du serveur primaire vers une base de données miroir définie en mode InRecovery.

Il existe trois différentes manières de configurer le mirroring SQL Server 2005 :

  • Mode Safety ON avec témoin (with witness) : les transactions sont envoyées vers le miroir et le serveur primaire attend l’acquittement du miroir. Il s’agit d’un mécanisme de transfert synchrone sans latence entre les 2 parties. Un troisième serveur témoin (witness) dans cette stratégie de miroir prend en charge la bascule automatique (automatic failover).
  • Mode Safety ON sans témoin (without witness) : le transfert est synchrone mais la bascule (failover) est manuelle.
  • Mode Safety OFF : les transactions sont envoyées mais le serveur primaire n’attend pas l’acquittement du miroir. Le mécanisme est complètement asynchrone et la bascule (failover) doit être forcée. Le miroir peut être désynchronisé par rapport au serveur primaire. Le mode asynchrone est donc la solution de miroir la plus performante mais elle implique l’acceptation de la perte de données.

La première stratégie est celle évoquée dans cet article (SAFETY ON avec témoin).

Présentation technique générale

Prérequis

Les noms des bases de données doivent être identiques dans les 2 parties.

Les instances SQL Server 2008 R2 communiquent entre elles, aussi le réseau, les comptes, etc. doivent être configurés proprement en conséquence.

La base de données primaire doit être impérativement dans le mode de restauration "full recovery", en effet les enregistrements dans les journaux qui correspondent à des opérations massives réalisées en journalisation minimale (mode "bulk-logged recovery") ne peuvent pas être envoyés vers la base de données miroir.

De l’espace disque suffisant doit être prévu pour les journaux de transactions pour éviter toute erreur d’espace.

La base de données miroir n’est pas accessible car celle-ci est en état de restauration.

Comment cela fonctionne ?

Les bases de données sont synchronisées en utilisant la stratégie backup/restore with norecovery. L’étape de sauvegarde/restauration du journal de transaction est obligatoire.

Des points de terminaison (Endpoints) doivent être définis dans chaque instance. Un point de terminaison représente un tunnel de communication entre un client et un serveur.

Une troisième instance dite témoin (witness) est nécessaire pour réaliser la bascule automatique de l’instance. Elle a le rôle de tiers vérifiant les connexions aux différents partenaires.

Guide pratique

Cinématique

cinematique de la mise en place du mirroir

Le schéma physique de l’architecture du cas pratique est le suivant :

architecture mirroir avec temoin

Implémentation scriptée

Nouveauté 2005, un fichier SQL peut désormais contenir des syntaxes de définition et manipulation de variables interprétées par l’utilitaire SQLCMD.

Dans l’exemple ci-dessous, le compte utilisé pour exécuter le script est aussi celui utilisé pour démarrer le service SQL sur les trois instances impliquées. 

DOS> SQLCMD -E -i miroir.sql

Avec miroir.sql contenant les instructions suivantes: 

miroir.sql
-- ########################################################################
-- mirror_setup.sql
-- Mise en place d’un miroir
-- A partir d’un script de Richard PRADE
-- ########################################################################


-- ########################################################################
-- Environnement

select getdate(), 'Start'
go

:SETVAR DATABASE "WCM_Data"
:SETVAR PRINCIPAL "SRVWINFR13"
:SETVAR MIRROR "SRVWINFR23"
:SETVAR WITNESS  "FRDCOR101"
:SETVAR PORT "7026"
:SETVAR BACKUPDIR "\\SRVWINFR13\d$"

:SETVAR ENDPOINT "mirroir"

:ON ERROR EXIT


-- ########################################################################
-- Endpoints

select getdate(), 'create endpoints'
go

-- -------------------------------------------------------------------------
-- 

:CONNECT $(PRINCIPAL) 

CREATE ENDPOINT $(ENDPOINT)
STATE=STARTED
AS TCP ( 
LISTENER_PORT = $(PORT), 
LISTENER_IP = ALL
)
FOR DATA_MIRRORING (
ROLE = PARTNER, 
AUTHENTICATION = WINDOWS NEGOTIATE, 
ENCRYPTION = REQUIRED ALGORITHM RC4
)
go


-- -------------------------------------------------------------------------
-- 

:CONNECT $(MIRROR)


CREATE ENDPOINT $(ENDPOINT)
STATE=STARTED
AS TCP ( 
LISTENER_PORT = $(PORT), 
LISTENER_IP = ALL
<)
FOR DATA_MIRRORING (
ROLE = PARTNER, 
AUTHENTICATION = WINDOWS NEGOTIATE, 
ENCRYPTION = REQUIRED ALGORITHM RC4
)
go


-- -------------------------------------------------------------------------
-- 

:CONNECT $(WITNESS)


CREATE ENDPOINT $(ENDPOINT)
STATE=STARTED
AS TCP ( 
LISTENER_PORT = $(PORT), 
LISTENER_IP = ALL
)
FOR DATA_MIRRORING (
ROLE = WITNESS, 
AUTHENTICATION = WINDOWS NEGOTIATE, 
ENCRYPTION = REQUIRED ALGORITHM RC4
)
go


-- ########################################################################
-- Preparation des bases

select getdate(), 'Prepare databases'
go

-- -------------------------------------------------------------------------
-- 

:CONNECT $(PRINCIPAL)

ALTER DATABASE $(DATABASE) 
SET RECOVERY FULL
GO

BACKUP DATABASE $(DATABASE) 
TO DISK = '$(BACKUPDIR)\$(DATABASE)_init_mirroir.bak' 
WITH INIT
GO

BACKUP LOG $(DATABASE) 
TO DISK = '$(BACKUPDIR)\$(DATABASE)_init_mirroir_log.bak' 
WITH INIT
GO


-- -------------------------------------------------------------------------
-- 

:CONNECT $(MIRROR)

RESTORE DATABASE $(DATABASE) 
FROM DISK = '$(BACKUPDIR)\$(DATABASE)_init_mirroir.bak' 
WITH NORECOVERY, REPLACE
GO

RESTORE LOG $(DATABASE) 
FROM DISK = '$(BACKUPDIR)\$(DATABASE)_init_mirroir_log.bak' 
WITH NORECOVERY
GO




-- ########################################################################
-- Mise en place des miroirs


select getdate(), 'Set partners'
go

-- -------------------------------------------------------------------------
-- 

:CONNECT $(MIRROR)

ALTER DATABASE $(DATABASE) 
SET PARTNER = 'TCP://$(PRINCIPAL):$(PORT)'
GO


-- -------------------------------------------------------------------------
-- 

:CONNECT $(PRINCIPAL)

ALTER DATABASE $(DATABASE) 
SET PARTNER = 'TCP://$(MIRROR):$(PORT)'
GO
ALTER DATABASE $(DATABASE) 
SET WITNESS = 'TCP://$(WITNESS):$(PORT)'
GO
ALTER DATABASE $(DATABASE) 
SET PARTNER SAFETY FULL
GO


-- ########################################################################
-- Environnement

select getdate(), 'Operation terminee'
go

SELECT 'DB'  = convert(varchar(20),'$(DATABASE)') , 
'Status' = convert(varchar(15),sd.mirroring_state_desc), 
'Partner'= sd.mirroring_partner_name, 
'Witness'= sd.mirroring_witness_name, 
'Statuswit'= sd.mirroring_witness_state_desc, 
'Role' = sd.mirroring_role_desc
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id('$(DATABASE)')
GO

Vérifications des statuts d’un miroir (vues systèmes)

La vue sys.database_mirroring lancée sur les serveurs principal et miroir permet de rapidement consulter les paramètres du miroir et son statut. Voici une des requêtes les plus utiles :

SELECT 'DB'  = db_name(database_id)  , 
  'Status' = convert(varchar(15),sd.mirroring_state_desc), 
  'Partner'= sd.mirroring_partner_name, 
  'Witness'= sd.mirroring_witness_name, 
  'Statuswit'= sd.mirroring_witness_state_desc, 
  'Role' = sd.mirroring_role_desc
FROM sys.database_mirroring AS sd
where sd.mirroring_guid is not null

Sur le serveur PRINCIPAL (SRVWINFR13) :

DB             Status       Partner                Witness               Statuswit      Role
-------------- ------------ ---------------------- --------------------- -------------- -----------
WCM_CMS        SYNCHRONIZED TCP://SRVWINFR23:7026  TCP://FRDCOR101:7026  CONNECTED      PRINCIPAL

Sur le serveur MIROIR (SRVWINFR23) : 

DB             Status       Partner                Witness               Statuswit      Role
-------------- ------------ ---------------------- --------------------- -------------- -----------
WCM_CMS        SYNCHRONIZED TCP://SRVWINFR13:7026  TCP://FRDCOR101:7026  CONNECTED      MIRROR

Cette requête très simple donne l’état du miroir (SYNCHRONIZED), les paramètres du témoin et du partenaire ainsi que le rôle principal ou miroir du serveur dans l’architecture de miroir (PRINCIPAL ou MIRROR)

Voici quelques autres commandes utiles qui interrogent les vues systèmes liées au miroir et renvoient des informations sur le statut du miroir. 

select db_name(database_id) 'DB', 
  mirroring_state_desc, 
  mirroring_role_desc, 
  mirroring_safety_level_desc,
  mirroring_partner_instance,
  mirroring_failover_lsn,
  mirroring_connection_timeout, 
  mirroring_redo_queue,
  mirroring_redo_queue_type
from sys.database_mirroring 
 where mirroring_guid is not null
          
select * from sys.database_mirroring_endpoints
          
select * from sys.dm_db_mirroring_connections
          
select * from sys.tcp_endpoints

Commandes utiles

Suspension et reprise d’un miroir (resume/suspend)

Pour suspendre un miroir, se connecter dans chaque partenaire (primaire et secondaire) et lancer la commande ALTER DATABASE avec l’option SET PARTNER SUSPEND. 

ALTER DATABASE [databaseName] SET PARTNER SUSPEND

Pour reprendre un miroir, se connecter dans chaque partenaire (primaire et secondaire) et lancer la commande ALTER DATABASE avec l’option SET PARTNER RESUME. 

ALTER DATABASE [databaseName] SET PARTNER RESUME

Visualiser le contenu d’un miroir (SNAPSHOT)

Le contenu d’un miroir peut uniquement être consulté en utilisant la nouvelle fonctionnalité de SNAPSHOT (photo instantanée) de SQL Server 2005. 

CREATE DATABASE [snap_databaseName] ON 
( NAME = N'databaseName_data_file01 ', FILENAME = N'd:\snap_test_data.snap' )
AS SNAPSHOT OF [databaseName]

Le snapshot peut être supprimé avec la commande DROP DATABASE 

DROP DATABASE snap_databaseName

Suppression d’un miroir

Pour supprimer un miroir, la commande ALTER DATABASE avec l’option SET PARTNER OFF est exécutée sur les serveurs primaire et miroir 

ALTER DATABASE [databaseName] SET PARTNER OFF

Exemples de bascule du miroir

Bascules automatiques du miroir (automatic failover)

Dans cet exemple, SRVWINFR13 n’est plus accessible pendant plus de 10 secondes (timeout). Les fichiers de log du miroir SRVWINFR23 et du témoin FRDCOR101 notifient cette indisponibilité :

SRVWINFR23> ERRORLOG
Apr 17 2011 01:28PM The mirroring connection to "TCP://SRVWINFR13:7026" has timed out for database "WCM_Data"
after 10 seconds without a response.
Check the service and network connections.

La bascule automatique vers le miroir SRVWINFR23 est alors déclenchée et notifiée avec le message suivant : The mirrored database "dbname" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.

SRVWINFR23> ERRORLOG
Apr 17 2011 01:28PM The mirrored database "WCM_Data" is changing roles from "MIRROR" to "PRINCIPAL"
due to Auto Failover. 
Apr 17 2011 01:28PM Recovery is writing a checkpoint in database 'WCM_Data' (5).
Apr 17 2011 01:28PM Recovery completed for database WCM_Data (database ID 5)
in 1753804 second(s) (analysis 0 ms, redo 0 ms, undo 20 ms.)
...
Apr 17 2011 01:29PM Database mirroring is active with database 'WCM_Data' as the principal copy.

La fin de la bascule est confirmée avec le message "Database mirroring is active with database 'dbname' as the principal copy".

Bascules manuelles du miroir (manual failover) : ALTER DATABASE SET PARTNER FAILOVER

Lorsque le miroir est synchronisé (état SYNCHRONIZED dans la vue sys.database_mirroring), le propriétaire de la base dbo peut initier une bascule manuelle vers le serveur miroir avec la commande ALTER DATABASE dbname SET PARTNER FAILOVER. Cette bascule manuelle ne peut être réalisée que depuis le serveur principal.

Pour rebasculer manuellement de SRVWINFR23 vers SRVWINFR13 à la suite de la bascule automatique précédente :

DOS> sqlcmd -E -S SRVWINFR23

use master
go
alter database WCM_Data set partner failover
go
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Le fichier ERRORLOG du serveur SRVWINFR23 notifie bien la bascule cette fois manuelle (disparition du mot clé Auto) :

Apr 17 2011 03:30PM  The mirrored database "WCM_Data" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.
…
Apr 17 2011 03:30PM  Database mirroring is active with database 'WCM_Data' as the mirror copy.

Propriétés des chaînes de connexion ODBC, OLE DB, ADO.NET et JDBC pour la prise en charge du mirroring

Le mot clé "Failover Partner" est utilisé dans les chaînes de connexion ODBC, OLE DB, ADO.NET et JDBC pour spécifier le second partenaire dans une architecture de mirroir. La session se connecte au serveur qui a le rôle PRINCIPAL lorsque la connexion est réalisée.

Voici un exemple de chaîne de connexion ADO.NET spécifiant le miroir

Data Source=SRVWINFR13;Failover Partner=SRVWINFR23;Initial Catalog=WCM_Data;Integrated Security=True

Le mot clé "Failover Partner" dans la chaîne de connexion diffère quelque peu en fonction du pilote :

  • OLE DB : FailoverPartner
  • ODBC : Failover_Partner
  • ADO.NET : Failover Partner
  • JDBC : failoverPartner

Monitoring des écritures vers le miroir (sp_dbmmonitorchangealert et sp_dbmmonitorresults)

La procédure stockée système sp_dbmmonitorchangealert active et modifie les seuils d’alerte sur les performances d’un miroir.

execute sp_dbmmonitorchangealert database_name 
        , alert_id 
        , alert_threshold 
        , enabled

Le paramètre alert_id identifie l’avertissement à ajouter ou modifier. L’alerte 4 monitore la latence vers le serveur miroir (temps pendant lequel l’instance principale attend l’acquittement de l’écriture de la transaction).

Pour activer l’alerte 4 et générer une alerte lorsqu’une latence de plus de 300 millisecondes est observée ente les serveurs principal et miroir pour la base WCM_Data

execute sp_dbmmonitorchangealert 'WCM_Data',4,300,1 -- Latence de 300ms

L’alerte est écrite dans le journal d’évènements Windows.

La latence peut être visualisée par ailleurs en temps réel avec la procédure système sp_dbmmonitorresults (à exécuter dans la base msdb) :

use msdb
go
execute sp_dbmmonitorresults 'WCM_Data'
go

database_name  ...   unsent_log    send_rate  transaction_delay
-------------        ----------    ---------  -----------------
WCM_Data             0             0          0

Cette procédure fournit de précieuses informations sur les délais dans les transactions.