Uso de transacciones marcadas para recuperar bases de datos relacionadas de forma coherente (modelo de recuperación completa)

Este tema solo es relevante para las bases de datos de SQL Server que usan los modelos de recuperación completos o optimizados para cargas masivas de registros.

Al realizar actualizaciones relacionadas con dos o más bases de datos, bases de datos relacionadas, puede usar marcas de transacción para recuperarlas hasta un punto lógicamente coherente. Sin embargo, esta recuperación pierde cualquier transacción que se haya confirmado después de la marca utilizada como punto de recuperación. El marcado de transacciones solo es adecuado cuando se prueban bases de datos relacionadas o cuando está dispuesto a perder transacciones confirmadas recientemente.

Marcar de forma rutinaria las transacciones relacionadas en cada base de datos relacionada establece una serie de puntos de recuperación comunes en las bases de datos. Las marcas de transacción se registran en el registro de transacciones y se incluyen en las copias de seguridad de registros. En caso de desastre, puede restaurar cada una de las bases de datos en la misma marca de transacción para recuperarlas en un punto coherente.

La recuperación de bases de datos relacionadas en los escenarios siguientes requiere que ya haya marcado transacciones en todas las bases de datos relacionadas:

• Se destruyen uno o varios registros de transacciones. Debes restaurar el conjunto de bases de datos a un estado coherente en el momento de tu última copia de seguridad del registro.

• Tiene que restaurar todo el conjunto de bases de datos a un estado mutuamente coherente en algún momento anterior.

Para obtener información sobre cómo crear transacciones de marcado, vea "Creación de transacciones marcadas", más adelante en este tema.

Escenario típico para usar transacciones marcadas

Un escenario típico para usar transacciones marcadas incluye los pasos siguientes:

1. Cree una copia de seguridad de base de datos completa o diferencial de cada una de las bases de datos relacionadas.

2. Marque un bloque de transacción en todas las bases de datos.

3. Realice una copia de seguridad del registro de transacciones para todas las bases de datos.

4. Restaurar copias de seguridad de base de datos WITH NORECOVERY.

5. Restaure los registros CON STOPATMARK.

Consideraciones para usar transacciones marcadas

Antes de insertar marcas con nombre en el registro de transacciones, tenga en cuenta lo siguiente:

• Dado que las marcas de transacción consumen espacio de registro, úselas solo para las transacciones que desempeñan un papel importante en la estrategia de recuperación de la base de datos.

• Después de confirmar una transacción marcada, se inserta una fila en la tabla logmarkhistory en msdb.

• Si una transacción marcada abarca varias bases de datos en el mismo servidor de bases de datos o en servidores diferentes, las marcas deben registrarse en los registros de todas las bases de datos afectadas.

Creación de las transacciones marcadas

Para crear una transacción marcada, use la instrucción BEGIN TRANSACTION y la cláusula WITH MARK [description]. La descripción opcional es una descripción textual de la marca. Se requiere un nombre de marca para la transacción. Se puede reutilizar un nombre de marca. El registro de transacciones registra el nombre de marca, la descripción, la base de datos, el usuario, la información de fecha y hora y el número de secuencia de registro (LSN). La información de fecha y hora se usa junto con el nombre de marca para identificar de forma única la marca.

Para crear transacciones marcadas en un conjunto de bases de datos:

1. Asigne un nombre a la transacción en la instrucción BEGIN TRAN y use la cláusula WITH MARK.

   Puede anidar el comando BEGIN TRAN new_mark_name WITH MARK dentro de una transacción existente. El valor de new_mark_name es el nombre de marca de la transacción, incluso si la transacción posee un nombre de transacción.

   Nota:

   Si ejecuta un segundo BEGIN TRAN anidado... WITH MARK, esa instrucción se omite pero provoca un mensaje de advertencia.

2. Ejecute una actualización en todas las bases de datos del conjunto.

   La marca de una transacción específica se inserta en los registros de transacciones solo en la instancia del servidor donde se ejecuta la instrucción BEGIN TRAN...WITH MARK. La marca de transacción se coloca en el registro de transacciones de cada base de datos actualizada por la transacción marcada en esa instancia del servidor. Si las bases de datos residen en instancias de servidor diferentes, se deben crear marcas idénticas en cada una de las instancias del servidor.

Ejemplos

En el ejemplo siguiente se restaura el registro de transacciones a la marca de la transacción marcada denominada 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';  

Forzar la propagación de una marca a otros servidores

Un nombre de marca de transacción no se distribuye automáticamente a otro servidor, ya que la transacción se distribuye allí. Para forzar la propagación de la marca a los demás servidores, se debe escribir un procedimiento almacenado que contenga una sentencia BEGIN TRAN name WITH MARK. Ese procedimiento almacenado debe ejecutarse en el servidor remoto bajo el ámbito de la transacción en el servidor de origen.

Por ejemplo, considere una base de datos con particiones que existe en varias instancias de SQL Server. En cada instancia hay una base de datos denominada coyote. En primer lugar, en cada base de datos, cree un procedimiento almacenado, por ejemplo, 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  

A continuación, cree un procedimiento sp_MarkAll almacenado que contenga una transacción que coloque una marca en cada base de datos. sp_MarkAll se puede ejecutar desde cualquiera de las instancias.

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  

confirmación de Two-Phase

La confirmación de una transacción distribuida se produce en dos fases: preparación y confirmación. Cuando se confirma una transacción marcada, el registro de registro de confirmación de cada base de datos de la transacción marcada se coloca en el registro en un punto en el que no hay transacciones dudosas en ninguno de los registros. En este momento, se garantiza que no haya transacciones que aparezcan como confirmadas en un registro, pero que no se confirmen en otro registro.

Los pasos siguientes se realizan durante la confirmación de una transacción marcada:

1. La fase de preparación de una transacción de marcado detiene todas las nuevas preparacións y confirmaciones.

2. Solo se permiten continuar confirmaciones de transacciones ya preparadas.

3. Marcando la transacción, luego espera a que todas las transacciones preparadas se completen (con límite de tiempo).

4. La transacción marcada está preparada y confirmada.

5. Se elimina la paralización de nuevas preparaciones y confirmaciones de código.

Los bloqueos generados por transacciones marcadas que abarcan varias bases de datos pueden reducir el rendimiento del servidor en el procesamiento de transacciones.

Se recomienda no ejecutar transacciones marcadas simultáneas. Es poco frecuente, pero la confirmación de una transacción marcada distribuida puede interbloquearse con otras que se confirman al mismo tiempo. Cuando esto sucede, la transacción señalada se elige como víctima del interbloqueo y se revierte. Cuando se produce este error, la aplicación puede reintentar la transacción marcada. Cuando varias transacciones marcadas intentan confirmarse simultáneamente, hay una mayor probabilidad de interbloqueo.

Recuperación a una transacción marcada

Para obtener información sobre cómo recuperar una base de datos que contiene transacciones marcadas en o justo antes de una marca determinada, vea Recuperación de bases de datos relacionadas que contienen transacciones marcadas.

Véase también

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
Realizar copias de seguridad y restaurar bases de datos del sistema (SQL Server)
BEGIN TRANSACTION (Transact-SQL)
Aplicar copias de seguridad de registros de transacción (SQL Server)
Copias de seguridad completas de bases de datos (SQL Server)
RESTORE (Transact-SQL)
Recuperación de bases de datos relacionadas que contienen transacciones marcadas