P+R SQL
Transações distribuídas, backups SQL e contadores de desempenho
Paul S. Randal
Pergunta: Usamos muitas transações distribuídas e agora estamos investigando o espelhamento para fornecer alta disponibilidade para um dos nossos bancos de dados críticos do banco de dados. Durante os nossos testes, descobrimos que transações distribuídas às vezes falham após tentamos para failover de banco de dados de espelhamento. Você pode explicar o que está acontecendo?
Resposta: Isso é realmente uma limitação documentada de usar transações distribuídas. Existe a limitação ao usar o espelhamento do banco de dados ou envio de log — basicamente qualquer tecnologia em que o nome do servidor Windows é diferente após um failover.
Ao usar transações Microsoft Distributed Transaction Coordinator (MSDTC), o coordenador de transações local tem uma identificação de recurso que identifica o servidor no qual está sendo executado. Quando ocorre um failover de espelhamento, o banco de dados principal torna-se hospedado em um servidor diferente (o parceiro de espelhamento) e, portanto, a identificação do recurso de coordenador de transações é diferente.
Se uma transação distribuída estiver ativa, o coordenador de transações sobre o espelhamento de parceiros tenta verificar o status da transação e não é possível porque ela tem a identificação do recurso errado; MSDTC não reconhece como ele originalmente não foi envolvido na transação distribuída. Nesse caso, a transação distribuída deve ser terminada — o comportamento que você está vendo.
Um problema semelhante pode ocorrer com transações de todos os bancos de dados (uma única transação simples envolvendo atualiza em mais de um banco de dados). Se um dos bancos de dados envolvidos é espelhado e um não for, é possível que a transação de banco de dados seja confirmada nos dois bancos de dados. Se ocorre um failover de espelhamento forçado (quando o principal e espelho não são sincronizados e um failover manual, permitindo que a perda de dados, é executado), a transação confirmada no banco de dados espelhado poderão ser perdida, quebrando a integridade da transação de banco de dados.
Isso pode ocorrer se o banco de dados espelhado não está sincronizado (consulte a Coluna de junho de 2009 onde posso explicar isso mais) e, portanto, os registros de log da transação de todos os bancos de dados comprometidos ainda não foram enviados para o espelho. Após o failover forçado, a transação não existe no novo banco de dados principal e, portanto, a integridade da transação de banco de dados é quebrada.
Pergunta: Recentemente eu estava monitorando alguns contadores de desempenho para descobrir um problema com o armazenamento do banco de dados. Ao fazer isso percebi algo bastante estranhos: Embora não havia nada acontecendo no banco de dados, ainda havia ocorrendo para os arquivos de banco de dados de atividade de gravação. Isso aconteceu para arquivos de dados e de log. Mesmo que eu fiz-se de que não havia nenhum conexões com o SQL Server, mas ele ainda continua. Como pode haver atividade de E/s quando não houver nenhuma conexão?
Resposta: SQL Server tem um número de operações de manutenção de casa que precisam ser executados; eles são chamados de tarefas em segundo plano. Um ou mais dos seguintes é algo em seu sistema e causando I/Os. Aqui está uma lista rápida de culpados possíveis:
Fantasma limpeza: Uma operação de exclusão somente marca registros excluídos, como uma otimização de desempenho no caso da operação será cancelada; não zero fisicamente o espaço. Depois que a operação de exclusão foi confirmada, algo que tem realmente remover os registros excluídos do banco de dados. Isso é feito pela tarefa de limpeza fantasma. Você pode ler mais sobre isso em Esta postagem no meu blog, que também explica como verificar se a tarefa de limpeza de fantasma está em execução.
Redução automática: Essa é uma tarefa que você pode ativar para remover automaticamente o espaço vazio em um banco de dados. Ele funciona movendo páginas do final de arquivos de dados para o início, consolidando o espaço livre no final e, em seguida, truncando os arquivos. Embora você possa ativá-lo, é absolutamente não deve — ele causa problemas de fragmentação do índice (levando a um desempenho ruim) e usa muitos recursos. Geralmente você terá crescimento automático habilitado para um banco de dados, também, assim, você pode obter em um ciclo redução crescer redução pague dá muito trabalho para nenhum ganho. Você pode verificar o status de todos os bancos de dados com esta consulta:
SELECT name, is_auto_shrink_on FROM sys.databases;
Foi adiado-soltar: Esta tarefa é responsável por fazer o trabalho necessário para descartar ou truncar tabelas e índices (o descarte do índice pode ter ocorrido devido a uma operação de recompilação de índice — o novo índice é criado e, em seguida, o antigo será descartado). Para pequenas tabelas e índices, a desalocação é feita imediatamente. Para maiores tabelas e índices, a desalocação é feita em lotes uma tarefa em segundo plano. Isso é para garantir que todos os bloqueios necessários podem ser adquiridos sem ficar sem memória. Você pode usar os contadores de desempenho de soltar adiada vários para monitorar essa tarefa, conforme descrito no manual online aqui.
Gravações lentas: Esta tarefa é responsável pela remoção páginas antigas do cache de memória (denominado pool de buffer). Quando o servidor está sob pressão de memória, páginas talvez precise ser removido mesmo se eles têm alterações neles. Nesse caso, a página alterada deve ser gravada em disco que possa ser removido da memória. Você pode usar a “ gravações lentas / s ” contador de desempenho para monitorar essa tarefa, conforme descrito no manual online aqui.
Todos esses recursos têm o potencial para fazer alterações no banco de dados. Todos eles use transações para fazer as alterações e sempre que uma transação confirmada, registros do log de transação gerados pela transação devem ser escritos para a parte do log do banco de dados no disco. Vez com tanta freqüência como as alterações estão sendo feitas no banco de dados, um ponto de verificação também deve ocorrer a liberar os dados alterados páginas de arquivo para o disco. Você pode ler mais sobre isso em meu artigo na edição de fevereiro de 2009 da TechNet Magazine de no Noções básicas sobre registro em log e recuperação no SQL Server.
Como você pode ver, simplesmente porque há não há conexões ativas para SQL Server, isso não significa necessariamente que o processo está inativo — pode ser ocupado com uma ou mais tarefas em segundo plano. Se a atividade de I/O continua tempo após a conclusão da todos de sua atividade de banco de dados, também convém verificar se há serviços agendados que possam estar em execução.
Pergunta: Eu sou uma DBAs involuntárias e eu tiver sido experimentar coisas diferentes para aprender. O DBA anterior configurado trabalhos para fazer backups em um arquivo mas não consigo saber como restaurá-los. Existe alguma forma de ver o que os backups estão no arquivo? E como posso restaurá-los corretamente?
Resposta: Embora seja possível acrescentar o mesmo arquivo backups, a maioria das pessoas colocar cada backup em um arquivo separado, com um nome significativo (e geralmente uma combinação de carimbo de data/hora). Isso pode ajudar a evitar o problema estiver enfrentando e tornar mais fácil de realizar outras tarefas:
- Copiar backups para segurança é fácil quando cada backup está em seu próprio arquivo. Se todos os backups estiverem em um único arquivo, uma cópia de backup mais recente só pode ser feita, copiando o arquivo de backup inteiro.
- Não é possível excluir backups antigos quando todos os backups estão em um único arquivo.
- Sobrescrever acidentalmente backups existentes não é provável se cada backup possui um arquivo nomeado separadamente.
Infelizmente, isso não ajudar a sua situação, você já tiver um arquivo em vários backups dentro dela. No entanto, há duas maneiras de você pode restaurar os backups: manualmente ou usando o SQL Server Management Studio (SSMS).
Para ver quais são os backups do arquivo, você pode usar o SSMS para criar um novo dispositivo de backup que faz referência ao arquivo. Depois que a referência é criada, você pode exibir os detalhes do backup para o que há no dispositivo de backup. Ou, você pode usar o comando RESTORE HEADERONLY. Ambos irão examinar o dispositivo de backup e fornecer uma linha de saída que descreve cada backup do arquivo. SSMS identifica tipos de backup com um nome amigável. Usando a sintaxe correta, você precisará descobrir que tipo de backup, cada uma é usando as informações fornecidas na entrada do SQL Server Books Online para o comando (consulte aqui Para obter a versão do SQL Server 2008) para que possa usar o comando RESTORE apropriado para restaurar o backup.
Você também precisará trabalhar sem o backup que deseja restaurar. Isso é um pouco complicado porque o nome da coluna de saída que precisa, de RESTORE HEADERONLY não coincide com a opção que você deve usar para restaurá-lo. Os backups no arquivo são numerados a partir de 1, com 1 sendo o mais antigo, e o número pode ser encontrado na coluna chamada posição. Para restaurar esse backup, você deve usar o número de WITH FILE = < número > parte um comando RESTORE. Veja a seguir um exemplo:
RESTORE DATABASE test FROM DISK = 'C:\SQLskills\test.bak' WITH FILE = 1, NORECOVERY;RESTORE LOG test FROM DISK = 'C:\SQLskills\test.bak'
WITH FILE = 2, NORECOVERY;
E assim por diante. Você deve iniciar a seqüência de restauração com um backup do banco de dados e, em seguida, restaurar zero ou mais banco de dados diferencial e/ou backups de log de transações. Ainda mais detalhes estão além do escopo desta coluna, mas você pode ler que mais sobre como restaurar seqüências e as outras opções de restauração que talvez seja necessário no meu artigo na edição de novembro de 2009 no Recuperação de desastres usando backups.
Usando o SSMS, você especificar o arquivo de backup no Assistente para restauração do banco de dados e automaticamente irá mostrar todos os backups do arquivo e permitem que você selecione aqueles que deseja. Um exemplo é mostrado na Figura 1.
.jpg)
Figura 1 usando o Assistente SSMS restaura o banco de dados para mostrar vários backups em um arquivo.
Qualquer opção escolhida, é vital que você Pratique restaurações de desempenho para outro local antes de fazê-lo por real durante a recuperação de desastres. Um dos Meus Favoritos princípios é “ É não precisa um backup até que você fez uma restauração bem-sucedida. ”
Pergunta: Tenho um banco de dados muito grande que preciso para copiar cada algumas semanas para um ambiente de desenvolvimento. Meu problema é que o banco de dados foi recentemente crescido prevendo mais dados chegando e agora é muito grande quando eu restaurá-lo no ambiente de desenvolvimento. Como posso obter que ele seja menor quando eu restaurá-lo?
Resposta: Essa é uma pergunta bastante comum para o qual não há, infelizmente, uma boa resposta.
Um backup de um banco de dados não altera o banco de dados em qualquer forma — ele apenas lê todas as partes usadas do banco de dados e inclui-las do backup, além de alguns do log de transação (consulte Esta postagem no meu blog para obter uma explicação do motivo e a quantidade). Uma restauração a partir de um backup do banco de dados apenas cria os arquivos, grava o que era no backup e, em seguida, executa a recuperação do banco de dados. Basicamente, o que tinha no banco de dados is what you get, quando você restaura. Não será a opção para reduzir um banco de dados de restauração, fragmentação de índice do endereço na restauração, as estatísticas de atualização em restauração ou qualquer uma das outras operações de pessoas podem desejar executar.
Então, como para alcançar o que você deseja? Você tem três opções, dependendo do seu cenário exato.
Primeiro, você poderia ter realizado uma operação de redução no banco de dados de produção para recuperar o espaço em branco. Isso seria fazer a restaurado cópia o banco de dados da mesma como a produção e sem o espaço desperdiçado, mas com um custo potencialmente alto. O banco de dados de produção precisaria ser aumentado novamente e a operação de redução pode ser extremamente caro (em termos de log de transação de CPU, E/s e) e causar fragmentação do índice. A fragmentação do índice teria que ser abordados, levando mais recursos. Isso não é a opção que deseja usar. (Para uma mais detalhada explicação dos perigos de utilizar uma redução de arquivo de dados, consulte Esta postagem no meu blog). Você pode considerar apenas removendo o espaço livre no final do arquivo (DBCC SHRINKFILE WITH TRUNCATEONLY), mas isso pode não apresentar a redução no tamanho que você precisa.
Segundo, se você só precisará restaurar o banco de dados de produção uma vez no desenvolvimento, precisará ter espaço suficiente para restaurar o banco de dados em tamanho normal e reduzi-la para recuperar o espaço. Depois que você precisará decidir se a fragmentação que foi criada pela operação de redução de endereço.
Se você for executar consultas para testes de desempenho ou para emissão de relatórios, a fragmentação poderá causar uma grande queda no desempenho das consultas. Se você não estiver, talvez não seja necessário remover a fragmentação em todos os. Para enfrentar a fragmentação, não é possível reconstruir os índices (usando o comando ALTER INDEX … REBUILD) à medida que requer espaço extra e fará com que o banco de dados crescer novamente — você terá que reorganizar os índices (usando o comando ALTER INDEX … REORGANIZE).
Se você decidir remover a fragmentação, tome cuidado que você alterne o banco de dados no modelo de recuperação SIMPLE para que o log de transações não crescer de todos os registros de log de transação gerados pela reorganização. Se você deixar o banco de dados no modelo de recuperação FULL, o log continuará a crescer, a menos que você executar backups de log — algo que você provavelmente deseja evitar lidar com em uma cópia de desenvolvimento do banco de dados.
Finalmente, se você precisar restaurar o banco de dados de produção várias vezes em desenvolvimento, você não vai desejar repetir as etapas em opção 2 várias vezes. Nesse caso, seria melhor siga as etapas em opção 2 e, em seguida, criar um backup adicional do banco de dados reduzido (e talvez desfragmentado).
Esse segundo backup, em seguida, pode ser usado para executar várias restaurações do banco de dados de produção com o mínimo de tamanho.
Para resumir, não é possível fácil mover um banco de dados de produção que tenha uma grande quantidade de espaço livre em um ambiente de desenvolvimento sem espaço livre thSQLat sendo necessário para a restauração inicial.
Graças à l Kimberly. Tripp da SQLskills.com para fornecer uma análise técnica da coluna deste mês!
Paul S. Randal é de o diretor administrativo da SQLskills.com, diretora regional do Microsoft e um SQL Server MVP. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft de 1999 a 2007. Randal escreveu o DBCC CHECKDB/repair para SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Ele é especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é apresentador regular em conferências em todo o mundo. He bloga em SQLskills.com/blogs/paul, e ele pode ser encontrado em Twitter no Twitter.com/PaulRandal.