P+R SQLErros de E/S, espelhamento de banco de dados e muito mais

Paul S. Randal

P Comecei a executar verificações de consistência regulares nos bancos de dados que gerencio e até mesmo adicionei alguns alertas do SQL Agent para capturar erros de E/S encontrados por consultas do usuário. Não sei se a lógica que implementei em relação a verificações e alertas funcionará porque nenhum dos meus bancos de dados apresenta danos. Como é possível criar danos em um banco de teste para que consiga ter a certeza de que tudo o que configurei funcione corretamente? Além disso, há mais alguma coisa que devo fazer para detectar erros de E/S?

R No SQL Server® 2000, o velho truque de criar um banco de dados danificado tendo em vista o teste era excluir manualmente uma linha da tabela sysindexes de um banco de dados de teste. Mas com o SQL Server 2005, danificar uma tabela do sistema dessa maneira é muito difícil. A melhor forma de danificar um banco de dados de teste por conta própria é usando um editor hexadecimal para alterar um arquivo de dados enquanto o banco de dados é desligado. Eis o que fazer:

• Desligue o banco de dados para que os arquivos de dados não sejam bloqueados. (Mas tome cuidado para não desanexar o banco de dados, porque caso danifique a página errada, você talvez não consiga anexá-la novamente.)
• Escolha um deslocamento maior que, digamos, 100 páginas no arquivo (pelo menos 819.200 bytes), mas verifique se ele está de acordo com um limite de 8.192 bytes (um limite de página). Isso evita páginas de metadados críticos e bitmaps de alocação, o que permite iniciar o banco de dados e executar o DBCC CHECKDB nele.
• Escreva alguns bytes de zeros no deslocamento escolhido do arquivo. Usar essa técnica é praticamente uma garantia de introdução de alguns danos no cabeçalho da página.

Dito isso, a forma mais rápida de criar um banco de dados de teste danificado é usar um já criado por alguém. É possível encontrar exemplos de bancos de dados do SQL Server 2000 e do SQL Server 2005 corrompidos (com explicações) no meu blog (em go.microsoft.com/fwlink/?LinkId=115151).

Quanto à sua segunda pergunta, sobre o que deve fazer para detectar erros de E/S, você deve habilitar somas de verificação da página. Esse recurso foi introduzido no SQL Server 2005 como uma forma de proteger toda a página do banco de dados de erros apresentados pelo subsistema de E/S.

Basicamente, quando uma página é gravada em disco, a última coisa que o SQL Server faz é calcular uma soma de verificação para toda a página de 8 KB e carimbar a soma de verificação nela. Quando uma página é lida no disco, caso ela tenha uma soma de verificação, esta é recalculada e comparada com a armazenada na página. Em caso de não haver correspondência entre elas, algo externo ao SQL Server que corrompeu a página, e um erro 824 foi suscitado. O erro é exibido para a conexão que causou a leitura da página, bem como registrado no log de erros do SQL Server e no log de eventos do aplicativo do Windows®.

Por padrão, as somas de verificação da página permanecem ativadas em todos os bancos de dados criados no SQL Server 2005 e no SQL Server 2008. No entanto, elas devem ser habilitadas manualmente para bancos de dados atualizados em relação a versões anteriores do SQL Server. É possível habilitar somas de verificação usando este código:

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Dica: altere a porta do SQL Server padrão

Por padrão, a porta configurada das instâncias do SQL Server é 1433. Assim que for usada por uma instância, essa porta não poderá ser ocupada por mais nenhuma outra. Por isso, se você instalar uma segunda instância que ouça a rede usando TCP, ela precisará de uma outra porta. E, em alguns casos, o administrador talvez queira alterar a porta por motivos de ofuscação (embora essa forma de ofuscação seja secundária e possa ser facilmente desfeita por um scanner de porta). Obviamente, você precisa configurar o cliente para usar uma porta diferente. Há três abordagens comuns para fazer isso.

Primeira, pressupondo que a administração tenha alterado a porta de uma instância para 5555, é possível especificar simplesmente o número da porta da instância no nome da máquina à qual você deseja se conectar usando a sintaxe MyServername,5555. Se a porta for alterada novamente, os clientes também terão de alterar connectionStrings novamente.

Outra opção é usar aliases do SQL Server, configurados no cliente. Além de especificar um nome de alias, o nome de servidor, o nome de porta e o protocolo também devem ser especificados. Uma vez configurado, o alias pode ser usado como um nome de servidor a ser conectado à instância do banco de dados. A vantagem dessa opção é que as alterações feitas na configuração do servidor podem ser implantadas por um administrador de domínio, porque as configurações são armazenadas no Registro.

A terceira opção para instâncias nomeadas em que o usuário só conhece o nome da instância e especifica o nome usando MachineName\InstanceName em connectionString é usar o serviço Navegador do SQL Server. Ele já está implementado no SQL Server 2000 como parte de um serviço em execução. No entanto, no SQL Server 2005, o serviço Navegador do SQL Server foi criado como um serviço separado. Além de realizar a descoberta da instância em nome da máquina, ele também responde solicitações UDP (User Datagram Protocol) na porta 1434 com o número da porta apropriado à instância solicitada, o que habilita o redirecionamento para o cliente e o suporte a uma conexão transparente.

– Jens K. Suessmeyer, consultor em banco de dados da Microsoft

P Para remover toda a fragmentação no meu banco de dados, configurei um plano de manutenção noturna que recria todos os índices do banco de dados de produção, em execução no SQL Server 2005 Enterprise Edition com SP2. Como percebi que isso faz com que o banco de dados cresça excessivamente, adicionei uma etapa para reduzir todo o espaço extra, já que não há muito espaço em disco. Agora parece que a etapa de recriação não está funcionando. O que está havendo?

R Você se deparou com um problema que as pessoas comumente enfrentam quando definem um plano de manutenção. Você sofreu do ciclo de efeito sanfona.

Quando um índice é recriado, uma nova cópia é criada antes da exclusão do índice existente. Esse procedimento exige espaço adicional nos arquivos do banco de dados – em geral, aproximadamente o mesmo espaço usado pelo índice atual. No SQL Server 2000, o espaço adicional também era necessário à classificação das linhas do índice (aproximadamente 20% do tamanho do índice), mas esse requisito também foi eliminado em uma recriação de índice simples no SQL Server 2005.

Às vezes, os administradores querem remover o espaço adicional criado durante a recriação do índice, e adicionam uma operação de redução ao plano de manutenção após a etapa de recriação. Porém, ainda não é amplamente sabido que essa redução causará a fragmentação do índice por conta da natureza de seu algoritmo. Isso significa que o índice recém-recriado e desfragmentado será fragmentado imediatamente – anulando, inicialmente, o efeito de sua recriação.

Considerando que o arquivo de banco de dados acabará crescendo novamente na próxima vez em que ocorrer a recriação, é melhor permitir que o banco de dados tenha o espaço adicional e evitar totalmente a execução da operação de redução. (Além disso, aumentar e reduzir continuamente os arquivos de banco de dados causará a fragmentação no nível do sistema operacional – o que pode contribuir para o mau desempenho da mesma forma que a fragmentação do índice.)

Por fim, algo que você deve considerar é a redução da freqüência com que recria índices. Você talvez até queira tentar usar um método alternativo como, por exemplo, o antigo DBCC INDEXDEFRAG que escrevi para o SQL Server 2000 ou a sintaxe ALTER INDEX REORGANIZE mais recente do SQL Server 2005 e do SQL Server 2008.

Existe um documento muito útil que aborda a fragmentação do índice e apresenta diretrizes em relação ao momento de remover a fragmentação (em go.microsoft.com/fwlink/?LinkId=115154). Ainda que esse documento tenha sido escrito para SQL Server 2000, os conceitos continuam os mesmos.

P Temos avaliado a estratégia de recuperação de desastre na minha organização e acho que o espelhamento de banco dados é a saída para a nossa situação. O servidor que estou tentando proteger tem muitos bancos de dados não relacionados (o resultado de um projeto de consolidação de servidor anterior) e gostaria de usar o espelhamento de banco de dados em todos eles. A pergunta que estou tentando responder é quantos bancos de dados poderei espelhar antes que o desempenho seja prejudicado?

R A resposta para essa pergunta é uma que uso muito: depende! As diretrizes publicadas dizem para não espelhar mais de 10 bancos de dados por instância, mas 10 é apenas uma estimativa quanto ao máximo em relação à maioria dos usuários. Você precisa considerar os seguintes fatores na configuração do hardware:

• Quanta memória as instâncias principal e espelho têm? (O ideal é que elas sejam iguais.)
• Quanto poder de processamento as instâncias principal e espelho têm? (Ele também deve ser igual.)
• Quanta largura de banda o subsistema de E/S tem na instância espelho? (Ela deve ser a mesma da principal.)
• Quantos logs de transação a carga de trabalho gera em cada um dos bancos de dados?
• Quanta largura de banda de rede há disponível entre as instâncias principal e espelho?

Os últimos dois fatores são os mais críticos. Se a largura de banda da rede disponível entre as duas instâncias não for o suficiente para tratar a taxa de geração de log da transação combinada por segundo de todos os bancos de dados espelho, o desempenho cairá nos bancos de dados principais. O SQL Server 2008 ajuda a aliviar parte dessa carga com a compactação do fluxo de log.

O próximo aspecto mais crítico a ser considerado são os requisitos de memória e thread tendo em vista o espelhamento. Cada banco de dados espelhado usa um thread e mais alguma memória. Em servidores menos potentes, muitos bancos de dados espelhados podem significar muita carga no servidor quando combinados com a carga de trabalho regular.

Você também precisa considerar como executará o espelhamento do banco de dados. No modo síncrono, as transações no banco de dados principal não podem ser confirmadas até que todos os registros do log de transação tenham sido copiados para o log de transação do banco de dados espelhado. Por isso, qualquer atraso causado por uma rede sobrecarregada poderia causar um problema no desempenho da carga de trabalho no principal.

No modo assíncrono, as transações podem ser confirmadas no principal sem que seja preciso aguardar, mas um atraso de rede poderia fazer com que o volume do log de transações aguardando envio para o espelho crescesse. Isso pode causar problemas em relação ao tamanho do log da transação. O que é pior: qualquer log de transação não-enviado será perdido em caso de falha. Assim, quanto mais logs de transação não-enviados, maior será o potencial de perda de dados em uma situação de recuperação.

Os cenários podem variar muito, e vi alguns exemplos interessantes em ambientes de produção reais. Por exemplo, vi um ambiente com 150 bancos de dados, todos com volumes de atividade muito baixos e nem todos ao mesmo tempo. Todos os 150 bancos de dados são espelhados sem problemas.

Por outro lado, vi uma instalação com apenas três bancos de dados bastante carregados, mas sem uma boa conexão de rede. Nesse cenário, eles mal conseguem espelhar um banco de dados antes da falta de largura de banda da rede causar prejuízos à carga de trabalho.

A chave para o sucesso é, primeiro, fazer o cálculo da geração de log. Se a largura de banda da rede disponível aparentemente der suporte ao número de banco de dados que deseja espelhar, você talvez não tenha nenhum problema. Teste a configuração antes de colocá-la em produção e não se esqueça de incluir todas as operações que poderiam gerar o log de transação – especialmente uma possível manutenção do banco de dados.

Paul S. Randal é diretor administrativo da SQLskills.com e MVP SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft entre 1999 e 2007. Paul 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. Especialista em recuperação de desastre, alta disponibilidade e manutenção de banco de dados, Paul é apresentador regular em conferências. Ele bloga em SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation e CMP Media, LLC. Todos os direitos reservados; é proibida a reprodução parcial ou completa sem autorização..