Gerenciar Recuperação Acelerada de Banco de Dados

Aplica-se a: SQL Server 2019 (15.x)

Este artigo contém informações sobre práticas recomendadas para gerenciar e configurar a ADR (recuperação acelerada de banco de dados) no SQL Server 2019 (15.x) e versões posteriores. Para obter mais informações sobre o ADR no Azure SQL, consulte Recuperação Acelerada de Banco de Dados no Azure SQL.

Quem deve considerar usar a recuperação de banco de dados acelerada

Muitos clientes consideram a ADR (recuperação de banco de dados acelerada) uma tecnologia valiosa para melhorar o tempo de recuperação. Um acúmulo dos fatores a seguir deve ser considerado antes de decidir quais bancos de dados devem usar a ADR, avaliar os fatores abaixo e se o acúmulo de fatores pesa a favor ou contra o uso da ADR.

• O recurso ADR é recomendado para cargas de trabalho com transações de longa duração que não podem ser evitadas. Por exemplo, nos casos em que as transações de longa duração correm o risco de serem revertidas, a ADR pode ajudar.

• A ADR é recomendado para cargas de trabalho com casos em que as transações ativas estão causando aumento significativo do log de transações.

• A ADR é recomendada para cargas de trabalho que tiveram longos períodos de indisponibilidade de banco de dados devido a recuperação de execução prolongada do SQL Server (assim como reinicialização inesperada do SQL Server ou reversão de transação manual).

• A ADR não é compatível com bancos de dados registrados no espelhamento de banco de dados.

• A ADR não é recomendada para bancos de dados maiores que 100 terabytes devido ao limpador de versão de PVS (armazenamento de versão persistente) de thread único.

• Se o seu aplicativo executa muitas atualizações incrementais, mas não em lotes, como atualizar um registro sempre que houver uma linha acessada/inserida, sua carga de trabalho pode não ser ideal para a ADR. Considere reescrever as consultas de aplicativo para atualizações em lotes, sempre que possível, até o final do comando e reduzir um grande número de transações de atualização pequenas.

Avaliar se a sua carga de trabalho é uma boa opção para a ADR

Depois de habilitar a ADR na sua carga de trabalho, procure sinais que indiquem que o PVS não consegue acompanhar. É recomendável monitorar a integridade da ADR usando os métodos encontrados no artigo que trata da solução de problemas de recuperação de banco de dados acelerada.

A ADR não é recomendada para ambientes de banco de dados com uma contagem alta de atualizações/exclusões, como OLTP de alto volume, sem um período de descanso/recuperação para o processo de limpeza do PVS reaver espaço. Normalmente, os ciclos de operação de negócios permitem esse tempo, mas, em alguns cenários, você pode querer iniciar o processo de limpeza do PVS manualmente para aproveitar as condições de atividade do aplicativo.

• Se o processo de limpeza do PVS estiver sendo executado por um longo período de tempo, você poderá descobrir que a contagem de transações anuladas aumentará, o que também causará o aumento do tamanho do PVS. Use o sys.dm_tran_aborted_transactions DMV para relatar a contagem de transações anuladas e use sys.dm_tran_persistent_version_store_stats para relatar os horários de início/término da limpeza juntamente com o tamanho do PVS. Para obter mais informações, confira sys.dm_tran_persistent_version_store_stats.

• Para ativar o processo de limpeza do PVS manualmente entre as cargas de trabalho ou durante as janelas de manutenção, use sys.sp_persistent_version_cleanup. Para obter mais informações, consulte Sys.sp_persistent_version_cleanup.

• Cargas de trabalho com consultas de longa execução nos modos de isolamento SNAPSHOT ou READ COMMITTED SNAPSHOT podem atrasar a limpeza de ADR PVS em outros bancos de dados, fazendo com que o arquivo PVS cresça. Para obter mais informações, consulte a seção sobre verificação de instantâneos ativos longos em Solucionar problemas de recuperação acelerada de banco de dados. Isto se aplica a instâncias do SQL Server e da Instância Gerenciada de SQL do Azure ou em um pool elástico do Banco de Dados SQL do Azure.

Melhores práticas para recuperação acelerada de banco de dados

Esta seção contém orientações e recomendações para a ADR.

• Para o SQL Server, isole o repositório de versão do PVS a um grupo de arquivos no armazenamento de camada superior, como um SSD de alta capacidade, um SSD avançado ou PMEM (memória persistente), às vezes chamada de SCM (memória de classe de armazenamento). Para mais informações, consulte Alterar o local do PVS para um grupo de arquivos diferente. Esta opção não está disponível para Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

• Verifique se há espaço suficiente no banco de dados para considerar o uso de PVS. Se o banco de dados não tiver espaço suficiente para o PVS aumentar, a ADR falhará ao gerar versões. A ADR economiza espaço no repositório de versão em comparação com o repositório de versão tempdb.

• Evite várias transações de longa execução no banco de dados. Embora um dos objetivos do ADR seja acelerar a recuperação do banco de dados devido a refazer, múltiplas transações de longa duração podem atrasar a limpeza da versão e aumentar o tamanho do PVS.

• Evite transações grandes com alterações de definição de dados ou operações DDL. A ADR usa um mecanismo de SLOG (fluxo de log do sistema) para rastrear as operações DDL usadas na recuperação. O SLOG é usado apenas enquanto a transação está ativa. O SLOG é um ponto de verificação, portanto, evitar transações grandes que usam o SLOG pode ajudar no desempenho geral. Esses cenários podem fazer com que o SLOG retenha mais espaço:

  • Muitos DDLs são executados em uma transação. Por exemplo, em uma transação, criar e remover rapidamente tabelas temporárias.

  • Uma tabela tem grande número de partições/índices que são modificados. Por exemplo, uma operação de SOLTAR TABELA nessa tabela exigiria uma grande reserva de memória SLOG, o que atrasaria o truncamento do log de transações e atrasará as operações de desfazer/refazer. A solução alternativa pode descartar os índices individualmente e gradualmente e, em seguida, remover a tabela. Para obter mais informações sobre o SLOG, consulte Componentes de recuperação ADR.

• Impedir ou reduzir situações anuladas desnecessárias. Uma taxa de anulação alta colocará pressão sobre o desempenho PVS e inferior de ADR. As anulações podem vir de uma alta taxa de deadlocks, chaves duplicadas ou outras violações de restrição.

  • A DMV sys.dm_tran_aborted_transactions mostra todas as transações anuladas na instância do SQL Server. A coluna nested_abort indica que a transação foi confirmada, mas há partes que foram anuladas (pontos de salvamento ou transações aninhadas) que podem bloquear o processo de limpeza de PVS. Para obter mais informações, veja sys.dm_tran_aborted_transactions (Transact-SQL).

Habilitar e controlar a ADR

O ADR está desativado por padrão no SQL Server 2019 (15.x) e pode ser controlado usando a sintaxe DDL:

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF};

Use essa sintaxe para controlar se o recurso está ativado ou desativado e atribua um grupo de arquivos específico para os dados do PVS (repositório de versão persistente). Se nenhum grupo de arquivos for especificado, o PVS será armazenado no grupo de arquivos PRIMARY.

Um bloqueio exclusivo é necessário no banco de dados para alterar esse estado. Isso significa que o comando ALTER DATABASE será paralisado até que todas as sessões ativas sejam encerradas e que todas as novas sessões aguardarão atrás do comando ALTER DATABASE. Se for importante concluir a operação e remover o bloqueio, você poderá usar a cláusula de terminação, WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT], para anular todas as sessões ativas no banco de dados. Para obter mais informações, confira Opções de ALTER DATABASE SET.

Gerenciar o grupo de arquivos de repositório de versão persistente

O recurso ADR baseia-se na existência das alterações com controle de versão, com versões diferentes de um elemento de dados mantidas no PVS. Há considerações para localizar onde o PVS está localizado e como gerenciar o tamanho dos dados no PVS.

Para habilitar a ADR sem especificar um grupo de arquivos

Essa operação requer acesso exclusivo ao banco de dados.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

Nesse caso, quando o grupo de arquivos de PVS não é especificado, o grupo de arquivos PRIMARY mantém os dados de PVS.

Para habilitar a ADR e especificar que o PVS deve ser armazenado em um grupo de arquivos

Você pode configurar o ADR para usar outro grupo de arquivos, além do grupo de arquivos PRIMARY padrão, para armazenar dados PVS.

Antes de habilitar a ADR em um grupo de arquivos além de PRIMARY, você deve criar o grupo de arquivos e o arquivo de dados.

Crie o grupo de arquivos VersionStoreFG e crie um novo arquivo de dados no grupo de arquivos. Por exemplo:

ALTER DATABASE [MyDatabase] ADD FILEGROUP [VersionStoreFG];
GO
ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'VersionStoreFG'
, FILENAME = N'E:\DATA\VersionStore.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VersionStoreFG];
GO

Apenas após a criação do grupo de arquivos e de um arquivo de dados secundário, habilite a ADR e especifique que o PVS deve ser armazenado em um grupo de arquivos específico. Essa operação requer acesso exclusivo ao banco de dados.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Para desabilitar o recurso de ADR

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Mesmo depois que o recurso ADR estiver desabilitado, haverá versões armazenadas no repositório de versão persistente que ainda serão necessárias para a reversão lógica do sistema.

Alterar o local do PVS para um grupo de arquivos diferente

No SQL Server, talvez seja necessário mover o local do PVS para um grupo de arquivos diferente por vários motivos. Por exemplo, o PVS pode exigir mais espaço ou um armazenamento mais rápido.

Alterar o local do PVS é um processo de três etapas.

1. Desative o recurso de ADR.

   ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
   GO
   

2. Aguarde até que todas as versões armazenadas no PVS possam ser liberadas.

   Para poder ativar a ADR com um novo local para o repositório de versão persistente, você deve primeiro verificar se todas as informações de versão foram limpas do local do PVS anterior. Para forçar essa limpeza a acontecer, execute o comando:

   EXEC sys.sp_persistent_version_cleanup [database name];
   

   O procedimento armazenado sys.sp_persistent_version_cleanup é síncrono, o que significa que ele não será concluído até que todas as informações de versão sejam limpas do PVS atual. Após a conclusão, você pode verificar se as informações de versão são realmente removidas consultando o DMV sys.dm_tran_persistent_version_store_stats e examinando o valor de persistent_version_store_size_kb.

   SELECT DB_Name(database_id), persistent_version_store_size_kb 
   FROM sys.dm_tran_persistent_version_store_stats where database_id = [MyDatabaseID];
   

   Quando o valor de persistent_version_store_size_kb for 0, você poderá reabilitar o recurso ADR, configurando o PVS para ser localizado no novo grupo de arquivos.

3. Ative a ADR, especificando o novo local do PVS:

   ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
   (PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);
   

Próximas etapas

• Conceitos da recuperação acelerada de banco de dados
• Solucionar problemas de recuperação acelerada de banco de dados
• Recuperação Acelerada de Banco de Dados no SQL do Azure