[SQL Server] Mise-en-place du database mirroring (via T-SQL)

Ce billet traite de la mise-en-place d’une solution de database mirroring en T-SQL, que ce soit en mode synchrone (avec ou sans failover automatique) ou en mode asynchrone. Il concerne toutes les versions de SQL Server à partir de 2005. Le mode d’authentification par certificat sera utilisé dans nos exemples. Pour comprendre le fonctionnement du database mirroring, référez-vous au billet [SQL Server] Un peu de théorie sur le database mirroring…

Pré-requis général du database mirroring

Pour mener à bien la mise-en-place du database mirroring, il faut surtout que :

  • Les deux serveurs (le principal) et le secondaire (ou miroir)) aient la même version de MSSQL (supérieur ou égal à 2005), ainsi que la même édition.
  • Que le serveur miroir ait assez d’espace pour la base de données secondaire.
  • Que le serveur principal n’ait pas de groupes de fichiers de type FILESTREAM.
  • Que le serveur sur lequel se trouve la base de données miroir doit permettre d’avoir une charge moyenne inférieure à 50% du CPU en cas d’utilisation en mode synchrone. En effet, en cas de surcharge (>50% du CPU consommé), le basculement (i.e., « transformation » de la base de données secondaire en base de données principale) est impossible à cause d’un gros temps de latence de la session de mirroring permettant aux bases de données concernées (i.e., base de données principale, base de données secondaire) de communiquer.
  • Chaque base de données concernée par la même session de mirroring doit avoir la même collation.
  • Le protocole de canaux nommés (Named Pipes) doit être activé.
  • Les serveurs doivent être accessibles entre eux via ce qu’on appelle FQDN (Fully Qualified Domain Names)[1] : <nom_serveur>.<nom_domaine>.local ou <nom_serveur>.<suffixe_dns_primaire>. Si les serveurs sont en Workgroup, il faut idéalement créer des alias dans le fichier host de chaque serveur pour que cette résolution puisse fonctionner.
  • Avant tout basculement d’une base principale vers une base secondaire, assurez-vous que le(s) login(s) SQL aient le même identifiant de sécurité (SID) des deux côtés de façon à ce que le mapping du (des) login(s) SQL se fasse automatiquement pour la base secondaire devenue miroir. Pour plus de détails, voir un prochain billet.

Dans notre exemple de mise-en-place, nous supposerons :

  • Que l’on possède 3 serveurs :
    • SRV-SQL01, comme serveur principal.
    • SRV-SQL02, comme serveur secondaire (ou miroir).
    • SRV-SQL03, comme serveur témoin (ou witness).
  • Qu’on ait pour nom de domaine, IFC.
  • Qu’on veuille mettre en miroir une base de données nommée IFC_SUITE située sur le serveur SRV-SQL01.

Modus operandi de mise-en-place du database mirroring

… en mode synchrone avec failover automatique

La mise en miroir d’une base de données est relativement simple. Elle peut se faire via T-SQL en respectant le modus operandi standard suivant :

  • Si ce n’est pas déjà le cas, sur le serveur secondaire, création d’une base de données vide en mode de récupération FULL portant le même nom que la base de données principale située sur le serveur principal.
  • Sur le serveur principal, sauvegarde complète de la base de données principale (appelons le fichier BAK IFC_full_backup.bak)
    ainsi que de son journal de transactions (ifc_suite_tran_backup.trn) :
USE master
GO
–- Full backup
BACKUP DATABASE IFC_SUITE
TO DISK=N'S:\SQL Backups\ifc_suite_full_backup.bak'
GO
–- TRN backup
BACKUP LOG IFC_SUITE
TO DISK=N'S:\SQL Backups\ifc_suite_tran_backup.trn'
GO
  • Sur le serveur secondaire, restauration de la base de données secondaire à partir des sauvegardes effectuées dans l’étape 2 en mode de restauration NORECOVERY :
USE master
GO
–- Restore full backup
RESTORE DATABASE IFC_SUITE
FROM DISK=N'S:\SQL Backups\ifc_suite_full_backup.bak'
WITH REPLACE, NORECOVERY
GO
–- Restore log
RESTORE LOG IFC_SUITE
FROM DISK=N'S:\SQL Backups\ifc_suite_tran_backup.trn'
WITH NORECOVERY
GO

On part bien sûr du principe que les fichiers de sauvegardes de la base de données principale ont été copies sur le serveur secondaire.

Si tout va bien, l’état de la base de données secondaire (futur miroir) IFC_SUITE sera en mode de restauration.

  • Création d’une master key, d’un certificat et d’un endpoint sur chaque serveur :
    • Sur SRV-SQL01 (principal) :
USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='p4ssSQL01!'
GO
CREATE CERTIFICATE SQL01_Cert
WITH SUBJECT='SQL01 certificate',
START_DATE= '12/09/2011',
EXPIRY_DATE= '11/09/2021'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS
TCP(LISTENER_PORT = 5022, LISTENER_IP=ALL)
FOR
Database_Mirroring(AUTHENTICATION = CERTIFICATE SQL01_Cert,
ENCRYPTION=DISABLED,ROLE=ALL) –- on peut utiliser 'PARTNER' à la place de 'ALL'
GO
BACKUP CERTIFICATE SQL01_Cert
TO FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL01_Cert.cer'
GO
  • Sur SRV-SQL02 (secondaire):
USE master
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='p4ssSQL02!'
GO
CREATE CERTIFICATE SQL02_Cert
WITH SUBJECT='SQL02 certificate',
START_DATE= '12/09/2011',
EXPIRY_DATE= '11/09/2021'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS
TCP(LISTENER_PORT = 5022, LISTENER_IP=ALL)
FOR
Database_Mirroring(AUTHENTICATION = CERTIFICATE SQL02_Cert,
ENCRYPTION=DISABLED,ROLE=ALL) –- on peut utiliser 'PARTNER' à la place de 'ALL'
GO
BACKUP CERTIFICATE SQL02_Cert
TO FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL02_Cert.cer'
GO
  • Sur SRV-SQL03 (witness):
USE master
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='p4ssSQL03!'
GO
CREATE CERTIFICATE SQL03_Cert
WITH SUBJECT='SQL03 certificate',
START_DATE= '09/12/2011',
EXPIRY_DATE= '09/11/2021'
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS
TCP(LISTENER_PORT = 5022, LISTENER_IP=ALL)
FOR
Database_Mirroring (AUTHENTICATION = CERTIFICATE SQL03_Cert,
ENCRYPTION=DISABLED,ROLE=ALL) –-doit obligatoirement etre WITNESS si Express Edition
GO
BACKUP CERTIFICATE SQL03_Cert
TO FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL03_Cert.cer'
GO

Le port d’écoute choisi doit être ouvert et non-utilisé par une autre application, sinon le choix d’un autre port disponible s’impose. Par ailleurs, le chemin de destination des certificats est, ici, arbitraire et peut être éventuellement modifié.

  • Récupération des 3 certificats (C:\Program Files\Microsoft SQL Server\Certificates\SQL01_Cert.cer, C:\Program Files\Microsoft SQL Server\Certificates\SQL02_Cert.cer et C:\Program Files\Microsoft SQL Server\Certificates\SQL03_Cert.cer respectivement sur SRV-SQL01, SRV-SQL02 et SRV-SQL03) et copie sur chaque serveur (dans le répertoire C:\Program Files\Microsoft SQL Server\Certificates de chaque serveur, par exemple) de façon à pouvoir les importer sur chacun d’entre eux.
  • Création et configuration des logins SQL de connexion :
    • Sur SRV-SQL01 :
USE master
GO
CREATE LOGIN SQL02_mirror WITH PASSWORD='SQL02P455L0g1n!'
GO
CREATE USER SQL02_mirror FROM LOGIN SQL02_mirror
GO
CREATE CERTIFICATE SQL02_Cert AUTHORIZATION SQL02_mirrorFROM FILE= N'C:\Program Files\Microsoft SQL Server\Certificates\SQL02_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring
TO SQL02_mirror
GO
–- Si witness SQL03 présent
CREATE LOGIN SQL03_witness WITH PASSWORD='SQL03P455L0g1n!'
GO
CREATE USER SQL03_witness FROM LOGIN SQL03_witness
GO
CREATE CERTIFICATE SQL03_Cert AUTHORIZATION SQL03_witness
FROM FILE='C:\Program Files\Microsoft SQL Server\Certificates\SQL03_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring
TO SQL03_witness
GO
  • Sur SRV-SQL02:
USE master
GO
CREATE LOGIN SQL01_mirror WITH PASSWORD='SQL01P455L0g1n!'
GO
CREATE USER SQL01_mirror FROM LOGIN SQL01_mirror
GO
CREATE CERTIFICATE SQL01_Cert AUTHORIZATION SQL01_mirrorFROM FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL01_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring
TO SQL01_mirror
GO
–- Si witness SQL03 présent
CREATE LOGIN SQL03_witness WITH PASSWORD='SQL03P455L0g1n!'
GO
CREATE USER SQL03_witness FROM LOGIN SQL03_witness
GO
CREATE CERTIFICATE SQL03_Cert AUTHORIZATION SQL03_witness
FROM FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL03_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring
TO SQL03_witness
GO
  • Sur SRV-SQL03 (witness):
USE master
GO
CREATE LOGIN SQL01_mirror WITH PASSWORD='SQL01P455L0g1n!'
GO
CREATE USER SQL01_mirror FROM LOGIN SQL01_mirror
GO
CREATE CERTIFICATE SQL01_Cert AUTHORIZATION SQL01_mirrorFROM FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL01_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL01_mirror
GO
USE
MASTER
GO
CREATE LOGIN SQL02_mirror WITH PASSWORD='SQL02P455L0g1n!'
GO
CREATE USER SQL02_mirror FROM LOGIN SQL02_mirror
GO
CREATE CERTIFICATE SQL02_Cert AUTHORIZATION SQL02_mirror
FROM FILE=N'C:\Program Files\Microsoft SQL Server\Certificates\SQL02_Cert.cer'
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO SQL02_mirror
GO
  • Mise-en-miroir de la base de données d’SRV-SQL01 :

La valeur des PARTNER et de WITNESS doit utiliser idéalement une adresse FQDN. On supposera que le suffixe du DNS primaire (pouvant être trouvable via ipconfig, pour rappel) est ifc.com.

  • Sur SRV-SQL02 :
USE master
GO
ALTER DATABASE IFC_SUITE SET PARTNER='TCP://SRV-SQL01.ifc.com:5022'
GO
  • Sur SRV-SQL01 :
USE master
GO
ALTER DATABASE IFC_SUITE SET PARTNER='TCP://SRV-SQL02.ifc.com:5022'
GO
  • Sur SRV-SQL01 :
USE master
GO
ALTER DATABASE IFC_SUITE SET WITNESS='TCP://SRV-SQL03.ifc.com:5022'
GO
Remarquons qu’en ce qui concerne la configuration des paramètres réseaux TCP/IP des serveurs, il est possible de substituer le nom de la machine par son IP. Mais n’est pas conventionnel.

… en mode synchrone sans failover automatique

Suivez les mêmes étapes que pour le mode synchrone avec failover automatique, sauf pour la création du witness. Puis au moment de la mise en miroir, ignorez simplement l’activation du witness.

… en mode asynchrone

Suivez les mêmes étapes que pour le mode synchrone avec failover automatique (en ignorant, bien sûr, la création du witness), puis au moment de la mise en miroir de la base principale mettez l’option SAFETY à OFF (par défaut, elle est à FULL ce qui correspond à la mise-en-miroir en mode synchrone (haute-sécurité)) :

  • Sur SRV-SQL02 :
USE master
GO
ALTER DATABASE IFC_SUITE SET PARTNER='TCP://SRV-SQL01.ifc.com:5022'
GO
  • Sur SRV-SQL01 (serveur principal, pour rappel) :
USE master
GO
ALTER DATABASE IFC_SUITE SET PARTNER='TCP://SRV-SQL02.ifc.com:5022'
GO
ALTER DATABASE IFC_SUITE SET PARTNER SAFETY OFF
GO
Notons que :

  • L’utilisation d’un witness n’est pas supportée. Il est recommandé de s’assurer de la désactivation de tout witness potentiel avant l’activation du mode de haute-performance (mode asynchrone).
  • Tout failover (automatique ou manuel) n’est pas possible, à l’instar que pour le database mirroring synchrone sans failover automatique.

Etat des bases de données impliquées

Après création et configuration de la session miroir, il est possible de noter l’état de

  • La base principale :

Et…

  • Celle secondaire :

Pour aller plus loin…

Dans un prochain billet, nous aborderons différents éléments d’audit relatifs au database mirroring, ainsi que des opérations avancées très utiles.


[1]Peut être identifiable via ifconfig sur l’invite de commande MS-DOS ou Windows Powershell.
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s