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:BACKUPSLAB_SRV1_cert.cer’
GO
USE master
GO
BACKUP CERTIFICATE LAB_SRV2_cert

TO FILE
= ‘C:BACKUPSLAB_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:BACKUPSLAB_SRV2_cert.cer’
GO
USE master
GO
CREATE CERTIFICATE LAB_SRV1_cert
   AUTHORIZATION LAB_SRV1_user
   FROM FILE
= ‘C:BACKUPSLAB_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:MSSQLDATADBMTest.mdf’)
LOG ON
 (NAME =
N’DBMTest_log’            ,
   FILENAME = N’C:MSSQLDATADBMTest_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:BACKUPSDBMTest.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:BACKUPSDBMTest_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:BACKUPSDBMTest.bak’
   WITH
FILE =
1, 
   MOVE N’DBMTest_log’
TO N’C:MSSQLDATADBMTest.ldf’,
   NORECOVERY,

NOUNLOAD, 

STATS =
10
GO
Restore transaction
log
RESTORE LOG [DBMTest]

FROM
DISK =
N’C:BACKUPSDBMTest_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

TAGS: how-to

speech-bubble-13-icon Created with Sketch.
Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*