Utiliser des transactions marquées pour récupérer des bases de données associées de manière cohérente (modèle de récupération complète)

Cette rubrique s’applique uniquement aux bases de données SQL Server qui utilisent les modèles de récupération complètes ou journalisées en bloc.

Lorsque vous effectuez des mises à jour connexes de deux ou plusieurs bases de données, vous pouvez utiliser des marques de transaction pour les récupérer à un point cohérent. Toutefois, cette récupération perd toute transaction validée après la marque utilisée comme point de récupération. Le marquage des transactions ne convient que lorsque vous testez des bases de données associées ou lorsque vous êtes prêt à perdre des transactions récemment validées.

Le marquage systématique des transactions associées dans chaque base de données associée établit une série de points de récupération courants dans les bases de données. Les marques de transaction sont enregistrées dans le journal des transactions et incluses dans les sauvegardes de journal. En cas de sinistre, vous pouvez restaurer chacune des bases de données sur la même marque de transaction pour les récupérer à un point cohérent.

La récupération des bases de données associées dans les scénarios suivants nécessite que vous ayez déjà marqué des transactions dans chaque base de données associée :

• Un ou plusieurs journaux de transactions sont détruits. Vous devez restaurer l’ensemble de bases de données dans un état cohérent au moment de votre dernière sauvegarde de journal.

• Vous devez restaurer l’ensemble des bases de données dans un état mutuellement cohérent à un moment donné.

Pour plus d’informations sur la création de transactions de marquage, consultez « Création des transactions marquées », plus loin dans cette rubrique.

Scénario classique pour l’utilisation de transactions marquées

Un scénario classique pour l’utilisation de transactions marquées comprend les étapes suivantes :

1. Créez une sauvegarde complète ou différentielle de base de données de chacune des bases de données associées.

2. Marquez un bloc de transaction dans toutes les bases de données.

3. Sauvegardez le journal des transactions pour toutes les bases de données.

4. Restaurez les sauvegardes de base de données WITH NORECOVERY.

5. Restaurez les journaux AVEC STOPATMARK.

Considérations relatives à l’utilisation de transactions marquées

Avant d’insérer des marques nommées dans le journal des transactions, tenez compte des éléments suivants :

• Étant donné que les marques de transaction consomment de l’espace journal, utilisez-les uniquement pour les transactions qui jouent un rôle significatif dans la stratégie de récupération de base de données.

• Une fois qu’une transaction marquée a été validée, une ligne est insérée dans la table logmarkhistory dans msdb.

• Si une transaction marquée s’étend sur plusieurs bases de données sur le même serveur de base de données ou sur différents serveurs, les marques doivent être enregistrées dans les journaux de toutes les bases de données affectées.

Création des transactions marquées

Pour créer une transaction marquée, utilisez l’instruction BEGIN TRANSACTION et la clause WITH MARK [description]. La description facultative est une description textuelle de la marque. Un nom de marque pour la transaction est requis. Un nom de marque peut être réutilisé. Le journal des transactions enregistre le nom, la description, la base de données, l’utilisateur, les informations datetime et le numéro de séquence de journal (LSN). Les informations datetime sont utilisées avec le nom de marque pour identifier de manière unique la marque.

Pour créer des transactions marquées dans un ensemble de bases de données :

1. Nommez la transaction dans l’instruction BEGIN TRAN et utilisez la clause WITH MARK

   Vous pouvez imbriquer l’instruction BEGIN TRAN new_mark_name WITH MARK dans une transaction existante. La valeur de new_mark_name est le nom de marque de la transaction, même si la transaction possède un nom de transaction.

   Remarque

   Si vous effectuez une deuxième opération BEGIN TRAN imbriquée, WITH MARK, cette instruction est ignorée, mais provoque un message d’avertissement.

2. Exécutez une mise à jour sur toutes les bases de données de l’ensemble.

   La marque d’une transaction spécifique est insérée dans les journaux des transactions uniquement sur l’instance de serveur où BEGIN TRAN... L’instruction WITH MARK est exécutée. La marque de transaction est placée dans le journal des transactions de chaque base de données mise à jour par la transaction marquée sur cette instance de serveur. Si les bases de données résident sur différentes instances de serveur, des marques identiques doivent être créées sur chacune des instances de serveur.

Exemples

L’exemple suivant restaure le journal des transactions sur la marque dans la transaction marquée nommée 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';  

Forcer la propagation d’une marque à d’autres serveurs

Un nom de marque de transaction n’est pas automatiquement distribué à un autre serveur, car la transaction se répartit là-bas. Pour forcer la propagation de la marque aux autres serveurs, il faut écrire une procédure stockée contenant une instruction BEGIN TRAN name WITH MARK. Cette procédure stockée doit ensuite être exécutée sur le serveur distant sous l’étendue de la transaction dans le serveur d’origine.

Par exemple, considérez une base de données partitionnée qui existe sur plusieurs instances de SQL Server. Sur chaque instance est une base de données nommée coyote. Tout d’abord, dans chaque base de données, créez une procédure stockée, par exemple 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  

Ensuite, créez une procédure sp_MarkAll stockée contenant une transaction qui place une marque dans chaque base de données. sp_MarkAll peut être exécuté à partir de l’une des instances.

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  

validation Two-Phase

La validation d’une transaction distribuée se produit en deux phases : préparer et valider. Lorsqu’une transaction marquée est validée, l’enregistrement du journal de validation pour chaque base de données de cette transaction est placé dans le journal à un moment où il n’y a pas de transactions incertaines dans aucun des journaux. À ce stade, il est garanti qu’il n’y a pas de transactions qui apparaissent comme validées dans un journal, mais pas validées dans un autre journal.

Les étapes suivantes effectuent cette opération pendant la validation d’une transaction marquée :

1. La phase de préparation d’une transaction de marquage bloque toutes les nouvelles préparations et validations.

2. Seuls les engagements des transactions déjà préparées peuvent continuer.

3. La transaction marquée attend ensuite que toutes les transactions préparées soient vidées (avec délai d'attente).

4. La transaction marquée est préparée et validée.

5. Le blocage des nouvelles préparations et validations est supprimé.

Les blocages générés par des transactions marquées qui s’étendent sur plusieurs bases de données peuvent réduire les performances de traitement des transactions du serveur.

Nous vous recommandons de ne pas exécuter de transactions marquées simultanées. Il est rare mais possible que la validation d’une transaction marquée distribuée soit bloquée avec d’autres transactions marquées distribuées qui s’exécutent en même temps. Lorsque cela se produit, la transaction de marquage est choisie comme victime d'interblocage et est annulée. Lorsque cette erreur se produit, l’application peut réessayer la transaction marquée. Lorsque plusieurs transactions marquées tentent de valider simultanément, il existe une probabilité plus élevée d’interblocage.

Récupération vers une transaction marquée

Pour plus d’informations sur la récupération d’une base de données contenant des transactions marquées vers ou juste avant une marque particulière, consultez Récupération des bases de données associées contenant une transaction marquée.

Voir aussi

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
Sauvegarder et restaurer des bases de données système (SQL Server)
BEGIN TRANSACTION (Transact-SQL)
Appliquer des sauvegardes de journal des transactions (SQL Server)
Sauvegardes complètes de base de données (SQL Server)
RESTORE (Transact-SQL)
Récupération des bases de données associées qui contiennent une transaction marquée