Por que é tão importante fazer backup do seu log de transações?

Question

Atualmente, estamos implementando uma solução de backup para um cliente e sua solução de ERP usa o SQL Server.

A solução de ERP foi criada por uma empresa diferente. E eles estão me dizendo que é super importante fazer backup e truncar o log de transações.

Eu tenho lido um pouco sobre esse log de transações e não entendo porque isso é tão importante quando eu já estou fazendo o backup da máquina inteira (Estamos usando o ArcServe UDP, que está ciente do SQL Server e usa o VSS). Entendo que as tarefas de limpeza na VM do SQL Server já estão cuidando do truncamento do log, no entanto, o UDP também permite o truncamento do log do SQL Server.

Entendo que o log de transações pode ser usado para restaurar bancos de dados corrompidos, porque, bem, é um log de todas as transações. Mas eu já tenho um backup de hora em hora do banco de dados inteiro, então, por que eu me importaria?

    

Answer 1

Você só precisa fazer isso se o modo de recuperação do banco de dados estiver definido como "cheio". Se estiver definido como "simples", você não precisará fazer um backup do log de transações. Mas cuidado com a diferença entre essas duas opções!

Primeiro de tudo: Se você quiser restaurar o banco de dados a um ponto de tempo específico , é necessário usar o modo "completo". (Eu acho que você pode ajustar o tempo tão preciso que você pode até especificar os milissegundos para o ponto de restauração) No modo "simples" você só pode voltar para o último backup completo .

Se você não fizer backup / truncar seu log de transações, ele aumentará o tempo todo (no modo completo). Vi bancos de dados em que o arquivo .trn era duas vezes maior que o próprio banco de dados. Isso depende de quantas vezes foram feitas alterações no banco de dados.

Outro ponto é que um backup de log normalmente é mais rápido que um backup completo.

Então, acho que seu plano de backup para fazer um backup completo a cada hora não é o ideal. Mas isso depende da sua situação:

Se você disser: Ok, se eu puder restaurar o DB para a última hora completa, tudo ficará bem. - > Você também pode pensar em definir o modo de recuperação como "simples" se quiser manter o backup completo a cada hora.

Na minha opinião, uma idéia melhor seria fazer um backup completo no início da manhã e fazer um backup do log de transações a cada hora. Deve ser muito mais rápido e você pode restaurar a qualquer momento que desejar. E também o seu arquivo .trn não vai crescer muito ...

Espero que isso ajude.

    

Answer 2

Bem. Você se preocupa porque, se tiver seu modelo de recuperação configurado como completo e não fizer backup do Log de transações usando o backup do SQL (e não o backup do servidor), o log de transações continuará a crescer até consumir todo o espaço em disco disponível. (Uma vez vi um colega menor instalar o SQL Server na unidade do sistema e nunca fazer backup do log de transações. ele era o Windows .

Sim, também será restaurado para um ponto específico no tempo. Até o minuto. Como Twinkles diz, sim, pessoas derrubando mesas e coisas do tipo.

Não sei o que você está usando para o backup por hora de todo o banco de dados e se é o mesmo produto que você usa para a máquina inteira. Em caso afirmativo, uma solução de backup não compatível com SQL não é suportada para restaurações. A quantidade de tempo que o VSS leva para copiar os arquivos MDF e LDF pode causar uma incompatibilidade de timestamp interno, por exemplo.

    

Answer 3

Também gerenciamos vários sistemas de ERP. E o problema é que muitas vezes, à noite, há trabalhos em lote de longa duração que sincronizam dados com outros sistemas. E eles tomam às vezes uma hora ou mais. Então, o que você quer fazer no caso de uma falha é pular para um ponto em que você tenha dados consistentes. (O que significa direito entre dois trabalhos em lote.) Se você olhar apenas para a hora, talvez nem sempre saiba exatamente qual era o status da base de dados no momento.

Mas é claro que depende da situação. Se você não tem nenhum trabalho automatizado, etc., pode ficar totalmente bem com um backup de hora em hora.

    

Answer 4

Existem vários motivos pelos quais você deseja fazer isso:

1. Um sistema de banco de dados geralmente está ocupado, talvez fazendo milhares de transações por segundo. Os dados podem ser espalhados por vários arquivos em diferentes sistemas de arquivos. Não é trivial certificar-se de que o banco de dados esteja em um estado consistente (a.k.a utilizável) após a restauração. Se a sua solução de backup estiver à altura da tarefa, ótimo, mas é melhor ter certeza disso antes de apostar seu trabalho nela.
2. Um exemplo: alguém derruba uma tabela com dados importantes por engano. Se você tiver um backup de banco de dados com capacidade de recuperação point-in-time, poderá restaurar os dados rapidamente, sem precisar restaurar o sistema inteiro.
3. Se o banco de dados estiver no modo de recuperação completa, o log de transações do SQL Server aumentará. O espaço de armazenamento no log de transações é reutilizado apenas se o log de transações tiver sido submetido a backup. Se você não fizer backup do log de transações regularmente, o sistema de arquivos ficará cheio até que não haja mais espaço. Nesse ponto, tudo chegará a uma parada imediata , já que nenhuma nova transação pode ser iniciada.

Answer 5

Quando o seu banco de dados cresce além do que você pode fazer backup em uma hora, você precisa de um modelo diferente.

Um backup completo do seu banco de dados truncará seus logs, mas ele precisa estar "ciente do SQL", porque nesse cenário, é o software de backup que informa ao servidor SQL o que ele fez backup e o que truncar.

Como outros mencionam, se você tiver um banco de dados no modelo de recuperação "Completo", o log de transações crescerá indefinidamente, até que você faça um backup com reconhecimento de SQL completo.

Recuperação é realmente o problema aqui, não o backup. E não é uma decisão técnica, é uma decisão comercial !

Se os donos de sua empresa concordarem em perder uma hora ou mais de suas transações no banco de dados (o que pode ser MUITO difícil ou impossível de refazer!), seu modelo funciona. Se eles estiverem bem com o sistema desligado por horas enquanto você restaura o banco de dados inteiro a partir do backup, seu modelo funciona.

No entanto, se a sua empresa considera o sistema ERP um ativo crítico para a sua operação (não é tudo?), definir um tempo de recuperação máximo aceitável (também conhecido como RTO, Recovery Time Objective) para seus serviços críticos será decisão comercial.

Além disso, os proprietários de empresas ou partes interessadas do sistema precisam definir a quantidade de dados que estão dispostos a arriscar perder em um incidente, também conhecido como RPO (Recovery Point Objective, objetivo de ponto de recuperação).

A resposta, se você perguntar a eles, pode ser: "Nenhum dado pode ser perdido! O sistema ERP deve estar disponível 24 horas por dia, 7 dias por semana!" ... o que todos sabemos é altamente improvável que seja econômico. Se você apresentá-los com o custo associado à construção de um sistema não redundante totalmente redundante, eles aparecerão com números mais razoáveis.;)

O ponto é, se você pode evitar a perda de qualquer transação, você está economizando sua empresa potencialmente centenas ou milhares de horas perdidas de trabalho. Isso equivale a uma enorme economia em qualquer empresa e cresce com o tamanho da sua empresa ...

    

Answer 6

Todos tiveram ótimas respostas a isso, mas gostaríamos de adicionar outra nota importante ... ou duas.

Conhecer os detalhes dos modelos de recuperação do SQL Server e seus requisitos de negócios para perda de dados são muito importantes; no entanto, neste caso, é imperativo que você entenda como o seu produto de backup funciona com o SQL Server. (Com base nos comentários acima, parece que você está fazendo backup de volumes de disco por meio da cópia do VSS, o que significa que os backups do SQL Server podem ou não ser necessários além disso.)

Tendo avaliado recentemente um produto similar, alguns dos pontos importantes que você deve perguntar são:

• Como as restaurações são executadas até um determinado momento para que um banco de dados seja totalmente recuperado?
• Como o backup inicial é tratado para um novo banco de dados em recuperação total?
• O produto de backup requer backups de log do SQL Server para restaurar em um determinado momento? (No meu caso, a resposta foi sim.)
• Sua infraestrutura de armazenamento pode manipular o volume de dados para cópias / diferenciais do VSS (em um determinado intervalo) além da carga normal de SQL?

Espero que isso seja útil.

A experiência que minha equipe teve com nossa recente avaliação forneceu algumas respostas muito interessantes para as perguntas acima. Uma coisa é certa: os backups são mais complexos para nós com um produto de backup do VSS.

    

Answer 7

Como muitos outros já disseram, se você estiver usando uma ferramenta de terceiros para fazer backup / instantâneo da VM ou do armazenamento, ainda corre o risco de não ter um backup válido. Todas as ferramentas de terceiros que gerenciam os backups do SQL Server implementarão e se conectarão ao SQL Server usando o VSS. Ele faz isso para solicitar que o SQL Server desligue toda a E / S dos arquivos de dados para que uma captura instantânea consistente possa ser executada. Se não, então você pode ter muitas transações em vários estados e uma restauração não saberá se essas transações podem ser roladas para frente ou para trás.

Eu não trabalhei com todas as ferramentas de snapshot VM / Storage de terceiros, mas as que eu trabalhei nunca conseguiram armazenar snapshots em que os bancos de dados do sistema estavam localizados - o SQL Server não pode quiesce esses bancos de dados. Eles TODOS fizeram backup desses bancos de dados de maneira fluida - isto é, ... emitindo os comandos BACKUP DATABASE e, em seguida, tirando o próprio arquivo de backup.

Além de tudo isso, como muitos já disseram, se você estiver no modelo de recuperação COMPLETO e não emitir instruções de BACKUP LOG regularmente, o log de transações continuará a crescer até que não haja mais espaço no disco.

A verdadeira pergunta que você precisa fazer, e eu poderia ter perdido acima ... você restaurou com sucesso esses backups várias vezes e está satisfeito com a consistência dos dados nessas restaurações. Pessoalmente, mesmo isso não seria o suficiente para mim, ainda parece uma rolagem de dados, e isso é algo que um bom DBA nunca faz quando se trata de backup e recuperação.

    

Answer 8

Reconheça que os logs de transação não são simplesmente um mecanismo de recuperação. A manutenção adequada do registro também pode desempenhar um papel crítico no desempenho geral do banco de dados (ou seja, taxa de transferência da transação).

Fazer cópias de segurança frequentes dos seus ficheiros de registo faz algumas coisas:

1. Reduz a contagem de VLF nos arquivos de log físicos, o que é bom para desempenho.
2. Você está mais bem preparado para usar os backups de log no caso de precisa recuperar um banco de dados.
3. É bem mais rápido que um backup completo

Se você conseguir fazer um backup completo por hora, não sei ao certo quanto você se beneficiaria com backups de log mais frequentes. Afinal, pelo que entendi, um backup completo também fará backup do log, tanto quanto for necessário, a fim de garantir uma restauração completa.

Por outro lado, se seu aplicativo gera toneladas de transações entre seus backups completos por hora, isso pode explicar por que os desenvolvedores originais sugeriram manutenção de log mais granular. Muitas transações podem aumentar a contagem de VLF em seus registros, o que pode acarretar uma penalidade de desempenho até que o log seja truncado. Eu vi isso expresso como um erro 'tempo limite de consulta expirado' dentro de um aplicativo (pouco antes de ser interrompido).

Recomendações relacionadas à manutenção do log de transações são descritas muito bem neste artigo 8 etapas para melhorar o throughput do log de transações . Além disso, este artigo Principais dicas para uma manutenção eficiente do banco de dados menciona uma contagem um tanto arbitrária de VLF para apontar (< 200) o que funcionou muito bem para mim.

    

Answer 9

Outras pessoas já deram a maioria das razões para um backup do translog etc. Parece haver alguma dúvida sobre por que isso é uma boa estratégia quando você já faz backup do servidor.

Algumas boas razões surgiram para mim que não estão acima. E se o aplicativo de terceiros não conseguir fazer um backup que você possa restaurar? Você já tentou restaurar seu backup? Que tal um novo servidor que você acabou de construir a partir de seus modelos (pense em DR)? E quanto a outro servidor no seu domínio que tenha um agrupamento diferente? ou instância do SQL?

Eu faço backups redundantes sem nenhum motivo além de, às vezes, seu aplicativo de terceiros não ser o modo mais rápido de restauração. Às vezes, o armazenamento que seu aplicativo de terceiros está salvando também é afetado ou está corrompido por seus próprios motivos.