P & R DO SQL Compactação de backup, redirecionamento de cliente com o espelhamento e mais

Paul S. Randal

P É vai ser atualizar a maioria dos nossos servidores para o SQL Server 2008, e um dos recursos que eu estou tentando encaminhar colocar em produção é compactação de backup. Sei que eu possa ativá-lo por padrão para todos os bancos de dados em cada servidor, mas também ouvi que que talvez não queira fazer isso. Eu não tenho certeza de que por que eu não poderia desejar ter o recurso ativado por padrão, como parece tem nada a perder. Você pode ajudar explicar os motivos subjacentes ouviu?

A resposta é meu favorito perene: depende! Deixe-me dar algumas plano de fundo para explicar.

O ponto-chave a considerar é a taxa de compactação que cada backup de banco de dados terá quando a compactação de backup está ativada. A taxa de compactação de algo que está sendo compactados por qualquer algoritmo é determinada pelos dados reais que está sendo compactados.

Procurando dicas do SQL Server?

Para obter dicas sobre como SQL Server, visite o Dicas do TechNet Magazine SQL Server a página.

Para obter mais dicas sobre outros produtos, visite o Índice da TechNet Magazine dicas.

Dados aleatórios (inteiro pequeno valores, por exemplo) não compactará muito bem, para que o conteúdo de tabelas e índices no banco de dados será determinar, na maior parte, a taxa de compactação que pode ser obtida.

Aqui estão alguns exemplos de quando a compactação de backup pode não produzir uma taxa de compactação alto:

• Se o banco de dados tiver dados transparente criptografia habilitada, em seguida, a taxa de compactação será muito baixa porque os dados sendo compactados são aleatórios valores pequenos.
• Se maior parte dos dados no banco de dados está criptografado no nível de coluna, em seguida, a taxa de compactação será baixa, novamente porque a criptografia de coluna essencialmente randomizes os dados.
• Se a maioria das tabelas no banco de dados tem habilitada de compactação de dados, em seguida, a taxa de compactação será baixa; compactação de dados que já principalmente são compactados normalmente terá um efeito.

No caso quando a taxa de compactação estiver baixa, o problema não é a taxa baixa, mas o fato de que os recursos da CPU são usados para executar o algoritmo de compactação para nenhuma ganho. Não importa como um bloco de dados pode ser compactado, os recursos da CPU de mensagens sempre são usados para executar os algoritmos de compactação e descompactação.

Isso significa que você precisa verificar quanto cada banco de dados compacta em um backup antes de decidir usar compactação de backup para esse banco de dados o tempo todo. Caso contrário, você pode, possivelmente, ser desperdiçar recursos da CPU. Isso é a base para o que você já ouviu.

Para resumir, se a maioria dos bancos de dados se beneficiará de compactação do backup, faz sentido para ativar a compactação de backup no nível do servidor e manualmente alterar alguns trabalhos de backup especificamente usar a opção de NO_COMPRESSION WITH. Como alternativa, se a maioria dos bancos de dados não se beneficiará de compactação do backup, faz sentido deixar compactação backup desativada no nível do servidor e manualmente alterar alguns trabalhos de backup para usar especificamente a opção de WITH COMPRESSION.

P último ano é atualizado nossos bancos de dados têm o espelhamento do banco de dados para que se ocorrer uma falha, possa executar failover para o espelho e o aplicativo continua. Enquanto que foram projetar o sistema, nós praticado fazer failovers do banco de dados e tudo funcionou corretamente. Semana passada tivéssemos uma falha real e ocorreu o failover do banco de dados, mas todas as transações de aplicativo interrompidas e o aplicativo não se conectar ao servidor de failover. No futuro, como pode configurar SQL Server para que ele não descartar as conexões de aplicativo durante o failover para que as transações podem continuar?

A Deixe-me dividir isso em duas partes — como os aplicativos podem lidar com failovers e como gerenciar o redirecionamento de cliente com o espelhamento do banco de dados.

Quando um failover ocorre usar qualquer um das tecnologias de alta disponibilidade disponíveis com o SQL Server, a conexão de cliente para o servidor com falha é eliminada e quaisquer transações em andamento são perdidas. Não é possível migrar uma transação em andamento entre os servidores (em uma situação de failover ou não). Dependendo a tecnologia de alta disponibilidade, a transação em andamento ou será não existir em todos os no servidor de failover ou ele irá existir como uma transação em andamento, mas será revertido como parte do processo para colocar o banco de dados online no servidor de failover.

Com considerar para espelhamento, do banco de dados fornecido continuamente registros do log de transações do servidor principal para o servidor de espelho, é geralmente o último caso, as transações em andamento serão revertidas como parte de colocar o banco de espelho dados online como o novo objeto.

Portanto, há duas coisas que um aplicativo deve ser capaz de fazer normalmente ao executar em um servidor com a possibilidade de ter para executar failover para outro servidor:

1. Ele deve ser capaz de elegantemente manipular a conexão de servidor que está sendo descartada e então tente reconectar-se após um intervalo de tempo pequeno.
2. Ele deve ser capaz para elegantemente manipular uma transação que está sendo cancelada e, em seguida, repetir a transação após uma conexão é estabelecida com o servidor de failover (possivelmente usando um gerenciador de transações mid-tier).

A apenas alta disponibilidade tecnologia aqui que não requer alterações de cliente especificamente para permitir o redirecionamento da conexão do cliente após um failover é cluster para failover. Clientes se conectar a um nome de servidor virtual e transparente são redirecionados para o nó do cluster física está ativo.

Com tecnologias de alta disponibilidade, como o envio de log e replicação, o nome do servidor do servidor de failover é diferente, que significa que manual redirecionamento de conexões de cliente é necessário após um failover. Esse redirecionamento manual pode ser feito de várias maneiras:

• Você pode codificar o nome do servidor de failover para o cliente para que as tentativas de reconexão são feitas para o servidor de failover.
• Você pode usar o balanceamento de carga de rede com um 100/0 — configuração de 0/100, que depois permitirá a conexão ser alternado para o servidor de failover.
• Você pode usar algo como um alias de nome de servidor ou alternar as entradas em uma tabela DNS.

Com o espelhamento do banco de dados, qualquer uma dessas opções irá funcionar. Mas o espelhamento do banco de dados também tem recursos de direção do cliente interno. A seqüência de conexão do cliente é capaz de especificar explicitamente o nome do servidor espelho, e se o servidor principal não pode ser contatado, o espelho automaticamente, em seguida, será tentado. Esse processo é conhecido como redirecionamento explícito.

Se a seqüência de conexão do cliente não pode ser alterada, em seguida, redirecionamento implícito talvez seja possível se o servidor com falha estiver sendo executado agora como o servidor de espelhamento. Qualquer conexão para ele será redirecionado automaticamente para o novo objeto, mas isso só funcionará se o servidor de espelhamento está sendo executado.

O SQL Server 2005 white paper" Implementando o failover do aplicativo com o espelhamento de banco de dados"explica essas opções mais detalhadamente.

P , quando é atualizado para SQL Server 2005, reprojetada nossas tabelas grandes a serem particionados para que nós pode tirar proveito de manutenção particionada e o mecanismo de janela deslizante. Isso é descrito na edição de agosto de 2008 " Particionamento, verificações de consistência e muito mais"). Mas nós já encontrou um problema. Ocasionalmente, consultas de aplicativo simultâneas estão tendo bloqueio entre a tabela inteira quando as consultas ainda não acessar as mesmas partições. Ouvi que o SQL Server 2008 corrige esse problema — pode por favor, explicar como eu pode interromper esse bloqueio?

Figura 1 examinando bloqueios em uma tabela particionada

A O problema que você está vendo é causado por um mecanismo chamado de escalonamento de bloqueio. SQL Server adquire bloqueios nos dados para protegê-los enquanto uma consulta é ler ou gravar os dados. Ele pode adquirir bloqueios em tabelas inteiras, páginas de arquivo de dados ou linhas de tabela/índice individual, e todo bloqueio ocupa um pouco de memória.

Se uma consulta faz com que muitos bloqueios ser adquirido, SQL Server pode optar por substituir todos os bloqueios nas linhas ou páginas em uma tabela de um bloqueio único em toda a tabela (o limite, quando isso ocorre, é de aproximadamente 5.000 bloqueios, mas o algoritmo exato é complicado e configurável). Esse processo é chamado escalonamento de bloqueio.

No SQL Server 2005, se A consulta está operando em uma única partição de uma tabela e faz com que o suficiente bloqueios a serem tomadas para disparar o escalonamento de bloqueio, em seguida, a tabela inteira fica bloqueada. Isso pode impedir que a consulta B ser capaz de operar em uma partição diferente da mesma tabela. Portanto, a consulta B está bloqueada até que A consulta é concluída e seus bloqueios são ignorados.

No SQL Server 2008, o mecanismo de escalonamento de bloqueio foi aperfeiçoado para permitir que uma tabela para ter o escalonamento de bloqueio de nível de partição. Usando o exemplo acima, isso significa que o escalonamento de bloqueio causado por consulta A somente pode bloquear a consulta de partição única A está usando, em vez da tabela inteira.

Consulta B será capaz de operar em outra partição sem sendo bloqueado. Consulta B até poderia acionar escalonamento de bloqueio, que poderia bloquear apenas a partição a consulta que B está funcionando, em vez da tabela inteira.

Esse modelo de escalonamento de bloqueio pode ser definido usando a seguinte sintaxe:

ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO);
GO

Essa sintaxe instrui o SQL Server bloquear Manager para usar o escalonamento de bloqueio de nível de partição se a tabela está particionada e escalonamento de bloqueio de tabela-nível normal se a tabela não está particionada. O comportamento padrão é usar o escalonamento de bloqueio em nível de tabela. Deve ter cuidado ao definir essa opção, que pode levar a deadlocks dependendo o comportamento de suas consultas.

Por exemplo, se consultas A e B ambos causar escalonamento de bloqueio em partições diferentes de uma tabela, mas, em seguida, cada tentam acessar a partição que a consulta tiver bloqueado, uma das consultas será anulada pelo processo do Monitor de bloqueio.

A figura mostra um exemplo de consulta a exibição de catálogo do sistema de sys.partitions (o primeiro conjunto de resultados) e o sys.dm_os_locks DMV (o segundo conjunto de resultados) para examinar os bloqueios mantidos para consultas em uma tabela particionada onde escalonamento de bloqueio de nível de partição ocorreu. Nesse caso, há dois bloqueios exclusivos nível de partição (os bloqueios HOBT na saída), mas os bloqueios de tabela (os bloqueios OBJECT na saída) não são exclusivos, para que várias consultas podem acessar partições, mesmo que o escalonamento de bloqueio ocorreu. Observe que as identificações de recurso para esses bloqueios dois partição corresponde à partição identificações para as duas primeiras partições da tabela a saída do sys.partitions.

Anteriormente neste ano publicou no blog anteriormente sobre uma exibição de script de exemplo como funciona de escalonamento de bloqueio de nível de partiçãoe a possibilidade de deadlocks. O tópico manuais online do SQL Server 2008 chamado" Bloqueio no mecanismo de banco de dados"tem uma explicação minuciosa de todos os aspectos de proteção do SQL Server 2008.

P um de nossos servidores tinha alguns problemas com o disco que contém o log de transações para um banco de dados, e o banco de dados se tornou suspeito. O backup total mais recente foi de cinco semanas atrás e ele foi pretende levar muito tempo para restaurar todos os os backups de log, muito. Ele estava fora do horas quando o problema ocorreu, portanto, é recriar o log de transações quebrado para evitar o tempo de inatividade. Em algumas circunstâncias, isso poderá causar problemas. Mas se nada foi acessando os dados, em seguida, ACREDITO que estamos seguros. Fizemos a coisa certa?

A resposta é que a única vez que eu consideraria recriar um log de transações é quando não é possível recuperar de backups. Embora você sejam atento os perigos de recriar um log de transações (para os leitores que não, consulte meu blog lançar " Resorts pela última vez que as pessoas tentem primeiro..." o fato de que o banco de dados aconteceu significa suspeita que falhou durante a recuperação — ao executar a recuperação de falha ou ao reverter uma transação. Isso significa que há a possibilidade real de corrupção de dados no banco de dados.

Embora o problema durante o período de espera, você considerar trabalhos agendados e tarefas em segundo plano? Um trabalho de manutenção poderia ter sido executando que foi recriar ou reorganizar um índice de cluster quando o log tornou-se corrompido. Uma tarefa de plano de fundo pode estava executando fantasma limpeza em páginas de um heap ou índice de cluster. Um deles, por exemplo, pode ter sido fazer alterações às estruturas de índice de cluster que, se não corretamente revertida, poderiam resultar em corrupção na perda de dados banco de dados e possíveis.

A linha inferior é que a recriação de um log de transações deve ser sempre a última alternativa absoluta em qualquer cenário de recuperação de desastres devido à enorme potencial de fazendo com que mais corrupção e perda de dados. No mínimo, você deve executar um DBCC CHECKDB completo em banco de dados para verificar se existe qualquer dano.

Mover para frente, você deve alterar sua estratégia de backup para que você é capazes de realizar restaurações oportunas e não precisa recorrer a drásticas medidas, como a recriação do log de transação. As etapas para criar uma estratégia de backup estendem além do escopo desta coluna, mas planeja abrangendo esse tópico em um artigo de recurso filmes em algum momento no próximo ano. Portanto, por favor, permanecer ajustado!

S. Paul Randal é o diretor de gerenciamento de SQLskills.come um MVP do SQL Server. HeHe trabalhou na equipe mecanismo de armazenamento do SQL Server da Microsoft de 1999 para 2007. Paul escreveu DBCC CHECKDB/reparo para o SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Paul é um especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é um apresentador regular em conferências em todo o mundo. Blogs de he em SQLskills.com/blogs/paul.