Mirroring Sqlserver 2005
Par Minitux le lundi, août 3 2009, 14:22 - Base de données - Lien permanent
Cet article peut sembler étrange car il n'est pas dans la lignée des autres articles libres mais ceci est le résultat d'une demande à mon boulot. Le tout est de mettre en place un système de mirroring sur une base sqlserver entre deux serveurs avec seulement deux serveurs, mais 3 instances (principal, mirror, témoin). Puisque nous ne sommes pas dans le monde merveilleux et secure de Microsoft (pas d'AD), il faudra tout d'abord créer une authentification entre les deux serveurs à l'aide d'un système de clé. Ce qui va permettre la communication entre le serveur principal et le seveur mirror.
1- Configuration des connexions sortantes sur le serveur principal
Dans la base de données master, créez la clé principale de base de données, si nécessaire.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO
Activez un certificat pour cette instance de serveur.
USE master;
CREATE CERTIFICATE HOST_princ_cert
WITH SUBJECT = 'HOST_princ certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Créez un point de terminaison de mise en miroir pour l'instance de serveur à l'aide du certificat.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_princ_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
Sauvegardez le certificat HOST_princ, et copiez-le sur l'autre système, HOST_mirror.
BACKUP CERTIFICATE HOST_princ_cert TO FILE = 'c:\HOST_princ_cert.cer';
GO
2- Configuration Host mirror pour les connexions sortantes
Dans la base de données master, créez la clé principale de base de données, si nécessaire.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
GO
Activez un certificat sur l'instance de serveur HOST_mirror.
CREATE CERTIFICATE HOST_mirror_cert
WITH SUBJECT = 'HOST_mirror certificate for database mirroring', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Créez un point de terminaison de mise en miroir pour l'instance de serveur sur HOST_mirror.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_mirror_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
Sauvegardez le certificat HOST_mirror, puis copie sur HOST_princ
BACKUP CERTIFICATE HOST_mirror_cert TO FILE = 'c:\HOST_mirror_cert.cer';
GO
3- Configuration Host temoin pour les connexions sortantes
Dans la base de données master, créez la clé principale de base de données, si nécessaire.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abc123!!';
GO
Activez un certificat sur l'instance de serveur HOST_temoin.
CREATE CERTIFICATE HOST_temoin_cert
WITH SUBJECT = 'HOST_temoin certificate', start_date = '2007/11/01', expiry_date = '2020/11/01';
GO
Créez un point de terminaison de mise en miroir pour l'instance de serveur sur HOST temoin.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7022
, LISTENER_IP = ALL
@
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_temoin_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = WITNESS
);
GO
Sauvegardez le certificat HOST_temoin, puis copie sur HOST_princ
BACKUP CERTIFICATE HOST_temoin_cert TO FILE = 'c:\HOST_temoin_cert.cer';
GO
4- Configuration des connexions entrantes sur HOST_princ
Créez une connexion sur HOST_princ pour HOST_mirror.
USE master;
CREATE LOGIN HOST_mirror_login WITH PASSWORD = '1Sample_Strong_Password!@#';
GO
Créez un utilisateur pour cette connexion.
CREATE USER HOST_mirror_user FOR LOGIN HOST_mirror_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_mirror_cert
AUTHORIZATION HOST_mirror_user
FROM FILE = 'c:\HOST_mirror_cert.cer'
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_mirror_login];
GO
Créez une connexion sur HOST_princ pour HOST_temoin.
CREATE LOGIN HOST_temoin_login with PASSWORD = 'abc123!!';
GO
Créez un utilisateur pour cette connexion.
CREATE USER HOST_temoin_user from login HOST_temoin_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_temoin_cert
AUTHORIZATION HOST_temoin_user
FROM FILE = 'c:\HOST_temoin_cert.cer';
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON Endpoint::endpoint_mirroring TO [HOST_temoin_login];
GO
5- Configuration Host mirror pour les connexions entrantes
Créez une connexion sur HOST_mirror pour HOST_princ.
USE master;
CREATE LOGIN HOST_princ_login WITH PASSWORD = '=Sample#2_Strong_Password2';
GO
Création d'un utilisateur pour cette connexion
CREATE USER HOST_princ_user FOR LOGIN HOST_princ_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_princ_cert
AUTHORIZATION HOST_princ_user
FROM FILE = 'c:\HOST_princ_cert.cer'
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_princ_login];
GO
Créez une connexion sur HOST_mirror pour HOST_temoin.
USE master;
CREATE LOGIN HOST_temoin_login WITH PASSWORD = 'abc123!!';
GO
Création d'un utilisateur pour cette connexion
CREATE USER HOST_temoin_user FROM LOGIN HOST_temoin_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_temoin_cert
AUTHORIZATION HOST_temoin_user
FROM FILE = 'c:\HOST_temoin_cert.cer';
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON Endpoint::Endpoint_mirroring to [HOST_temoin_login];
GO
6- Configuration Host temoin pour les connexions entrantes
Créez une connexion sur HOST_temoin pour HOST_princ.
CREATE LOGIN HOST_princ_login WITH PASSWORD = 'abc123!!';
GO
Création d'un utilisateur pour cette connexion
CREATE USER HOST_princ_user FROM LOGIN HOST_princ_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_princ_cert
AUTHORIZATION HOST_princ_user
FROM FILE = 'c:\HOST_princ_cert.cer';
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON Endpoint::Endpoint_mirroring to [HOST_princ_login];
GO
Créez une connexion sur HOST_temoin pour HOST_mirror.
CREATE LOGIN HOST_mirror_login with PASSWORD = 'abc123!!';
GO
Création d'un utilisateur pour cette connexion
CREATE USER HOST_mirror_user from login HOST_mirror_login;
GO
Associez le certificat à l'utilisateur.
CREATE CERTIFICATE HOST_mirror_cert
AUTHORIZATION HOST_mirror_user
FROM FILE = 'c:\HOST_mirror_cert.cer';
GO
Accordez l'autorisation CONNECT à la connexion pour le point de terminaison de mise en miroir distant.
GRANT CONNECT ON Endpoint::endpoint_mirroring to [HOST_mirror_login];
GO
7- Préparation une base de données miroir pour la mise en miroir
Pour utiliser la mise en miroir de la base AdventureWorks , on la modifie de restauration complète :
USE master;
GO
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
Sur le serveur principal, création d'une sauvegarde complète de la base de données principale comme suit :
BACKUP DATABASE AdventureWorks
TO DISK = 'c:\AdventureWorksfull.bak'
WITH FORMAT
GO
Copiez cette sauvegarde complète sur le serveur miroir.
Restaurez ensuite la sauvegarde complète à l'aide de l'option WITH NORECOVERY sur le serveur miroir.
RESTORE DATABASE AdventureWorks
FROM DISK='c:\AdventureWorksfull.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO
'c:\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO
'c:\AdventureWorks_Log.ldf';
GO
Après avoir créé la sauvegarde complète, création d'une sauvegarde du journal sur la base de données principale.
BACKUP LOG AdventureWorks
TO DISK = 'C:\AdventureWorksfull_log.bak'
GO
Avant de démarrer la mise en miroir, vous devez appliquer la sauvegarde du journal requise
RESTORE LOG AdventureWorks
FROM DISK = 'c:\AdventureWorksfull_log.bak'
WITH FILE=1, NORECOVERY
GO
8- Configuration des serveurs partenaires de mise en miroir
Sur l'instance de serveur miroir de HOST_mirror, définissez l'instance de serveur de HOST_princ en tant que serveur partenaire.
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://XX.XX.XX.XX:7024';
GO
Sur l'instance de serveur principal de HOST_princ, définissez l'instance de serveur de HOST_mirror
et l'instance de temoin en tant que serveur partenaire
ALTER DATABASE AdventureWorks
SET PARTNER = 'TCP://XX.XX.XX.XX:7024';
GO
ALTER DATABASE AdventureWorks
SET WITNESS = 'TCP://XX.XX.XX.XX:7022';
GO
Failover manuelle sur le mirroring
Sur le serveur principale
ALTER DATABASE GLOW3
SET PARTNER SAFETY FULL
GO
USE master;
ALTER DATABASE AdventureWorks SET PARTNER FAILOVER
Pour savoir qui est qui ?
SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks'
GO
Que faire lors du crash du serveur principal sans une instance de temoin
ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
9- Quelques liens
Pour plus d'infos voici d'où viennent les sources