Usare transazioni contrassegnate per recuperare coerentemente i database correlati

Si applica a:SQL Server

Le informazioni contenute in questo argomento sono rilevanti solo per i database di SQL Server che usano i modelli di recupero completo o con registrazione minima.

Quando si eseguono aggiornamenti in due o più database correlati, è possibile usare i contrassegni delle transazioni per recuperarli fino a un punto consistente logico. Tuttavia, questo recupero perde qualsiasi transazione eseguita dopo il contrassegno utilizzato come punto di recupero. L'utilizzo della marcatura delle transazioni è adeguato solo quando si testano database correlati o quando si è disposti a perdere delle transazioni di cui è stato eseguito il commit di recente.

L'applicazione ripetuta di contrassegni alle transazioni correlate in ogni database correlato determina una serie di punti di recupero comuni nei database. I contrassegni delle transazioni sono registrati nel log delle transazioni e sono inclusi nei backup del log. In caso di emergenza, è possibile ripristinare ciascun database rispetto allo stesso punto di transazione per recuperarli fino a un punto coerente.

Nota

I backup del log nei diversi database possono essere creati in modo indipendente gli uni dagli altri e non è necessario che siano simultanei.

Il recupero di database correlati negli scenari seguenti richiede che siano già presenti transazioni contrassegnate in ogni database correlato:

• Uno o più log delle transazioni sono danneggiati. È necessario ripristinare il set di database in uno stato consistente al momento dell'ultimo backup del log.

• È necessario ripristinare l'intero set di database in uno stato mutuamente consistente a un momento precedente.

Importante

È possibile recuperare database correlati solo fino a una transazione contrassegnata e non a un momento specifico.

Per informazioni su come creare transazioni contrassegnate, vedere "Creazione di transazioni contrassegnate", di seguito in questo argomento.

Scenario tipico per l'utilizzo delle transazioni contrassegnate

Uno scenario tipico per l'utilizzo delle transazioni contrassegnate include i passaggi seguenti:

1. Creare un backup completo o differenziale di ciascun database correlato.

2. Contrassegnare un blocco di transazioni in tutti i database.

3. Eseguire il backup del log delle transazioni per tutti i database.

4. Ripristinare i backup dei database WITH NORECOVERY.

5. Ripristina i log "WITH STOPATMARK".

Considerazioni sull'utilizzo delle transazioni contrassegnate

Prima di inserire contrassegni denominati nel log delle transazioni, considerare quanto segue:

• I contrassegni di transazione occupano spazio nei log e pertanto è consigliabile utilizzarli esclusivamente per transazioni importanti ai fini della strategia di recupero dei database.

• Dopo il commit di una transazione contrassegnata, viene inserita una riga nella tabella logmarkhistory del database 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.

Creazione delle transazioni contrassegnate

Per creare una transazione contrassegnata, usare l'istruzione BEGIN TRANSACTION e la clausola WITH MARK [description]. L'argomento description è facoltativo e rappresenta una descrizione del contrassegno. Il nome del contrassegno della transazione è obbligatorio. È possibile riutilizzare il nome di un marchio. 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 log (LSN). Le informazioni di data/ora sono utilizzate insieme al nome del contrassegno per identificare il contrassegno in modo univoco.

Per creare transazioni contrassegnate in un set di database:

1. Denominare la transazione nell'istruzione BEGIN TRAN e utilizzare la clausola WITH MARK

   È possibile annidare l'istruzione BEGIN TRAN nuovo_nome_contrassegno CON MARK all'interno di una transazione esistente. Il valore di nuovo_nome_contrassegno è il nome di contrassegno per la transazione, anche se la transazione ha un nome di transazione.

   Nota

   Se si esegue una seconda istruzione nidificata BEGIN TRAN...WITH MARK, l'istruzione verrà ignorata ma provocherà un messaggio di avviso.

2. Eseguire un aggiornamento su tutti i database del set.

   Il contrassegno per una transazione specifica viene inserito nei log delle transazioni solo nell'istanza del server in cui viene eseguita l'istruzione BEGIN TRAN...WITH MARK. Il contrassegno della transazione viene inserito nel log delle transazioni di ogni database aggiornato dalla transazione contrassegnata in tale istanza del server. Se il database si trova in istanze del server diverse, è necessario creare contrassegni identici in ogni istanza del server.

Esempi

Nell'esempio seguente, il log delle transazioni viene ripristinato fino al segno nella transazione etichettata denominata ListPriceUpdate.

USE AdventureWorks2022;
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 = 'UPDATE Product list prices';  

Forzare un contrassegno a diffondersi ad altri server

Il nome di un contrassegno di transazione non viene distribuito automaticamente a un altro server quando la transazione si estende lì. Per forzare la distribuzione del contrassegno agli altri server, è necessario scrivere una stored procedure contenente un'istruzione BEGIN TRAN name WITH MARK. La procedura memorizzata deve quindi essere eseguita nel server remoto nell'ambito della transazione presente nel server di origine.

Ad esempio, considerare un database partizionato esistente in più istanze di SQL Server. In ogni istanza è presente un database denominato coyote. Innanzitutto, in ogni database, creare una stored procedure, ad esempio 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  

Crea poi una stored procedure sp_MarkAll contenente una transazione che inserisce un contrassegno in ogni database. sp_MarkAll può essere eseguita da qualsiasi delle istanze.

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 fasi

L'esecuzione di una transazione distribuita avviene in due fasi: preparazione e 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. A questo punto, si garantisce che non vi siano transazioni che appaiono come eseguite in un log ma non in un altro log.

I passaggi seguenti garantiscono questo risultato durante il commit di una transazione contrassegnata:

1. La fase di preparazione di una transazione contrassegnata blocca tutte le nuove operazioni di preparazione e commit.

2. È consentito il proseguimento solo delle operazioni di commit di transazioni già preparate.

3. La marcatura delle transazioni attende quindi che tutte le transazioni preparate siano completate (con timeout).

4. La transazione contrassegnata viene preparata e confermata.

5. Il blocco di nuove operazioni di preparazione e commit viene annullato.

I blocchi generati dalle transazioni contrassegnate che si estendono su più database possono rallentare le prestazioni di elaborazione delle transazioni nel server.

È consigliabile non eseguire transazioni contrassegnate simultanee. È 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. In tal caso, la transazione di marcatura verrà scelta come vittima del deadlock e sarà annullata. Se si verifica questo errore, l'applicazione può ripetere il tentativo di esecuzione della transazione contrassegnata. Quando più transazioni contrassegnate tentano di eseguire il commit simultaneamente, la probabilità che venga generato un deadlock è maggiore.

Recupero di una transazione contrassegnata

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.

Vedi anche

INIZIA TRANSAZIONE DISTRIBUITA (Transact-SQL)
Backup e ripristino di database di sistema (SQL Server)
BEGIN TRANSACTION (Transact-SQL)
Applicare backup di log delle transazioni (SQL Server)
Backup completo del database (SQL Server)
RESTORE (Transact-SQL)
Recupero di database correlati che contengono transazioni contrassegnate