SQL Server Database Mirroring–Instâncias em Workgroup

Este post é para aqueles que tem este desafio: espelhar bancos de dados em servidores fora de um domínio Active Directory.

Em um domínio é utilizado automaticamente o FQDN do servidor para estabelecer a conexão. Em um workgroup, alguns passos extras são exigidos na configuração, como a criação de certificados para conexão.

Créditos:
Suhas De e Varun Dhawan, citados em seus (MSDN) Blogs:
http://blogs.msdn.com/b/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

Step 1: Create encryption key, certificate and end-points on Principal Instance

/* Execute this against the Principal Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_PRIN_cert
    WITH SUBJECT = 'HOST_PRIN certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_PRIN_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_PRIN_cert
    TO FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO

Step 2: Create encryption key, certificate and end-points on Mirror Instance

/* Execute this against the Mirror Instance */
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_MIRR_cert
    WITH SUBJECT = 'HOST_MIRR certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_MIRR_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = ALL
    )
GO
BACKUP CERTIFICATE HOST_MIRR_cert
    TO FILE = 'D:\certificate\HOST_MIRR_cert.cer';
GO

Step 3: Create login, user and associate certificate with user on Principal Instance

/*
*  Execute this against the Principal Instance. The HOST_MIRR_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
    AUTHORIZATION HOST_MIRR_user
    FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 4: Create login, user and associate certificate with user on Mirror Instance

/*
*  Execute this against the Mirror Instance. The HOST_PRIN_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
    GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO

Step 5: Create encryption key, certificate and end-points on Witness Instance

/* Execute this against the Witness Instance */
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password!'
GO
CREATE CERTIFICATE HOST_WITT_cert
    WITH SUBJECT = 'HOST_WITT certificate',
    START_DATE = '01/07/2009'
GO
CREATE ENDPOINT End_Mirroring
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING
    (
        AUTHENTICATION = CERTIFICATE HOST_WITT_cert,
        ENCRYPTION = REQUIRED ALGORITHM RC4,
        ROLE = Witness
    )
GO
BACKUP CERTIFICATE HOST_WITT_cert
    TO FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO

Step 6: Create login, user and associate certificate with user on Principal Instance

/*
*  Execute this against the Principal Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Principal Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 7: Create login, user and associate certificate with user on Mirror Instance

/*
*  Execute this against the Mirror Instance. The HOST_WITT_cert.cer
*  needs to be copied on the Mirror Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_WITT_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_WITT_user FOR LOGIN HOST_WITT_login
GO
CREATE CERTIFICATE HOST_WITT_cert
    AUTHORIZATION HOST_WITT_user
    FROM FILE = 'D:\certificate\HOST_WITT_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_WITT_login]
GO

Step 8: Create login, user and associate certificate with user on Witness Instance

/*
*  Execute this against the Witness Instance. The HOST_PRIN_cert.cer
*  and HOST_MIRR_cert.cer needs to be copied on the Witness Server.
*/
USE MASTER
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_PRIN_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_PRIN_user FOR LOGIN HOST_PRIN_login
GO
CREATE CERTIFICATE HOST_PRIN_cert
    AUTHORIZATION HOST_PRIN_user
    FROM FILE = 'D:\certificate\HOST_PRIN_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_PRIN_login]
GO
/*
*  We are creating a SQL Login here. For Windows logins,
*  use the Grant Login instead of Create Login
*/
CREATE LOGIN HOST_MIRR_login WITH PASSWORD = 'Password!'
GO
CREATE USER HOST_MIRR_user FOR LOGIN HOST_MIRR_login
GO
CREATE CERTIFICATE HOST_MIRR_cert
AUTHORIZATION HOST_MIRR_user
FROM FILE = 'D:\certificate\HOST_MIRR_cert.cer'
GO
GRANT CONNECT ON ENDPOINT::End_Mirroring TO [HOST_MIRR_login]
GO

Step 9: Create the Mirrored Database on the Mirror Server using backups from the Principal Server

/*
*  Execute this against the Principal Instance.
*/
USE MASTER
GO
BACKUP DATABASE MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
GO
BACKUP LOG MirrorDB
    TO DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
GO
/*
*  Copy MirrorDB_FullBackup.bak and MirrorDB_LogBackup.trn to the
*  Mirror Server.
*  Execute this against the Mirror Instance.
*/
USE MASTER
GO
RESTORE DATABASE MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_FullBackup.bak'
    WITH NORECOVERY
GO
RESTORE LOG MirrorDB
    FROM DISK = 'D:\Backups\MirrorDB_LogBackup.trn'
    WITH NORECOVERY
GO

Step 10: Setup Mirroring

/*
*  Execute this against the Mirror Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<<your principal server name here>>:5022'
GO
/*
*  Execute this against the Principal Instance.
*/
ALTER DATABASE MirrorDB
    SET PARTNER = 'TCP://<<your mirror server name here>>:5023'
GO
ALTER DATABASE MirrorDB
    SET WITNESS = 'TCP://<<your witness server name here>>:5024'
GO
Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s