Utiliser les transactions marquées pour récupérer des bases de données associées uniformémentUse Marked Transactions to Recover Related Databases Consistently

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Cette rubrique s'applique uniquement aux bases de données SQL ServerSQL Server employant le mode de récupération complète ou le mode de récupération utilisant les journaux de transactions.This topic is relevant only for SQL ServerSQL Server databases that are using the full or bulk-logged recovery models.

Quand vous effectuez des mises à jour associées vers 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.When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. Cependant, cette récupération perd les transactions validées après la marque utilisée comme point de récupération.However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. 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.Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

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.Routinely marking related transactions in every related database establishes a series of common recovery points in the databases. Les marques de transaction sont enregistrées dans le journal des transactions et incluses dans les sauvegardes du journal.The transaction marks are recorded in the transaction log and included in log backups. 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.In the event of a disaster, you can restore each of the databases to the same transaction mark to recover them to a consistent point.

Notes

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.Log backups on the different databases can be created independently of each other and do not have to be simultaneous.

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 :Recovering related databases in the following scenarios requires that you have already marked transactions in every related database:

  • Un ou plusieurs journaux des transactions sont détruits.One or more transaction logs are destroyed. Vous devez restaurer l’ensemble des bases de données dans un état cohérent au moment de votre dernière sauvegarde de journal.You have to restore the set of databases to a consistent state at the time of your last log backup.

  • Vous devez restaurer la totalité des bases de données dans un état mutuellement cohérent correspondant à un moment antérieur dans le temps.You have to restore the entire set of databases to a mutually consistent state at some earlier point in time.

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.You can recover related databases only to a marked transaction, not to a specific point in time.

Pour des informations sur la création des marquages de transactions, consultez « Création des transactions marquées » plus loin dans cette rubrique.For information about how to create marking transactions, see "Creating the Marked Transactions," later in this topic.

Scénario classique d'utilisation des transactions marquéesTypical Scenario for Using Marked Transactions

Ce scénario inclut les procédures suivantes :A typical scenario for using marked transactions includes the following steps:

  1. la création d'une sauvegarde complète ou différentielle de chaque base de données associée ;Create a full or differential database backup of each of the related databases.

  2. le marquage d'un verrou de transaction dans toutes les bases de données ;Mark a transaction block in all the databases.

  3. la sauvegarde du journal des transactions pour toutes les bases de données ;Back up the transaction log for all the databases.

  4. la restauration des sauvegardes de base de données à l'aide de WITH NORECOVERY ;Restore database backups WITH NORECOVERY.

  5. la restauration des journaux à l'aide de WITH STOPATMARK.Restore logs WITH STOPATMARK.

Considérations relatives à l'utilisation des transactions marquéesConsiderations for Using Marked Transactions

Avant d'insérer des marques nommées dans le journal des transactions, prenez en compte les éléments suivants :Before inserting named marks into the transaction log, consider the following:

  • 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 ;Because transaction marks consume log space, use them only for transactions that play a significant role in the database recovery strategy.

  • 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.After a marked transaction commits, a row is inserted in the logmarkhistory table in 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.If a marked transaction spans multiple databases on the same database server or on different servers, the marks must be recorded in the logs of all the affected databases.

Création des transactions marquéesCreating the Marked Transactions

Pour créer une transaction marquée, utilisez l’instruction BEGIN TRANSACTION et la clause WITH MARK [description].To create a marked transaction, use the BEGIN TRANSACTION statement and the WITH MARK [description] clause. La description facultative est une description textuelle de la marque.The optional description is a textual description of the mark. Un nom de marque pour la transaction est requis.A mark name for the transaction is required. Un nom de marque peut être réutilisé.A mark name can be reused. 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).The transaction log records the mark name, description, database, user, datetime information, and the log sequence number (LSN). 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.The datetime information is used along with the mark name to uniquely identify the mark.

Pour créer des transactions marquées dans un jeu de bases de données :To create marked transactions in a set of databases:

  1. Nommez la transaction dans l'instruction BEGIN TRAN et utilisez la clause WITH MARK.Name the transaction in the BEGIN TRAN statement and use the WITH MARK clause

    Vous pouvez imbriquer l’instruction BEGIN TRAN nom_nouvelle_marque WITH MARK au sein d’une transaction existante.You can nest the statement BEGIN TRAN new_mark_name WITH MARK within an existing transaction. La valeur de nom_nouvelle_marque correspond au nom de la marque de la transaction, même si la transaction a un nom de transaction.The value of new_mark_name is the mark name for the transaction, even if the transaction possesses a transaction name.

    Notes

    Si vous émettez une deuxième instruction BEGIN TRAN...WITH MARK, celle-ci est ignorée mais génère un message d'avertissement.If you issue a second nested BEGIN TRAN...WITH MARK, that statement is skipped but causes a warning message.

  2. Exécutez une mise à jour sur toutes les bases de données dans le jeu.Run an update against all of the databases in the set.

    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.The mark for a specific transaction is inserted into transaction logs only on the server instance where the BEGIN TRAN...WITH MARK statement is executed. 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.The transaction mark is placed in the transaction log of every database updated by the marked transaction on that server instance. 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.If the databases reside on different server instances, identical marks must be created on each of the server instances.

ExemplesExamples

L'exemple suivant restaure le journal des transactions jusqu'à la marque dans la transaction marquée nommée ListPriceUpdate.The following example restores the transaction log to the mark in the marked transaction named 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 serveursForcing a Mark to Spread to Other Servers

Le nom d'une marque de transaction n’est pas automatiquement distribué à un autre serveur au moment où la transaction atteint ce dernier.A transaction mark name is not automatically distributed to another server as the transaction spreads there. Pour forcer la distribution de la marque aux autres serveurs, vous devez écrire une procédure stockée contenant une instruction BEGIN TRAN nom WITH MARK.To force the mark to spread to the other servers, a stored procedure must be written that contains a BEGIN TRAN name WITH MARK statement. 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.That stored procedure must then be executed on the remote server under the scope of the transaction in the originating server.

Supposons, par exemple, qu'une base de données partitionnée existe sur plusieurs instances de SQL ServerSQL Server.For example, consider a partitioned database that exists on multiple instances of SQL ServerSQL Server. Sur chaque instance se trouve une base de données nommée coyote.On each instance is a database named coyote. Créez d’abord une procédure stockée, par exemple sp_SetMark, dans chaque base de données.First, in every database, create a stored procedure, for example, 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 stockée sp_MarkAll contenant une transaction qui place une marque dans chaque base de données.Next, create stored procedure sp_MarkAll containing a transaction that places a mark in every database. sp_MarkAll peut être exécutée à partir de n’importe quelle instance.sp_MarkAll can be run from any of the 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 en deux tempsTwo-Phase Commit

La validation d'une transaction distribuée s'effectue en deux étapes : préparation et validation.Committing a distributed transaction occurs in two phases: prepare and commit. 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.When a marked transaction is committed, the commit log record for each database in the marked transaction is placed in the log at a point where there are no in-doubt transactions in any of the logs. À 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.At this point, it is guaranteed that there are no transactions that appear as committed in one log, but not committed in another log.

Les étapes suivantes accomplissent cela lors de la validation d’une transaction marquée :The following steps accomplish this during the commit of a marked transaction:

  1. La phase de préparation d’une transaction marquée bloque toutes les nouvelles préparations et validations.Prepare phase of a marking transaction stalls all new prepares and commits.

  2. Seules les validations de transactions déjà préparées sont autorisées à continuer.Only commits of already prepared transactions are allowed to continue.

  3. Le marquage des transactions attend ensuite toutes les transactions préparées à traiter (avec un délai d’attente).Marking transaction then waits for all prepared transactions to drain (with time-out).

  4. La transaction marquée est préparée puis validée.Marked transaction is prepared and committed.

  5. Le blocage des nouvelles préparations et validations est supprimé.The stall of new prepares and commits is removed.

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.The stalls generated by marked transactions that span multiple databases can reduce the transaction processing performance of the server.

Il est recommandé de ne pas exécuter simultanément des transactions marquées.We recommend that you do not run concurrent marked transactions. 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.It is rare but possible for the commit of a distributed marked transaction to deadlock with other distributed marked transactions that are committing at the same time. Dans ce cas, la transaction marquée est choisie comme victime du blocage et est annulée.When this happens, the marking transaction is chosen as the deadlock victim and is rolled back. Lorsque cette erreur se produit, l’application peut tester à nouveau la transaction marquée.When this error occurs, the application can retry the marked transaction. Lorsque plusieurs transactions marquées tentent une validation simultanée, l’éventualité d’un verrouillage est plus élevée.When multiple marked transactions try to commit concurrently, there is a higher probability of deadlock.

Récupération jusqu'à une transaction marquéeRecovering to a Marked Transaction

Pour plus d’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.For information about how to recover a database that contains marked transactions to or just before a particular mark, see Recovery of Related Databases That Contain Marked Transaction.

Voir aussiSee Also

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL) BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
Sauvegarder et restaurer des bases de données système (SQL Server) Back Up and Restore of System Databases (SQL Server)
BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
Appliquer les sauvegardes du journal des transactions (SQL Server) Apply Transaction Log Backups (SQL Server)
Sauvegardes complètes de bases de données (SQL Server) Full Database Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Récupération de bases de données associées contenant une transaction marquéeRecovery of Related Databases That Contain Marked Transaction