Script de mise en miroir de base de données SQL Server

Date de publication : 12/03/2009 , Date de mise à jour : 24/05/2009

Version PDF (Miroir) Version hors-ligne (Miroir)

I. Introduction
II. Le script
II-A. Sauvegarde de la base à mirorrer
II-B. Restauration de la base à mororer avec l'option WITH NORECOVERY
II-C. Validation de la visibilité réseau des serveurs
II-D. Création d'une clef de cryptage pour la base de données master
II-E. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http source
II-F. Création du point de terminaison http sur le serveur source en utilisant le certificat pour l'authentification
II-G. Sauvegarde du certificat sous forme de fichier
II-H. Copiez le certificat sur les serveurs témoins et cible
II-I. Installation sur serveur cible
II-I-1. Création d'une clef de cryptage pour la base de données master
II-I-2. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http cible
II-I-3. Création du point de terminaison http sur le serveur cible en utilisant le certificat pour l'authentification
II-I-4. Sauvegarde du certificat sous forme de fichier
II-I-5. Copiez le certificat sur les serveurs témoins et source
II-J. Installation sur serveur témoin
II-J-1. Création d'une clef de cryptage pour la base de données master
II-J-2. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http témoin
II-J-3. Création du point de terminaison http sur le serveur cible en utilisant le certificat pour l'authentification
II-J-4. Sauvegarde du certificat sous forme de fichier
II-J-5. Copiez le certificat sur les serveurs cible et source
II-K. Mettez en place la sécurité côté source
II-K-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring
II-K-2. Créez un utilisateur associé à ce compte de connexion
II-K-3. Associez les certificats externes à cet utilisateur :
II-K-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion
II-L. Mettez en place la sécurité côté cible
II-L-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring
II-L-2. Créez un utilisateur associé à ce compte de connexion
II-L-3. Associez les certificats externes à cet utilisateur :
II-L-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion
II-M. Mettez en place la sécurité côté témoin
II-M-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring
II-M-2. Créez un utilisateur associé à ce compte de connexion
II-M-3. Associez les certificats externes à cet utilisateur :
II-M-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion
II-N. Préparez le serveur cible pour le démarrage de la session de miroir
II-O. Préparez le serveur source pour le démarrage de la session de miroir
II-P. Contrôlez la session
III. Auditer le mirroir

I. Introduction

Le principe du miroir consiste à capturer au fil de l'eau les transactions se produisant sur une base, afin de les reproduire sur une base dite miroir sur un serveur distant. Le serveur source envoi sur couche http cryptée les données des transactions à reproduire sur le serveur cible. Un serveur témoin (SQL) peut être mis en place afin de permettre le basculement automatique des applications. Ce basculement automatique ne peut être effectif dans les applications que pour celles utilisant le pilote SQL NCLI (Native Client), auquel cas vous devez indiquer dans la chaîne de connexion le nom du serveur partenaire (FAILOVER PARTNER=...). Notez cependant que ce paramètre n'est pas nécessaire si votre application utilise le framework .net en accès SQL NCLI car c'est la bibliothèque cliente qui rapatrie toute seule cette information des tables systèmes en se connectant au serveur source.

Il y a différents modes de gestion du miroir :

haute protection : suppose un mirroring asynchrone avec basculement automatique (nécessite un serveur témoin)
haute performances : mirroring asynchrone à basculement manuel (sans serveur témoin)
haute sécurité : mirroring synchrone à basculement manuel (sans serveur témoin)

Par nature :

il ne peut y avoir qu'un seul miroir partant d'une base. Le miroir d'une base étant une base passive, vous ne pouvez en aucun cas l'utiliser à des fins de production, ni même de sources comme sauvegarde ou miroir d'une autre base en cascade, car la base est en permanence en mode restauration. Cependant vous pouvez effectuer un snapshot de cette base afin de produire une base ayant les données à un instant T et en lecture seule, par exemple pour de la consultation (reporting ou source d'alimentation d'un datawarehouse).
Le miroir est symétrique, tant est si bien qu'en cas de basculement automatique, il n'y a rien à faire pour que la base source devienne cible. En effet en cas de basculement, les bases de données échangent leurs rôles sous le contrôle du témoin.
La base cible doit être en mode de journalisation FULL pour pouvoir être mirrorée. En cas de changement du RECOVERY MODEL comme en cas d'arrêt du miroir, le système est définitivement brisé dans le sens ou l'envoi des transactions repose sur le chaînage de ces dernières à l'aide du LSN (Log Sequence Number).

Le serveur témoin peut être un vulgaire PC avec XP et une édition Express de SQL Server. Il n'a pas non plus besoin de ressources particulière car son rôle est de scruter régulièrement quels sont les serveurs accessibles. Vous pouvez donc utiliser un serveur quelconque déjà productif si votre organisation compte de nombreuses machines.

Soit SRV_SOURCE, SRV_CIBLE et SRV_TEMOIN un serveur source, un serveur cible et le serveur témoin pour le mirroring.
Soit DB_TO_MIRROR la base de données à mirorer

II. Le script

Le script suivant propose de faire cette manœuvre en 16 étapes et 34 commandes Transact SQL !

II-A. Sauvegarde de la base à mirorrer

----------------------------------------- 
-- depuis le serveur source SRV_SOURCE -- 
----------------------------------------- 
USE master   
GO   
BACKUP DATABASE DB_TO_MIRROR    
   TO DISK = N'D:\DBBackup\DB_TO_MIRRORBackup.bak'   
   WITH NAME = N'Full Database Backup',   
    INIT, STATS = 10   
GO   
 
BACKUP LOG DB_TO_MIRROR    
   TO DISK = N'D:\DBBackup\DB_TO_MIRRORBackupLog.trn'   
   WITH NAME = N'Transaction Log Backup',     
       STATS = 10   
GO

II-B. Restauration de la base à mororer avec l'option WITH NORECOVERY

----------------------------------------- 
-- depuis le serveur source SRV_CIBLE  -- 
----------------------------------------- 
 
RESTORE DATABASE DB_TO_MIRROR   
FROM DISK = N'D:\DBBackup\DB_TO_MIRRORBackup.bak'   
WITH NORECOVERY 
GO   
 
RESTORE LOG DB_TO_MIRROR   
FROM DISK = N'D:\DBBackup\DB_TO_MIRRORBackupLog.trn'   
WITH NORECOVERY 
GO

II-C. Validation de la visibilité réseau des serveurs

Faites un ping croisé des serveurs.
À défaut, éditez le fichier hosts (situé dans %systemroot%\system32\drivers\etc\) pour faire la mapping nom/adresse IP.

----------------------------------------- 
-- depuis le serveur source SRV_SOURCE -- 
-----------------------------------------

II-D. Création d'une clef de cryptage pour la base de données master

USE master  
GO 
CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'Mon mot de passe';  
GO

II-E. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http source

CREATE CERTIFICATE CRT_MIRROR_SOURCE 
WITH SUBJECT = 'certificat CRT_MIRROR_SOURCE pour la mise en miroir de la base DB_MIRROR', 
     START_DATE = 'AAAAMMJJ'; ; 
GO

II-F. Création du point de terminaison http sur le serveur source en utilisant le certificat pour l'authentification

CREATE ENDPOINT EDP_MIRROR  
STATE = STARTED 
AS TCP (LISTENER_PORT = 7022,  
        LISTENER_IP = ALL)  
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE CRT_MIRROR_SOURCE,  
                        ENCRYPTION = REQUIRED ALGORITHM AES,  
                        ROLE = ALL);  
GO

Pour contrôle :

SELECT *  
FROM   sys.endpoints; 
SELECT * 
FROM   sys.http_endpoints;

II-G. Sauvegarde du certificat sous forme de fichier

BACKUP CERTIFICATE CRT_MIRROR_SOURCE  
TO FILE = 'C:\CRT_MIRROR_SOURCE_BACKUP.cer';  
GO

II-H. Copiez le certificat sur les serveurs témoins et cible

Par exemple dans un répertoire de nom C:\mirror_objects\

----------------------------------------- 
-- depuis le serveur cible SRV_CIBLE   -- 
-----------------------------------------

II-I. Installation sur serveur cible

II-I-1. Création d'une clef de cryptage pour la base de données master

USE master  
GO 
CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'mon mot de passe';  
GO

II-I-2. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http cible

CREATE CERTIFICATE CRT_MIRROR_CIBLE  
WITH SUBJECT = 'certificat CRT_MIRROR_CIBLE pour la mise en miroir de la base DB_MIRROR', 
          START_DATE = 'AAAAMMJJ'; ; 
GO

II-I-3. Création du point de terminaison http sur le serveur cible en utilisant le certificat pour l'authentification

CREATE ENDPOINT EDP_MIRROR  
STATE = STARTED  
AS TCP(LISTENER_PORT = 7022,  
       LISTENER_IP = ALL)  
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE CRT_MIRROR_CIBLE,  
                        ENCRYPTION = REQUIRED ALGORITHM AES,  
                        ROLE = ALL);  
GO

II-I-4. Sauvegarde du certificat sous forme de fichier

BACKUP CERTIFICATE CRT_MIRROR_CIBLE  
TO FILE = 'C:\CRT_MIRROR_CIBLE_BACKUP.cer';  
GO

II-I-5. Copiez le certificat sur les serveurs témoins et source

Par exemple dans un répertoire de nom C:\mirror_objects\

----------------------------------------- 
-- depuis le serveur témoin SRV_TEMOIN -- 
-----------------------------------------

II-J. Installation sur serveur témoin

II-J-1. Création d'une clef de cryptage pour la base de données master

USE master; 
GO 
CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'mon mot de passe';  
GO

II-J-2. Création du certificat qui sera utilisé pour crypter les données du flux de mirroir sur le point de terminaison http témoin

CREATE CERTIFICATE CRT_MIRROR_TEMOIN  
WITH SUBJECT = 'certificat CRT_MIRROR_TEMOIN pour la mise en miroir de la base DB_MIRROR' 
          START_DATE = 'AAAAMMJJ'; 
GO

II-J-3. Création du point de terminaison http sur le serveur cible en utilisant le certificat pour l'authentification

CREATE ENDPOINT EDP_MIRROR  
STATE = STARTED  
AS TCP(LISTENER_PORT = 7022,  
       LISTENER_IP = ALL)  
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE CRT_MIRROR_TEMOIN,  
                        ENCRYPTION = REQUIRED ALGORITHM AES,  
                        ROLE = ALL);  
GO

II-J-4. Sauvegarde du certificat sous forme de fichier

BACKUP CERTIFICATE CRT_MIRROR_TEMOIN  
TO FILE = 'C:\CRT_MIRROR_TEMOIN_BACKUP.cer';  
GO

II-J-5. Copiez le certificat sur les serveurs cible et source

Par exemple dans un répertoire de nom C:\mirror_objects\

----------------------------------------- 
-- depuis le serveur source SRV_SOURCE -- 
-----------------------------------------

II-K. Mettez en place la sécurité côté source

II-K-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring

USE master;  
GO 
CREATE LOGIN CXN_MIRROR 
WITH PASSWORD = 'mon password à moi';  
GO

II-K-2. Créez un utilisateur associé à ce compte de connexion

CREATE USER U_MIRROR 
FOR LOGIN CXN_MIRROR;  
GO

II-K-3. Associez les certificats externes à cet utilisateur :

D'abord avec le certificat créé pour le serveur cible :

CREATE CERTIFICATE CRT_MIRROR_CIBLE  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_CIBLE_BACKUP.cer'  
GO

Ensuite avec le certificat créé pour le serveur témoin :

CREATE CERTIFICATE CRT_MIRROR_TEMOIN  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_TEMOIN_BACKUP.cer'  
GO

II-K-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion

GRANT CONNECT ON ENDPOINT::EDP_MIRROR TO CXN_MIRROR;  
GO

----------------------------------------- 
-- depuis le serveur cible SRV_CIBLE   -- 
-----------------------------------------

II-L. Mettez en place la sécurité côté cible

II-L-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring

USE master;  
GO 
CREATE LOGIN CXN_MIRROR 
WITH PASSWORD = 'mon password à moi';  
GO

II-L-2. Créez un utilisateur associé à ce compte de connexion

CREATE USER U_MIRROR 
FOR LOGIN CXN_MIRROR;  
GO

II-L-3. Associez les certificats externes à cet utilisateur :

D'abord avec le certificat créé pour le serveur source :

CREATE CERTIFICATE CRT_MIRROR_SOURCE  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_SOURCE_BACKUP.cer'  
GO

Ensuite avec le certificat créé pour le serveur témoin :

CREATE CERTIFICATE CRT_MIRROR_TEMOIN  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_TEMOIN_BACKUP.cer'  
GO

II-L-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion

GRANT CONNECT ON ENDPOINT::EDP_MIRROR TO CXN_MIRROR;  
GO

----------------------------------------- 
-- depuis le serveur témoin SRV_TEMOIN -- 
-----------------------------------------

II-M. Mettez en place la sécurité côté témoin

II-M-1. Créez un compte de connexion pour la connexion au serveur dans la cadre du mirroring

USE master;  
GO 
CREATE LOGIN CXN_MIRROR 
WITH PASSWORD = 'mon password à moi';  
GO

II-M-2. Créez un utilisateur associé à ce compte de connexion

CREATE USER U_MIRROR 
FOR LOGIN CXN_MIRROR;  
GO

II-M-3. Associez les certificats externes à cet utilisateur :

D'abord avec le certificat créé pour le serveur source :

CREATE CERTIFICATE CRT_MIRROR_SOURCE  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_SOURCE_BACKUP.cer'  
GO

Ensuite avec le certificat créé pour le serveur témoin :

CREATE CERTIFICATE CRT_MIRROR_CIBLE  
AUTHORIZATION U_MIRROR  
FROM FILE = 'C:\CRT_MIRROR_CIBLE_BACKUP.cer'  
GO

II-M-4. Donnez les privilèges de connexion au point de terminaison http de mirroring à ce compte de connexion

GRANT CONNECT ON ENDPOINT::EDP_MIRROR TO CXN_MIRROR;  
GO

----------------------------------------- 
-- depuis le serveur cible SRV_CIBLE   -- 
-----------------------------------------

II-N. Préparez le serveur cible pour le démarrage de la session de miroir

Indiquer la source sur la cible

USE master; 
GO  
ALTER DATABASE DB_TO_MIRROR  
SET PARTNER = 'TCP://SRV_SOURCE:7022';  
GO

----------------------------------------- 
-- depuis le serveur source SRV_SOURCE -- 
-----------------------------------------

II-O. Préparez le serveur source pour le démarrage de la session de miroir

Indiquer la cible sur la source :

USE master  
GO  
ALTER DATABASE DB_TO_MIRROR  
SET PARTNER = 'TCP://SRV_CIBLE:7022';  
GO

Indiquer le témoin sur la source

ALTER DATABASE DB_TO_MIRROR  
SET PARTNER = 'TCP://SRV_TEMOIN:7022';  
GO

Ceci a démarré la session de mirroring

II-P. Contrôlez la session

Dans SSMS, sur la base cible, cliquez droit. Dans le menu contextuel choissisez "tâches/Lancer le moniteur de mise en miroir..."

Vous pouvez aussi voir ce qu'il se passe à l'aide de la commande :

SELECT * 
FROM   sys.database_mirroring  
WHERE  database_id = DB_ID('DB_TO_MIRROR')

Si vous voulez modifier le time out de basculement, vous pouvez faire cela sur le serveur source à l'aide de la commande :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET PARTNER TIMEOUT 30;

Le minimum étant 5 seconde, mais je ne vous conseille pas d'aller si bas, car le basculement risque de se faire en cas de micro coupure ou de switchs encombrés. Pour ma part je le fixe en général à 30 secondes.

Si vous voulez un basculement automatique, assurez vous de manière impérative que le réseau entre les serveur est rapide et fiable. Dédiez si besoin est une fibre optique pour ce faire, et utilisez les paramètres d'administration de vos switchs afin de définir un priorité entre le nœud source et le nœud cible. Si tel n'étais pas le cas, alors optez pour le mode asynchrone sinon, en cas de retard pour l'acquittement des transactions envoyées vers la cible, cela va faire grossir le journal de transaction de la base source sans possbilité de le réduire, et plus ce journal grossit, plus il est illusoire de vouloir rattraper le retard !

Si vous voulez vous placer en mode haute sécurité, vous devez le préciser à l'aide de la commande :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET PARTNER SAFETY OFF;

Dans ce cas le témoin ne sera plus utilisé. Vous avez donc intérêt à le désactiver comme ceci :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET WITNESS OFF;

Si vous voulez revenir en mode haute protection, commencez par indiquer quel est le témoin :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET WITNESS OFF;

Puis revenez au au mode SAFETY FULL :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET PARTNER SAFETY FULL;

Pour forcer la cible à devenir la source en cas de suspension de service du miroir, lancez la commande :

USE master; 
GO 
ALTER DATABASE DB_TO_MIRROR 
SET PARTNER RESUME;

La mise en miroir d'une base de données ne reproduit que les données de la base.

Vous pouvez avoir besoin d'autres informations sur le serveur cible en cas de basculement :

Les comptes de connexion ;
Les éléments de l'Agent SQL Server (travaux, alertes et opérateurs) ;
Les paquetages SSIS voire DTS ;
Les serveurs liés ;
Les unités de sauvegarde (devices) ;
Les plan de maintenance ;
La configuration de Database Mail (ou pire de SQL Mail).

Vous pouvez utiliser des déclencheurs DDL niveau serveur et à l'aide d'un serveur lié reproduire immédiatement sur le serveur cible les commandes SQL découlant de la mise en place de ces objets. Mais attention. Afin que cela ne fasse pas ping-pong, je vous conseille de tester l'état de miroir de votre serveur pour la base mirorée, avant de lancer la reproduction de cette commande :

SELECT mirroring_state --> doit être 2 ou 4 (voir NULL) 
FROM   sys.database_mirroring  
WHERE  database_id = DB_ID('DB_TO_MIRROR')

Enfin, sachez que les transactions distribuées ne sont pas supportées dans le cas de la mise en miroir.

III. Auditer le mirroir

Si l'envie vous en prend, ne serait-ce que pour analyser la charge de vos tuyaux, vous pouvez utiliser le moniteur de performances (perfmon.exe) et scruter les compteurs afférent à l'entrée MSSQL[$instance]:Database Mirroring.

Version PDF (Miroir) Version hors-ligne (Miroir)

Copyright © 2009 SQLPro. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.