Partilhar via

Use transações marcadas para recuperar bancos de dados relacionados de forma consistente

  • Artigo

Aplica-se a:SQL Server

Este tópico é relevante apenas para bases de dados do SQL Server que utilizam os modelos de recuperação completo ou de registo em massa.

Ao efetuar atualizações relacionadas a dois ou mais bancos de dados, bancos de dados relacionados, pode-se usar marcas de transação para recuperá-los a um ponto logicamente consistente. No entanto, essa recuperação perde todas as transações confirmadas depois do ponto de recuperação estabelecido. A marcação de transações é adequada apenas quando você está testando bancos de dados relacionados ou quando está disposto a perder transações confirmadas recentemente.

A marcação rotineira de transações relacionadas em cada banco de dados relacionado estabelece uma série de pontos de recuperação comuns nos bancos de dados. As marcas de transação são registradas no log de transações e incluídas nos backups de log. No caso de um desastre, você pode restaurar cada um dos bancos de dados para a mesma marca de transação para recuperá-los para um ponto consistente.

Observação

Os backups de log nos diferentes bancos de dados podem ser criados independentemente uns dos outros e não precisam ser simultâneos.

A recuperação de bancos de dados relacionados nos cenários a seguir requer que você já tenha marcado transações em todos os bancos de dados relacionados:

  • Um ou mais logs de transações são destruídos. Você precisa restaurar o conjunto de bancos de dados para um estado consistente no momento do último backup de log.

  • Você precisa restaurar todo o conjunto de bancos de dados para um estado mutuamente consistente em algum momento anterior.

Importante

Você pode recuperar bases de dados relacionadas apenas para uma transação marcada, não para um momento específico.

Para obter informações sobre como criar transações de marcação, consulte "Criando as transações marcadas", mais adiante neste tópico.

Cenário típico para usar transações marcadas

Um cenário típico para usar transações marcadas inclui as seguintes etapas:

  1. Crie um backup de banco de dados completo ou diferencial de cada um dos bancos de dados relacionados.

  2. Marque um bloco de transação em todos os bancos de dados.

  3. Faça backup do log de transações para todos os bancos de dados.

  4. Restaure backups de banco de dados COM NORECOVERY.

  5. Restaure os logs COM STOPATMARK.

Considerações sobre o uso de transações marcadas

Antes de inserir marcas nomeadas no log de transações, considere o seguinte:

  • Como as marcas de transação consomem espaço de log, use-as apenas para transações que desempenham um papel significativo na estratégia de recuperação do banco de dados.

  • Depois que uma transação marcada é confirmada, uma linha é inserida na tabela logmarkhistory em msdb.

  • Se uma transação marcada abranger vários bancos de dados no mesmo servidor de banco de dados ou em servidores diferentes, as marcas deverão ser registradas nos logs de todos os bancos de dados afetados.

Criando as transações marcadas

Para criar uma transação marcada, use a instrução BEGIN TRANSACTION e a cláusula WITH MARK [description]. A descrição opcional é uma descrição textual da marca. É necessário um nome de marca para a transação. Um nome de marca pode ser reutilizado. O log de transações registra o nome da marca, a descrição, o banco de dados, o usuário, as informações de data/hora e o número de sequência do log (LSN). As informações de data/hora são usadas junto com o nome da marca para identificar exclusivamente a marca.

Para criar transações marcadas em um conjunto de bancos de dados:

  1. Nomeie a transação na instrução BEGIN TRAN e use a cláusula WITH MARK

    Você pode aninhar a instrução BEGIN TRAN new_mark_name WITH MARK dentro de uma transação existente. O valor de new_mark_name é o nome da marca para a transação, mesmo que a transação possua um nome de transação.

    Observação

    Se você emitir um segundo aninhado BEGIN TRAN... COM MARK, essa instrução é ignorada, mas causa uma mensagem de aviso.

  2. Execute uma atualização em todos os bancos de dados do conjunto.

    A marca para uma transação específica é inserida nos logs de transações somente na instância do servidor onde a instrução BEGIN TRAN...WITH MARK é executada. A marca de transação é colocada no log de transações de cada banco de dados atualizado pela transação marcada nessa instância do servidor. Se os bancos de dados residirem em instâncias de servidor diferentes, marcas idênticas deverão ser criadas em cada uma das instâncias do servidor.

Exemplos

O exemplo a seguir restaura o log de transações para a marca na transação marcada chamada ListPriceUpdate.

USE AdventureWorks2022;
GO  
BEGIN TRANSACTION ListPriceUpdate  
   WITH MARK 'UPDATE Product list prices';  
GO  
  
UPDATE Production.Product  
   SET ListPrice = ListPrice * 1.10  
   WHERE ProductNumber LIKE 'BK-%';  
GO  
  
COMMIT TRANSACTION ListPriceUpdate;  
GO  
  
-- Time passes. Regular database   
-- and log backups are taken.  
-- An error occurs in the database.  
USE master  
GO  
  
RESTORE DATABASE AdventureWorks  
FROM AdventureWorksBackups  
WITH FILE = 3, NORECOVERY;  
GO  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups   
   WITH FILE = 4,  
   RECOVERY,   
   STOPATMARK = 'UPDATE Product list prices';

Forçando uma marca a espalhar-se para outros servidores

Um nome de marca de transação não é distribuído automaticamente para outro servidor à medida que a transação se espalha lá. Para forçar a marca a propagar-se para os outros servidores, deve ser escrito um procedimento armazenado que contenha uma instrução BEGIN TRAN name WITH MARK. Esse procedimento armazenado deve então ser executado no servidor remoto sob o escopo da transação no servidor de origem.

Por exemplo, considere um banco de dados particionado que existe em várias instâncias do SQL Server. Em cada instância há um banco de dados chamado coyote. Primeiro, em cada banco de dados, crie um procedimento armazenado, por exemplo, sp_SetMark.

CREATE PROCEDURE sp_SetMark  
@name nvarchar (128)  
AS  
BEGIN TRANSACTION @name WITH MARK  
UPDATE coyote.dbo.Marks SET one = 1  
COMMIT TRANSACTION;  
GO

Em seguida, crie o procedimento armazenado sp_MarkAll contendo uma transação que coloque uma marca em cada banco de dados. sp_MarkAll pode ser executado a partir de qualquer uma das instâncias.

CREATE PROCEDURE sp_MarkAll  
@name nvarchar (128)  
AS  
BEGIN TRANSACTION  
EXEC instance0.coyote.dbo.sp_SetMark @name  
EXEC instance1.coyote.dbo.sp_SetMark @name  
EXEC instance2.coyote.dbo.sp_SetMark @name  
COMMIT TRANSACTION;  
GO

Two-Phase Comprometer-se

A confirmação de uma transação distribuída ocorre em duas fases: preparar e confirmar. Quando uma transação marcada é confirmada, o registro de log de confirmação para cada banco de dados na transação marcada é colocado no log em um ponto onde não há transações duvidosas em nenhum dos logs. Neste ponto, é garantido que não há transações que aparecem como confirmadas em um log, mas não confirmadas em outro log.

As etapas a seguir realizam isso durante a confirmação de uma transação marcada:

  1. A fase de preparação de uma transação de marcação paralisa todas as novas preparações e confirmações.

  2. Apenas as confirmações de transações que já foram preparadas têm permissão para continuar.

  3. Marcar a transação e, em seguida, aguardar que todas as transações preparadas sejam completadas (com limite de tempo).

  4. A transação marcada é preparada e comprometida.

  5. A paralisação de novas preparações e compromissos é removida.

As interrupções geradas por transações marcadas que abrangem vários bancos de dados podem reduzir o desempenho de processamento de transações do servidor.

Recomendamos que você não execute transações marcadas simultâneas. É raro, mas possível, que a confirmação de uma transação marcada distribuída fique bloqueada com outras transações marcadas distribuídas que estão sendo cometidas ao mesmo tempo. Quando isso acontece, a transação de marcação é escolhida como a vítima do impasse e é anulada. Quando esse erro ocorre, o aplicativo pode tentar novamente a transação marcada. Quando várias transações marcadas tentam confirmar simultaneamente, há uma maior probabilidade de impasse.

Recuperando de uma transação marcada

Para obter informações sobre como recuperar um banco de dados que contém transações marcadas até ou imediatamente antes de uma marca específica, consulte Recuperação de Bancos de Dados Relacionados que Contêm Transações Marcadas.

Ver também

INICIAR TRANSAÇÃO DISTRIBUÍDA (Transact-SQL)
Backup e restauração de bancos de dados do sistema (SQL Server)
INICIAR TRANSAÇÃO (Transact-SQL)
Aplicar backups de log de transações (SQL Server)
Backups Completos de Banco de Dados (SQL Server)
RESTORE (Transact-SQL)
Recuperação de bancos de dados relacionados que contêm transações marcadas