Salvo pelo Default Trace!

Olá pessoal,

Hoje vou falar de uma caso real, que aconteceu comigo. Um amigo entrou em contato comigo e disse que queria saber qual usuário excluiu algumas tabelas do banco de dados, só que no servidor não estava habilitado a auditoria, nem por triggers DDL e nem pela feature de Auditoria do SQL Server 2008.

E agora, como saber qual login executou o comando de DROP TABLE no banco de dados de produção?

A situação foi a seguinte: Um pessoa (mal intencionada) que tinha acesso ao banco de dados da empresa, insatisfeita por algum motivo que não vem ao caso agora, excluiu algumas tabelas do banco de dados com o objetivo de prejudicar a empresa e seus dados.

Por sorte, o problema foi resolvido parcialmente pois outro funcionário responsável pelo banco de dados tinha backups atualizados e conseguiu recuperar os dados, mas como provar para seus superiores quem causou essa indisponibilidade do serviço e convece-los de que o que ocorreu não foi uma falha da equipe de banco de dados?

Quando se tem uma política de auditoria habilitada e bem planejada fica fácil, mas quando nenhum desses recursos estão disponíveis, temos uma última carta na manga que é o Default Trace!.

Default Trace, como o nome já diz é um trace padrão que é criado após a instalação do SQL Server e fica habilitado capturando apenas alguns pequenos conjuntos de eventos, a intenção é capturar eventos que pode ser causas para problemas de estabilidade do sistema, etc. O eventos capturados são alterações ao banco de dados, erros, e para nossa sorte este captura  as operações de CREATE, ALTER e DROP em objetos, dentre outros tipos de eventos. O arquivo de saída do Default Trace, fica localizado dentro do diretório de instalação do SQL Server dentro do diretório Log. O arquivo pode atingir até 20 MB e um máximo de 5 arquivos.

Para simular o cenário proposto, vamos criar um banco de dados e uma tabela e logo após criaremos um login e usuário para excluir a tabela de exemplo.

CREATE DATABASE Producao

USE Producao

CREATE TABLE VENDAS
(
    ID INT IDENTITY(1,1),
    PRODUTO VARCHAR(30)
)

INSERT INTO VENDAS VALUES(‘A’)
INSERT INTO VENDAS VALUES(‘B’)
INSERT INTO VENDAS VALUES(‘C’)

Criando o login e usuário de exemplo.

USE [Producao]
GO
–Criando o login e usuario de teste.
CREATE LOGIN [Joao] WITH PASSWORD=‘password’,
DEFAULT_DATABASE = [Producao],CHECK_POLICY=OFF
GO
CREATE USER [Joao] FOR LOGIN [Joao] WITH DEFAULT_SCHEMA=[dbo]
GO
–Dando permissao para o usuario na tabela VENDAS.
GRANT CONTROL ON [dbo].[VENDAS] TO [Joao]
GO

Agora, que criamos o usuário vamos fazer logon utilizando o usuário e excluir a tabela. Após excluir a tabela vamos utilizar a função fn_trace_gettable para armazenar os dados do trace dentro de uma tabela temporária e analisar os dados.

USE Producao

–Excluindo a tabela VENDAS com o usuario Joao
DROP TABLE VENDAS

–Retornando os dados do trace.
SELECT * INTO temp_trc
FROM fn_trace_gettable (‘D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc’,default);

Agora com os resultados do trace dentro da tabela temporária temp_trc vamos selecionar os dados relativos ao usuário Joao, para recuperar as operações realizadas por este.

SELECT   DB_NAME(DatabaseID) AS [Database]
        ,HostName
        ,ApplicationName
        ,LoginName
        ,StartTime
        ,ObjectID
        ,EventClass
        ,ObjectName
        ,DatabaseName
FROM temp_trc
WHERE LoginName = ‘JOAO’

Podemos ver o resultado da query acima, e observe o EventClass 47 que significa Object:Deleted conforme a tabela relativa aos eventos que pode ser encontrada no BOL, se estiver abrindo o arquivo de trace utilizando o SQL Server Profiler é mostrado como o EventClass Object:Deleted.

Tabela de eventos do BOL.

47 Object:Deleted Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

Desta forma podemos ver que o Default Trace nos foi útil, capturando o evento e provando que o usuário Joao excluiu a tabela VENDAS, com detalhes de data e hora de execução da operação, aplicação utilizada, banco de dados, etc.

É bom lembrar que o Default Trace é limitado para apenas os eventos pré-configurados, e que este não é a solução ideal para auditoria, pois é criado apenas 5 arquivos e pode serem sobrescritos a cada restart do serviço SQL Server. Para uma solução eficiente e completa de auditoria, pode ser utilizar a feature presente no SQL Server 2008 de qual possui diversos recursos.

Espero que seja útil, abraços!

Monitorando Database Mirror com System Monitor

Olá pessoal,

Neste artigo vou mostrar alguns contadores do System Monitor relacionado ao Database Mirroring que acredito ser útil em situações onde é preciso monitorar a atividade de um banco de dados espelhado, verificar a taxa de transferencia dos registros de log, gerenciar a fila de registros em falha do banco de dados mirror ou principal, e com essas informações ter uma estimativa do tempo necessário para a sincronização dos databases, etc.

Neste artigo não irei abortar o processo de implementação e configuração do Database Mirror pois já tenho escrito um artigo com esse objetivo aqui. Como quase todas as features do SQL Server possui contadores de desempenho para monitorar suas atividades, com o Database Mirror não é diferente, vou explicar alguns dos principais contadores para Database Mirror e mostrar situações onde cada um deve ser verificado durante a atividade do sistema.

Para iniciarmos vamos entender como funciona os principais contadores:

Log Bytes Sent/sec: Este contador medi o número de bytes de log enviados ao mirror por segundo, é a taxa de qual o principal transfere registros de logs de transação para o banco de dados mirror.

Log Send Queue KB: Este contador medi a fila de dados de log de transação que não tem sido enviado ainda ao banco de dados mirror.

Transaction Delay: Este contador medi o total de delay em milisegundos que o banco de dados principal espera pelo “aviso” de commit a partir do banco de dados espelho.

Transactions/sec: Este contador medi o número de transações por segundos em um banco de dados.

No Banco de dados Mirror, vamos verificar os contadores abaixo:

Log Bytes Received/sec: Este contador medi o número de bytes de log recebidos pelo banco de dados mirror através do banco de dados principal.

Redo Queue KB: Este contador medi a fila de registros que não foram aplicados ainda ao banco de dados espelho.

Redo Bytes/sec: Este contador medi o número de bytes de log de transação estar sendo aplicado ao banco de dados espelho por segundo.

Irei detalhar as descrições dos contadores no decorrer do artigo. Para agente iniciar a monitoração, vamos abordar os três primeiros contadores descritos acima: Log Bytes Sent/sec, Log Send Queue KB, Transaction Delay. Para isso abra o System Monitor em sua instancia do banco de dados principal e adicione os contadores citados que estão localizados no Object Management SQLServer:Database Mirroring selecione os contadores e a instancia relacionada ao seu banco de dados espelhado, conforme a imagem abaixo.

Como disse acima para quem não tenha ainda configurado o Database Mirror neste artigo mostro passo a passo como configura-lo e suas devidos conceitos. No ambiente da simulação abaixo, estou utilizando o cenário de High Protection, ou seja, Alta Proteção da transferencia de dados entre o banco de dados principal e espelho, conforme a transação é primeiro confirmada “commit” no banco de dados espelho e após isso confirmada no banco de dados principal. Para monitorar as atividades no banco de dados espelho faça o mesmo procedimento acima selecionando os contadores Log Bytes Received/sec, Redo Queue KB, Redo Bytes/sec.

No meu exemplo estou utilizando um banco de dados chamado Performance e vou executar a seguinte instrução para simular uma carga de trabalho.

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 20000

Após executar a instrução acima, alterne para o System Monitor no banco de dados principal para iniciar a monitoração conforme a imagem abaixo. 

Neste momento alterne para o System Monitor no banco de dados mirror para verificar a monitoração conforme a imagem abaixo. (Poderia ter colocado todos os contadores do banco de dados principal e mirror monitorados pelo System Monitor remotamente, mas atrapalhou um pouco a visualização, por isso preferi executar o System Monitor em cada servidor separadamente.)

O gráfico mostrado no System Monitor no banco de dados principal medi os contadores Log Bytes Send/s de vermelho, Log Send Queue KB de verde e Transaction Delay na cor azul. Como descrevi acima o contador Log Bytes Send/s é a taxa de transferencia de bytes de log de transação para o banco de dados espelho e em nosso exemplo esse contador estar indicando um alto valor na transferencia de qual sua “linha” vermelha estar permanecendo na parte superior do gráfico, permanecendo até o final da transação. Podemos verificar que a quantidade de bytes de log enviados por segundo é alta. Já o contador Log Send Queue KB permaneceu como zero durante toda a transação pois como a taxa de envio de bytes de log por segundo (Log Bytes Send/s) e a taxa de recebimento de bytes de log (Log Bytes Received/sec) no banco de dados mirror estava estável o banco de dados principal não precisou armazenar registros dentro da fila de envio de log no log de transação, ou seja, o banco de dados principal estava conseguindo enviar os logs de transação em tempo satisfátório para o banco de dados espelho.

O contador Transaction Delay nos indica o total em milisegundos que o banco de dados principal espera pelo aviso de commit no banco de dados espelho. Em nosso gráfico esse contador permaneceu na parte inferior, indicando um baixo valor de espera. Esse contador é o principal contador a ser monitorado em um ambiente de High Protection e High Availability de qual utiliza Safety Full um alto valor constante pode impactar problemas de performance nas transações. Como disse que este contador indicava um total em milisegundos de delay nas transações, para verificar qual o delay estimado em cada transação apenas divida o contador Transaction Delay pelo Transactions/sec de qual irá indicar essa média.

No banco de dados mirror vamos analisar o contador Log Bytes Received/sec de qual indica a quantidade de bytes de log recebidos a partir do banco de dados principal. Em nosso gráfico este contador também é indicado pela cor vermelha e teve um alto valor permanecendo numa escala próximo ao 70% indicando que a taxa de recebimento dos bytes de log de transação estava alta. O contador Redo Bytes/sec indicado pela cor verde, variou por quase as mesmas medidas do contador Log Bytes Received/sec. Este contador medi o número de bytes de log de transação aplicados ao banco de dados espelho por segundo, em nosso caso podemos observar que a medida que os bytes de log foram recebidos estes já eram aplicados ao banco de dados espelho, quando o tráfego de envio de log é muito grande e o banco de dados espelho não consegui aplicar os registros em tempo satisfatório é gerada uma fila armazenado os registros recebidos pelo banco de dados principal mas que ainda nao tem sido aplicado ao banco de dados espelho. Essa fila é medida pelo contador Redo Queue KB indicada pela cor azul em nosso gráfico. Reparem que este contador permaneceu zero, ou seja, não foi preciso armazenar os registros na fila, pois estes já foram aplicados na medida em que eram recebidos.

Agora que já vimos alguns dos conceitos mais importantes, vamos simular cenários para agente verificar o comportamento dos contadores. Em nosso ambiente de testes vamos alterar o modo de segurançã da transação, vamos alterar para Safety OFF ,ou seja, o banco de dados principal nao irá esperar o “aviso” de commit a partir do banco de dados espelho para confirmar a transação, com isso vamos monitorar o comportamento do contador Transaction Delay. Para esse exemplo, adicione o contador Transactions/sec de qual estar no Object Management Databases ao System Monitor junto com os demais contadores e utilize a instrução abaixo para habilitar o modo de segurança OFF e execute a transação novamente.

ALTER DATABASE [Performance] SET SAFETY OFF

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 20000

No Gráfico acima monitado a partir do System Monitor do banco de dados principal selecionei o contador Transaction Delay para destacar que sua atividade foi zero. Com isso percebemos que o banco de dados principal não estar tendo delay na transação, não estar esperando o “aviso” do banco de dados espelho para confirmar a transação, a transação é confirmada e após enviada ao banco de dados espelho. Como já esperado o contador Log Bytes Send/s permaneceu na parte superior e o contador Transactions/sec também mediu as transações por segundo enviadas ao banco de dados espelho. Para melhor visualização alterei a escala do contador Transactions/sec para não sobrepor a linha do contador Log Bytes Send/s.

Agora iremos simular um cenário onde o banco de dados principal é desconectado do banco de dados espelho para verificar a fila de envio de log medida pelo contador Log Send Queue KB, para isso vou desconectar o adaptador de rede do servidor espelho simulando uma indisponibilidade qualquer. Se você é um daqueles DBA´s que só acredita vendo ou melhor “lendo” vamos ler o conteúdo do errorlog do SQL Server para verificar que o banco de dados espelho não estar respondendo, assim deixando o banco de dados principal desconectado do banco de dados espelho.

2010-07-07 11:13:05.32 spid23s     Error: 1479, Severity: 16, State: 1.
2010-07-07 11:13:05.32 spid23s     The mirroring connection to "TCP://SERVER2008:1450" has timed out for database "Performance" after 10 seconds without a response.  Check the service and network connections.
2010-07-07 11:13:05.32 spid23s     Database mirroring is inactive for database ‘Performance’. This is an informational message only. No user action is required.

Com o banco de dados principal isolado, emita novamente a transação. (Neste exemplo irei inserir 200 mil linhas para a fila crescer bastante para visualizarmos melhor)

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 200000

Podemos visualizar através do gráfico acima que o contador Log Send Queue KB indicado pela cor verde foi crescendo gradativamente de acordo com as transações vao preenchendo a fila até permanecer na parte superior do gráfico, o interessante é que este contador permanece estável durante e após a transação indicando que as transações estão na fila aguardando para serem enviadas ao servidor espelho. Neste momento se verificarmos a wait do log de transação indicando qual o motivo pelo qual este não pode ser sobrescrito podemos visualizar como DATABASE_MIRRORING de qual indica que existem transções ativas no log de qual são as transações que ainda não foram enviadas ao banco de dados espelho por isso o log de transação não pode ser truncado. Para visualizar emita a instrução abaixo:

SELECT name,log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘Performance’

name              log_reuse_wait_desc
—————– —————————-
Performance   DATABASE_MIRRORING

(1 row(s) affected)

Caso quando emita a instrução acima, apareça na coluna log_reuse_wait_desc o valor BACKUP_LOG faça um backup do log de transação para retirar a porção inativa do log de transação causado por outras transações e emita novamete a transação para verificar o valor desta coluna.

Voltando ao gráfico podemos observar que o contador Transactions/sec permaneceu na parte superior do gráfico medindo as transações por segundo no banco de dados principal e logo mais este contador teve uma queda brusca pois a transação foi concluída. Reparem que os contadores Log Bytes Send/s e Transaction Delay permanecem como zero, pois não estar havendo transferencia de bytes de log para o banco de dados espelho.

Agora que já visualizamos o aumento da fila de envio de log vamos colocar novamente o banco de dados espelho no ar e monitorar os contadores no banco de dados principal e espelho. Após restabelecer a conexão com o banco de dados espelho verifique o errorlog do SQL Server e monitore os contadores em ambos os servidores conforme as imagens abaixo.

2010-07-07 15:44:47.18 spid23s Database mirroring is active with database ‘Performance’ as the principal copy. This is an informational message only. No user action is required.

No gráfico do banco de dados principal podemos ver que quando o banco de dados espelho foi colocado online a fila de envio de log medida pelo contador  Log Send Queue KB caiu gradativamente, os registros foram transferidos para o banco de dados espelho, como podemos observar o comportamente do contador Log Send Sent/sec que aumentou repentinamente assim que conexão foi restabelecida. Verifique as imagens abaixo sobre o gráfico do banco de dados espelho:

No primeiro gráfico podemos observar o comportamento dos contadores  Log Bytes Received/sec e Redo Bytes/sec que atigiram picos de atividade assim que a conexão com o banco de dados principal foi estabelecida indicando que os bytes de log de transação começaram novamente a ser recebidos e que já estavam começando a serem aplicados ao banco de dados espelho. O mais interessante do gráfico foi que a linha do contador Redo Queue KB começou a subir pelo fato de as transações estarem sendo armazenadas em fila para ser aplicadas ao banco de dados espelho, pois o volume de bytes de log recebidos não era o mesmo que o número de bytes aplicados ao banco de dados espelho, por isso foi preciso criar a fila.

No segundo e último gráfico podemos observar que a fila de redo (Redo Queue KB) subiu como disse acima mas no decorrer do gráfico essa começa a cair, conforme as transações são aplicadas ao banco de dados espelho. Conforme a linha do tempo do gráfico vai passando podemos verificar a atividade de sincronização do banco de dados espelho de forma clara, com todas as fases bem distintas.

Espero que este artigo seja útil para aqueles que precisem monitorar as atividades do Database Mirror, que precisem estimar dados de performance e utilização de recursos. Este artigo se aplica ao SQL Server 2005 e também SQL Server 2008, apesar do SQL Server 2008 possui mais alguns contadores (Este pode ser um tema para outro artigo).

Muito Obrigado.

Abraços.

Primeiros passos com Powershell.

Olá Pessoal,

Neste artigo vou mostrar alguns scripts úteis utilizando o Powershell e SQL Server. Eu não irei falar do poder do Powershell e porque você deva ou não utiliza-lo, pois o Laerte já mostrou por "N" formas qual sua utilidade e vantagens, vou deixar ao critério do amigo leitor chegar nessa conclusão. 

Para simular situações do dia a dia pensei em algumas tarefas importantes como por exemplo: Preciso criar um inventário simples de meus servidores, armazenando nome da instancia, processadores, memóra, etc. Podemos criar um script Powershell para se conectar em nossos servidores e listar essas opções, com isso iremos manter essas informações atualizadas de arcordo com a frequencia de execução do script. Para implementar nossa solução proposta acima vamos escrever o seguinte script: 

   1: $ServerList = Get-Content C:TempScriptServers.txt

   2: ForEach ($svr in $ServerList)

   3: { 

   4: $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"

   5: $Server | Select-Object Name,Edition,Language,LoginMode,PhysicalMemory,Platform,Processors,Product,ProductLevel,VersionString,Collation,ErrorLogPath 

   6: | ConvertTo-Html -As LIST | Out-File C:TempScriptInventary.html -Append

   7: "" | Out-File C:TempScriptInventoryServers.html -Append

   8: }

Este script armazena na variável $ServerList o conteúdo do arquivo C:TempScriptServers.txt de qual contém os nomes dos servidores no padrão SERVERINSTANCE um em cada linha, após isso o script se conecta em cada servidor e lista as opções Name,Edition,Language,PhysicalMemory,Processors … e de "quebra" ainda manda para um arquivo HTML de qual pode ser hospedado em um servidor em sua intranet para uma melhor visualização. Veja a saída do comando abaixo.

Podemos perceber através das linhas marcadas os nomes dos servidores, e suas configurações, neste exemplo recuperamos configurações de duas instâncias SQL Server de qual com o mesmo script poderia se conectar em várias instâncias. A próxima situação é Como posso verificar quais databases de usuário em minhas instâncias estão com as opções AutoClose e AutoShrink habilitadas ?.Para esse exemplo vamos continuar com a mesma linha de raciocínio, conectar em nossas instâncias especificadas no arquivo Servers.txt e verificar os status das opções acima. Neste caso vamos fazer um filtro pois queremos apenas os bancos de dados de usuário.Vamos ao script:

   1: $ServerList = Get-Content C:TempScriptServers.txt

   2: ForEach ($svr in $ServerList)

   3: {

   4:  $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"

   5:  $Databases = $Server.Databases

   6:  $Server.Name

   7:  

   8: ForEach ($db in $Databases)

   9: {

  10:  if (!$db.IsSystemObject) {

  11:  $db | Where-Object {$_.AutoClose -eq $true -or $_.AutoShrink -eq $true } | Select-Object Name,AutoClose,AutoShrink | Format-Table

  12:      }

  13:  }

  14: }

Neste script verificamos se o banco de dados é um banco de dados de sistema, e após filtramos apenas os banco de dados que tenham pelo menos uma das opções como True, ou seja, retorna apenas os databases que tenha a opção AutoClose ou AutoShrink habilitadas.

Neste momento voce deve estar pensando: "Legal, mas e se eu quiser alterar esses valores ?  quero que desabilite as duas opções nos bancos de dados.selecionados" Fácil vamos alterar nosso script para desabilitar essas opções.

   1: $ServerList = Get-Content C:TempScriptServers.txt

   2: ForEach ($svr in $ServerList)

   3: {

   4:  $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"

   5:  $Databases = $Server.Databases

   6:  $Server.Name

   7:  

   8: ForEach ($db in $Databases)

   9:  {

  10:      if (!$db.IsSystemObject){

  11:          if (!$db.IsDatabaseSnapshot){

  12:              $db.AutoClose = $false

  13:              $db.AutoShrink = $false

  14:              $db.Alter()

  15:              }

  16:      $db | Select-Object Name,AutoClose,AutoShrink | Format-Table

  17:  

  18:          }

  19:      }

  20: } 

Notem que em nosso exemplo estamos validando além do banco de dados ser um banco de dados de usuário, se o banco de dados é um Database Snapshot pois sabemos que não é possível altera-lo. Logo após o script seta as opções do tipo Boolean para false, por último o script lista as opções alteradas.

Outra situação é "Como posso visualizar quando todas as minhas estatísticas em um determinado ou vários databases de uma ou várias tabelas foram atualizadas?" no script abaixo listamos a data da última atualização de todas as statísticas de todas as tabelas do banco de dados AdventureWorks.

   1: $Server = Get-SqlServer -sqlserver "SERVER2008PRODUCAO" -username "usuario" -password "senha"

   2: Get-SqlDatabase -sqlserver $Server -dbname "AdventureWorks" |Get-SqlTable | Get-SqlStatistic | Select-Object Table,Name,IsAutoCreated,LastUpdated | Format-Table –AutoSize

Sim, isso mesmo, para atualizarmos todas essas statísticas apenas adicionamos o cmd-let Update-Statistics como no script abaixo:

   1: $Server = Get-SqlServer -sqlserver "SERVER2008PRODUCAO" -username "usuario" -password "senha"

   2: Get-SqlDatabase -sqlserver $Server -dbname "AdventureWorks" |Get-SqlTable | Get-SqlStatistic | Update-SqlStatistic 

O Grande diferencial do Powershell é a possíbilidade de trabalhar em operações multi-servidores, o script acima poderia ser facilmente alterado para executar em uma range de servidores, automatizando algumas tarefas rotineiras de um DBA. Com isso concluo este artigo, espero ter demostrado um pouco da produtividade e flexibilidade oferecida pelo Powershell voltado ao SQL Server.

Abraços, até o próximo!

Referencias: http://laertejuniordba.spaces.live.com/

Curso Administração de banco de dados SQL Server 2008.

Olá Pessoal, 

Hoje trago uma notícia boa, foi confirmado o meu curso sobre Administração de banco de dados SQL Server 2008 na faculdade Unipac Teófilo Otoni, irei dar um curso rápido e introdutório sobre o SQL Server 2008, revisando alguns conceitos sobre bancos de dados e SQL Server.

O Curso será oferecido para as tumas de Sistemas de Informação e Ciencia da Computação nos dias 19 e 20 de Junho, vamos iniciar com uma introdução  teórica sobre o que é o SQL Server 2008, a família de produtos, principais edições e alguns fundamentos sobre arquitetura. Logo após iremos abortar algumas novas funcionalidades do SQL Server 2008 voltadas para a administração do banco dados como Auditoria, Resource Governor, Compressão de dados e Backup, Policy-Based Management e claro Powershell.

Conversando com o Laerte Junior um dos maiores influenciadores e pioneiros de Powershell e SQL Server no Brasil e no mundo, ele me autorizou a mostrar seus scripts e utiliza-los, dos quais serão de grande utilidade para os alunos que terão uma visão do poder do Powershell integrado ao SQL Server, (Claro que irei mostrar e mencionar o autor).

Segue abaixo os  tópicos que serão abordados no curso:     

• Pré-Requisitos e Instalação do Microsoft SQL Server 2008.
  • Preparar o ambiente de sistema operacional pra instalação do produto.
  • Verificar requisitos de software e hardware necessários.
  • Verificar componentes do SQL Server 2008 a serem instalados.
  • Selecionar funcionalidades do SQL Server 2008 a serem instaladas.
  • Especificar diretório para instalação de binários do SQL Server, definir instalação de instância nomeada ou padrão.
  • Definir configurações de segurança e serviços.
  • Especificar modos de autenticação.
• Configuração de opções da instância.
  • Configuração de memória e processadores.
  • Definição dos diretórios de backup, e arquivos de banco de dados de sistema.
  • Configuração e otimização do banco de dados de sistema tempdb.
  • Configuração da instância para acesso remoto.
  • Definir configuração padrão para arquivos de log e dados.
  • Configurar opções de cursores, paralelismo, locks, etc.
• Configuração de segurança e auditoria.
  • Criação de Logins para acesso a instância.
  • Entender as Fixed Server Roles e permissões de acesso.
  • Atribuir Logins a Fixed Server Roles.
  • Criar objetos de auditoria no nível de instância e bancos de dados.
  • Configurar auditoria para escrever em logs de aplicação, segurança e arquivos do Windows.
• Criação e configuração de bancos de dados.
  • Criação do banco de dados, utilizando o SQL Server Management Studio e Transact-SQL.
  • Criação e configuração de Files e Filegroups.
  • Configuração do log de transação e melhores práticas.
  • Configuração de opções automáticas para manutenção do banco de dados e performance.
  • Configuração de opções para verificação de corrupção do banco de dados.
• SQL Server Integration Services para importar e exportar dados.
  • Entender os conceitos do processo de ETL (Extract Transform Load)
  • Entender os conceitos a  importação a partir de outras fontes de dados como Oracle, Access, Excel,
    etc.
  • Utilizar o SQL Server  Integration Services para  importar massa de dados a partir de um arquivo de
    texto.
  • Criar tabelas, configurar colunas e data types para receber os dados.
  • Configurar agendamentos para pacotes Integration Services
• Backup e Recover de base de dados.
  • Entender o conceito de Backup e Recover de banco de dados.
  • Entender os tipos de backup e estratégias de recuperação.
  • Criar backup Full, Differential, Log de Transação.
  • Criar backups comprimidos.
  • Entender o que é um Database Snapshot.
  • Criando e restaurando um banco de dados a partir de um Snapshot.
  • Técnicas de Disaster Recover.
  • Restaurar e recuperar bancos de dados em caso de falha.
  • Recuperando bancos de dados corrompidos.
• Configurar o Resource Governor para gerenciar recursos do servidor.
  • Entender como funciona o Resource Governor.
  • Criar funções de classificação e Workloads Groups.
  • Configurar a alocação de Pool´s utilizados pelos Workloads Groups.
  • Monitorar a utilização de recursos do servidor utilizando o Performance Monitor.
• Ferramentas para monitoração do SQL Server 2008.
  • Monitorar a atividade do servidor utilizando o Activity Monitor.
  • Monitorar consultas e instruções enviadas ao servidor utilizando o SQL Server Profiler.
  • Utilizar Dynamics Management View´s para consultar metadados de objetos do SQL Server.
• Gerenciamento baseado em políticas e Compressão de dados.
  • Entender o conceito de Gerenciamento baseado em políticas no SQL Server 2008.
  • Criar políticas e especificar objetos alvos.
  • Entender o conceito de compressão de dados.
  • Utilizar a compressão de dados a nível de página.
  • Utilizar a compressão de dados a nível de linhas.
• Powershell e SQL Server 2008
  • Como funciona a linguagem de script Powershell e sua integração com o SQL Server.
  • Escrevendo scripts para automatizar tarefas no SQL Server.
  • Visualizando  alguns  exemplos  da  usabilidade  da  linguagem  Powershell  integrada  ao  SQL Server.

É isso ai pessoal, estou ancioso por esse dia e acredito que será muito bom aprender e compartilhar meu humilde conhecimento com os amigos, espero que gostem, até lá

Usando DMV´s para consultar metadados de uma tabela

Olá Pessoal

Hoje vou falar de um recurso legal que é utilizar as DMV´s para consultar metadados de uma tabela. As vezes criamos uma tabela ou mesmo quando esta já estar criada e queremos saber algumas informações vamos dizer assim: “internas” sobre ela como:

1. Quantas páginas de dados nossa tabela ocupa?
2. Qual Data File e filegroup nossa tabela reside?
3. Quantas partições nossa tabela utiliza?
4. Quais Unidades de Alocação são utilizadas por nossa tabela?
5. Quais colunas e seus tipos de dados, se a coluna é identity ou não, qual collation para cada coluna?

Para podermos responder essas perguntas precisamos entender ao menos o básico da estrutura de armazenamento de uma tabela no SQL Server. Para começar vamos analisar a imagem abaixo de qual inlustra este processo.

Considerando a imagem acima podemos observar que uma tabela ou índex no SQL Server pode conter uma ou várias partições com um limite de 1000 partições, com isso quando criamos uma tabela que não seja particionada esta irá ser amazenada em uma única partição. Caso criamos uma tabela particionada para cada partição esta será criada a mesma estrutura de armazenamento como mostrado na imagem acima. O Termo utilizado pelo SQL Server 2005 para representar uma tabela ou índex em uma única partição é o Hobt que significa Heap ou B-Tree.

Cada partição pode conter até tres tipos de linhas de quais são armazenadas em seus próprios conjuntos de página, esses conjuntos de páginas para uma determinada partição são chamados unidades de alocação. As tres unidades de alocação exibidas acima são: IN_ROW_DATA, LOB_DATA e ROW_OVERFLOW_DATA. Cada unidade de alocação armazena linhas de acordo com seus tamanhos, ou seja, dentro da unidade de alocação IN_ROW_DATA são armazenadas as linhas de dados e index para valores comuns que não ultrapassem o limite de 8K, dentro da unidade de alocação LOB_DATA serão armazenados dados do tipo LOB (Large Objects) exemplo são dados do tipo TEXT e na unidade de alocação ROW_OVERFLOW_DATA serão armazenados dados que excedem os 8K como dados do tipo VARCHAR que ultrapassem o limite.

Toda tabela ou índex contém uma entrada na DMV sys.objects de qual armazena informações sobre a tabela, como tipo do objeto que no caso é tabela de usuário ou de sistema, data de criação, data de modificação, etc. A DMV sys.objects se relaciona com a DMV sys.indexes através da coluna Object_ID de qual armazena informações sobre os indexes no objeto. Para uma Heap a informação retornada da DMV sys.indexes na coluna Type_desc é HEAP, se for criado um índex Clustered a coluna Type_Desc retorna CLUSTERED para o objeto informado, pois quando se indexa uma tabela com um índex clustered os dados da tabela são parte do índex.

A DMV sys.indexes se relaciona com a DMV sys.partitions no modelo 1 para N até o limite de 1000 partições, através da coluna Object_ID, com isso podemos visualizar as partições para uma determinada tabela ou index. A DMV sys.partitions se relaciona com a DMV sys.allocation_units de qual lista as unidades de alocação de uma determinada partição.

Agora que entendemos um pouco da estrutura de armazenamento de uma tabela ou index e os relacionamentos das DMV´s responsáveis, vamos criar nosso ambiente de testes para responder nossas perguntas enumeradas acima. Para criar nosso ambiente vamos criar um banco de dados de teste chamado Storage_Engine e criar nossa tabela chamada TB_Storage conforme o script abaixo:

–Criando o banco de dados.
CREATE DATABASE Storage_Engine

–Criando a tabela de teste TB_Storage.
USE Storage_Engine
CREATE TABLE TB_Storage
(
     ID INT IDENTITY(1,1)
    ,NAME CHAR(8000)
)

Criei a tabela TB_Storage com o tipo de dados CHAR(8000) para que a cada linha inserida a tabela ocupe uma página de dados. Com nossa tabela criada vamos consultar a DMV sys.objects e a DMV sys.indexes para verificar algumas informações das quais detalhamos anteriormente. Poderíamos fazer um Inner Join entre as duas DMV´s, mas para visualizar todas as colunas de cada DMV, preferi dividir em dois lotes de consultas.

–Consultando a DMV sys.objects
SELECT *
FROM sys.objects
WHERE Object_id = Object_id(‘TB_Storage’)

–Consultando a DMV sys.indexes
SELECT *
FROM sys.indexes
WHERE Object_id = Object_id(‘TB_Storage’)

 

 

Com os dados retornados a partir das consulta na DMV sys.objects podemos verificar o nome da tabela, o object_id do objeto, descrição da tabela como User_Table, data de criação, modificação, etc. Com os dados retornados pela DMV sys.indexes podemos verificar algumas informações sobre index como Index_ID que no caso é 0 pois nao contém nenhum index em nossa tabela, o tipo de descrição é HEAP como discutimos anteriormente.

Agora que já listamos informações sobre nossa tabela, vamos começar a tentar responder as perguntas no inicio do artigo. Para responder a primeira pergunta vamos utilizar a query abaixo de qual lista as páginas de dados utilizadas por nossa tabela.

–Inserindo alguns valores para o SQL Server alocar páginas para a tabela
INSERT INTO TB_STORAGE VALUES(‘A’)
INSERT INTO TB_STORAGE VALUES(‘B’)
INSERT INTO TB_STORAGE VALUES(‘C’)

–Listando as páginas de dados usadas e reservadas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)

Através da saída acima podemos observar as colunas total_pages, used_pages, data_pages na coluna Total_pages retorna o total de páginas alocadas para a tabela incluíndo páginas IAM que são para controle do SQL Server, como em nosso exemplo inserimos 3 valores foram criadas 3 páginas de dados. Na coluna Used_Pages como o nome já diz, é retornado todas as páginas de dados usadas pela tabela incluindo a página IAM, é diferente da coluna Total_pages de qual lista as páginas alocadas, que não necessáriamente estão usadas pela tabela, supondo que inserimos mais 6 linhas, nossa tabela teria 9 páginas de dados com isso o SQL Server iria alocar um extend uniforme para a tabela, com isso a coluna Total_pages retornaria 17 páginas (16 páginas de dados alocadas + 1 página IAM) e a coluna Used_pages retornariam 10 páginas (9 páginas de dados + 1 página IAM). Na coluna Data_pages é retornado somente as páginas utilizadas como páginas de dados para a tabela, em nosso exemplo 3 páginas de dados.

Reparem que com essa query respondemos duas perguntas, a pergunta 1 a 4. Como em nosso exemplo somente temos dados comuns, estes são apenas armazenados na unidade de alocação IN_ROW_DATA para exemplificar isto, vamos alterar a estrutura da tabela adicionando duas novas colunas uma do tipo varchar(8000) e outra do tipo de dados text, vamos verificar o que acontece.

–Criando a coluna do tipo varchar(8000)
ALTER TABLE TB_STORAGE
ADD EMAIL VARCHAR(8000)

–Criando a coluna do tipo text
ALTER TABLE TB_STORAGE
ADD ENDERECO TEXT

–Listando as unidades de alocações e suas respectivas páginas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)

O SQL Server criou mais 2 unidades de alocação para a tabela. Observem que com a mesma query respondemos as perguntas 1 e 4. Foi listado as tres unidades de alocação para nossa tabela e suas páginas de dados, como não inserimos dados nas colunas criadas, as unidades de alocação não armazenam nenhuma página de dados.

Para respondermos as pergunta 2 vamos utilizar o comando DBCC IND de qual retorna diversais informações inclusive o ID do arquivo físico de qual nossa tabela reside. Essa informação é retornada através da coluna PageFID que significa Index File ID, para listar o nome lógico e o filegroup associado ao arquivo utilizamos a query abaixo de qual relaciona as DMV´s sys.sysfiles e sys.filegroups.

–Listando o ID do Data File da tabela TB_Storage
DBCC IND(Storage_Engine,TB_Storage,-1)

–Listando o nome lógico e físico do arquivo retornado.
SELECT   f.name
             ,f.filename
             ,fg.name as ‘Filegroup Name’
             ,fg.type_desc
FROM sys.sysfiles f
INNER JOIN sys.filegroups fg
ON f.fileid = fg.data_space_id
WHERE f.fileid = 1

Além da coluna PageFID a saída do comando DBCC IND nos retorna informações úteis sobre a tabela como o ID da Partição, Index ID, Tipo de Página (1= Página de dados, 10 = Página IAM) entre outras informações. Através da coluna Partition ID podemos responder a pergunta 3 pois, a saída do comando DBCC IND já nos lista as partições de nossa tabela, mas para ficar mais claro essas informações vamos utilizar a DMV sys.partitions.

–Listando partições da tabela TB_Storage
SELECT *
FROM sys.partitions
WHERE object_id = object_id(‘TB_Storage’)

Podemos observar que nossa tabela tem apenas uma partição, o número da partição é 1 e existem 3 linhas de dados nessa partição. Vamos agora responder a nossa quinta pergunta listando as colunas e tipos de dados em nossa tabela, informações sobre collation e propriedade identity. Para pegarmos esses valores devemos relacionar as DMV´s sys.columns e sys.types de quais armazenam as colunas para cada tabela e os tipos de dados de sistema e tipos de dados definidos por usuário. Para visualizarmos os nomes dos tipos de dados para cada coluna precisamos relacionar as DMV´s pois na DMV sys.columns é exibido somente o código do tipo de dados, por isso pegamos esse nome a partir da DMV sys.types conforme o script abaixo.

SELECT    C.object_id
              ,C.name
              ,C.column_id
              ,D.name as ‘Data Type’
              ,C.max_length
              ,C.collation_name
              ,C.is_nullable
              ,C.is_identity
              ,C.is_computed
FROM sys.columns C
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE C.Object_id = Object_id(‘TB_Storage’)
ORDER BY C.column_id

Com essa saída de dados podemos verificar qual a ordem das colunas através da coluna column_id, o tipo de dados, tamanho máximo, nome da collation para cada coluna, se a coluna aceita valores nulos e se é uma coluna computada.

Algumas DMV´s utilizadas para responder algumas perguntas também poderiam ser utilizadas para responder outras ou mais de duas questões, porém utilizei DMV´s diferentes para mostrar de forma clara cada uma e suas informações retornadas. Na verdade esse é um dos principais benefícios em utilizar DMV´s pois os dados estão sempre relacionados e as DMV´s nos mostram uma informação completa, precisa e atualizada dos objetos e estruturas internas do SQL Server.

Espero que o artigo seja útil para a comunidade. Até o próximo!

Com apenas o Recovery Model Bulk-Logged sua importação é minimamente registrada em log?

Olá Pessoal,

Hoje vou falar de uma situação que talvez muitas pessoas tenham dúvidas, ou alguma vez passou pela sua cabeça se apenas alterando o Recovery Model do Banco de dados para Bulk Logged fosse suficiente para que uma carga em massa de dados seja minimamente registrada em log.

Na verdade existem alguns pre-requisitos para serem satisfeitos para garantir esse objetivo, tais como a tabela de destino é um HEAP ? ou tem um Índice Clustered ? tem algum índice NonClustered ? a tabela estava vazia durante a importação? etc. Para visualizarmos de maneira mais clara essas situações iremos utilizar a tabela abaixo:

Observe que existem alguns fatores na estrutura da tabela de destino que devem ser avaliados antes da importação dos dados. De acordo com a tabela acima em uma tabela vazia independente desta tiver índices ou não a importação será sempre minimamente registrada em log e caso esta já contenha dados e tiver índices a importação não será minimamente registrada em log, ou seja, todos os registros serão inseridos no log de transação por completo (Exceto para o índex NonClustered de qual será minimamente registrada em log a tabela de dados e um registro completo no index).

Para demonstrar essas situações vou criar um banco de dados de teste chamado DB_BULK criar uma tabela e inserir alguns registros para povoa-la, após isso vamos exportar os dados para um arquivo de texto simples e recriar o banco de dados e a tabela para fazermos a importação alterando o Recovery Model do banco de dados para Bulk Logged e verificar se o Transaction Log irá ser preenchido ou não após a importação validando se a importação foi minimamente registrada em log ou não. Para referenciar os scripts utilizados irei colocar um número para identificar cada bloco de script.

–Script1: Criando o banco de dados DB_Bulk especificando como tamanho inicial do Transaction Log 200MB
–crecimento automatico de 100MB.
CREATE DATABASE DB_Bulk
ON
(
    NAME = ‘Bulk_DATA’,
    FILENAME = ‘C:BulkBulk_DATA.MDF’,
    SIZE = 100MB,
    FILEGROWTH = 100MB
)
LOG ON
(
    NAME = ‘Bulk_LOG’,
    FILENAME = ‘C:BulkBulk_LOG.LDF’,
    SIZE = 200MB,
    FILEGROWTH = 100MB
)
GO

–Backup Full para o banco de dados sair do modo "Pseudo Simple"
BACKUP DATABASE DB_Bulk TO DISK = ‘C:BACKUPDB_Bulk.BAK’ WITH INIT

–Criando a tabela de teste e gerando os dados.
USE DB_Bulk

CREATE TABLE TB_Bulk
(
     COD INT IDENTITY(1,1)
    ,NOME VARCHAR(100)
    ,DATA DATETIME
    ,ENDERECO VARCHAR(100)
)
–Inserindo 500 Mil linhas na tabela
INSERT INTO TB_Bulk VALUES (‘A’,GETDATE() – 10,‘B’)
GO 500000

–Criando os indices Clustered e NonClustered
CREATE CLUSTERED INDEX [IND_CL] ON TB_Bulk(COD ASC)
CREATE INDEX [IND_NC] ON TB_Bulk(NOME ASC)

Com a tabela criada e os dados gerados vamos exportar os dados para um arquivo de texto utilizando o aplicativo BCP, conforme o script abaixo.

Script 2:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk out IMPORT.txt -t, -U sa –P (senha) -c

Arquivo IMPORT.txt:

 

 

 

 

Ok, com nosso arquivo de texto gerado com os dados para a importação vamos excluir o banco de dados e executar novamente o Script1 para recriar o banco de dados e a tabela de teste. Execute todo o Script 1 exceto a instrução INSERT para povoar os dados, iremos utilizar o arquivo IMPORT.txt para importar os dados. Vamos alterar o Recovery Model do banco de dados para Bulk-Logged e verificar o tamanho do Transaction Log e seu espaço livre.

Script 3:

USE MASTER

DROP DATABASE DB_Bulk

Execute o Script 1 Exceto a instrução INSERT.

–Alterando o Recovery Model para Bulk Logged
ALTER DATABASE DB_Bulk SET RECOVERY BULK_LOGGED

–Visualizando o tamanho e espaco livre do Transaction log para o banco de dados DB_Bulk
DBCC SQLPERF (LOGSPACE)

Observe que antes da importação o Transaction Log do nosso banco de dados DB_Bulk tem o tamanho de 200 MB (199.9922 exatamente) e neste momento estar com 0.3425427% de seu espaço utilizado. Agora com o Recovery Model Bulk Logged configurado vamos fazer a importação no banco de dados e verificar os resultados. Para realizar a importação utilize novamente o utilitário BCP conforme script abaixo.

Script 4:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha)-c

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 22853  Average : (21878.97 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Com o resultado do comando DBCC SQLPERF (LOGSPACE) podemos verificar que o tamanho atual do nosso Transaction Log é 399 MB, ou seja, o Transaction Log cresceu durante a importação dos dados, como configuramos na opção FILEGROWTH no momento da criação do banco de dados para o crescimento de 100 em 100 MB, o nosso Log cresceu 2 vezes até atingir os 399 MB. Podemos observar também que a coluna Log Space Used(%) estar indicando que o espaço usado dentro do log é 32.1288% de 399.9922MB cerca de 128MB utilizados.

A saída do utilitário BCP nos mostra que foram importadas 500 mil linhas e que o tempo gasto na importação foi de 22853 milisegundos. Com esses resultados podemos concluir que a importação não foi minimamente registrada em log ou seja, os dados foram completamente logados dentro do Transaction Log. Como podemos observar apenas alterar o Recovery Model do banco de dados para Bulk Logged não assegura que a importação será minimamente registrada em log, para garantirmos isso é preciso utilizar a opção TABLOCK do utilitário BCP, com essa opção estamos especificando o modo de bloqueio exclusivo durante a importação. O TABLOCK é necessário para garantir que a importação será minimamente registrada em log juntamente com os outros fatores relacionados a index que descrevi no inicio do artigo. Além de ser necessário, este fornece uma melhor performance durante a importação pois impede que o SQL Server adquira bloqueios a nível de linha para cada instrução durante a importação.

Outro fator importante durante a importação é a opção para especificar o tamanho do lote, o tamanho do lote é quem irá definir a quantidade de linhas inseridas em cada lote, por exemplo poderíamos definir que a importação iria utilizar 5 lotes de 100 mil linhas cada. Qual a desvantagem disso? Se voltarmos no inicio do artigo falei que o SQL Server trabalha diferente durante a importação para tabelas vazias e tabelas que já contenham dados e esteja indexada, com isso se dividirmos em vários lotes nossa importação durante o primeiro lote o SQL Server irá minimamente registrar em log os registros, mas a partir do segundo lote a importação não será mais minimamente registrada em log, porque a tabela não estará mais vazia, assim as instruções serão registradas em log por completo. Vamos ver um exemplo desta situação mais adiante. Por padrão o utilitário BCP trabalha com o conceito de único lote, as mensagems informadas durante a importação são apenas informativas, para especificar o tamanho do lote utilize a opção b e o número de linhas.

Agora que entendemos o porque que nossa importação não foi minimamente registrada em log vamos refazer os testes utilizando a opção TABLOCK do utilitário BCP e verificar os resultados. Para refazermos os testes siga a sequencia de script listada abaixo para recriar o banco de dados e a tabela de teste.

• Execute a instrução DROP DATABASE DB_Bulk do Script 3.
• Execute o Script 1 exceto a instrução INSERT.
• Execute a instrução ALTER DATABASE do Script 3.

Com o banco de dados recriado e configurado o Recovery Model Bulk Logged execute o script abaixo para importar os dados para a tabela TB_Bulk,

Script 5:

C:Program FilesMicrosoft SQL Server90ToolsBinn>BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha) -c -h TABLOCK -b 500000

Starting copy…
500000 rows sent to SQL Server. Total sent: 500000

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 5688   Average : (87904.36 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Agora podemos observar que a saída do comando DBCC SQLPERF (LOGSPACE) é bastante diferente e por si só já fala muita coisa hein ? Neste caso podemos ver que o tamanho atual do Transaction Log não foi alterado, ou seja, durante a importação são foi preciso estender o log como a importação anterior. O nosso Transaction Log permanece com os mesmos 200 MB de qual foi especificado durante a criação do banco de dados e olhando a coluna Log Space Use(%) podemos perceber também uma grande diferença em relação a importação anterior pois agora apenas 10.4655% do tamanho do Transaction Log é utilizado cerca de 20MB, a primeira importação ocupou cerca de 128MB do tamanho do log.

A saída do utilitário BCP também nos mostra informações importantes como o tempo total da importação que em nosso exemplo foi apenas 5688 milisegundos comparados com a ultima importação é uma diferença muito grande. O Fato da primeira importação precisar estender o Transaction Log contribuiu para o aumento no tempo total da importação. Com estes resultados podemos ver que a importação foi minimamente registrada em log.

Para demostrarmos a diferença entre a tabela indexada vazia e a tabela contendo alguns valores no momento da importação vamos executar os seguintes passos. Execute todo o procedimento listado abaixo para recriar a estrutura e inserir um único valor a tabela TB_Block, após isso vamos realizar novamente a importação e verificar os resultados.

Execute a instrução DROP DATABASE DB_Bulk do Script 3.

Execute o Script 1, inserindo apenas 1 registro na tabela TB_Bulk.

Execute a instrução ALTER DATABASE do Script 3.

Execute o script 5 para importar os dados.

Starting copy…
500000 rows sent to SQL Server. Total sent: 500000

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 21290  Average : (23485.20 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Podemos observar que durante esta importação obtemos quase o mesmo resultado como a primeira importação,ou seja, a importação não foi minimamente registrada em log. Isso aconteceu pois com a tabela indexada e que já tenha registro a importação não será minimamente registrada em log como descrito acima e demonstrado a partir da tabela no inicio do artigo.

Com isso podemos concluir que apenas especificando o Recovery Model Bulk Logged durante a importação de uma massa de dados não é suficiente para esta ser minimamente registrada em log, é preciso satisfazer diversos fatores como existencia de index, a tabela estar vazia ou não, utilizar a opção TABLOCK do utilitário BCP, utilizar um lote único durante a importação e outro ponto a observar que não comentei acima é que a tabela de destino não pode estar participando de uma replicação. É extremamente recomendado realizar importação de dados durante uma janela de manutenção para melhor otimização do processo, diminuindo contenção de recursos do servidor, melhor situação para utilizar a opção TABLOCK de qual adquire um lock exclusivo na tabela de destino e talvez até dropar alguns índices antes da importação dos dados para melhor performance e assegurar que a importação será minimamente registrada em log, feita a importação os índices seriam reconstruídos, sei que não é algo tão simples pois alguns índices são utilizados para garantir a integridade dos dados.

Espero que este artigo seja útil para a comunidade, até o próximo.

Coletando e Aplicando Workloads com Replay Trace

Olá pessoal,

Hoje vou falar de um recurso que considero muito interessante disponível com o SQL Server Profiler chamado Replay Trace, de qual consiste de reproduzir um trace gerado, ou seja, com o trace coletado a partir de uma instancia SQL Server é possível reproduzi-lo em outro servidor ou no servidor local de forma que este irá executar todas as instruções armazenadas no arquivo ou tabela trace, simulando conexões e autenticação de usuários, o que permite reproduzir a atividade no momento do rastreamento. Isto é basicamente o conceito de Replay Trace!

Onde isso seria útil ? Podemos pensar na utilidade do Replay Trace em diversas situações das quais irei listar algumas abaixo:

• Aplicar uma Workload coletada pelo Replay Trace em um novo Hardware com o objetivo de testar qual será seu desempenho.
• Testar a compatibilidade do sistema antes de aplicar um novo Service Pack ou Hotfix, assim coletaria uma carga de trabalho com o Replay Trace e aplicaria em um servidor de simulação verificando e analisando os resultados antes de colocar em produção.
• Testar qual será o desempenho e compatibilidade de uma aplicação diante a uma nova versão do produto.

São inumeras as situações onde o Replay Trace pode ser útil. Para começar a utilizar o Replay Trace inicialmente precisamos definir algumas colunas que precisam obrigatoriamente constar no arquivo trace para que este possa ser reproduzido. O Profiler disponibiliza um template chamado TSQL_Replay onde todas as colunas e eventos necessários ao Replay Trace já estão selecionados, para a visualização das colunas e eventos necessários clique aqui.

Antes de colocar a mão na massa vou fazer algumas considerações sobre o uso do Replay Trace em um servidor diferente, de qual será o foco do nosso artigo.

Para que o Replay Trace tenha sucesso precisamos assegurar que todos os logins e usuários sejam criados no servidor secundário e estejam no banco de dados correspondente, tenham as mesmas permissões, mesmas senhas e estejam configurados para utilizar o mesmo banco de dados como banco de dados padrão. Além dos logins o ID dos bancos de dados devem ser os mesmos,ao menos que a coluna DatabaseName esteja selecionada.

Agora que conhecemos o conceito e vimos algumas considerações podemos iniciar nossos testes, em nosso exemplo irei criar 4 conexões com o banco de dados e cada conexão irá executar uma instrução SQL aleatória para demonstrar atividade na base de dados, as instruções serão coletadas com o Replay Trace, após isso iremos reproduzir o Replay Trace em uma segunda instancia para analisar os resultados e verificar as conexões criadas na instancia no momento da execução do trace.

Em nossa instancia primária chamada SERV-SQLPRODUCAO vamos criar 4 conexões. A minha primeira conexão recebeu o SPID 52 (lembre-se dos SPID´s iremos nos referenciar a eles mais adiante), estou logado com o login sa e vou executar o seguinte lote de instruções SQL para criar no banco de dados Northwind e nossa tabela de teste.

USE Northwind

–Criando a tabela ReplayTeste.
CREATE TABLE ReplayTeste
(
     COD INT IDENTITY(1,1)
    ,TEXT VARCHAR(50) DEFAULT ‘REPLAY’
    ,DATE DATETIME DEFAULT GETDATE()
)

Após criar a tabela ainda na mesma conexão insira a instrução SQL abaixo para povoar a tabelas e simular a atividade na base, nao a EXECUTE iremos executa-la no momento do Trace.

–Lote de registros
INSERT INTO ReplayTeste VALUES(DEFAULT,DEFAULT)
GO 50000

Agora abra mais uma nova conexão,altere o contexto do banco de dados para Northwind e insira a instrução abaixo, mas não a execute agora,note que o spid desta conexão é 53.

–Comando DBCC Checkdb para simular atividade na base.
DBCC CHECKDB(‘Northwind’)

Repita os passos anteriores para criar uma nova conexão, o spid desta conexão é 54.

–simulando acesso simultaneo a base de dados.
SELECT     O.OrderID
        ,Od.ProductID
        ,Od.UnitPrice
        ,Od.Quantity
        ,P.ProductID
        ,P.ProductName
FROM Orders O
INNER JOIN [Order Details] Od
ON O.OrderID = Od.OrderID
INNER JOIN Products P
ON Od.ProductID = Od.ProductID
WHERE P.ProductName = ‘Chang’

Em nossa última conexão vamos inserir a instrução SQL abaixo, o spid desta conexão é 55.

–Simulando atividade na base.
UPDATE Employees
SET BirthDate = GETDATE() – 1

Pronto agora com as nossas conexões preparadas vamos inciar nosso Replay Trace e executar cada uma, para coletar os dados. Antes de iniciar nosso trace vamos dar uma olhada nas conexoes ativas no momento utilizando a Store Procedure SP_WHO2, conforme a imagem abaixo.

Podemos observar as 4 conexões ativas através dos SPID´s 52,53,54,55. Para iniciar o Replay Trace iremos abrir o SQL Server Profiler criar um novo Trace especificando o template TSQL_Replay, especifique para salvar o trace em um arquivo como mostrado na imagem abaixo:

Podemos visualizar após a definição do template TSQL_Replay as colunas e eventos selecionados por padrão, conforme mencionei acima clicando na aba Events Selection como mostrado na figura abaixo:

Especifique um filtro para o DatabaseName Northwind, no botão Column Filters conforme abaixo.

Após definir o Filtro clique em Run para iniciar o trace. Com o trace iniciado vamos executar todas as instruções em cada conexão começando pelo SPID 52 para começar a coletar os dados. Durante a execução do Trace a tela do Profile irá ficar conforme a imagem abaixo, espere até o Loop terminar e pare o trace. Para visualizar as outras instruções utilize a opção Find no menu Edit para localizar as instruções dentro da saída.

Agora que temos o arquivo trace, precisamos importa-lo no segundo servidor para reproduzir o trace. Como mencionei acima para o Replay Trace ter sucesso em sua execução é preciso satisfazer algumas considerações como mesmos logins e senhas, banco de dados padrão para cada login utilizado no Trace, Database ID iguais ou coluna DatabaseName selecionada. Para reproduzir o trace faça um backup do Banco de dados Northwind e restaure o mesmo no segundo servidor, transfira o arquivo trace de qual será importado no SQL Server Profiler do nosso segundo servidor. Neste artigo não irei mostrar os detalhes de Backup e Restore, para uma completa informação veja no post SQL Server 2005 Backup e Recover.

Com o Banco de dados Northwind restaurado, iremos truncar a tabela ReplayTeste para eliminar os registros inseridos a partir do Loop. Com as outras instruções não precisamos nos preocupar pois o SPID 53 executa o comando DBCC CHECKDB para testar a integridade da base de dados, o SPID 54 executa uma instrução SELECT na tabela Orders, Order Details e Products e o SPID 55 executa um UPDATE na tabela Employees definindo sempre um valor diferente para a coluna Birthdate.

TRUNCATE TABLE ReplayTeste

Antes de importar o arquivo trace e reproduzi-lo vamos executar a store procedure SP_WHO2 para visualizarmos as conexões ativas conforme a imagem abaixo.

Repare que apenas o SPID 52 estar ativo pois foi esta conexão que utilizamos para Restaurar Backup do Banco de dados Northwind, Truncar a tabela ReplayTeste e executar a store procedure SP_WHO2, agora vamos iniciar o SQL Server Profiler e importar o arquivo trace para reproduzir o trace. Após importar o arquivo trace clique no botão Start Trace para iniciar o trace, se conecte ao segundo servidor (no meu exemplo é SERVER02) e é aberto a tela Replay Configuration. Neste momento podemos especificar se queremos salvar o resultado de saída do trace para um arquivo ou uma tabela, se iremos executar o trace na sequencia exata de qual os eventos ocorreram no servidor, esta opção habilita Debugging ou se iremos utilizar multiplas threads para executar os eventos trace de qual otimiza a performance do servidor, podemos também definir quantas threads serão utilizadas e a ultima opção podemos selecionar se será exibido o resultado do trace. Deixei as configurações padrão conforme a imagem abaixo pois quero simular a mesma carga do servidor original.

Na aba Advanced Replay Options podemos especificar algumas opções interessantes sobre o Replay Trace como definir se serão executados SPID´s do sistema e ainda especificar apenas um SPID em particular para executar, ou seja, podemos especificar que apenas uma conexão de usuário será executada. A imagem abaixo lista os SPID´s envolvidos no trace, reparem que é exatamente as mesmas conexões que abrimos para simular a atividade no banco de dados SPID 52,53,54 e 55. Abaixo temos a opção para limitar a execução do replay de acordo com uma determinada data e hora. Não irei selecionar para executar um SPID em particular para visualizarmos a atividade real com todas as conexões envolvidas. Desmarque a opção Replay one SPID only e clique em OK para iniciar o Replay Trace.

Durante a execução do Trace a janela do SQL Server Profiler irá ficar mais ou menos conforme a imagem abaixo, notem que é exibido uma porcentagem da conclusão do Replay Trace, o painel é divido exibindo em sua parte superior o bloco de instruções sendo executadas, no meio os resultados.

Antes de concluir a execução do trace alterne para o SQL Server Management Studio e execute a store procedure SP_WHO2 para visualizar as conexões existentes no momento da execução do trace, reparem na coluna ProgramName de qual indica as conexões através do SQL Server Profiler conforme imagem abaixo.

Após a conclusão do Replay Trace é exibido os resultados no painel inferior como total de eventos executados, tempo gasto durante o Replay, total de erros, erros internos, etc. 

Para verificarmos que o Replay Trace foi executado com sucesso, podemos executar a seguinte instrução SELECT de qual irá retornar o número de linhas armazenadas na tabela ReplayTeste.

SELECT COUNT(*)
FROM ReplayTeste

———–
50000

(1 row(s) affected)

Pronto ! O Replay Trace foi executado com sucesso, foram inseridos as 50 mil linhas em nossa tabela de teste. Com isso concluo meu artigo sobre Replay Trace, mostrando algumas vantagens em utilizar essa poderosa feature disponível no SQL Server.

Até o próximo.

Qual Backup Full meu Differential e Log referenciam?

Bom dia pessoal,

Hoje vou falar de um problema muito interessante que observei no Forum MSDN,que pode confundir alguns pessoas.Situação do Problema:

Você é o DBA da sua empresa e estar com dois Backup Set para restaurar seu banco de dados,ou fazer uma migração,etc.Um Backup Set estar armazenado o Backup Full da sua Base e o outro um Backup Differential.Você não sabe a precedencia destes backups,apenas “cairam” em sua mao,como se fosse uma bomba,juntamente com aquela velha expressão: “Se vira !”.

Ok,com os Backup Set´s em maos,voce começa o restore.Vamos exemplificar a situação utilizando o banco de dados de exemplo Northwind.

–Restaurando o Backup Full
RESTORE DATABASE NORTHWIND FROM DISK = ‘C:BACKUPNORTHWIND_FULL.BAK’ WITH NORECOVERY,REPLACE,STATS = 10

Processed 3768 pages for database ‘NORTHWIND’, file ‘Northwind’ on file 1.
Processed 1 pages for database ‘NORTHWIND’, file ‘Northwind_log’ on file 1.
RESTORE DATABASE successfully processed 3769 pages in 10.481 seconds (2.945 MB/sec).

–Restaurando o Backup Differential
RESTORE DATABASE NORTHWIND FROM DISK = ‘C:BACKUPNORTHWIND_DIFF.BAK’ WITH STATS = 10

Msg 3136, Level 16, State 3, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Opa ! Ocorreu um erro no processo de restore do Backup Differential,O que aconteceu ? O SQL Server apontou o erro durante o restore do Backup Differential porque este Backup Differential estar com sua caidea de LSN quebrada,ou seja,o backup Full que ele referencia,de qual ele armazena todas as alterações não é o Backup Full previamente restaurado,com isso o SQL Server não pode restaurar o Backup Differential com a cadeia quebrada de qual iria gerar problemas de integridade na base.

Como poderíamos prever o problema este problema,antes de perder tempo restaurando nosso Backup Full ?

Para assegurar que os nossos Backup Set´s estejam com cadeia de LSN correta,podemos utilizar o comando RESTORE HEADERONLY de qual nos retorna diversas informações sobre nossa midia de backup,como Nome do Backup Set,Tamanho em Bytes,Descrição,Usuário que efetuou o Backup,hora de começo e fim do Backup,entre outras,mas em nosso caso as informações  imprecindíveis são as colunas FirstLSN e DatabaseBackupLSN.

A coluna FirstLSN armazena o Log Sequence Number(LSN) do primeiro ou mais antigo registro de log dentro do Backup Set e a coluna DatabaseBackupLSN armazena o Log Sequence Number(LSN) do mais recente backup Full,é o começo do Checkpoint no instante do começo da operação de backup,esta coluna pode igualar com o valor da coluna FirstLSN caso o banco de dados esteja ocioso no momento do backup.Com isso temos que comparar os valores da coluna FirstLSN da saída do comando RESTORE HEADERONLY contra o Backup Set do Backup Full com o valor da coluna DatabaseBackupLSN da saída do comando RESTORE HEADERONLY contra o Backup Set do backup Differential,essa coluna deve referenciar o mesmo valor da coluna FirstLSN do backup Full,pois o backup differential armazena todas as alterações após este LSN,caso seja diferente a cadeia de LSN estará quebrada,ou seja,este backup differential tem como “base” outro backup Full.

Agora com as informações corretas vamos aos testes.Vamos utilizar o comando RESTORE HEADERONLY contra os Backup Set´s utilizados anteriormente para visualizar a discrepancia entre os LSN.

–Visualizando os LSN.
RESTORE HEADERONLY FROM DISK = ‘C:BACKUPNORTHWIND_FULL.BAK’
RESTORE HEADERONLY FROM DISK = ‘C:BACKUPNORTHWIND_DIFF.BAK’

Podemos observar através da imagem,que a primeira linha corresponde a saída do comando RESTORE HEADERONLY do Backup Set Full e a segunda do Backup Set Differential,na coluna FirstLSN marcada como verde visualizamos o LSN do Backup Set Full e se compararmos a coluna DatabaseBackupLSN podemos ver a discrepancia entre os valores,como disse que esta coluna armazena o LSN do mais recente backup Full,se repararmos nesta mesma coluna mas para o Backup Set Full podemos ver que é o mesmo LSN da coluna DatabaseBackupLSN do Backup Set Differential,com isso concluimos que este backup differential referencia o backup Full efetuado anterior a este em nosso exemplo.

Agora vou mostrar a cadeia de LSN completa,efetuando um Backup Differential e um Backup Log para verificarmos os valores em sequencia,representando a cadeia de backup suportada pelo SQL Server.

Exemplo da cadeia de LSN integra,utilizando Backup Full,Differential e Log.

–Backup Full.
RESTORE HEADERONLY FROM DISK = ‘C:BACKUPNORTHWIND_FULL.BAK’
–Backup Differential.
RESTORE HEADERONLY FROM DISK = ‘C:BACKUPNORTHWIND_DIFF(1).BAK’
–Backup Log.
RESTORE HEADERONLY FROM DISK = ‘C:BACKUPNORTHWIND_LOG.TRN’

Podemos observamos que a coluna DatabaseBackupLSN dos backups set´s Differential e Log referencia a coluna FirstLSN do Backup Set Full,ou seja,a cadeia de LSN estar integra.

Apenas para finalizar ou mostrar a situação,onde poderíamos visualisar as informações de nosso backup Full de qual nosso primeiro Backup Differential mostrado na primeira imagem referenciava.Caso agente tivesse acesso a uma instancia do SQL Server onde foram efetuados os Backup´s poderíamos consultar a tabela de sistema chamada Backupset do banco de dados MSDB,para recuperar algumas informações úteis sobre o backup Full realizado,como “quem” realizou o backup, data e hora,etc. Segue a query de qual responderia as nossas perguntas.

USE MSDB
SELECT *
FROM BACKUPSET
WHERE first_lsn = 2109000000035200044

 

Com isso,concluo meu artigo,sobre como descobri qual backup full os nossos backups differenciais e log referenciam,bem como uma solução para entender o erro 3136 de qual define a cadeia de log quebrada.Espero que gostem,até o próximo.

 

Implementando Database Mirror

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.

SQL Server 2005: Backup e Recover.

Olá

Neste artigo vou explicar os procedimentos para realizar uma das operações mais importantes no dia a dia de um DBA.Backup e Recover é o procedimento de criar-se uma cópia da base de dados de qual pode ser restaurada em um momento de falha,onde é preciso recuperar um banco de dados preservando os dados com o mínimo ou nenhuma perda de dados.Vamos discutir alguns dos tipos de Backup existentes no SQL Server 2005 bem como as variadas estratégias de backup e restore.

Backup Full: O Backup Full (Completo) copia todos os dados do banco de dados,armazenando todos os objetos do banco de dados.O Backup Full é transacionalmente consistente de qual captura toda a estrutura do banco de dados e seus dados relacionados.Como outros tipos de backup o Backup Full é online,ou seja,este pode ser executado enquanto o banco de dados segue sua utilização normalmente.Com o Backup Full o SQL Server armazena a porção do Log de Transação durante o intervalo da realização do backup,com isso o Backup Full pode ser utilizado para retornar o estado do banco de dados no momento em que se acabou a tarefa de backup.O Backup Full é utilizado como base no momento de restore,com isso antes e restaurar qualquer outro tipo de backup,deve ser restaurado o backup full e após ir restaurando os tipos adicionais de backup para completar o recovery deixando o banco de dados operacional.

Backup Differential: O Backup Differential (Diferencial) captura todos os dados alterados desde o ultimo Backup Full do banco de dados.Após realizar um Backup Full do banco de dados,uma página chamada DCM – Differential Change Map é responsável por armazenar um bit para cada página de dados alterada desde o ultimo backup full.Quando o SQL Server vai realizar o Backup Differential este ler esta pagina e captura todas as páginas listadas dentro da DCM.O Backup Differential pode economizar espaço em disco e diminuir o tempo da janela de backup em relação ao backup full,mas não pode ser esquecido que este armazena todas as páginas de dados alteradas desde o ultimo backup full,não desde o ultimo diferencial com isso pode se gerar um grande volume de informações.

Transaction Log Backups: Backups do log de transação armazena todos os registros contidos dentro do log de transação.Registros de atualização da base dados de qual podem ser utilizados para restaurar o banco de dados em um determinado momento.O backup do log de transações é limitado aos Recovery Model Full e Bulk Logged,no Recovery Model Simple não é possível utilizar backups do log de transação.

Existem mais alguns tipos de backup no SQL Server como Copy-Only,Partial Backups,File e Filegroup Backup,Differential Partial Backup,neste artigo iremos conhecer apenas os citados acima.

Agora que já entendemos alguns tipos de backups do SQL Server vamos ver algumas estratégias de backup e possíveis opções de restore.Vamos criar nosso banco de dados de exemplo,criar algumas tabelas e povoa-las para criar nosso ambiente de teste.

–Criando o banco de dados de exemplo.
CREATE DATABASE Alimentos

–Mudando o contexto para o banco Alimentos.
USE Alimentos

–Criando a tabela Frutas.
CREATE TABLE dbo.Frutas
(
     CodFruta INT NOT NULL
    ,Nome VARCHAR(50) NOT NULL
    ,Quantidade SMALLINT NOT NULL
    ,Preco MONEY NOT NULL
)

–Inserindo alguns registros.
INSERT INTO dbo.Frutas VALUES(1,‘Laranja’,10,0.10)
INSERT INTO dbo.Frutas VALUES(2,‘Manga’,3,0.50)
INSERT INTO dbo.Frutas VALUES(3,‘Uva’,25,1)

–Visualizando os dados inseridos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- ——-
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                25         1,00

(3 row(s) affected)

Agora que criamos o nosso banco de dados e inserimos alguns registros vamos dar uma olhada em nossa primeira estratégia de backup de acordo com a imagem abaixo.

Em nossa imagem definimos nossa estratégia de backup como um Backup Full na Segunda Feira as 7 Horas da manha,um Backup Differential na Terça e Quarta Feira também as 7 Horas da manha,na Quinta Feira realizamos novamente o Backup Full e nos demais dias Sexta,Sábado e Domingo realizamos o backup differential.

Esta estratégia de backup estar apenas utilizando o Backup Full e o Backup Differential com certeza não é a maneira mais eficiente de proteger os dados.Digamos que a linha vermelha corresponda as atualizações a base de dados.

Agora imaginemos que ocorra um problema em nosso servidor exatamente as 15 Horas da Quarta Feira,com isso precisamos restaurar nossos backups para recuperar o banco de dados.Como eu disse acima que o Backup Full é a base para qualquer outro tipo de backup,este deve ser restaurado antes e logo após em nosso caso o ultimo differential (Quarta Feira as 7 Horas) porque ? Porque como expliquei na descrição do backup differential,este armazena todos as páginas de dados desde o ultimo backup full,com isso o backup differential de Terça Feira não é necessário já que o backup de Quarta Feira armazena os mesmos dados da terça feira mais as alterações de terça pra quarta até as 7 horas que é a hora do termino do backup.

Agora você deve estar se perguntando: Mais e os dados de 7Horas as 15 Horas perdemos ? Sim,a resposta é cruel,como não definimos em nossa estratégia de backup os backups do log de transação estamos vulneráveis a estes longos períodos de perda de dados,já que o intervalo de um backup differential para outro é cerca de 24 horas.

Agora vamos comprovar isso através dos exemplos nos scripts abaixo.

Para fazer o backup Full do nosso banco de dados utilizaremos o comando BACKUP DATABASE de qual especifica no nome do banco de dados e localização.Antes de efetuar o comando Backup Database iremos criar um Backup Device,ou seja,um dispositivo de backup,onde iremos definir o nome e a localização física do arquivo.A store procedure sp_addumpdevice pede como parametro o tipo de dispositivo como DISK para armazenar em disco ou TAPE para armazenar em uma fita,o nome do dispositivo criado que em nosso caso é DEV-BKP-FULL e localização física do arquivo a ser gerado que é C:BACKUPSQLSegundaFeira-Full.BAK,indicando que será armazendo o backup full da segunda feira conforme o exemplo em nossa imagem acima.

–Criando o Backup Device para o backup full.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-FULL’,‘C:BACKUPSQLSegundaFeira-Full.BAK’

Após criar o Device podemos agora fazer o Backup Full do nosso banco de dados,especificando o device criado acima,a opção INIT do comando Backup Database define que deve formatar o device,garantindo que apenas um backup será armazenado no device.

–Criando o Backup Full.
BACKUP DATABASE Alimentos TO [DEV-BKP-FULL] WITH INIT

Processed 160 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE successfully processed 161 pages in 0.192 seconds (6.869 MB/sec).

Em nosso Backup Full estar armazenado os três registros listados na query acima,vamos simular umas atualizações e inserções e vamos realizar os backups diferenciais para demonstrar o que é armazenado em cada tipo de backup,até o momento da falha.

–Inserindo alguns registros após o Backup Full.
INSERT INTO dbo.Frutas VALUES(4,‘Banana’,50,0.25)
UPDATE dbo.Frutas SET Quantidade = 17 WHERE CodFruta = 3

–Visualizando os dados alterados após o backup full
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25

(4 row(s) affected)

Verifique que essas alterações como o novo registro na tabela Frutas foram feitos após o backup full,com isso o backup diferencial irá armazenar estes valores.Agora vamos fazer o backup diferencial da Terça Feira simulando que essas alterações foram feitas no intervalo de Segunda Feira após o Backup Full e o backup differencial da quarta feira.

–Criando o backup differential da Terça Feira.
BACKUP DATABASE Alimentos TO [DEV-BKP-DIFF] WITH DIFFERENTIAL,INIT

Processed 40 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.132 seconds (2.540 MB/sec).

Agora iremos fazer mais algumas alterações simulando o intervalo de Terça Feira após o backup diferencial até o backup diferencial da Quarta Feira.

–Inserindo alguns registros após o backup diferencial da Terça Feira.
INSERT INTO dbo.Frutas VALUES(5,‘Maçã’,30,0.75)
INSERT INTO dbo.Frutas VALUES(6,‘Limão’,30,0.12)

–Criando o backup device para o backup differencial da Quarta Feira.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-DIFF-QuartaFeira’,‘C:BACKUPSQLQuartaFeira-Diff.BAK’

–Criando o backup Diferencial da Quarta Feira.
BACKUP DATABASE Alimentos TO [DEV-BKP-DIFF-QuartaFeira]

Processed 160 pages for database ‘Alimentos’, file ‘Alimentos’ on file 1.
Processed 1 pages for database ‘Alimentos’, file ‘Alimentos_log’ on file 1.
BACKUP DATABASE successfully processed 161 pages in 0.177 seconds (7.451 MB/sec).

–Visualizando os dados contidos no backup diferencial da Quarta Feira.
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12

(6 row(s) affected)

Lembrem que agora o Backup Diferencial da Quarta Feira contém todos os registros inseridos desde o Backup Full,com isso os registros inseridos após o backup full e antes e após o backup diferencial da Terça estão contidos no backup diferencial da Quarta Feira.Como mostrado na Query acima de qual lista todos os registros da tabela,com as devidas alterações após o Backup Full.

Em nossa simulação uma falha ocorreu na Quarta Feira as 15 Horas,vamos fazer algumas alterações simulando as atividades exercidas na base de dados e logo após iremos fazer o restore.

–Inserindo alguns valores após o Backup Diferencial da Quarta.
INSERT INTO dbo.Frutas VALUES(7,‘Pêssego’,15,1.78)
INSERT INTO dbo.Frutas VALUES(8,‘Pera’,23,2.43)

–Visualizando os dados contidos na tabela após o backup diferencial da Quarta.
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- ———————
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12
7           Pêssego                                            15         1,78
8           Pera                                               23         2,43

(8 row(s) affected)

Como expliquei previamente em nossa estratégia estamos mostrando as vantagens e desvantagens de trabalhar com Backup Full e Backup Differential somente,esta situação de falha poderia ser solucionada utilizando Backups do Log de Transação,de quais serão exemplificados mais adiante.Em nossa simulação o log de transação não foi preservado,necessitando reconstruir o banco de dados através dos backups existentes.

Vamos ao processo de Recover,o primeiro backup a ser restaurado é o Backup Full de qual é a base para os demais tipos de backup no SQL Server.Neste exemplo vou utilizar o comando Restore Database com a opção STANDBY,pois durante o processo de Restore o banco de dados fica inacessível,com isso não conseguimos visualizar os dados até termine o restore.Utilizando a opção STANDBY posso definir um arquivo onde armazenará as transações imcompletas,assim me concede o acesso somente leitura a base de dados.

–Restaurando o Backup Full.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-FULL] WITH REPLACE,NORECOVERY,STANDBY = ‘C:BACKUPtemp.standby’

–Visualizando os dados restaurados pelo Backup Full.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                25         1,00

(3 row(s) affected)

Notem que é retaurados os tres primeiros registros da tabela Frutas,de qual inserimos e fizemos nosso primeiro Backup Full.Vamos restaurar nossos backups diferenciais e acompanhar passa a passo seus arquivos restaurados.

–Restaurando o Backup Diferencial da Terça.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-DIFF] WITH STANDBY = ‘C:BACKUPtemp2.standby’

–Visualizando os dados restaurados pelo Backup Diferencial.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25

(4 row(s) affected)

Agora temos realmente os dados alterados de quais foram armazenados pelo Backup Diferencial,vamos restaurar o backup diferencial da Quarta Feira e visualizar os dados.

–Restaurando o Backup Diferencial da Quarta.
USE MASTER
RESTORE DATABASE Alimentos FROM [DEV-BKP-DIFF-QuartaFeira] WITH STANDBY = ‘C:BACKUPtemp3.standby’

–Visualizando os dados restaurados pelo Backup Diferencial da Quarta Feira.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12

(6 row(s) affected)

Podemos verificar que todos os registros foram modificados desde o ultimo Backup Full,esta é funcionalidade do Backup Diferencial.Voce deve estar se perguntando: “Então quer dizer que se eu restaura-se o Backup Full + meu Ultimo Backup Differential iria dar o mesmo resultado ?” Sim.O backup differential armazena todas as informações alteradas desde o ultimo Backup Full,restauramos desta maneira para demostrar cada parte do processo com a intenção do amigo leitor entender a necessidade.Notem também que neste momento acabou nossos backups e ainda falta registros.Os Registros inseridos após o backup diferencial da quarta feira(Codigo 7 e 8) de qual simulavam as atividades na base foram perdidos,essa é a desvantagem de usar o Backup Full + o Backup Differential,sem utilizar o Backup do Log de transações.

Com o Backup do log de transações podemos armazenar as modificações da base de dados em um intervalo de tempo menor,reduzindo a perda de dados.Em nosso exemplo a falha simulada,nao teria preservado o log de transação.Mas se fosse o caso onde o log estaria preservado,poderíamos fazer um backup do tail do log armazenando a porção ativa de qual continha os registros 7 e 8 e restaura-lo junto com os backups full e diferencial,vou mostrar neste exemplo,como realizar o backup do tail do log em situações onde o data file esteja comprometido,de qual pode ser útil para evitar a perda de dados.

Antes de começar o processo de Restore,poderiamos emitir o comando Backup Log especificando a opção,NO_TRUNCATE essa opção diz ao SQL Server que ele não irá verificar no Data File os registros de quais constam no Log e irá trunca-los,desta forma estamos “backupeando” o Tail do Log ou seja as informações ainda ativas no log.

Segue abaixo o script para a criação do Device para o backup do log e a criação do Backup do log,relembrem que para resolver nosso problema esse procedimento deveria ser feito antes de começar o processo de Restore com o Backup Full e os demais backups.

–Criando o Backup Device para o Backup do Log.
EXEC sp_addumpdevice ‘DISK’,‘DEV-BKP-LOG’,‘C:BACKUPSQLBKP-LOG.TRN’

–Criando o backup do Tail do Log,colocando o banco de dados em estado de Recovering.
USE MASTER
BACKUP LOG Alimentos TO [DEV-BKP-LOG] WITH NORECOVERY,NO_TRUNCATE

Depois que criamos nosso Backup do Tail do Log,refazemos todo o processo de Restore mostrado acima e por ultimo restauramos o Backup do Log de qual irá recuperar nossos registros 7 e 8 como segue.

–Restaurando o Tail do Log,terminando o processo de restore deixando o banco de dados online.
USE MASTER
RESTORE LOG Alimentos FROM [DEV-BKP-LOG] WITH RECOVERY

–Visualizando os dados finais da tabela.
USE Alimentos
SELECT CodFruta,Nome,Quantidade,Preco
FROM dbo.Frutas

CodFruta    Nome                                               Quantidade Preco
———– ————————————————– ———- —–
1           Laranja                                            10         0,10
2           Manga                                              3          0,50
3           Uva                                                17         1,00
4           Banana                                             50         0,25
5           Maçã                                               30         0,75
6           Limão                                              30         0,12
7           Pêssego                                            15         1,78
8           Pera                                               23         2,43

(8 row(s) affected)

Podemos observar que os nossos dados foram recuperados com sucesso e nosso banco de dados está operacional.Neste artigo mostrei algumas vantagens e desvantagens do backup Full,differential e Transaction log.Bem como algumas estratégias de backup e restore e disaster recover.

Espero que seja útil para comunidade,até o próximo.