P+R SQLParticionamento, verificações de consistência e muito mais

Paul S. Randal

P Anexei acidentalmente um banco de dados de produção a um servidor do SQL Server® 2005 e agora estou tentando anexá-lo ao servidor correto, que está executando o SQL Server 2000. Tentei simplesmente desanexar o banco de dados e depois anexá-lo ao servidor do SQL Server 2000. Tentei também fazer o mesmo com um procedimento de backup e restauração, mas nenhuma das duas formas funcionou. Por que o banco de dados não funciona com meu servidor do SQL Server 2000? Essa é a única cópia que tenho do banco de dados.

R O primeiro ponto a ser observado é a importância de se manter backups. Os DBAs geralmente pensam na necessidade dos backups para recuperar-se de corrupção de dados ou de outro desastre causador de destruição. Mas você se deparou com um desastre menos óbvio: algo que deu errado durante uma atualização, embora, nesse caso, trate-se de uma atualização acidental. Seja como for, a questão é que você deve ter sempre um backup completo e recente do banco de dados, para o caso de que algo aconteça a ele.

Uma atualização, seja intencional ou acidental, é uma operação unidirecional, e é extremamente difícil reverter seus efeitos. Quando você faz uma atualização entre versões do SQL Server, uma série de etapas de atualização é realizada no banco de dados. Cada etapa geralmente envolve algumas alterações físicas no banco de dados e aumenta seu número de versão.

Por exemplo, uma das principais alterações realizadas ao atualizar um banco de dados do SQL Server 2000 para o SQL Server 2005 é a alteração da estrutura dos catálogos de sistema do banco de dados (geralmente chamados de tabelas do sistema ou metadados do banco de dados). Esses catálogos contêm diversos metadados sobre tabelas, índices, colunas, alocações e outros detalhes referentes à estrutura física e relacional do banco de dados.

À medida que cada uma dessas etapas de atualização é realizada, o número de verão do banco de dados é aumentado. Por exemplo, os bancos de dados do SQL Server 7.0 têm o número de versão 515, os do SQL Server 2000 têm o número de versão 539 e os do SQL Server 2005 têm o número de versão 611 (ou 612, se o recurso vardecimal estiver habilitado). Isso permite que o SQL Server saiba qual foi a última etapa de atualização realizada no banco de dados.

As versões do SQL Server não são capazes de ler bancos de dados atualizados para versões mais recentes (por exemplo, o SQL Server 2000 não pode ler um banco de dados atualizado para o SQL Server 2005). Isso ocorre porque versões mais antigas não contêm o código necessário para interpretar as estruturas e o layout atualizados do banco de dados. A isso se deve o problema com o seu banco de dados, que foi atualizado para o SQL Server 2005: ele não pode ser anexado de volta ao SQL Server 2000.

Na ausência de um backup completo, sua única opção é exportar todos os dados do banco de dados atualizado e transferi-los manualmente para um novo banco de dados do SQL Server 2000. Desde que você não tenha usado nenhum novo recurso do SQL Server 2005, poderá extrair o script da estrutura do banco de dados, criar o novo banco de dados no SQL Server 2000 e exportar/importar os dados.

Para extrair o script do banco de dados no SQL Server 2005, você pode usar o Object Explorer do SQL Server Management Studio. Clique com o botão direito do mouse no banco de dados, selecione Tasks (Tarefas) e depois a opção Generate Scripts (Gerar Scripts). O assistente é auto-explicativo e gera um script para criar todos os objetos, índices, restrições, gatilhos etc.

P Reprojetamos recentemente nosso esquema para usar o particionamento de tabelas na tabela principal. Soubemos que isso ajuda a melhorar o desempenho. O banco de dados está armazenado em uma só unidade de 120 GB e a tabela está contida em um único grupo de arquivos. Não está sendo criada nenhuma janela deslizante. Apenas novas partições são adicionadas semanalmente, e todos os dados precisam estar disponíveis online. A maioria das nossas consultas processa dados de uma única semana. Apenas algumas atuam no último ano. Parece ser mais fácil fazer isso em um único grupo de arquivos. Estou certo ou há mais detalhes que não conheço?

R Embora pareça mais fácil usar um único grupo de arquivos, é pouco provável que, dessa forma, você perceba os benefícios do particionamento. As principais utilizações do particionamento são para obter uma manutenção mais eficiente do banco de dados e para aumentar a disponibilidade de dados em caso de desastre; além disso, você pode criar um esquema que gere ganhos de desempenho.

O exemplo típico de particionamento é uma tabela de vendas com 12 partições, cada uma representando um mês dos dados de vendas do último ano. No final do mês, a partição mais antiga é retirada (e arquivada ou excluída), e uma nova partição é incluída. Esse é o cenário de janela deslizante que você mencionou. A partição do mês atual é configurada para leitura/gravação e os meses mais antigos são somente leitura. Cada partição é armazenada em um grupo de arquivos separado. Esse esquema proporciona todas as vantagens do particionamento, mas não é o ideal em todas as circunstâncias.

Minha esposa, Kimberly, criou uma variação do esquema descrito acima que permite uma indexação mais eficiente. Imagine dividir a tabela de vendas em duas tabelas — uma para manter a única partição de leitura/gravação e outra para manter as 11 partições somente leitura, com uma exibição particionada das duas tabelas.

Isso permite que a tabela de leitura/gravação tenha uma pequena quantidade de índices, e que a tabela somente leitura tenha mais índices para dar suporte às consultas para a geração de relatórios. Isso, por sua vez, torna as operações em DML (linguagem de manipulação de dados) nos dados de leitura/gravação consideravelmente mais eficientes, pois eles não precisam manter tantos índices não clusterizados.

Além disso, as consultas aos dados de leitura/gravação não precisam processar também os dados somente leitura. A eliminação de partições em planos de consulta não é perfeita no SQL Server 2005, especialmente em casos com predicados complexos, mas foi muito aperfeiçoada no SQL Server 2008. Para obter mais informações sobre isso, consulte a postagem no blog de Kimberly em sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

Para mostrar o que quero dizer, explicarei alguns recursos habilitados pelo particionamento em vários grupos de arquivos.

Disponibilidade parcial do banco de dados É a capacidade de um banco de dados de estar online e acessível durante uma situação de recuperação de desastre, desde que o grupo de arquivos principal esteja online. Se você tiver apenas um grupo de arquivos, todo o banco de dados ficará inativo durante a restauração. Com os dados distribuídos por vários grupos de arquivos, somente os grupos danificados ficarão offline durante a restauração, e talvez o seu aplicativo possa continuar em operação.

Restauração por etapas Esse esquema é semelhante à disponibilidade parcial do banco de dados. Com um único grupo de arquivos, a unidade de restauração é uma só página ou o banco de dados inteiro. Com vários grupos de arquivos, você pode restaurar apenas um deles, permitindo a disponibilidade parcial do banco de dados.

Manutenção do banco de dados particionado Com qualquer um dos esquemas de particionamento abordados anteriormente, você pode realizar a remoção da fragmentação por índice de partição, até mesmo quando todas as partições estiverem em um único grupo de arquivos. Mas, com um único grupo de arquivos, você perde a capacidade de realizar verificações de consistência por grupo de arquivos, o que pode reduzir drasticamente o volume de dados a serem processados pela DBCC (verificação de consistência de banco de dados) — e, conseqüentemente, o volume de recursos de CPU e E/S utilizados.

Simplesmente, embora você possa ter várias partições no mesmo grupo de arquivos, há diversas vantagens em ter um mapeamento 1-1 entre as partições e os grupos de arquivos.

P Há pouco tempo, levamos um susto com um de nossos servidores avançados de banco de dados: uma placa de memória defeituosa estava causando corrupções. Descobrimos isso quando dados aleatórios começaram a aparecer em nosso aplicativo. Executamos o DBCC CHECKDB e encontramos todos os tipos de corrupção. Infelizmente, os dados corrompidos também estavam presentes em nossos backups. Então, foi preciso removê-los manualmente.

Para encurtar a história, substituímos o hardware defeituoso e ativamos as somas de verificação de páginas. Gostaríamos de executar verificações de consistência regularmente, mas não temos uma janela de manutenção grande, e a verificação do nosso banco de dados de 2,4 TB é demorada. O que podemos fazer?

R A dúvida sobre como realizar verificações de consistência e outros tipos de manutenção em um VLDB (banco de dados muito grande) vem se tornando cada vez mais comum. Muitos DBAs desistem depois de descobrir que DBCC CHECKDB leva mais tempo para ser executado do que permite a janela de manutenção (em algumas situações, o banco de dados é usado 24 horas por dia, 7 dias por semana, e não há tempo hábil para abrir mão da sobrecarga de CPU e E/S exigida pelo DBCC CHECKDB por um período extenso).

Além de desistir e não executar nenhuma verificação de consistência (o que eu definitivamente não recomendo), há quatro métodos que você pode utilizar. Já ajudei clientes a utilizar todas as quatro abordagens.

Usar a opção WITH PHYSICAL_ONLY do DBCC CHECKDB Um DBCC CHECKDB normal executa uma grande quantidade de verificações de consistência lógica, consumindo muita capacidade da CPU (e sendo, basicamente, uma operação vinculada à CPU). O uso da opção WITH PHYSICAL_ONLY limita essa execução às verificações rápidas de consistência de bitmaps de alocação DBCC CHECKALLOC, e depois à leitura e auditoria de todas as páginas alocadas no banco de dados, obrigando todas as somas de verificação de páginas presentes nos documentos a serem testadas. Isso transforma o DBCC CHECKDB em uma operação vinculada à E/S, com um tempo de execução muito menor (na verdade, chega a ser dez vezes mais rápido do que um DBCC CHECKDB completo, o que representa uma economia de tempo significativa).

Dividir a carga de trabalho da verificação de consistência Isso envolve dividir as tabelas do banco de dados em grupos do mesmo tamanho (a abordagem mais simples é fazer isso por número de páginas) e depois usar os comandos de DBCC CHECKTABLE para verificar a consistência de todas as tabelas em um único grupo a cada noite. Então, por exemplo, com sete grupos sendo verificados, um por dia, com um DBCC CHECKALLOC e um DBCC CHECKCATALOG uma vez por semana, é possível realizar o equivalente a um DBCC CHECKDB, embora dividido ao longo da semana.

Usar o particionamento de tabelas com vários grupos de arquivos As maiores tabelas do VLDB podem ser divididas em vários grupos de arquivos. Um exemplo de esquema de verificação de consistência pode ser a execução diária de DBCC CHECKFILEGROUP no grupo de arquivos que contém a partição de leitura/gravação, e também execuções semanais de DBCC CHECKFILEGROUP nos grupos de arquivos contendo as partições somente leitura. A lógica disso é que os dados somente leitura contam com backups abrangentes e não são usados no processamento diário. Por isso, não precisam de verificações de consistência tão freqüentes, pois a corrupção não é tão crítica nesses dados.

Descarregar as verificações de consistência para outro servidor Essa opção envolve restaurar os backups completos regulares do banco de dados em outro servidor e executar o DBCC CHECKDB nesse servidor. É claro que isso descarrega completamente do servidor de produção a carga de trabalho da verificação de consistência. Contudo, a desvantagem é que, se uma corrupção for encontrada, será preciso executar uma verificação de consistência no servidor de produção — mas isso deve ocorrer com muito pouca freqüência.

Como você pode ver, há várias opções disponíveis para que os DBAs realizem verificações de consistência em um VLDB, reduzindo assim a carga extra de recursos necessária para executar um DBCC CHECKDB completo. Como já mencionei, ajudei clientes a utilizar todas as quatro abordagens. Acredito que essas opções funcionarão no seu caso.

Dica: usando gatilhos para implementar lógica no servidor

Em alguns cenários, é necessário implementar lógica no servidor com gatilhos. Mas você deve estar atento a certas armadilhas. Eis aqui alguns pontos importantes que é preciso ter em mente.

• Os gatilhos são acionados por instruções, e não por linha. Assim, certifique-se de colocar lógica adicional na lógica do seu gatilho para lidar com o cenário em que várias ou nenhuma linha é afetada pela instrução (os gatilhos são acionados por instruções, mesmo que nenhuma linha seja afetada). Os dados afetados são mantidos em tabelas virtuais para as instruções DML (linguagem de manipulação de dados). Essas tabelas podem ser unidas, permitindo que você trabalhe com os dados.
• Os gatilhos são executados na sua transação de forma síncrona. Lembre-se disso sempre que quiser chamar um aplicativo externo ou acessar um recurso externo e não tiver certeza se ele retornará com uma resposta ou em um tempo razoável. Por exemplo, se você acionar uma instrução Update em relação a uma tabela e um gatilho for acionado nessa ação, a transação (o aspecto implícito da instrução Update) não será encerrada até que toda a lógica do gatilho tenha sido concluída. Se o aplicativo ou o processo externo retornar um código de erro, o SQL Server poderá cancelar ou reverter a transação (dependendo do tratamento de erros implementado e do código de erro). Então, se você precisar realizar alguma operação externa com um gatilho que não seja essencial para a transação (ou que não precise ser executada no mesmo escopo), dimensione-a para outro processo, coletando dados de forma assíncrona. O SQL Server 2005 introduziu o SQL Server Service Broker, que pode realizar essas tarefas de forma assíncrona.
• É extremamente difícil localizar um erro causado por uma instrução em um gatilho. Se houver várias tabelas envolvidas na transação, lembre-se de inspecionar os gatilhos em caso de erro e de implementar o tratamento de erros adequado. Se você alterar o esquema no seu banco de dados, certifique-se de também manter o controle da lógica dos gatilhos. Caso contrário, um pequeno gatilho poderá causar um grande impacto no desempenho e na estabilidade gerais. A maioria das implicações relativas a alterações de esquema pode ser verificada usando o Visual Studio® for Database Professionals, realizando verificações automáticas de esquema ao editar o projeto e executando a análise estática do código para verificar inconsistências de tipo de dados.

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

Paul S. Randal é diretor administrativo da SQLskills.com e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft de 1999 a 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 é expositor em conferências regularmente. Ele mantém um blog em SQLskills.com/blogs/paul.

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