P+R SQL: Recuperação de desastres e espelhamento de banco de dados

  • Artigo

Backups, recuperação de desastres e espelhamento de banco de dados têm variações intermináveis, todas adequadas para cenários ilimitados.

Paul S. Randal

Solução temporária

**P.**Eu li um monte de conselhos conflitantes sobre quantos arquivos de dados em meu servidor deve configurar para tempdb reduzir a contenção de PAGELATCH. Você pode lançar alguma luz sobre isso?

**R.**Você está correto — há muito mau aconselhamento por aí sobre configuração de tempdb. Contenção de PAGELATCH em tempdb vem de cargas de trabalho onde muitas conexões simultâneas criar e soltar tabelas pequenas. Estas operações requerem alocar e desalocar páginas de arquivo de dados no tempdb. Este, por sua vez, requer acesso exclusivo para páginas de arquivo de dados na memória alocação bitmap (páginas especiais que tome nota do que arquivo de dados de páginas estão em uso ou não).

Se houver muitas conexões simultâneas, tentando simultaneamente alocar e desalocar, apenas uma conexão pode ter acesso para um bitmap de alocação de uma vez. Isto leva a uma redução no desempenho e contenção.

Uma maneira de aliviar um pouco essa contenção é habilitar o sinalizador de rastreamento 1118 (você pode saber mais sobre este no meu blog SQLskills.com). Uma forma mais eficaz é criar vários tempdb arquivos de dados. Criando vários arquivos de dados, o SQL Server executará alocações (e desalocações) rodízio os arquivos de dados. Desta forma, o número de bitmaps de alocação aumenta (um ou mais por arquivo de dados) e a contenção de sistema global diminuirá.

A pergunta é: Quantos arquivos de dados, você deve criar? Durante muito tempo, o melhor Conselho que o povo poderia dar era que a posição oficial da Microsoft de criação de dados de tempdb de um arquivo para cada núcleo de processador lógico (por exemplo, dois processadores com quatro núcleos cada e hyper-threading habilitado equivale a oito núcleos lógicos) estava incorreto. Essa abordagem pode levar a slow-downs com vazamentos de memória em servidores com mais de oito núcleos. Outro generalizada a crença era que começando com um quarto a metade o número de núcleos de processador foi um bom começo.

Em seguida, na Conferência de cúpula de SQL PASS no final de 2011, Bob Ward do suporte ao produto Microsoft apresentou uma fórmula mais elegante para determinar o número de arquivos que você deve criar. Se o servidor tiver menos de oito núcleos lógicos, use o número de núcleos lógicos como o número de arquivos de dados tempdb. Se o servidor tiver mais de oito núcleos lógicos, iniciar com oito arquivos de dados tempdb e adicione quatro mais de uma vez se disputa continua.

Manter em mente este é um Conselho generalizado. Há pelo menos três ocasiões onde com 64 núcleos precisa 128 arquivos de dados tempdb — duas vezes o número de núcleos — para aliviar a contenção. Sua milhagem real definitivamente irá variar.

O plano perfeito

**P.**Recentemente revi nossos planos de recuperação de desastres e encontrei que não estamos fazendo backups regulares dos nossos bancos de dados do sistema. Você aconselha a fazer isso? O que é o pior que poderia acontecer se não fizermos isso?

**R.**É uma boa idéia rever periodicamente os seus planos de recuperação de desastres. É ainda melhor para a prática desses planos. Uma das coisas que você teria descoberto se você percorreu uma restauração bare-metal de prática é que seu ambiente de SQL Server não volta a funcionalidade completa, porque você estaria perdendo os bancos de dados do sistema.

Muitos DBAs não consideram os bancos de dados do sistema (master, model, msdb e qualquer banco de dados de distribuição de replicação) em planejamento ou testar um procedimento de recuperação de desastres. Este é um grande erro. Esses bancos de dados são essenciais para as instâncias do SQL Server. Você precisa proteger estas e verificar sua integridade, tanto como você faz com seus bancos de dados do usuário.

Não há nenhum ponto em ter seus dados disponíveis se você não pode se conectar a uma instância do SQL Server.

O mesmo é verdadeiro se você não pode trazer a instância em um estado de trabalho, quando o mestre está ausente, porque você não tem todas as informações de login necessário. Sem um backup do master, você procura recriar todas as informações de login para todos os bancos de dados antes que os aplicativos podem ficar online.

É fundamental para fazer backup de banco de dados msdb, porque contém todos os seus trabalhos do SQL Agent (como backups e verificações de consistência), alertas do SQL Agent (como erros de alta gravidade e precoces que o subsistema de e/S está errado), os pacotes SSIS e suas tabelas de histórico de backup. Se você tem qualquer tipo de sistema automatizado que gera um conjunto de restauração instruções para facilitar a fácil banco de dados de recuperação de desastres, provavelmente está usando as tabelas de histórico de backup no msdb para fazê-lo. Sem uma cópia do msdb (se o desastre tirou seu todo subsistema de e/S), você precisa reunir as instruções RESTORE manualmente, que é um trabalho tedioso que adiciona ao tempo de inatividade.

O banco de dados de modelo é fundamental se você veio acima com uma configuração que você deseja replicar para todos os novos bancos de dados. Por exemplo, se você tem um ambiente onde cada cliente hospedado tem seu próprio banco de dados, você precisa do modelo. Sem ele, você teria que definir as opções de configuração novamente.

Bancos de dados de distribuição de replicação são essenciais para restabelecer os fluxos de dados de replicação sem ter que realizar a demorada re-initializations de bancos de dados de assinatura. Em geral, você não tem uma estratégia de recuperação de desastre, a menos que você estiver fazendo backup de seus bancos de dados do sistema, bem como seus bancos de dados do usuário.

Para começar, confira estes SQL Server Books Online sobre backup de banco de dados do sistema e restauração:

Crescendo, crescendo, crescendo

**P.**Estamos conseguindo compreender um problema onde o log de transação continua crescendo, mesmo que nós manualmente a encolher. Nós estamos cometendo o trabalho em nossas transações internas e backups de log de desempenho, então por que o log continuar crescendo?

**R.**O problema aqui parece ser que seus desenvolvedores estão usando transações aninhadas no código, sem perceber que eles não se comportam da maneira que eles olham. Um fluxo de código de exemplo que ilustra o que você está fazendo é:

BEGIN TRAN; Do some work … BEGIN TRAN; Do some more work … COMMIT TRAN Continue with more work …

O segundo BEGIN TRAN, que inicia uma transação aninhada, realmente não começa um sub-transaction tanto quanto o mecanismo de armazenamento. Tudo o que ele faz é incremento @ @ TRANCOUNT de 1. Não há nada escrito para o log de transação indicando que iniciou uma nova transação. Todo o trabalho feito pela transação aninhada é realmente parte da transação inicial.

Isso significa que quando o COMMIT TRAN é emitido para a transação aninhada, nada acontece, exceto decrementar @ @ TRANCOUNT, porque lá não é realmente uma transação aninhada. Nada está comprometido até a transação inicial compromete-se, trazendo @ @ TRANCOUNT para trás para baixo a zero. Eis porque o log de transações está crescendo. Você ainda tem uma transação única de longa duração.

Também, você não deve realizar operações de redução de log de transações regulares. Sempre que o log de transações tem que crescer, a parte nova do log deve ser inicializado em zero. Ele será substituído com zeros, que anteriormente era parte do volume NTFS. Isso acontece para que qualquer operação de recuperação de falha subseqüente não falha (ver meu blog SQLskills.com para obter uma explicação).

Enquanto a nova parte do log de transações está sendo inicializado em zero, toda atividade de log desse banco de dados está em pausa. Sua carga de trabalho pára momentaneamente. Esta pausa pode ser bastante longa, se você tiver definido a quantidade de aumento automático de log de transação para ser muito grande.

É sempre melhor evitar o log de transação tendo auto-crescer se possível. Se o log de transação cresce novamente cada vez que você reduzi-lo, deixá-lo sozinho. Deve ser óbvio que precisa ser maior que o tamanho para o qual você está encolhendo ele.

Espelho espelho

**P.**Implementamos apenas a base de dados de espelhamento e encontrado que já não podemos realizar reconstruções de índice para algumas de nossas mesas. A enorme quantidade de log de transação gerados Sobrecargas retarda nossa rede e o espelhamento de banco de dados. Por que isso acontece e como podemos trabalhar em torno dele?

**R.**Esse problema é encontrado por muitos que implementar o espelhamento de banco de dados. Isso decorre do fato de que o desempenho e confiabilidade teste feito ao vivo com o espelhamento de banco de dados não inclui a manutenção de banco de dados normal.

Muitas pessoas usam o modelo de recuperação bulk-logged, ao realizar operações de reconstrução de índice. Isso limita a quantidade de log de transação gerado, para que o log de transações não cresce durante a operação. Espelhamento de banco de dados permite apenas que o modelo de recuperação completa, onde operações de reconstrução de índice são totalmente registradas. Em seguida, eles podem gerar tanto volume de log de transações como o tamanho do índice que está sendo reconstruído.

A quantidade de registros de log de transações adicional quando recria de índice de desempenho no modelo de recuperação completa pode ser realmente grande e saturar o link de rede entre os servidores de banco de dados principal e espelhado. Se isso acontecer, uma fila de envio pode acumular-se no banco de dados principal. Isso pode causar a transação processamento de atrasos para qualquer carga de trabalho de aplicativos simultâneos.

O que isto significa é que, para muitas pessoas, não operações de reconstrução de índice são possíveis ao usar o espelhamento de banco de dados. Isso é verdadeiro mesmo com o compactação de fluxo de log incluída com o SQL Server 2008 e versões posteriores de espelhamento de banco de dados.

Uma estratégia de manutenção de índice alternativo é usar ALTER INDEX... REORGANIZE em vez de ALTER INDEX... RECONSTRUA. Reorganizar um índice só aborda a fragmentação do índice existente. Você pode interrompê-lo sem perder o trabalho já concluído. A recriação de um índice, por outro lado, sempre cria um novo índice, independentemente do grau de fragmentação. Se você interrompe-lo, você não ganha nada. Tudo é revertido.

Para índices maiores que não são práticos para reconstruir, execute as seguintes etapas:

  • **Um dia:**Inicie executar ALTER INDEX... REORGANIZE-se durante o período de manutenção. Deixá-lo correr por uma hora ou assim. Mate o comando. Ele não vai rolar nada de volta e você vai ter alguns progressos em termos de remoção de fragmentação do índice.
  • **Dia dois:**Comece a reorganizar novamente. Ele não se lembra de um dia, mas rapidamente deve atravessar o trabalho que ele fez em um dia e começar a remoção da fragmentação da parte próxima do índice. Matá-lo novamente após uma hora ou assim.
  • **Após dia dois:**Repita até que as gotas do nível de fragmentação abaixo de qualquer limite que você estabeleceu, ou apenas continuar o processo de dia-a-dia, indefinidamente.

Isso permite que você limite a quantidade de log de transação gerados (e portanto transmitidos usando espelhamento de banco de dados) pela sua manutenção regular do índice. Se você deseja obter mais avançado, em vez de matar o processo de reorganização após um determinado período de tempo, você pode monitorar o log de transações é gerado e matá-lo, uma vez que atinge um determinado limiar (ver meu blog SQLskills.com para mais detalhes).

Paul S. Randal

Paul S. Randal é o diretor administrativo da SQLskills.com, diretor regional da Microsoft e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft de 1999 a 2007. Ele escreveu o DBCC CHECKDB/repair para o SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Randal é especialista em recuperação de desastre, alta disponibilidade e manutenção de bancos de dados, e participa regularmente de conferências em todo o mundo. Blogs de He em sqlskills.com /Blogs/Paul., e você pode encontrá-lo no Twitter em Twitter.com /PaulRandal..

Conteúdo relacionado