Configurando Database Mirroring em instâncias do SQL Server em Workgroup – Parte II

Pessoal, peço desculpas na demora para publicar a segunda parte do artigo sobre Mirroring, mas andei ocupado, tendo que organizar meu tempo entre trabalho, estudos, família e hobbies (afinal, sou humano também), porém a espera acabou. Vamos lá.

Na segunda parte do artigo Configurando Database Mirroring em instâncias do SQL Server em Workgroup, irei mostrar como configurar o mirroring. Nesse tipo de configuração (servidores em workgroup), o processo de configuração é um pouco mais complexo, pois a comunicação entre os servidores deve ser realizada através de endpoints e sem um domínio, precisamos atribuir certificados para os endpoints.

Então mãos à obra.

PS.: Fica aqui meu agradecimento ao Glauco Carvalho, que além da sugestão do post, elaborou todo o passo a passo da configuração.

Passo 1: Criar Master Key, certificados e Endpoints na instância Principal:

-- Criando MASTER KEY na instancia Principal

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO

-- Backup MASTER KEY da instância Principal

USE master
GO
BACKUP MASTER KEY TO FILE = 'E:\Backup\Mirror\MasterKeyPRIN_24072012.bak'
 ENCRYPTION BY PASSWORD = 'Password'
GO

-- Criando Certificado digital na instância Principal

USE master
GO
CREATE CERTIFICATE PRIN_cert
 WITH SUBJECT = 'PRIN certificate',
 START_DATE = '07/24/2012',
 EXPIRY_DATE = '07/23/2020'
GO

-- Backup do certificado PRIN_cert na instância Principal

USE master
GO
BACKUP CERTIFICATE PRIN_cert
 TO FILE = 'E:\Backup\Mirror\PRIN_cert.cer'
GO

-- Criando Endpoint na instância Principal

USE master
GO
CREATE ENDPOINT Prod_mirror
 STATE = STARTED
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATABASE_MIRRORING
 (
  AUTHENTICATION = CERTIFICATE PRIN_cert,
  ENCRYPTION = REQUIRED ALGORITHM RC4,
  ROLE = ALL
 )
GO

Passo 2: Criar Master Key, certificados e Endpoints na instância Mirror:

-- Criando MASTER KEY na instancia Mirror

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO

-- Backup MASTER KEY da instância Mirror

USE master
GO
BACKUP MASTER KEY TO FILE = 'E:\Backup\Mirror\MasterKeyMIRR_24072012.bak'
 ENCRYPTION BY PASSWORD = 'Password'
GO

-- Criando Certificado digital na instância Mirror

USE master
GO
CREATE CERTIFICATE MIRR_cert
 WITH SUBJECT = 'Mirr certificate',
 START_DATE = '07/24/2012',
 EXPIRY_DATE = '07/23/2020'
GO

-- Backup do certificado mirr_cert na instância Principal

USE master
GO
BACKUP CERTIFICATE MIRR_cert
 TO FILE = 'E:\Backup\Mirror\MIRR_cert.cer'
GO

-- Criando Endpoint na instância Mirror

USE master
GO
CREATE ENDPOINT Prod_mirror
 STATE = STARTED
 AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
 FOR DATABASE_MIRRORING
 (
  AUTHENTICATION = CERTIFICATE MIRR_cert,
  ENCRYPTION = REQUIRED ALGORITHM RC4,
  ROLE = ALL
 )
GO

Passo 3: Criar User, login e associar certificado no user da instância Principal:


-- Criando login e user no database da instância Principal
-- Nessa etapa estamos criando um login SQL.
Use master
GO
CREATE LOGIN SQL_MIRR_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_MIRR_U01 FOR LOGIN SQL_MIRR_L01
GO

-- restaurar certificado MIRR_cert e definindo acesso ao user da instância Mirror na
-- instância Principal

CREATE CERTIFICATE MIRR_cert
 AUTHORIZATION SQL_MIRR_U01
 FROM FILE = 'E:\Backup\Mirror\MIRR_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Principal

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_MIRR_L01
GO

Passo 4: Criar User, login e associar certificado no user da instância Mirror:


-- Criando login e user no database da instância Mirror
-- Nessa etapa estamos criando um login SQL.
Use master
GO
CREATE LOGIN SQL_PRIN_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_PRIN_U01 FOR LOGIN SQL_PRIN_L01
GO

-- restaurar certificado PRIN_cert e definindo acesso ao user da
-- instância Mirror na instância Principal

CREATE CERTIFICATE PRIN_cert
 AUTHORIZATION SQL_PRIN_U01
 FROM FILE = 'E:\Backup\Mirror\PRIN_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Mirror

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_PRIN_L01
GO

OBS.: Os passos 5, 6, 7 e 8 são opcionais, devem ser executados apenas se for configurar uma instância Witness. Caso contrário, podem ser ignorados.

Passo 5: Criar Master Key, certificados e Endpoints na instância Witness:


-- Criando Master Key, Certificado e endpoint na instância Witness
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
GO

CREATE CERTIFICATE WIT_cert
 WITH SUBJECT = 'WIT Certificate',
 START_DATE = '07/24/2012',
 EXPIRY_DATE = '07/23/2020'
GO
CREATE ENDPOINT PROD_MIRROR
 STATE = STARTED
 AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
 FOR DATABASE_MIRRORING
 (
   AUTHENTICATION = CERTIFICATE WIT_cert,
   ENCRYPTION = REQUIRED ALGORITHM RC4,
   ROLE = WITNESS
 )
 GO
 
BACKUP CERTIFICATE PROD_WIT_cert TO FILE = 'E:\Backup\Mirror\WIT_cert.cer'
GO

Passo 6: Criar User, login e associar certificado no user da instância Principal:


-- Criando login e user no database da instância Principal
-- Nessa etapa estamos criando um login SQL.

Use master
GO
CREATE LOGIN SQL_WIT_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_WIT_U01 FOR LOGIN SQL_WIT_L01
GO

-- restaurar certificado WIT_cert e definindo acesso ao user
-- na instância Principal

CREATE CERTIFICATE WIT_cert
 AUTHORIZATION SQL_WIT_U01
 FROM FILE = 'E:\Backup\Mirror\WIT_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Principal

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_WIT_L01
GO

Passo 7: Criar User, login e associar certificado no user da instância Mirror:


-- Criando login e user na instância Mirror
-- Nessa etapa estamos criando um login SQL.

Use master
GO
CREATE LOGIN SQL_WIT_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_WIT_U01 FOR LOGIN SQL_WIT_L01
GO

-- restaurar certificado WIT_cert e definindo acesso ao user
-- na instância Mirror

CREATE CERTIFICATE WIT_cert
 AUTHORIZATION SQL_WIT_U01
 FROM FILE = 'E:\Backup\Mirror\WIT_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Principal

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_WIT_L01
GO

Passo 8: Criar User, login e associar certificado no user da instância Witness:


-- Criando login e user na instância Witness
-- Nessa etapa estamos criando um login SQL.
Use master
GO
CREATE LOGIN SQL_PRIN_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_PRIN_U01 FOR LOGIN SQL_PRIN_L01
GO

-- restaurar certificado PRIN_cert e definindo acesso ao
-- user da instância Principal

CREATE CERTIFICATE PRIN_cert
 AUTHORIZATION SQL_PRIN_U01
 FROM FILE = 'E:\Backup\Mirror\PRIN_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Principal

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_PRIN_L01
GO

-- Criando login e user na instância Mirror
-- Nessa etapa estamos criando um login SQL.

Use master
GO
CREATE LOGIN SQL_MIRR_L01 WITH PASSWORD = 'Password'
GO
CREATE USER SQL_MIRR_U01 FOR LOGIN SQL_MIRR_L01
GO

-- restaurar certificado MIRR_cert e definindo acesso ao
-- user da instância Mirror

CREATE CERTIFICATE MIRR_cert
 AUTHORIZATION SQL_MIRR_U01
 FROM FILE = 'E:\Backup\Mirror\MIRR_cert.cer'
GO

-- Dar permissão ao USER no endpoint criado na instância Mirror

USE master
GO
GRANT CONNECT ON ENDPOINT::PROD_MIRROR TO SQL_MIRR_L01
GO

Passo 9: Criar o database Mirror no servidor secundário usando backups do database principal


-- Criando backups do banco principal

Use master
GO
BACKUP DATABASE DB_Mirror TO DISK = 'E:\Backup\DB_MIRROR_bkpfull.bak'
GO
BACKUP LOG DB_Mirror TO DISK = 'E:\Backup\DB_MIRROR_bkplog.trn'
GO

-- Copie os arquivos de backup gerados para o servidor da instância mirror

RESTORE DATABASE DB_Mirror FROM DISK = 'E:\Backup\DB_MIRROR_bkpfull.bak'
WITH NORECOVERY
GO
RESTORE LOG DB_Mirror FROM DISK = 'E:\Backup\DB_MIRROR_bkplog.trn'
WITH NORECOVERY
GO

Passo 10: Configurar o mirroring


-- Executar na instância Mirror
ALTER DATABASE DB_Mirror
SET PARTNER = 'TCP://nome_servidor_principal:5022'
GO

-- Executar na instância Principal

ALTER DATABASE DB_Mirror
SET PARTNER = 'TCP://nome_servidor_mirror:5023'
GO

ALTER DATABASE DB_Mirror
SET WITNESS = 'TCP://nome_servidor_witness:5024'
GO

No passo 10, deve ser informado um nome no formato FQDN (Ex. servidor.local), pois o SQL somente conectará nos servidores, membros do mirroring, através da resolução de nomes nesse formato. Para configurar, basta inserir as entradas nos arquivos Hosts, respectivos de cada servidor envolvido no mirroring.

Para quem não leu o artigo com os conceitos de mirroring, basta acessar o artigo Configurando Database Mirroring em instâncias do SQL Server em Workgroup – Parte I, contendo a primeira parte do artigo.

Espero que esse artigo seja útil.

Abraços e até a próxima.

Anúncios

9 comentários sobre “Configurando Database Mirroring em instâncias do SQL Server em Workgroup – Parte II

  1. Pingback: Implementando Espelhamento de Banco de Dados (Database Mirror) « Alex Souza

  2. Está apresentando esse erro.

    sg 1456, Level 16, State 3, Line 1
    The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://host:7024’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

  3. Boa tarde, parabéns pelo post, excelente, fiquei 1 semana procurando uma solução para o mirror que estava tentando implantar, só consegui resolver usando seu passo-a-passo.

  4. Excelente tutorial, parabéns!! Apenas dois comentários:

    Passo 1:
    linha: EXPIRY_DATE = 07/23/2020
    correção: EXPIRY_DATE = ’07/23/2020′

    Passo 9:
    linhas:
    RESTORE DATABASE DB_Mirror FROM DISK = ‘E:\Backup\DB_MIRROR_bkpfull.bak’
    GO
    RESTORE LOG DB_Mirror FROM DISK = ‘E:\Backup\DB_MIRROR_bkplog.trn’
    GO

    correção:
    RESTORE DATABASE DB_Mirror FROM DISK = ‘E:\Backup\DB_MIRROR_bkpfull.bak’
    WITH NORECOVERY
    GO
    RESTORE LOG DB_Mirror FROM DISK = ‘E:\Backup\DB_MIRROR_bkplog.trn’
    WITH NORECOVERY
    GO

    Caso não utilize a restauração no servidor mirror com ‘WITH NORECOVERY’ , irá apresentar erro no momento da conexão com o servidor principal.

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