Depuis SQL Server 2005 (SP1) il est possible de mettre une base de données en miroir. Cette solution de haute disponibilité et/ou de disaster recovery solutionne quelques problématiques liées au RTO (la bascule se fait très rapidement) et au RPO (il n’y a pas de perte de données pour un miroir synchrone lorsque toutes les instances sont en cours d’exécution).
Lorsque qu’une transaction arrive sur le server principal, cette transaction est alors “dupliquée” vers les serveur miroir. En mode synchrone, un acquittement n’est renvoyé à l’applicatif client que lorsque l’instance principale et l’instance miroir ont écrit sur disque (dans le fichier LDF) la transaction. En mode asynchrone, le client reçoit un acquittement lorsque le serveur principal, et seulement lui, a écrit l’information dans le journal des transactions.
Les communications entre instances (principal, miroir et éventuellement témoin) se font au travers de points de terminaison TCP, ou end points.
Pour s’authentifier mutuellement, il existe deux possibilités :
-
Soit les instances sont dans le même domaine active directory, auquel cas, l’authentification du compte de service de chaque instance peut suffire,
-
Soit les instances sont dans des domaines ou des workgroups distincts, auquel cas l’authentification passe par des certificats.
Et comme tous certificats, ceux utilisés par SQL Server ont une durée de validité … De un an si aucune mention contraire n’a été spécifiée lors de leur création.
CREATE CERTIFICATE Certificate_Principal_Instance
WITH SUBJECT = 'Certificate Principal Instance';
GO
Un certificat généré de la sort va expirer un an après. Un tel cas s’est présenté à moi il y a quelques jours. Le journal d’évènement de SQL Server présente alors des erreurs du type :
Database Mirroring login attempt failed with error: ‘Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.’. [CLIENT: xxx.xxx.xxx.xxx]
Pas de panique pour autant, les connexions et les transactions sur le serveur principal sont toujours possible. En fait la base n’est plus protégée, les bascules ne seront pas possibles.
Le changement de certificat est possible, sans casser le miroir ni provoquer d’indisponibilité. Voici un petit script qui vous permettra de changer vos certificats.
--
-- Pause du mirroring
--
:Connect Principal_Instance
USE master;
ALTER DATABASE my_mirrorred_db SET PARTNER SUSPEND;
GO
--
-- Création et sauvegarde des nouveaux certificats
--
:Connect Principal_Instance
USE master;
CREATE CERTIFICATE [New_Certificate_Principal_Instance]
WITH SUBJECT = 'New Certificate Principal Instance',
START_DATE='01/01/2012',
EXPIRY_DATE='01/01/2020';
GO
BACKUP CERTIFICATE New_Certificate_Principal_Instance
TO FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Principal_Instance.cer';
GO
:Connect Mirror_Instance
USE master;
CREATE CERTIFICATE [New_Certificate_Mirror_Instance]
WITH SUBJECT = 'New Certificate Mirror Instance',
START_DATE='01/01/2012',
EXPIRY_DATE='01/01/2020';
GO
BACKUP CERTIFICATE New_Certificate_Mirror_Instance
TO FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Mirror_Instance.cer';
GO
:Connect Witness_Instance
USE master;
CREATE CERTIFICATE [New_Certificate_Witness_Instance]
WITH SUBJECT = 'New Certificate Witness Instance',
START_DATE='01/01/2012',
EXPIRY_DATE='01/01/2020';
GO
BACKUP CERTIFICATE New_Certificate_Witness_Instance
TO FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Witness_Instance.cer';
GO
--
-- Ajout des nouveaux certificates sur autres servers de la session de mise en miroir
--
:Connect Principal_Instance
USE master;
CREATE CERTIFICATE New_Certificate_Mirror_Instance
AUTHORIZATION SQL_User_Mirror_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Mirror_Instance.cer'
GO
CREATE CERTIFICATE New_Certificate_Witness_Instance
AUTHORIZATION SQL_User_Witness_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Witness_Instance.cer'
GO
:Connect Mirror_Instance
USE master;
CREATE CERTIFICATE New_Certificate_Principal_Instance
AUTHORIZATION SQL_User_Principal_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Principal_Instance.cer'
GO
CREATE CERTIFICATE New_Certificate_Witness_Instance
AUTHORIZATION SQL_User_Witness_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Witness_Instance.cer'
GO
:Connect Witness_Instance
USE master;
CREATE CERTIFICATE New_Certificate_Principal_Instance
AUTHORIZATION SQL_User_Principal_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Principal_Instance.cer'
GO
CREATE CERTIFICATE New_Certificate_Mirror_Instance
AUTHORIZATION SQL_User_Mirror_Instance
FROM FILE = '\\FileServer\FileShare\Certificates_backup\New_Certificate_Mirror_Instance.cer'
GO
--
-- Mise à jour des endpoint du mirroring pour utiliser les nouveaux certificats
--
:Connect Principal_Instance
USE master;
ALTER ENDPOINT Endpoint_Mirroring
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [New_Certificate_Principal_Instance])
GO
:Connect Mirror_Instance
USE master;
ALTER ENDPOINT Endpoint_Mirroring
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [New_Certificate_Mirror_Instance])
GO
:Connect Witness_Instance
USE master;
ALTER ENDPOINT Endpoint_Mirroring
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [New_Certificate_Witness_Instance])
GO
--
-- Reprise du Mirroring
--
:Connect Principal_Instance
USE master;
ALTER DATABASE my_mirrorred_db SET PARTNER RESUME;
GO
Rien de bien compliqué …
N’hésitez pas non plus à tester SQL Server 2012 qui propose une version très améliorée du database mirroring. Les groupes de disponibilité (AlwaysOn Availability Groups) permettent de pallier à différentes lacunes du DBM :
- On peut grouper plusieurs bases et les protéger, la bascule sur un serveur secondaire se fera pour toutes les bases du groupe.
- On peut avoir jusqu’à 4 instances secondaires.
- Au maximum 2 réplicas en bascule automatique.
- Au maximum 3 réplicas en mode synchrone.
- Le REDO se fait en permanence sur les serveurs secondaires ce qui permet d’avoir accès en lecture seule sans passer par un snapshot de base de donnée.
- La sauvegarde peut se faire sur les réplicas secondaires (Backup Database with COPY_ONLY, Backup LOG).
- Ajout d’un listener qui permet d’avoir un nom réseau et une adresse IP virtuelle pour les groupe de disponibilité, évitant ainsi les problèmes de chaines de connexion ne supportant pas le Failover Partner et qui élimine le problème des serveurs liés.
- Les bases sur le réplica sont en mode d’isolation read committed snapshot et les statistiques d’index temporaires sont créées en TempDB.
- La réplication est supportée (pour l’éditeur et l’abonné).
- Le listener permet aussi d’aiguiller les requêtes qui font de la lecture seule (SELECT).
- Les bases de données partiellement contenues permettent de se détacher de la base Master pour l’authentification.
