Como realizar o espelhamento do banco de dados SQL Server

Olá a todos,

Sábado, 24 de maio, frio e chuva em São Paulo, escutando um Pink Floyd, nada melhor do que um copo bem cheio de café quente do lado para comemorar o dia nacional do café e aproveitar o momento de inspiração para fazer uma publicação de uma coisa simples, porém um pouco chatinha de se fazer. Hoje presenteio vocês com um rápido manual:

Como realizar o espelhamento do banco de dados SQL Server.

Basta seguir esse passo-a-passo que você tira de letra e pode dar mais segurança as informações de seu banco de dados, claro, presumindo que você já possua em conjunto uma rotina de backup automático do banco de dados SQL. Então vamos ao que interessa. Segundo definição da própria Microsoft:
espelhamento de banco de dados é uma solução de software usada, essencialmente, para aumentar a disponibilidade do banco de dados. O espelhamento é implementado por base de banco de dados e só funciona com bancos de dados que usam o modelo de recuperação completa. Os modelos de recuperação simples e bulk-logged não oferecem suporte ao espelhamento de banco de dados. Por isso, todas as operações em massa são sempre totalmente registradas. O espelhamento de banco de dados funciona com qualquer nível de compatibilidade de banco de dados para o qual haja suporte.
Sendo como benefícios o aumento a proteção dos dados, o aumento a disponibilidade de um banco de dados e o aumento a disponibilidade do banco de dados de produção durante as atualizações.

Tudo bem, mas o que acontece realmente por trás dos panos?

Basicamente você terá não apenas um, mas dois servidores rodando SQL Server e seu banco de dados, e o próprio SQL se encarrega de sincronizar os seus dados até que seja necessário apontar (automaticamente ou manualmente) qual dos servidores será  o seu banco de dados principal.

Nisso, você passa a ter duas opções aqui.

Primeira - Você configura o espelhamento através de dois sevidores, o principal e o espelho. Sendo esse o modo manual, em caso de falha, você terá que apontar qual será o seu servidor "principal". Segunda - Caso queira o modo automático, precisará de um terceiro servidor rodando o SQL Server, e esse passará a ser chamado de "testemunha". Esse cara será o responsável por ficar "escutando" o seu banco de dados e quando um apresentar alguma instabilidade/indisponibilidade ele irá fazer o outro servidor "assumir" o papel de banco principal.

Ok, acho que entendi, próximo!

Como estamos falando de troca de informações entre banco de dados, dê preferência para que os bancos estejam na mesma rede (mas não nos mesmos pontos físicos ok? Sugiro brincar com o mundo do cloud computing), e de mais preferência para uma rede isolada, e em um mundo perfeito, somente seus serviços terão acesso a esse banco, mas isso não entra no conteúdo dessa postagem, apenas assegure-se de que os bancos conseguem conversar entre si pela porta 5022 (padrão do SQL para isso) ou alguma outra que você escolher.
Liberado?
Presumindo então que os servidores estão se comunicando, vamos seguir:

Certificados

De novo essa história de certificados? - Sim, mas dessa vez é mais fácil, prometo! O SQL para garantir uma conexão mais segura irá necessitar de certificados gerados em cada servidor para ser associado aos usuários que irão fazer a conexão para espelhamento. Ok, Como faço? Segue abaixo a sequência que você pode executar diretamente no SQL, em negrito está os campos em que você irá entrar com os dados a sua escolha. USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' $&nH@dEm@sTerKey666' GO CREATE CERTIFICATE cert_sqlmirror_* WITH SUBJECT = 'Certificado SQL *', START_DATE = '24/05/2014', EXPIRY_DATE = '12/12/2021' GO CREATE ENDPOINT ENDPOINT_MIRROR STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE cert_sqlmirror_*, ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL ) GO BACKUP CERTIFICATE cert_sqlmirror_* TO FILE = 'C:\cert_sqlmirror_*.cer'; GO Esse código você terá que utilizar nos dois (ou três) servidores que está utilizando.
  • $&nH@dEm@sTerKey666 é a senha da sua chave mestre, guarde-a em algum lugar seguro, caso já possua uma, pode ocultar essa linha;
  • cert_sqlmirror_* é o nome do seu certificado, você pode completar com _principal, _mirror, _witness para melhor se localizar;
  • 24/05/201412/12/2021, aqui é a data inicial e data final de validade do seu certificado, coloquei de hoje (dia nacional do café) até o dia 12/12/2021 (já que novamente dizem que o mundo vai acabar nesse dia mesmo - kkk);
  • 5022 é a porta que será utilizada para o serviço de espelhamento, deixe padrão ou escolha outra (mas garanta que há comunicação entre os servidores por ela);
  • E por fim o C:\cert_sqlmirror_*.cer é onde você vai salvar o seu certificado.
Agora é só copiar esse certificado para o outro (ou outros) servidores. Ok, presumindo que você tenha em mãos os 2 ou 3 certificados com os nomes de cert_sqlmirror_principal.cer, o cert_sqlmirror_mirror.cer e o cert_sqlmirror_witness.cer, vamos dar sequência.

Criando usuários e logins nos servidores.

Aqui é um pouco embaralhado, você precisa prestar atenção no que vai fazer. Lembra daquele jogo de barbante chamado cama de gato? Então, se é do seu tempo e você lembrou é mais ou menos isso, senão, olhe a figura a seguir que você entenderá.
sql mirroring
Espelhamento do banco de dados SQL Server
Então, tenha em mente que:
Com 2 servidores:
- Você precisará de um login e usuário do MIRROR no servidor PRINCIPAL; - Você precisará de um login e usuário do PRINCIPAL no servidor MIRROR;
Com 3 servidores:
- Você precisará de um login e usuário do MIRROR e do WITNESS no servidor PRINCIPAL; - Você precisará de um login e usuário do PRINCIPAL e do WITNESS no servidor MIRROR; - Você precisará de um login e usuário do PRINCIPAL e MIRROR no servidor WITNESS;
Fazendo isso no SQL:
USE MASTER GO CREATE LOGIN sqlmirror_Login* WITH PASSWORD = 'sU@$&nh4' GO CREATE USER sqlmirror_User* FOR LOGIN sqlmirror_Login* GO CREATE CERTIFICATE cert_sqlmirror_* AUTHORIZATION sqlmirror_User* FROM FILE = 'C:\cert_sqlmirror_*.cer'; GO GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRROR TO [sqlmirror_Login*] GO Esse código você terá que utilizar um vez (sem witness) ou duas vezes (com witness) em cada servidor ok? Atente-se de trocar o * em todos os lugares.
  • sqlmirror_Login* é login que fará os acessos, ficando no meu exemplo, sqlmirror_LoginPrincipal, sqlmirror_LoginMirror e sqlmirror_LoginWitness;
  • sU@$&nh4 é uma senha que você irá colocar para o login, seja criativo e dificulte a vida de invasores;
  • sqlmirror_User* é o nome de usuário que estará associado ao login, ficando no meu exemplo os seguintes: sqlmirror_UserPrincipal,  sqlmirror_UserMirror e sqlmirror_UserWitness;
  • C:\cert_sqlmirror_*.cer é o caminho do certificado que você gerou lá em cima e copiou para todos os servidores.
Nesse passo basicamente você cria um login com uma senha, cria um usuário para o login, e importa o certificado para o sql autorizando o usuário e garantindo conexão segura para o login que irá tentar acessar de outro servidor.

Estamos quase lá...

Backup e Restore
Para que o espelhamento funcione e comece a sincronizar, é necessário realizar um backup do banco principal e restaura-lo no servidor espelho(MIRROR), porém esse backup tem que ser de uma base no modo RECOVERY FULL. Não, não é necessário realizar no witness, pois ele será apenas um "escutador de disponibilidade", não realizando conexões nem transações. Se você já tem uma base em recovery full e possui os backups, basta restaura-los no seu servidor espelho.
Backup do banco de dados
USE MASTER GO ALTER DATABASE bancodedados SET RECOVERY FULL GO BACKUP DATABASE bancodedados TO DISK = 'C:\bancodedados_FullBackup.bak' GO BACKUP LOG bancodedados TO DISK = 'C:\bancodedados_LogBackup.trn' GO
Restore do banco de dados
USE MASTER GO RESTORE DATABASE bancodedados FROM DISK = 'C:\bancodedados_FullBackup.bak' WITH NORECOVERY GO RESTORE LOG bancodedados FROM DISK = 'C:\bancodedados_LogBackup.trn' WITH NORECOVERY GO
E agora?
Conecte no seu servidor principal remotamente e abra o Management Studio, clique na base de dados que irá criar o espelhamento, vá em Tasks -> Mirror. Uma janela semelhante a essa irá aparecer:
Mirroring - 01
Espelhamento do banco de dados SQL Server (Database Mirroring) - 01
Clique em Configure Security, irá aparecer uma janela perguntando se deseja configurar também um servidor witness, caso escolha sim, a próxima irá perguntar onde deseja salvar a configuração (único opcional sendo o próprio witness), na próxima já irá aparecer para configurar os servidores:
Mirroring - 02
Espelhamento do banco de dados SQL Server (Database Mirroring) - 02
  • O principal já virá com os campos desabilitados, pois você está nele, sendo necessário apenas configurar o mirror e o witness;
  • Clicando em Connect você dará o endereço do servidor e o modo de autenticação, se a conexão estiver ok, você não terá problemas nessa parte, caso tenha algum problema, confira se existe algum firewall ou outro serviço barrando a conexao (lembrando que precisam de acessos nas portas 1433 ou 5022, a menos que tenha alterado elas, claro);
  • E caso tenha trocado a porta ou o nome do endpoint de mirror, altere nos campos como na imagem, se não, deixe o padrão e continue.
  • A próxima janela irá perguntar qual as contas que ele irá utilizar para se conectar aos servidores. Lembra quando criamos usuários e logins lá atrás (sqlmirror_Login*)? Ótimo, aqui você irá apontar eles, sendo eles: sqlmirror_LoginPrincipal para o servidor PRINCIPAL, sqlmirror_LoginMirror para o servidor MIRROR e sqlmirror_LoginWitness para o servidor WITNESS.
  • Clique em Finish (HIM!);
  • Ele irá configurar os Endpoints, verificar se há comunicação, e em caso de sucesso, só clicar a seguir nos botões Close, Start Mirroring e Yes!

e Pronto!

Se você seguiu direito esse tutorial, teve um pouco de fé, um bocado de sorte, deu tudo certo e você verá que agora ao lado do nome do seu banco de dados está um parênteses (Principal, Synchronized). Dependendo do tamanho do seu banco, e do tanto de transações que tiveram entre o backup-restore e o Start Mirroring ele pode demorar um tempo e ficar em Synchronizing. E é isso, espero que isso ajude alguém, pois demorei um certo tempo para realmente conseguir realizar esse processo, tutoriais sempre dão uma travada em algum ponto, e dessa forma comigo pelo menos sempre funciona!
Dúvidas, críticas, sugestões, erros detectados, por favor, sejam gentis 😉