Recent snippets posted on Snipplr.com https://snipplr.com/ Tue, 09 Jun 2026 22:14:34 +0000 https://snipplr.com/view/69089/implementando-database-mirror <p>Olá Pessoal, Neste artigo vou falar sobre Database Mirror, uma nova funcionalidade do SQL Server 2005 para alta disponibilidade.Database Mirror consiste em basicamente um espelhamento de um banco de dados, uma solução que fornece quase em tempo real failover a nível de banco de dados,sem qualquer necessidade de compatibilidade de hardware e baixo custo. Com o failover fornecido pelo Database Mirror é possível subir uma segunda base de dados de forma automática em menos de 3 segundos,tudo isso transparente para a aplicação. É sem dúvida uma solução fantástica, de qual representa uma ótima justificativa para uma migração a partir de uma versão anterior. Com o Database Mirror é espelhado todo o banco de dados para um separado servidor, uma cópia completa quer será mantida e atualizada em tempo real utilizando a tecnologia Copy-on-Write (Cópia na Escrita).Como toda solução existem custos que devem ser levados em consideração como o volume de espaço em disco no segundo servidor deve ser equivalente ao servidor principal. Database Mirror trabalha através do Transaction Log do banco de dados principal (O banco de dados de qual terá seus dados espelhados). Para implementar o database mirror é preciso configurar o banco de dados para o Recovery Model Full,onde as entradas do Transaction Log são copiadas imediatamente para o banco de dados espelho a cada nova alteração nos dados,uma vez a transação é confirmada no banco de dados espelho este emite um aviso ao banco de dados principal e assim a transação é confirmada e completa. Repare que com a cópia da transação para o banco de dados espelho cria uma certa proteção da informação,de forma que a cada alteração ou inserção é mantido cópias redundantes em ambos servidores.Esta é a tecnologia Copy-on-Write! Em um cenário típico do Database Mirror existem basicamente três componentes que são eles: Principal Database Server: É o servidor na qual armazena o banco de dados principal, ou seja, de qual terá suas informações espelhadas. É possível espelhar mais de um banco de dados da mesma instancia para outra instancia SQL Server, mas não é possível espelhar um banco de dados para outro na mesma instancia. Mirror Database Server: É o servidor na qual armazena o banco de dados espelho, ou seja, o banco de dados que atuará como standby do servidor principal, recebendo transações. O Mirror Database permanece em estado de Restore e não pode ser acessado diretamente, pelo fato deste permanecer sempre recebendo registros de transações a partir do Principal Database. Witness Database Server: É um opcional servidor que será a testemunha (Witness) na qual ficará monitorando caso tenha alguma falha no servidor principal, este ajudará tomar a decisão de realizar o failover para o servidor mirror. Quando ocorre alguma falha no servidor principal o servidor mirror e o servidor witness percebem a falha e juntos decidem que o servidor mirror deve substituir o servidor principal, assumindo a regra de servidor principal, respondendo requisições da aplicação. Sem o servidor Witness não existe o failover automático, no momento da falha o servidor mirror percebe que a conexão com o servidor principal foi perdida, mas não pode tomar a decisão de assumir a regra de principal sozinho, desta forma o failover deve ser manual, a partir do servidor mirror. A ilustração a seguir demonstra os componentes citados acima: 1. Uma transação é escrita através da aplicação para o transaction log do banco de dados AdventureWorks no servidor principal. 2. Imediatamente esta transação é copiada para o transaction log do banco de dados do servidor mirror, este então confirma a transação e envia para o servidor principal uma confirmação de escrita com sucesso. 3. Apos receber a confirmação do servidor mirror, o servidor principal confirma a transação em seu transaction log e retorna o aviso de confirmação para a transação. O Database Mirror pode ser configurado para executar em modos de operação de quais podem priorizar a performance da aplicação ou segurança dos dados.Os modos de operação são high availability,high-protection e high-performance.Cada modo de operação opera de acordo com as configurações abaixo. Synchronous operations (Operações Síncronas): Com operações síncronas a transação é confirmada em ambos os parceiros de replicação, banco de dados principal e mirror, claro que com este modo de operação irá gerar um custo adicional na transação pois que a transação somente é completada quando esta é confirmada em ambos os parceiros.O modo High-availability e high-protection usam operações síncronas. Asynchronous operations (Operações Assíncronas): Com operações assíncronas a transação é confirmada no banco de dados principal sem esperar que o banco de dados no servidor espelho escreve a transação para seu log de transação no disco. Com esse modo de operação a performance da aplicação é melhorada,já que a transação não precisa do custo adicional de confirmar em ambos servidores para completar,porém temos uma falta de segurança na informação.O modo de High Performance utiliza este modo de operação.Para finalizar o nosso conceito sobre Database Mirror,precisamos entender os tipos de Failover disponíveis e seus requisitos. Failover Automático: O Failover Automático é necessário o ambiente de três instancias de quais desempenham as três regras de Principal Server, Mirror Server e Witness Server, com o Failover automático caso aconteça um problema com a instancia principal o Mirror Server assume o papel de Principal de forma automática, sem intervenção humana, isto é o modo de High Availability. Failover Manual: O Failover Manual é composto apenas das instancias Principal Server e Mirror Server, sem a o servidor Witness e com o modo de operação síncrona, a alteração da regra deve ser manual. Isto é o modo de High Availability e High Protection. Forced Service: Serviço forçado é quando ocorre alguma falha com o servidor principal, mas o servidor mirror não esta disponível, porém os dados não estão totalmente sincronizados. Com isso é preciso forçar a alteração da regra para o servidor mirror, isso significa perca de dados, pois as transações não estão sincronizadas. Isto é o modo high-protection ou high-performance.Configurando o Database Mirror. Agora que já vimos os conceitos e componentes associados ao Database Mirror, vamos entender e configurar nosso ambiente.A conexão entre os servidores envolvidos no Database Mirror é feita através de Endpoints, para os endpoints atribuímos uma conta de serviço do Windows para sua autenticação, caso estejamos em um ambiente com um domínio do Active Directory poderíamos criar uma conta exclusiva para os endpoints e utilizar esta de qual seria válidas em todos os servidores. No nosso exemplo, iremos configurar uma conta de usuário local do Windows de qual será exclusiva para os endpoints em todos as três instancias.Atribuiremos portas diferentes para os endpoints em cada instancia. Devemos verificar também se em todas as nossas instancias estar aplicado o Service Pack 1 ou superior,pois este é requerido pelo Database Mirror. Em nosso exemplo usaremos as edições Enterprise Edition, o Database Mirror somente é suportado nas edições Enterprise, Standard e Develop. A edição Workgroup e Express não são suportadas, somente desempenhando a regra de servidor Witness.Criaremos um banco de dados de teste chamado Livraria. A ilustração abaixo resume as configurações do nosso ambiente: O Servidor Server01 atuando como Principal Server, servidor Server02 atuando como Mirror Server e o servidor 03 atuando como Witness Server (Express Edition). Para o endpoint do Server01 iremos configurar a porta 1400, Server02 com a porta 1450 e o Server03 com a porta 1500. Agora com nosso ambiente planejado iremos partir para a configuração dos endpoints, podemos utilizar o SQL Server Management Studio ou código T-SQL para criar os endpoints, nesse exemplo usaremos códigos T-SQL para a criação. Para a criação do Endpoint, se conecte no Server01 e utilize o comando abaixo para criar o endpoint: –Cria o endpoint para Database Mirror no Server01.. CREATE ENDPOINT ENDPOINT_MIRROR STATE = STARTED AS TCP(LISTENER_PORT = 1400,LISTENER_IP = ALL) FOR DATA_MIRRORING(ROLE= PARTNER,AUTHENTICATION= WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4) No exemplo acima criamos o endpoint ENDPOINT_MIRROR com status iniciado, com a porta TCP 1400, escutando em todos os endereços IP, endpoint do tipo DATA_MIRRORING, e com a ROLE (Regra definida como PARTNER de qual significa que este endpoint pode atua como um servidor principal ou espelho), utilizando autenticação do Windows e o algoritmo de criptografia RC4. Se conecte no Server02 e utilize o mesmo comando listado acima para criar o endpoint. Lembre-se de alterar o número da porta para conexão como segue abaixo. –Cria o endpoint para Database Mirror no Server02.. CREATE ENDPOINT ENDPOINT_MIRROR STATE = STARTED AS TCP(LISTENER_PORT = 1450,LISTENER_IP = ALL) FOR DATA_MIRRORING(ROLE= PARTNER,AUTHENTICATION= WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4) *Somente é possível criar um Endpoint para Database Mirror por vez na mesma instancia.Se conecte no Server03 e crie o Endpoint como listado abaixo,alterando o número da porta,e a regra para Witness. –Cria o endpoint para Database Mirror no Server03.. CREATE ENDPOINT ENDPOINT_MIRROR STATE = STARTED AS TCP(LISTENER_PORT = 1500,LISTENER_IP = ALL) FOR DATA_MIRRORING(ROLE= WITNESS,AUTHENTICATION= WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4) Após a criação dos Endpoints em todas as instancias envolvidas na configuração do Database Mirror,execute a query abaixo para listar os endpoints criados em cada instancia. –Query lista os endpoints criados na instancia. SELECT name ,type_desc ,port ,ip_address FROM sys.tcp_endpoints –Query lista as informações sobre os endpoints como role,descrição do status,etc. SELECT name ,role_desc ,state_desc FROM sys.database_mirroring_endpoints O resultado da query acima deve ser similar a este: Para testar a conectividade entre os servidores com as portas especificadas nos endpoints,podemos usar o comando TELNET para verificar se os servidores estão escutando nas portas definidas.Segue o exemplo de testando a conexão do Server02,faça o teste em todos os servidores. Agora que configuramos os endpoints em todas as instancias associadas ao Database Mirror, devemos criar o nosso usuário de qual iremos criar um login nas instancias SQL Server e atribuir a permissão de CONECT nos endpoints. Crie o usuário em todos os três servidores com o mesmo nome e senha,lembrando de especificar que a senha deste usuário não deve expirar e o usuário não pode alterar a senha.Como disse anteriormente o procedimento de criar o mesmo usuário em todas os servidores é necessário quando não estamos em um ambiente com um domínio do Active Directory ,com isso criando o mesmo usuário em cada servidor estamos assegurando que o usuário é válido em todos os servidores. Utilize o comando abaixo para criar o usuário em cada servidor, especificando o nome de usuário e senha, especificando a conta do usuário como ativa, usuário não deve alterar sua senha,e sua senha não expira.Essas são configurações normalmente utilizadas para contas de serviço. Após criarmos o usuário em todos os servidores, vamos criar o login no SQL Server associado ao usuário recém criado, e atribuindo a permissão de CONNECT nos endpoints do Database Mirror,com esta permissão o nosso usuário poderá se conectar nos endpoints para o acesso(novamente crie em todos os servidores).Segue o comando para criação do login e atribuição da permissão CONNECT. –Conecte no Server01.. CREATE LOGIN [SERVER01SQLUser] FROM WINDOWS GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRROR TO [SERVER01SQLUser] –Conecte no Server02.. CREATE LOGIN [SERVER02SQLUser] FROM WINDOWS GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRROR TO [SERVER02SQLUser] –Conecte no Server03.. CREATE LOGIN [SERVER03SQLUser] FROM WINDOWS GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRROR TO [SERVER03SQLUser] Após criarmos o usuário e o login correspondente,coloque a conta de usuário para executar o serviço SQL Server em todos os servidores através do SQL Server Configuration Manager. Agora vamos criar o nosso banco de teste chamado Livraria,e duas tabelas com um relacionamento,segue o script. –Criando o banco de dados Livraria CREATE DATABASE Livraria –Criando as tabelas de exemplo Autores e Livros. USE Livraria CREATE TABLE dbo.Autores ( AutorID SMALLINT NOT NULL ,Nome VARCHAR(50) ,Email VARCHAR(50) ) ALTER TABLE dbo.Autores ADD CONSTRAINT [PK_COD_Autores] PRIMARY KEY(AutorID) CREATE TABLE dbo.Livros ( LivroID SMALLINT NOT NULL ,AutorID SMALLINT ,Nome VARCHAR(50) ,Valor MONEY ) ALTER TABLE Livros ADD CONSTRAINT [PK_LivroID_Livros] PRIMARY KEY(LivroID) –Criando o relacionamento entra as tabelas. ALTER TABLE Livros ADD CONSTRAINT [FK_AutorID_Livros] FOREIGN KEY(AutorID) REFERENCES dbo.Autores(AutorID) ON DELETE CASCADE ON UPDATE CASCADE –Inserindo alguns valores para povoar nossa tabela de autores. INSERT INTO Autores VALUES(1,‘Kalen Delaney’,‘kalen@microsoft.com’) INSERT INTO Autores VALUES(2,‘Paul S Randal’,‘paul@microsoft.com’) –Inserindo alguns valores para povoar nossa tabela de Livros. INSERT INTO Livros VALUES(1,1,‘SQL Server The Stogare Engine’,100) INSERT INTO Livros VALUES(2,2,‘SQL Server For Develops’,80) –Query para verificar o relacionamento entre as tabelas. SELECT A.AutorID ,A.Nome ,A.Email ,L.Nome ,L.Valor FROM Autores A INNER JOIN Livros L ON A.AutorID = L.AutorID Com o nosso banco criado,precisamos fazer o backup Full e o restore em nosso servidor Mirror,especificando a opção NORECOVERY para manter o banco em estado de restoring,recebendo as transações a partir do principal.Script para realizar o backup do banco de dados. –Backup Full para restore no banco de dados mirror. BACKUP DATABASE [Livraria] TO DISK = ‘C:BACKUPLivraria_Full.bak’ WITH INIT –Backup do Transaction Log para restore no banco de dados mirror. BACKUP LOG [Livraria] TO DISK = ‘C:BACKUPLivraria_Log.bak’ WITH INIT Após o backup precisamos transferir os dispositivos para o servidor mirror e fazer o restore. Se conecte no Server02 e execute os comandos abaixo para criar o banco de dados Livraria a partir do backup criado anteriormente.No exemplo abaixo estou referindo ao driver C: como o local de qual armazenei os dispositivos de backup,caso tenha salvo em outra localização especifique esta. –Restore do backup FULL no servidor Mirror,especificando a opção NORECOVERY. RESTORE DATABASE [Livraria] FROM DISK = ‘C:BACKUPLivraria_Full.bak’ WITH NORECOVERY –Restore do backup do log de transação no servidor Mirror,especificando a opção NORECOVERY,deixando o banco de dados em estado de restoring,de qual é requerido para configurar o Database Mirror. RESTORE LOG [Livraria] FROM DISK = ‘C:BACKUPLivraria_Log.bak’ WITH NORECOVERY Após recuperar nosso banco de dados no servidor Mirror,devemos configurar o espelhamento utilizando o comando Alter Database ,especificando assim as regras exercidas por cada servidor. O Script abaixo deve ser executado no servidor Mirror, indicando que seu “parceiro” será o servidor principal que nosso exemplo é o server01. No comando Alter Database especificando o FQDN do nosso servidor,para uma instalação em um ambiente Workgroup talvez seja necessário a configuração de um sufixo DNS para completar o nome da máquina,no meu exemplo configurei o sufixo chamado local.Apos o FQDN especificamos a porta configurada no ENDPOINT previamente criado,o comando completo segue abaixo. –Especificando o server01 como parceiro ALTER DATABASE Livraria SET PARTNER = ‘TCP://server01.local:1400′ Agora precisamos definir em nosso servidor principal o server02 como parceiro e definir o server03 como Witness,usaremos o mesmo comando Alter Database.Se conecte no server01 e emita os comandos abaixo: –Especificando o server02 como parceiro ALTER DATABASE Livraria SET PARTNER = ‘TCP://server02.local:1450′ –Especificando o server03 como Witness ALTER DATABASE Livraria SET WITNESS = ‘TCP://server01.local:1500′ Se ocorrer algum erro no momento da execução dos comandos abaixo,como problemas em encontrar algum dos parceiros envolvidos,teste as conexões de rede,verifique a resolução de nome entre os servidores e caso não esteja utilizando um servidor DNS adicione ao arquivo Host localizado em %SystemRoot%system32driversetc as entradas com os nomes dos servidores e seus FQDNs com seus respectivos IP´s como mostrado abaixo. Com isso podemos verificar o status,pausar,forçar o failover e até mesmo remover nossa configuração Database Mirror,selecionando as propriedades do banco de dados Livraria,na opção Mirroring como mostrado na figura abaixo. No object Explorer se registrarmos os servidores Principal e Mirror podemos visualizar parcialmente o status e qual regra determinado servidor estar atuando no momento como abaixo podemos visualizar o Servidor Principal Sincronizado. Servidor Mirror Sincronizado e em estado de Restoring.. Pronto,neste momento estamos com nosso ambiente espelhado,caso ocorra algum problema com o server01, o server02 assumirá sua regra e passará a atuar como servidor principal,após restabelecer o server01 este irá assumir a regra de mirror e assim sucessivamente.Para testar a funcionalidade você pode simular um problema no server01 e verificar se o server02 passou a ser o Principal automáticamente. Com isso concluo meu artigo sobre Database Mirror,mostrando sua configuração e conceitos,espero ter demonstrado de forma clara e completa as vantagems e benefícios desta solução disponível no SQL Server 2005 e 2008,bem como os passos necessários para realizar sua implementação com sucesso. Obrigado e até o próximo post.</p> Thu, 13 Dec 2012 04:04:35 UTC https://snipplr.com/view/69089/implementando-database-mirror