Usare transazioni contrassegnate per recuperare coerentemente i database correlatiUse Marked Transactions to Recover Related Databases Consistently

In questo argomento si applica a: SìSQL ServernonDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Le informazioni contenute in questo argomento sono rilevanti solo per i database di SQL ServerSQL Server che utilizzano i modelli di recupero con registrazione completa o con registrazione minima delle operazioni bulk.This topic is relevant only for SQL ServerSQL Server databases that are using the full or bulk-logged recovery models.

Quando si eseguono aggiornamenti in due o più database correlati, è possibile usare i contrassegni delle transazioni per recuperarli fino a un punto consistente logico.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. In questo recupero viene tuttavia persa qualsiasi transazione di cui sia stato eseguito il commit dopo il contrassegno utilizzato come punto di recupero.However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. L'utilizzo del contrassegno delle transazioni è adeguato solo quando si esegue il test di database correlati o quando la perdita delle transazioni di cui sia stato eseguito il commit di recente non è importante.Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

L'applicazione ripetuta di contrassegni alle transazioni correlate in ogni database correlato determina una serie di punti di recupero comuni nei database.Routinely marking related transactions in every related database establishes a series of common recovery points in the databases. I contrassegni delle transazioni vengono registrati nel log delle transazioni e inclusi nei backup del log.The transaction marks are recorded in the transaction log and included in log backups. In caso di emergenza, è possibile ripristinare tutti i database rispetto allo stesso contrassegno della transazione per recuperarli fino a una punto consistente.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.

Nota

I backup del log nei diversi database possono essere creati in modo indipendente gli uni dagli altri e non è necessario che siano simultanei.Log backups on the different databases can be created independently of each other and do not have to be simultaneous.

Il recupero di database correlati negli scenari seguenti richiede che siano già presenti transazioni contrassegnate in ogni database correlato:Recovering related databases in the following scenarios requires that you have already marked transactions in every related database:

  • Uno o più log delle transazioni sono danneggiati.One or more transaction logs are destroyed. È necessario ripristinare il set di database in uno stato consistente al momento dell'ultimo backup del log.You have to restore the set of databases to a consistent state at the time of your last log backup.

  • È necessario ripristinare l'intero set di database in uno stato mutualmente consistente fino a un punto nel tempo precedente.You have to restore the entire set of databases to a mutually consistent state at some earlier point in time.

Importante

È possibile recuperare database correlati solo fino a una transazione contrassegnata e non a un momento specifico.You can recover related databases only to a marked transaction, not to a specific point in time.

Per informazioni su come creare transazioni contrassegnate, vedere "Creazione di transazioni contrassegnate", di seguito in questo argomento.For information about how to create marking transactions, see "Creating the Marked Transactions," later in this topic.

Scenario tipico per l'utilizzo delle transazioni contrassegnateTypical Scenario for Using Marked Transactions

Uno scenario tipico per l'utilizzo delle transazioni contrassegnate include i passaggi seguenti:A typical scenario for using marked transactions includes the following steps:

  1. Creare un backup completo o differenziale del database per ogni database correlato.Create a full or differential database backup of each of the related databases.

  2. Contrassegnare un blocco di transazioni in tutti i database.Mark a transaction block in all the databases.

  3. Eseguire il backup del log delle transazioni per tutti i database.Back up the transaction log for all the databases.

  4. Ripristinare i backup dei database WITH NORECOVERY.Restore database backups WITH NORECOVERY.

  5. Ripristinare i log WITH STOPATMARK.Restore logs WITH STOPATMARK.

Considerazioni sull'utilizzo delle transazioni contrassegnateConsiderations for Using Marked Transactions

Prima di inserire contrassegni denominati nel log delle transazioni, considerare quanto segue:Before inserting named marks into the transaction log, consider the following:

  • I contrassegni di transazione occupano spazio nei log e pertanto è consigliabile utilizzarli esclusivamente per transazioni importanti ai fini della strategia di recupero dei database.Because transaction marks consume log space, use them only for transactions that play a significant role in the database recovery strategy.

  • Dopo il commit di una transazione contrassegnata, viene inserita una riga nella tabella logmarkhistory del database msdb.After a marked transaction commits, a row is inserted in the logmarkhistory table in msdb.

  • Se una transazione contrassegnata si estende su più database dello stesso server di database o di server diversi, i contrassegni devono essere registrati nei log di tutti i database interessati.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.

Creazione delle transazioni contrassegnateCreating the Marked Transactions

Per creare una transazione contrassegnata, usare l'istruzione BEGIN TRANSACTION e la clausola WITH MARK [description].To create a marked transaction, use the BEGIN TRANSACTION statement and the WITH MARK [description] clause. L'argomento description è facoltativo e rappresenta una descrizione del contrassegno.The optional description is a textual description of the mark. Il nome del contrassegno della transazione è obbligatorio.A mark name for the transaction is required. È possibile riutilizzare il nome di un contrassegno.A mark name can be reused. Il log delle transazioni registra il nome del contrassegno, la descrizione, il database, l'utente, le informazioni di data/ora e il numero di sequenza del file di log (LSN).The transaction log records the mark name, description, database, user, datetime information, and the log sequence number (LSN). Le informazioni di data/ora sono utilizzate insieme al nome del contrassegno per identificare il contrassegno in modo univoco.The datetime information is used along with the mark name to uniquely identify the mark.

Per creare transazioni contrassegnate in un set di database:To create marked transactions in a set of databases:

  1. Denominare la transazione nell'istruzione BEGIN TRAN e utilizzare la clausola WITH MARKName the transaction in the BEGIN TRAN statement and use the WITH MARK clause

    È possibile nidificare l'istruzione BEGIN TRAN nuovo_nome_contrassegno WITH MARK all'interno di una transazione esistente.You can nest the statement BEGIN TRAN new_mark_name WITH MARK within an existing transaction. Il valore di nuovo_nome_contrassegno è il nome di contrassegno per la transazione, anche se è dotata di un nome di transazione.The value of new_mark_name is the mark name for the transaction, even if the transaction possesses a transaction name.

    Nota

    Se si esegue una seconda istruzione nidificata BEGIN TRAN...WITH MARK, l'istruzione verrà ignorata ma provocherà un messaggio di avviso.If you issue a second nested BEGIN TRAN...WITH MARK, that statement is skipped but causes a warning message.

  2. Eseguire un aggiornamento di tutti i database nel set.Run an update against all of the databases in the set.

    Il contrassegno per una transazione specifica viene inserito solo nei log delle transazioni dell'istanza del server in cui viene eseguita l'istruzione BEGIN TRAN.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. Il contrassegno della transazione viene inserito nel log delle transazioni di ogni database aggiornato dalla transazione contrassegnata in tale istanza del server.The transaction mark is placed in the transaction log of every database updated by the marked transaction on that server instance. Se il database si trova in istanze del server diverse, è necessario creare contrassegni identici in ogni istanza del server.If the databases reside on different server instances, identical marks must be created on each of the server instances.

EsempiExamples

Nell'esempio seguente viene ripristinato il log delle transazioni fino al contrassegno nella transazione contrassegnata denominata 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';  

Distribuzione forzata di un contrassegno in altri serverForcing a Mark to Spread to Other Servers

Il nome di un contrassegno di transazione non viene distribuito automaticamente in altri server se la transazione viene distribuita.A transaction mark name is not automatically distributed to another server as the transaction spreads there. Per forzare la distribuzione del contrassegno in altri server, è necessario scrivere una stored procedure contenente un'istruzione BEGIN TRAN name 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. Tale stored procedure deve quindi essere eseguita nel server remoto nell'ambito della transazione del server di origine.That stored procedure must then be executed on the remote server under the scope of the transaction in the originating server.

Ad esempio, considerare un database partizionato esistente in più istanze di SQL ServerSQL Server.For example, consider a partitioned database that exists on multiple instances of SQL ServerSQL Server. In ogni istanza è presente un database denominato coyote.On each instance is a database named coyote. Creare innanzitutto una stored procedure in ogni database, ad esempio sp_SetMark.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  

Creare quindi una stored procedure sp_MarkAll contenente una transazione che inserisce un contrassegno in ogni database.Next, create stored procedure sp_MarkAll containing a transaction that places a mark in every database. sp_MarkAll può essere eseguita da qualsiasi istanza.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  

commit in due fasiTwo-Phase Commit

L'esecuzione del commit di una transazione distribuita avviene in due fasi: la preparazione e il commit.Committing a distributed transaction occurs in two phases: prepare and commit. Quando si esegue il commit di una transazione contrassegnata, il record di commit del log per ogni database interessato dalla transazione contrassegnata viene inserito nel log in un punto in cui non sono presenti transazioni in sospeso in nessuno dei log.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. In questo modo, si garantisce che non vi siano transazioni di cui è stato eseguito il commit in un log ma di cui non è stato eseguito il commit in un altro log.At this point, it is guaranteed that there are no transactions that appear as committed in one log, but not committed in another log.

I passaggi seguenti garantiscono questo risultato durante il commit di una transazione contrassegnata:The following steps accomplish this during the commit of a marked transaction:

  1. La fase di preparazione di una transazione contrassegnata blocca tutte le nuove operazioni di preparazione e commit.Prepare phase of a marking transaction stalls all new prepares and commits.

  2. È consentito il proseguimento solo delle operazioni di commit di transazioni già preparate.Only commits of already prepared transactions are allowed to continue.

  3. La transazione contrassegnata attende quindi il completamento di tutte le transazioni preparate (con timeout).Marking transaction then waits for all prepared transactions to drain (with time-out).

  4. La transazione contrassegnata viene preparata e quindi ne viene eseguito il commit.Marked transaction is prepared and committed.

  5. Il blocco di nuove operazioni di preparazione e commit viene annullato.The stall of new prepares and commits is removed.

    I blocchi generati dalle transazioni contrassegnate che si estendono su più database possono rallentare le prestazioni di elaborazione delle transazioni nel server.The stalls generated by marked transactions that span multiple databases can reduce the transaction processing performance of the server.

    È consigliabile non eseguire transazioni contrassegnate simultanee.We recommend that you do not run concurrent marked transactions. È raro ma possibile che il commit di una transazione contrassegnata distribuita generi un deadlock quando viene eseguito il commit simultaneo di altre transazioni contrassegnate distribuite.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. In tal caso, la transazione contrassegnata verrà scelta come oggetto del deadlock e ne verrà eseguito il rollback.When this happens, the marking transaction is chosen as the deadlock victim and is rolled back. Se si verifica questo errore, l'applicazione può ripetere il tentativo di esecuzione della transazione contrassegnata.When this error occurs, the application can retry the marked transaction. Quando più transazioni contrassegnate tentano di eseguire il commit simultaneamente, la probabilità che venga generato un deadlock è maggiore.When multiple marked transactions try to commit concurrently, there is a higher probability of deadlock.

Recupero fino a una transazione contrassegnataRecovering to a Marked Transaction

Per informazioni su come recuperare un database che contiene transazioni contrassegnate fino a un contrassegno particolare o appena prima di esso, vedere Recupero di database correlati che contengono transazioni contrassegnate.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.

Vedere ancheSee Also

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL) BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
Backup e ripristino di database di sistema (SQL Server) Back Up and Restore of System Databases (SQL Server)
BEGIN TRANSACTION (Transact-SQL) BEGIN TRANSACTION (Transact-SQL)
Applicare backup di log delle transazioni (SQL Server) Apply Transaction Log Backups (SQL Server)
Backup completo del database (SQL Server) Full Database Backups (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Recupero di database correlati che contengono transazioni contrassegnateRecovery of Related Databases That Contain Marked Transaction