¿Cómo hacer DB Mirroring utilizando certificados? (SQL Server 2008 R2)
A continuación facilitamos un método de puesta a punto de Mirroring de Base de Datos en SQL Server 2008 R2.
Las sentencias a ejecutar se encuentran organizadas por orden secuencial (o cronológico) de ejecución y clasificadas entre el servidor principal (o máster) y el de mirroring (o “reflejo de Base de Datos”).
Siguiendo los pasos estipulados en la tabla siguiente podrá configurar un sistema de DB Mirroring de SQL Server 2008 (R2).
Tenga en cuenta que es probable que su instalación de SQL Server o que su Base de Datos difiera del sistema estándar que comentamos. Recomendamos primero que comprenda el porqué de todos los pasos explicados a continuación y luego los aplique bajo su criterio según los requisitos de sus sistemas.
|
Principal
|
Mirror
|
||
|
Prepare instances
|
|||
|
Create database
master key, if needed |
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<strong password>’
GO
|
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<strong password>’
GO
|
|
|
If you want or
need to delete the data base master key |
USE master
GO
DROP MASTER KEY
GO
|
USE master
GO
DROP MASTER KEY
GO
|
|
|
Make certificates
|
USE master
GO
CREATE CERTIFICATE LAB_SRV1_cert
WITH SUBJECT
= ‘LAB-SRV1 certificate’ GO
|
USE master
GO
CREATE CERTIFICATE LAB_SRV2_cert
WITH SUBJECT
= ‘LAB-SRV2 certificate’ GO
|
|
|
If you want or need to
delete certificates |
USE master
GO
DROP CERTIFICATE LAB_SRV1_cert
GO
|
USE master
GO
DROP CERTIFICATE LAB_SRV2_cert
GO
|
|
|
Create mirroring
endpoint |
USE master
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP ( LISTENER_PORT=5022
, LISTENER_IP = ALL
) FOR DATABASE_MIRRORING( AUTHENTICATION
= CERTIFICATE LAB_SRV1_cert , ENCRYPTION = REQUIRED ALGORITHM
AES , ROLE = ALL
) GO
|
USE master
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED AS TCP ( LISTENER_PORT=5022
, LISTENER_IP = ALL
) FOR DATABASE_MIRRORING( AUTHENTICATION
= CERTIFICATE LAB_SRV2_cert , ENCRYPTION = REQUIRED ALGORITHM
AES , ROLE = ALL
) GO
|
|
|
If you want or need to delete endpoint
|
USE master
GO
DROP ENDPOINT [Endpoint_Mirroring]
GO
|
USE master
GO
DROP ENDPOINT [Endpoint_Mirroring]
GO
|
|
|
Backup certificates
|
USE master
GO
BACKUP CERTIFICATE LAB_SRV1_cert
TO FILE = ‘C:\BACKUPS\LAB_SRV1_cert.cer’ GO
|
USE master
GO
BACKUP CERTIFICATE LAB_SRV2_cert
TO FILE = ‘C:\BACKUPS\LAB_SRV2_cert.cer’ GO
|
|
|
Copy certificates
between instances |
Use any secure copy method
|
Use any secure copy method
|
|
|
Configure inbound connections
|
|||
|
Create logins
|
USE master
GO
CREATE LOGIN LAB_SRV2_login
WITH PASSWORD = ‘<strong password>’ GO
|
USE master
GO
CREATE LOGIN LAB_SRV1_login
WITH PASSWORD = ‘<strong password>’ GO
|
|
|
Create user for
login |
USE master
GO
CREATE USER LAB_SRV2_user FOR
LOGIN LAB_SRV2_login GO
|
USE master
GO
CREATE USER LAB_SRV1_user FOR
LOGIN LAB_SRV1_login GO
|
|
|
Associate the
certificate with the user |
USE master
GO
CREATE CERTIFICATE LAB_SRV2_cert
AUTHORIZATION LAB_SRV2_user
FROM FILE
= ‘C:\BACKUPS\LAB_SRV2_cert.cer’ GO
|
USE master
GO
CREATE CERTIFICATE LAB_SRV1_cert
AUTHORIZATION LAB_SRV1_user
FROM FILE
= ‘C:\BACKUPS\LAB_SRV1_cert.cer’ GO
|
|
|
Grant CONNECT
permission at login for the remote mirroring endpoint |
USE master
GO
GRANT CONNECT
ON ENDPOINT::Endpoint_Mirroring TO [LAB_SRV2_login] GO
|
USE master
GO
GRANT CONNECT
ON ENDPOINT::Endpoint_Mirroring TO [LAB_SRV1_login] GO
|
|
|
Prepare database
|
|||
|
Create database
|
CREATE DATABASE [DBMTest] ON PRIMARY
(NAME =
N’DBMTest’, FILENAME = N’C:\MSSQL\DATA\DBMTest.mdf’)
LOG ON
(NAME =
N’DBMTest_log’ , FILENAME = N’C:\MSSQL\DATA\DBMTest_log.ldf’)
GO
USE [DBMTest]
GO
CREATE TABLE [dbo].[TTest](
[ID] [int] IDENTITY(1,1) NOT NULL, [DayAndTime] [datetime] NOT NULL )
GO
|
||
|
Backup database
|
BACKUP DATABASE [DBMTest]
TO DISK = N’C:\BACKUPS\DBMTest.bak’ WITH NOFORMAT,
INIT,
NAME = N’DBMTest-Full Database Backup’, SKIP,
NOREWIND,
NOUNLOAD,
STATS =
10 GO
|
||
|
Backup transaction
log |
BACKUP LOG [DBMTest]
TO DISK = N’C:\BACKUPS\DBMTest_log.bak’
WITH NOFORMAT,
INIT,
NAME = N’DBMTest-Transaction Log Backup’, SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10 GO
|
||
|
Restore database
|
RESTORE DATABASE [DBMTest]
FROM
DISK = N’C:\BACKUPS\DBMTest.bak’ WITH
FILE = 1, MOVE N’DBMTest_log’
TO N’C:\MSSQL\DATA\DBMTest.ldf’, NORECOVERY,
NOUNLOAD, STATS = 10 GO
|
||
|
Restore transaction
log |
RESTORE LOG [DBMTest]
FROM DISK = N’C:\BACKUPS\DBMTest_log.bak’ WITH
FILE = 1, NORECOVERY,
NOUNLOAD,
STATS =
10 GO
|
||
|
Configure the mirroring
partners and start mirroring |
|||
|
First, on the mirror
instance |
ALTER DATABASE DBMTest
SET PARTNER = ‘TCP://LAB-SRV1:5022′ GO
|
||
|
And then, on
the principal instance |
ALTER DATABASE DBMTest
SET PARTNER = ‘TCP://LAB-SRV2:5022′ GO
|
||
|
Configure performance
and safety mode |
|||
|
High performance (asynchronous
mode) |
ALTER DATABASE DBMTest SET
PARTNER SAFETY OFF GO
|
||
|
High safety
(synchronous mode) |
ALTER DATABASE DBMTest SET
PARTNER SAFETY FULL GO
|
||
|
Managing database
mirroring |
|||
|
Pause mirroring
|
ALTER DATABASE DBMTest SET PARTNER SUSPEND
|
||
|
Resume mirroring
|
ALTER DATABASE DBMTest SET PARTNER RESUME
|
||
|
Manual failover (asynchronous
mode) |
ALTERDATABASE DBMTest SET SAFETY FULL
GO
ALTERDATABASE DBMTest SET PARTNER FAILOVER
GO
ALTERDATABASE DBMTest SET SAFETY FULL
GO
|
||
|
Manual failover
(synchronous mode) |
ALTERDATABASE DBMTest SET PARTNER FAILOVER
GO
|
||
|
Remove database mirroring
|
ALTER DATABASE DBMTest SET PARTNER OFF
|
||
Sobre el autor
Toni Planas es Ingeniero de Sistemas y forma parte del equipo de especialistas de CAPSiDE. A lo largo de su carrera profesional se ha convertido en especialista de tecnologías y soluciones de Microsoft, centrándose sobretodo en lo referente a Sistemas Operativos, Bases de Datos SQL Server y Soluciones de Correo/Mensajería.
