Modelos de recuperação de Backup de logs de transação no SQL Server

Neste artigo abordaremos os modelos de recuperação de bancos de dados e como eles definem diferentes formas de realização das tarefas de backup e restauração no banco de dados.

Ao escolhermos um modelo de recuperação, precisamos primeiramente identificar suas necessidades de negócios, ou seja, registrarmos o consumo de espaço, opções de restauração para o banco de dados, a simplicidade referente ao planejamento de recuperação de desastres, dentre outros pontos importantes. Em geral, os registros de manutenção e backup são conceitos-chave que diferenciam estes modelos.

Trabalhando no modo simples

No modelo de recuperação simples, o único objetivo do log de transações é garantir as propriedades ACID de transações para reforçar a consistência do banco de dados e sua durabilidade durante as operações de recuperação de banco de dados. Neste modelo temos o impasse de que o log de transações não pode ser copiado e utilizado para as restaurações de banco de dados nem para o envio de log. Aqui o que ocorre com o SQL Server é que ele executa automaticamente a transação de truncamento do log quando um checkpoint de transações é alcançado.

Todas as transações realizadas neste processo ainda são registradas, embora certas operações em massa são minimamente conectadas, o nível aplicado de registros aqui é muito semelhante ao aplicado no modo BULK_LOGGED, onde a parte ativa do log é mantida como normal, de modo que sempre que um banco de dados em modo de reinicialização simples, o processo de recuperação será processado e os arquivos de dados serão conciliados com o conteúdo do log de transações. No entanto, neste modelo todos os arquivos de log virtuais (nossos VLF’s) são marcados como inativos (podem ser recuperados), pois estes são automaticamente truncados durante os checkpoints de banco de dados regulares. Isto significa que qualquer VLF onde o LSN mais alto nesse ficheiro seja menor do que o MinLSN que será truncado, quando ocorrer um checkpoint. Como resultado, o espaço no log de transações é regularmente e rotineiramente reutilizado.

Isto se dá devido a necessidade de recuperarmos o espaço de log e, assim, mantermos o consumo de espaço de log no mínimo possível. Quando todas as transações forem concluídas e os dados forem gravados no banco de dados SQL, o log será truncado e o espaço utilizado por esta transação poderá ser reutilizado por novas transações. Como neste caso não haverá nenhuma informação no log de transações, podemos restaurar o banco de dados para um ponto específico na linha de tempo e restaurá-lo, por exemplo, para um momento em que você tomou um backup completo ou diferencial, dessa forma, as alterações realizadas desde o último backup completo ou o diferencial não poderão ser restaurados.

Precisamos utilizar os modelos simples para restauração quando:

Qual a frequência de ocorrência de checkpoints?

Sabemos que são realizados checkpoints durante os processos existentes no banco de dados, mas com qual frequência isso pode acontecer? Bem, o próprio core do SQL Server que toma a decisão com relação a frequência com a qual será executado um checkpoint, isto, baseado na quantidade de registros de log que serão necessários para a realização dos processamentos necessários para a recuperação de um banco de dados no tempo especificado pela opção de configuração de servidor. No caso de nosso banco de dados ser apenas para leitura, principalmente, o tempo entre os pontos de verificação podem ser longo. No entanto, em sistemas em produção onde os dados são atualizados constantemente, os checkpoints podem ocorrer a cada minuto.

Quando estamos em modo de recuperação total do log de transações, este mantém uma "história de transações inativas/fechadas", juntamente com os abertos/ativos. Esta "história" pode ser capturada em um backup do log e ser usada para a restauração do banco de dados para um ponto anterior no tempo, antes de uma quebra. No entanto, no modo SIMPLE (ou simples), estas histórias não existem e devido a isso, o registro não pode ser usado para restauração do banco de dados. Na Listagem 1 temos um exemplo prático sobre essa restrição da qual estamos tratando para melhorar o entendimento sobre esse modelo.

Listagem 1. Tentativa de backup em modo Simple.

USE master; ALTER DATABASE TestDB SET RECOVERY SIMPLE; BACKUP Log TestDB TO DISK ='C:\Backups\TestDB_log.bak' GO

Quando executamos este script, o resultado que nos é retornado é um erro de acordo com a Figura 1, pois como já explicado, este tipo de recuperação ocorre apenas nos outros modelos.

Figura 1. Resultado referente ao script da Listagem 1.

Trabalhando no modo FULL (completo)

No modelo de recuperação completa as operações dentro do log de transações são mantidos, a menos que o backup do log de transação ou o truncamento seja feito. Aqui, podemos realizar backups completos ou diferenciais juntamente com backups do log de transações (diferenciando do modo simples). Isso pode ocorrer, desde que possamos manter o controle de todas as transações que ocorram no banco de dados. Assim, podemos fazer pontos no tempo de recuperação. No caso de um erro ser relatado e a transação não puder ser concluída, podemos reverter o banco de dados para um ponto antes desta operação e deixá-lo funcionando novamente. Normalmente, um banco de dados será definido com o modelo de recuperação FULL quando os dados que o consistem forem críticos ou mesmo quando estivermos usando o banco de dados de espelhamento. A fim de definirmos o banco de dados para "recuperação completa" usando o SQL Server Management Studio, devemos seguir alguns passos, os quais seguem abaixo:

  1. Primeiramente, clique com o botão direito do mouse sobre o banco de dados que estiver em uso e, em seguida, clique em "Propriedades";
  2. Busque pela opção 'Opções' e clique nela;
  3. Escolha a opção "Full" para o modelo de recuperação e clique em "OK" para que as alterações sejam salvas.

No modo de recuperação completa, apenas um backup do log pode causar truncamento, como tal, o log de transações realizará um registro completo das operações realizadas desde a última vez que o log de transações realizou um backup. Uma vez que todas as operações forem totalmente conectadas, o arquivo de log poderá crescer, muito rapidamente, em sistemas encontrados em produção. Portanto, quando trabalhamos em modo de recuperação completa, é de vital importância que façamos backups do log de transações regularmente, além de backups completos e, opcionalmente, backups diferenciais. O truncamento do log ocorre assim que o backup do log é feito, assumindo que um checkpoint ocorreu desde o último backup e que haja outros fatores que estão atrasando o truncamento, como um backup de dados ou operação de restauração.

Não é possível realizarmos um backup do log de transações sem que ao menos o primeiro backup tenha sido realizado. Na verdade, se tivermos um banco de dados que esteja em modo de recuperação completa, mas que nunca foi feito um backup, então ele realmente não trabalhará em modo de recuperação completa. O banco de dados estará ainda no modo de auto truncado até que o primeiro backup completo seja executado.

Todos os backups de banco de dados, completo, log ou de outra forma, são realizados utilizando o comando BACKUP. O comando para executar um backup completo para o disco é apresentado de acordo com a Listagem 2.

Listagem 2. Script para criação de backups.

BACKUP DATABASE DatabaseNameTO DISK ='FileLocation\DatabaseName.bak';

Se este fosse o primeiro backup a ser executado, o arquivo DatabaseName.bak seria criado no diretório especificado. Se esse arquivo já existir, então o comportamento padrão é acrescentar backups subsequentes a este arquivo. Para substituirmos este comportamento e estipularmos que qualquer arquivo existente deve ser sobrescrito, podemos usar a opção INIT, da mesma forma como é apresentada pela Listagem 3. Mas o que normalmente acontece é que cada backup subsequente recebe um nome único.

Listagem 3. Script para substituição de backups.

BACKUP DATABASE DatabaseNameTO DISK ='FileLocation\DatabaseName.bak'WITH INIT;

É evidente que o backup de dados e arquivos de log não devem ser armazenados na mesma unidade que estiver hospedando os arquivos correntes. Devido ao fato de que se essa unidade sofrer uma falha de hardware, então todas as suas cópias serão perdidas junto com os arquivos em execução, os backups não terão nenhuma valia. Os arquivos devem ser copiados em dispositivos separados, ou mesmo, o backup ser realizado numa unidade local, espelhado.

Com que frequência devemos criar backups no modo FULL?

O que seria mesmo o esquema de backup, se não um compromisso entre o idealizado e a prática, entre a avaliação do verdadeiro risco de perda de dados, e qual será o custo da empresa e os custos envolvidos na mitigação desse risco? A frequência de criação de backups do log pode ser ditada pelo número de transações em que o banco de dados está sujeito. Para bancos de dados muito requisitados, pode ser necessário fazer o backup com maior frequência, a fim de controlar o tamanho do log.

Não há, no entanto, uma maneira simples de calcularmos quantas vezes serão feitos os backups de log. A maioria dos DBAs tem sua melhor estimativa em como frequentemente os logs de backups devem ser tomados, a partir disso, observam as características de crescimento dos arquivos e, em seguida, ajustam o esquema de backup que for necessário para evitar que eles fiquem com grandes dimensões.

Cadeia de logs

Não é possível realizar um backup do log de transações sem primeiro termos, pelo menos, um backup completo. Para recuperarmos um banco de dados para um determinado ponto no tempo, ou ao fim de um backup de log específico ou mesmo para um ponto no tempo dentro de um backup do log em particular, deve existir uma cadeia ininterrupta cheia de registros de log, certo? Isso acontece a partir do primeiro backup de log realizado após um backup completo (ou um backup diferencial), até o ponto de falha. Esta é conhecida como cadeia de log.

Há diversas maneiras de se quebrar uma cadeia de logs, e se o fizermos isso significa que só seremos capazes de recuperar o banco de dados para o tempo do backup de log antes do acontecimento que quebrou esta cadeia. Resumidamente, quebrar uma cadeia de logs não é uma boa ideia, caso estejamos preocupados com a capacidade de restauração de nossos dados. Estaremos apresentando duas das formas mais comuns para quebrar uma cadeia, as quais são as seguintes:

Em versões anteriores ao SQL Server 2008, havia um par de comandos, ou seja, BACKUP LOG com o no_log ou o BACKUP LOG WITH TRUNCATE_ONLY, que são equivalentes funcionalmente ao que temos hoje, que, quando emitidos, forçariam um truncamento do arquivo de log e assim quebrariam a cadeia de logs. De qualquer forma, não devemos emitir esses comandos em nenhuma versão do SQL Server, mas estamos mencionando aqui apenas a título de conhecimento, pois ao tentarmos lidar com um "arquivo de log ", sem a devida compreensão as implicações que temos para a sua capacidade de restauração do nosso banco de dados não serão alcançadas.

Trabalhando no modo BULK_LOGGED

Este título, de certa forma, pode ser um pouco enganador, uma vez que não seria geralmente um gerenciamento de registro, de qualquer forma a longo prazo, quando operarmos um banco de dados no modelo de recuperação BULK_LOGGED. No entanto, um DBA pode considerar a mudança de um banco de dados para o modelo de recuperação BULK_LOGGED durante um curto espaço de tempo, por exemplo, operações de cargas em massa. Quando um banco de dados está operando no modelo BULK_LOGGED estas, e algumas outras operações, tais como índice de recriação, podem ser minimamente registradas e, portanto, irão usar menos espaço no log. Ao reconstruirmos o índice agrupado para tabelas muito grandes, ou quando carregarmos milhões de linhas de dados, a redução no uso de espaço em log quando operando no modo de recuperação BULK_LOGGED, em comparação com o modelo de recuperação completa, pode ser muito mais elevado.

Mesmo assim, devemos usar a recuperação BULK_LOGGED apenas com pleno conhecimento das implicações que tem para banco de dados de restauração e recuperação. Por exemplo, não é possível restaurarmos a um ponto específico no tempo dentro de um backup de log que contenham registros de log relativos a operações minimamente registradas. Além disso, há um caso em especial no qual um backup do log de “tail” falhará, onde as operações minimamente registradas, que foram gravadas durante um processo do banco de dados operando em modelo de recuperação BULK_LOGGED, existam na parte ativa do log de transações e um arquivo de dados tornar-se indisponível, como resultado de uma falha grave.

Em caso de estarmos sem sorte e algo do tipo ocorrer, então qualquer uma destas limitações pode levar a perda de dados importantes. É bom verificarmos o Acordo de Nível de Serviço (SLA) para o banco de dados em questão, para níveis aceitáveis de perda de dados; se ele expressar que a tolerância é zero, então é altamente improvável que o uso do modelo BULK_LOGGED, mesmo por curtos períodos, será aceitável. Por outro lado, é claro, se essa base de dados estiverem sujeita a índices regulares de reconstrução ou de cargas, em seguida, os proprietários do banco de dados devam compreender as implicações para a alocação de espaço de log desse banco de dados, de realizar estas operações sob o modelo de recuperação completa.

Dito tudo isto, para muitos bancos de dados, a capacidade de alternar para a recuperação BULK_LOGGED para que o SQL Server minimizará determinadas operações de log, o que o torna uma arma muito útil na batalha contra o crescimento excessivo do log. Na maioria dos casos, o SLA vai permitir uma margem de manobra suficiente para fazer o seu uso aceitável e, com planejamento e procedimentos de cuidado, os riscos serão mínimos.

Com isso finalizamos este artigo, onde demos uma noção geral com relação aos modelos de recuperação de banco de dados, o que não quer dizer que é suficiente para uma compreensão prática, mas de forma teórica, já demos um grande avanço por aqui. Trataremos com relação aos modelos de recuperação FULL e BUL_LOGGED na prática nos próximos artigos. No entanto, mostramos como podemos criar arquivos de backup e mostramos de forma simplificada o que acontece quando tentamos realizar recuperações de banco de dados em modo simples.

Links

Checkpoints
http://msdn.microsoft.com/en-gb/library/ms189573.aspx

Truncamento de VLF’s recuperáveis
http://msdn.microsoft.com/en-gb/library/ms345414.aspx

Comando BACKUP
http://msdn.microsoft.com/en-us/library/ms186865.aspx

Artigos relacionados