Usar transações marcadas para recuperar bancos de dados relacionados consistentemente (modelo de recuperação completa)

Este tópico é relevante apenas para bancos de dados do SQL Server que estão usando os modelos de recuperação completos ou em massa registrados.

Ao fazer atualizações relacionadas a dois ou mais bancos de dados , bancos de dados relacionados, você pode usar marcas de transação para recuperá-las em um ponto logicamente consistente. No entanto, essa recuperação perde qualquer transação confirmada após a marca usada como ponto de recuperação. Marcar transações é adequado somente quando você estiver testando bancos de dados relacionados ou quando estiver disposto a perder transações confirmadas recentemente.

Marcar transações relacionadas rotineiramente 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 registro de transações e incluídas nas cópias de segurança do 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 em um ponto consistente.

A recuperação de bancos de dados relacionados nos seguintes cenários 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.

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ções em todos os bancos de dados.

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

4. Restaurar backups de banco de dados COM NORECOVERY.

5. Restaurar logs usando o comando WITH STOPATMARK.

Considerações sobre como usar 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 de banco de dados.

• Depois que uma transação marcada é confirmada, uma linha é inserida na tabela logmarkhistory no 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. Um nome de marca para a transação é necessário. 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 datetime e o LSN (número de sequência de log). As informações de data e hora são usadas junto com o nome da marca para distinguir a marca de forma única.

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

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

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

   Observação

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

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

   A marca de 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, as 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 até a marca na transação identificada como ListPriceUpdate.

USE AdventureWorks  
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 = 'ListPriceUpdate';  

Forçando uma marca a se espalhar 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 por lá. Para forçar a marca a se espalhar para os outros servidores, um procedimento armazenado deve ser escrito que contenha uma instrução BEGIN TRAN nome WITH MARK. Esse procedimento armazenado deve ser executado no servidor remoto no 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 um procedimento sp_MarkAll armazenado contendo uma transação que coloque uma marca em cada banco de dados. sp_MarkAll pode ser executado em 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  

Confirmação de Two-Phase

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 em que não há transações em dúvida em nenhum dos logs. Neste ponto, é garantido que não há transações que apareçam como confirmadas em um log, mas não confirmadas em outro log.

As seguintes etapas fazem isso durante a confirmação de uma transação marcada:

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

2. Somente confirmações de transações já preparadas têm permissão para continuar.

3. Marcar transação então aguarda que todas as transações preparadas sejam esgotadas (com tempo limite).

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

5. A barra de novas preparações e confirmações é removida.

As paradas 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 distribuída marcada entre em deadlock com outras transações distribuídas marcadas que estão sendo confirmadas ao mesmo tempo. Quando isso acontece, a transação de marcação é escolhida como vítima de deadlock e é revertida. Quando esse erro ocorre, o aplicativo pode repetir a transação marcada. Quando várias transações sinalizadas tentam confirmar ao mesmo tempo, há uma probabilidade maior de deadlock.

Recuperação de uma transação marcada

Para obter informações sobre como recuperar um banco de dados que contém transações marcadas para ou pouco antes de uma marca específica, consulte Recuperação de bancos de dados relacionados que contêm transação marcada.

Consulte Também

INICIAR TRANSAÇÃO DISTRIBUÍDA (Transact-SQL)
Fazer backup e restaurar bancos de dados do sistema (SQL Server)
BEGIN TRANSACTION (Transact-SQL)
Aplicar backups de log de transações (SQL Server)
Backups completos do banco de dados (SQL Server)
RESTORE (Transact-SQL)
Recuperação de bancos de dados relacionados que contêm transação marcada