SQL p & r: Movimentando, ajustando o desempenho, fazendo backup e espelhando bancos de dados

  • Artigo

Paul s. Randal

Novo dia da mudança dos array

**P:**Nosso RAID atual está preenchendo rapidamente, portanto, precisamos mover alguns bancos de dados do SQL Server 2005 para outro local. A nova matriz está pronta e tenha sido Preparando para mover os bancos de dados. Você simplesmente descobri que um dos bancos de dados é um editor de replicação transacional e sei que isso significa que não consigo mover o banco de dados. O que devo fazer?

**R:**Há boas notícias para você — somente o SQL Server 2000 (e anterior) tinha a limitação de permissão para mover um banco de dados de publicação sem reinicializando replicação transacional ou alterar diretamente as várias tabelas de sistema.

Com o SQL Server 2005 e o SQL Server 2008, há um processo documentado, que permite mover um banco de dados sem ter que fazer nada com a replicação transacional, desde que o banco de dados permaneça conectado à mesma instância do SQL Server. Você tem que aceitar algum tempo de inatividade, pois não há nenhuma maneira de mover um arquivo de banco de dados enquanto ele está ainda on-line. O procedimento é o seguinte:

Primeiro, fazer o banco de dados off-line usando o código a seguir. Se houver usuários conectados ao banco de dados, você precisará soltá-los pela primeira vez, para que esse processo seja bem-sucedida:

ALTER DATABASE MyDatabaseName SET OFFLINE;

Em seguida, copie os arquivos de dados para o novo local. Use a cópia em vez de mover para permitir uma reversão rápida caso algo dê errado (caso contrário, você terá que executar uma restauração). Em seguida, informe do SQL Server o novo local de cada arquivo com o código a seguir:

ALTER DATABASE MyDatabaseName 
MODIFY FILE
   (NAME = N'LogicalFileName',
   FILENAME = N'pathname\filename');

Depois de fisicamente todos os arquivos copiados e atualizados a seus locais no SQL Server, coloque o banco de dados on-line com o código:

ALTER DATABASE MyDatabaseName SET ONLINE;

Fechando o Engate de página

**P:**Estou tendo problemas ao compreender alguns conceitos em torno de ajuste de desempenho. Li várias vezes que eu preciso evitar problemas de “ trava de página ”. Não sei o que significa “ página ” ou “ trava ” ou por uma trava de página poderia até mesmo ser um problema. Você pode explicar tudo isso?

**R:**Todos os dados em um banco de dados do SQL Server são armazenados em arquivos de dados. Internamente, esses arquivos são organizados em seqüências de chamadas de páginas de de blocos de 8 KB. Uma página é a unidade básica de armazenamento e e/S que podem gerenciar o SQL Server. Páginas normalmente são os arquivos de dados no disco e precisa do SQL Server (conhecido como o pool de buffers de ) para lê-las antes de processar todas as consultas de cache.

O SQL Server usa diferentes tipos de páginas para armazenar diferentes tipos de dados relacionais (como, por exemplo, linhas de uma tabela, linhas de um índice não agrupado ou dados de texto/LOB). Há também páginas armazenam partes das estruturas de dados interno necessárias para o SQL Server organizar e acessar as páginas de armazenar os dados relacionais.

Uma trava de é um mecanismo interno leve que do SQL Server usa para sincronizar o acesso a uma página no cache. Existem dois tipos de travas de página, você precisa observar — de travas regularpage e de travas de página e/S . Se um thread do SQL Server tem de esperar para adquirir uma das seguintes travas, isso indica um problema de desempenho.

Quando o SQL Server está esperando por uma parte de um arquivo de dados a serem lidos a partir do disco, pode fazer com que uma espera de trava de página e/S. Se uma trava de página e/S leva uma quantidade excessiva de tempo, isso geralmente indica um problema de desempenho com o subsistema de disco subjacente (ou seja, ele está sobrecarregado).

Quando vários segmentos dentro do SQL Server estão tentando acessar a mesma página de arquivo de dados de 8 KB na memória e não há contenção para o acesso à página, isso pode causar uma espera de trava de página. A ocorrência mais comum envolve o uso intenso de pequenos objetos temporários no banco de dados tempdb.

Uma explicação mais profunda sobre como monitorar e atenuar as esperas de trava de página está fora do escopo desta coluna, mas você pode encontrar mais informações em:

Procurando por meio de instantâneos de banco de dados

**P:**Assim que descobri instantâneos de banco de dados. Agora estou considerando usá-los como uma alternativa para os backups de log e o modelo de recuperação total. Criarei um instantâneo de cada hora, ou então e assim se algo der errado, pode puxar novamente os dados danificados. Eles parecerem muito menos incômodo e uma maneira muito mais rápida de restaurar. Você vê algum problema com essa alteração?

**R:**Sim, os instantâneos de banco de dados não são um substituto prático ou viável para uma estratégia de recuperação de desastre abrangente. Um instantâneo de banco de dados não oferece a mesma capacidade de um backup do log de transações em termos de totalmente a recuperação de desastres. O instantâneo de banco de dados não contém uma cópia de todas as páginas do banco de dados, apenas aqueles que foram alterados desde que foi o primeiro criado. Isso significa que, se o banco de dados estiver danificado de alguma forma, o instantâneo de banco de dados é inútil sem o banco de dados subjacente. Ele é simplesmente uma coleção de diferentes páginas do banco de dados e não pode ser usado para recuperação.

Um instantâneo de banco de dados permitem que você efetuar pull volta dos dados que foi acidentalmente excluídos do banco de dados, desde que o banco de dados ainda está disponível. Se ainda existe uma tabela descartada do banco de dados do instantâneo, por exemplo, você pode usá-lo para recriar a tabela descartada.

Dito isso, ele não é uma boa idéia criar muitos instantâneos de banco de dados (como um substituto para um backup do log de transações por hora única de half) devido a possíveis problemas de desempenho. Antes de troca de uma página de banco de dados (consulte a explicação em resposta a “ fechando a seção de Engate de página ”), primeiro você deve sincronicamente copiar a página em todos os instantâneos de banco de dados existente que não contêm uma versão dessa página. Ao criar instantâneos de banco de dados de mais, quanto mais cópias de página que você deve fazer e o desempenho fica prejudicado.

Um outro motivo para não criar muitos instantâneos de banco de dados é que cada uma conterá pre-change cópias das páginas do banco de dados. Cada uma delas aumentará conforme mais as alterações do banco de dados. Isso pode levar a problemas de espaço em disco, assim como problemas de desempenho.

Instantâneos de banco de dados não são projetados para ser um substituto para backups de log freqüente. Você pode ler um estudo mais detalhado das implicações de desempenho de instantâneos de banco de dados no white paper “ do Considerações de desempenho do banco de dados de instantâneo em I/O-intensivos cargas de trabalho. ”

Além disso, se você estiver usando os backups de log de transações e o modelo de recuperação total, em seguida, interesse, obviamente, sendo capaz de recuperar-se até o ponto de desastres e/ou fazer uso de restaurações point in time. (Para uma explicação ver meus artigos de 2009 julho e novembro de 2009 “ do Noções básicas sobre backups do SQL Server” e “ do SQL Server: Recuperação de desastres usando backups, ” respectivamente.)

Espelho, espelho

**P:**Eu já foi solicitado a configurar um espelho de banco de dados para o nosso banco de dados, mas estou preocupado que o espelhamento de banco de dados não vai ajudá-lo com o nosso problema. Tivemos alguns problemas de corrupção com nossos SAN, portanto, o plano é para que o espelhamento de banco de dados de impedir danos. Não danos automaticamente serão enviados ao longo para o espelho? Como é banco de dados de espelhamento contínuo para ajudá-lo com isso?

**R:**Este é um problema que causa muita confusão. Ele parece qualquer tecnologia que fornece uma cópia redundante de um banco de dados poderia ser suscetível à corrupção de propagação do principal ao espelho de banco de dados (para usar o terminologia de espelhamento de banco de dados) — mas, na verdade, isso não ocorre.

O xis da questão está na compreensão de como o banco de dados espelho é mantido. Corrupção, certamente, poderia ser propagada para o espelho se o mecanismo de sincronização subjacente copiado páginas do banco de dados completo do principal para o banco de dados espelho. Uma página danificada do principal, em seguida, seria colocada no espelho.

No entanto, o espelhamento de banco de dados especificamente evita isso porque ele não copia as páginas do banco de dados de um banco de dados para outro. O espelhamento de banco de dados funciona, copiando os registros de log de transação de banco de dados principal ao espelho. Registros de log de transação descrevem físicas alterações feitas às páginas do banco de dados e não contêm as próprias páginas reais. (Para obter uma explicação completa de registros de log de transações, log e de recuperação, consulte meu artigo de fevereiro de 2009: “Noções básicas sobre registro em log e recuperação no SQL Server.”)

Mesmo que uma página de banco de dados está corrompida pelo subsistema de e/S subjacente do banco de dados principal, não há nenhuma maneira para que essa corrupção propagar diretamente no banco de dados espelho. O pior, possivelmente, pode acontecer é o caso do SQL Server não detectar uma corrupção da página (porque as somas de verificação de página não estão ativadas), e um valor de coluna corrompido é usado para calcular um valor armazenado no banco de dados. O resultado incorreto resultante seria ser propagado ao banco de dados espelho — um efeito de corrupção de segunda ordem de .  Como mencionei, se as somas de verificação de página estiverem ativadas, essa corrupção permaneceria sem serem detectados quando a página é lida a partir do disco e o corrompimento de segunda ordem não teria ocorrido.

Esse comportamento também explica por que executar uma verificação de consistência no banco de dados principal não produzem qualquer informação sobre o estado de consistência do banco de dados de espelhamento e vice-versa. Eles são dois bancos de dados separados mantidos sincronizados por remessas descrições das alterações físicas no banco de dados, não as páginas do banco de dados real.

Nota do Editor: Graças à l. de Kimberly Tripp da SQLskills.com para fornecer uma revisão técnica da coluna deste mês.

Paul s. Randal é o Diretor de SQLskills.com diretor regional translation from VPE for picture itExclMark e MVP do SQL Server. Ele trabalhou na equipe o mecanismo de armazenamento do SQL Server em translation from VPE for picture itExclMark de 1999 a 2007. Ele 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. Randal é 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. Ele bloga em do SQLskills.com/blogs/paul e você poderá localizá-lo no Twitter no Twitter.com/PaulRandal.

Conteúdo relacionado