Utiliser les transactions marquées pour récupérer des bases de données associées uniformément (mode de récupération complète)

Article
06/25/2013

Cette rubrique s'applique uniquement aux bases de données SQL Server employant le mode de récupération complète ou le mode de récupération utilisant les journaux de transactions.

Lorsque vous effectuez des mises à jour associées vers deux ou plusieurs bases de données (bases de données associées), vous pouvez utiliser des marques de transaction pour les récupérer à un point cohérent d'un point de vue logique. Cependant, cette récupération perd les transactions validées après la marque utilisée comme point de récupération. Le marquage des transactions est adapté uniquement au test des bases de données associées ou si vous êtes prêt à risquer la perte des transactions validées récemment.

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

[!REMARQUE]

Les sauvegardes du journal sur les différentes bases de données peuvent être créées indépendamment et ne doivent pas être simultanées.

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

Un ou plusieurs journaux des transactions sont détruits. Vous devez restaurer l’ensemble des bases de données dans un état cohérent au moment de votre dernière sauvegarde de journal.
Vous devez restaurer la totalité des bases de données dans un état mutuellement cohérent correspondant à un moment antérieur dans le temps.

Important

Vous ne pouvez récupérer des bases de données associées qu'en fonction d'une transaction marquée, et non d'un moment précis.

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

Scénario classique d'utilisation des transactions marquées

Ce scénario inclut les procédures suivantes :

la création d'une sauvegarde complète ou différentielle de chaque base de données associée ;
le marquage d'un verrou de transaction dans toutes les bases de données ;
la sauvegarde du journal des transactions pour toutes les bases de données ;
la restauration des sauvegardes de base de données à l'aide de WITH NORECOVERY ;
la restauration des journaux à l'aide de WITH STOPATMARK.

Considérations relatives à l'utilisation des transactions marquées

Avant d'insérer des marques nommées dans le journal des transactions, prenez en compte les éléments suivants :

les marques de transaction occupant de l'espace dans le journal, utilisez-les seulement pour les transactions ayant un rôle significatif dans la stratégie de récupération de la base de données ;
Après la validation d'une transaction marquée, une ligne est insérée dans la table logmarkhistory de la base de données msdb.
Si une transaction marquée s'étend à plusieurs bases de données sur le même serveur de bases de données ou sur différents serveurs, les marques doivent être enregistrées dans les journaux de toutes les bases de données concerné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 de la marque, la description, la base de données, l'utilisateur, la date et l'heure, et le numéro séquentiel dans le journal (LSN, Log Sequence Number). Les informations de date et d'heure sont utilisées conjointement avec le nom de la marque pour identifier celle-ci de façon univoque.

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

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 au sein d'une transaction existante. La valeur de new_mark_name correspond au nom de la marque de la transaction, même si la transaction possède un nom de transaction.

[!REMARQUE]

Si vous émettez une deuxième instruction BEGIN TRAN...WITH MARK, celle-ci est ignorée mais génère un message d'avertissement.
Exécutez une mise à jour sur toutes les bases de données dans le jeu.

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

Exemples

L'exemple suivant restaure le journal des transactions jusqu'à 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';

Distribution forcée d'une marque à d'autres serveurs

Le nom d'une marque de transaction n’est pas automatiquement distribué à un autre serveur au moment où la transaction atteint ce dernier. Afin de forcer la distribution de la marque aux autres serveurs, il convient d'é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 en fonction de la portée de la transaction dans le serveur d'origine.

Supposons, par exemple, qu'une base de données partitionnée existe sur plusieurs instances de SQL Server. Sur chaque instance se trouve une base de données nommée coyote. Créez d’abord une procédure stockée, par exemple sp_SetMark, dans chaque base de données.

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 stockée sp_MarkAll contenant une transaction qui place une marque dans chaque base de données. sp_MarkAll peut être exécutée à partir de n'importe quelle instance.

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 en deux temps

La validation d'une transaction distribuée s'effectue en deux étapes : préparation et validation. Lorsqu’une transaction marquée est validée, l’enregistrement du journal de validation pour chaque base de données dans la transaction marquée est placé dans le journal à un point où il n’existe aucun doute concernant les transactions dans les journaux. À partir de ce point, il est garanti qu’il n’existe aucune transaction qui apparaît comme validée dans un journal, mais pas dans un autre.

Les étapes suivantes accomplissent cela lors de la validation d’une transaction marquée :

La phase de préparation d’une transaction marquée bloque toutes les nouvelles préparations et validations.
Seules les validations de transactions déjà préparées sont autorisées à continuer.
Le marquage des transactions attend ensuite toutes les transactions préparées à traiter (avec un délai d’attente).
La transaction marquée est préparée puis validée.
Le blocage des nouvelles préparations et validations est supprimé.

Ces blocages générés par des transactions marquées qui s’étendent à plusieurs bases de données peuvent atténuer les performances de traitement de transactions sur le serveur.

Il est recommandé de ne pas exécuter simultanément des transactions marquées. Il est rare mais possible qu’une transaction marquée distribuée en cours de validation se bloque avec d’autres transactions analogues également en cours de validation. Dans ce cas, la transaction marquée est choisie comme victime du blocage et est annulée. Lorsque cette erreur se produit, l’application peut tester à nouveau la transaction marquée. Lorsque plusieurs transactions marquées tentent une validation simultanée, l’éventualité d’un verrouillage est plus élevée.

Récupération jusqu'à une transaction marquée

Pour des informations sur la manière de récupérer une base de données qui contient des transactions marquées jusqu'à une marque particulière ou juste avant, consultez Récupération de bases de données associées contenant une transaction marquée.