Configurare le transazioni distribuite per un gruppo di disponibilità Always On

Si applica a:SQL Server

SQL Server 2017 (14.x) e versioni successive supportano tutte le transazioni distribuite, inclusi i database in un gruppo di disponibilità. Questo articolo illustra come configurare un gruppo di disponibilità per le transazioni distribuite.

Per garantire le transazioni distribuite, il gruppo di disponibilità deve essere configurato in modo da registrare i database come strumenti di gestione delle risorse delle transazioni distribuite.

In una transazione distribuita, le applicazioni client interagiscono con Microsoft Distributed Transaction Coordinator (MSDTC o DTC) per garantire la coerenza delle transazioni tra più origini dati. DTC è un servizio disponibile nei sistemi operativi basati su Windows Server supportati. Per una transazione distribuita, DTC è il coordinatore di transazioni. In genere, un'istanza di SQL Server è lo strumento di gestione delle risorse. Quando un database fa parte di un gruppo di disponibilità, ogni database deve disporre di un proprio gestore risorse.

SQL Server non impedisce le transazioni distribuite per i database in un gruppo di disponibilità, anche quando il gruppo di disponibilità non è configurato per le transazioni distribuite. Tuttavia, quando un gruppo di disponibilità non è configurato per le transazioni distribuite, in alcuni casi il failover potrebbe non riuscire. In particolare, l'istanza di SQL Server della nuova replica primaria potrebbe non essere in grado di ottenere l'esito della transazione da DTC. Per consentire all'istanza di SQL Server di ottenere il risultato delle transazioni in dubbio da DTC dopo il failover, configurare il gruppo di disponibilità per le transazioni distribuite.

DTC non è coinvolto nella gestione del gruppo di disponibilità, a meno che un database non faccia anche parte di un cluster di failover. All'interno di un gruppo di disponibilità, la coerenza tra le repliche viene mantenuta dalla logica del gruppo di disponibilità: la replica primaria non completa il commit né lo conferma al chiamante finché la replica secondaria non conferma di aver reso permanenti i record del log nell'archiviazione permanente. Solo a questo punto il database primario dichiarerà il completamento della transazione. In modalità asincrona, non si attende la conferma da parte della replica secondaria ed è esplicitamente prevista la possibilità di perdere una piccola quantità di dati.

Prerequisiti

Prima di configurare un gruppo di disponibilità per supportare le transazioni distribuite, è necessario soddisfare i prerequisiti seguenti:

• La versione di tutte le istanze di SQL Server che fanno parte della transazione distribuita deve essere SQL Server 2016 (13.x) o versioni successive.

• I gruppi di disponibilità devono essere in esecuzione in Windows Server 2012 R2 o versioni successive. Per Windows Server 2012 R2, è necessario installare l'aggiornamento in KB3090973.

Creare un gruppo di disponibilità per le transazioni distribuite

Configurare un gruppo di disponibilità per supportare le transazioni distribuite. Impostare il gruppo di disponibilità per consentire a ogni database di essere registrato come strumento di gestione delle risorse. Questo articolo illustra come configurare un gruppo di disponibilità in modo che ogni database possa essere uno strumento di gestione delle risorse in DTC.

È possibile creare un gruppo di disponibilità per le transazioni distribuite in SQL Server 2016 (13.x) o versioni successive. Per creare un gruppo di disponibilità per le transazioni distribuite, includere DTC_SUPPORT = PER_DB nella definizione del gruppo di disponibilità. Lo script seguente crea un gruppo di disponibilità per le transazioni distribuite.

CREATE AVAILABILITY
GROUP MyAG
WITH (DTC_SUPPORT = PER_DB)
FOR DATABASE DB1,
    DB2 REPLICA
ON 'Server1' WITH (
   ENDPOINT_URL = 'TCP://SERVER1.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
),
'Server2' WITH (
   ENDPOINT_URL = 'TCP://SERVER2.corp.com:5022',
   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
   FAILOVER_MODE = AUTOMATIC
);

Modificare un gruppo di disponibilità per le transazioni distribuite

È possibile modificare un gruppo di disponibilità per le transazioni distribuite in SQL Server 2017 (14.x) o versioni successive. Per modificare un gruppo di disponibilità per le transazioni distribuite includere DTC_SUPPORT = PER_DB nello script ALTER AVAILABILITY GROUP. Lo script di esempio modifica il gruppo di disponibilità per supportare le transazioni distribuite.

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = PER_DB);

Per disabilitare le transazioni distribuite, usare il comando Transact-SQL seguente:

ALTER AVAILABILITY GROUP MyaAG
SET (DTC_SUPPORT = NONE);

Transazioni distribuite - concetti tecnici

Una transazione distribuita si estende su due o più database. DTC in qualità di strumento di gestione transazioni coordina la transazione tra le istanze di SQL Server e altre origini dati. Ogni istanza del motore di database di SQL Server può operare come strumento di gestione delle risorse. Quando un gruppo di disponibilità è configurato con DTC_SUPPORT = PER_DB, i database possono operare come strumenti di gestione delle risorse. Per ulteriori informazioni, vedere la documentazione di MSDTC.

Una transazione con due o più database in una singola istanza del motore di database è in effetti una transazione distribuita. Nell'istanza le transazioni distribuite vengono gestite internamente e appaiono all'utente come transazioni locali. SQL Server 2017 (14.x) alza di livello tutte le transazioni tra database a DTC quando i database si trovano in un gruppo di disponibilità configurato con DTC_SUPPORT = PER_DB, anche all'interno di una singola istanza di SQL Server.

A livello dell'applicazione le transazioni distribuite vengono gestite in modo simile alle transazioni locali. Al termine della transazione, l'applicazione richiede che venga eseguito il commit o il rollback della transazione. Il gestore di transazioni deve gestire un commit di transazioni distribuito in modo diverso per minimizzare il rischio che un errore della rete possa far sì che alcuni resource manager eseguano il commit correttamente mentre altri eseguano il rollback della transazione. Ciò si ottiene gestendo il processo di commit in due fasi (la fase di preparazione e la fase di commit), noto come commit a due fasi.

• Fase preparatoria

  Quando il gestore delle transazioni riceve una richiesta di commit, invia un comando di preparazione a tutti gli strumenti di gestione delle risorse coinvolti nella transazione. Ogni gestore risorse esegue quindi tutte le operazioni necessarie per rendere persistente la transazione e tutti i buffer che contengono le immagini di log della transazione vengono inoltre scaricati su disco. Dopo avere completato la fase preparatoria, il resource manager comunica al gestore delle transazioni l'esito, positivo o negativo, della preparazione.

• Fase di commit

  Se il gestore delle transazioni riceve conferme positive alla preparazione da tutti i gestori delle risorse, invia comandi di commit a ciascun gestore delle risorse. I responsabili delle risorse possono quindi completare il commit. Se il commit viene eseguito correttamente da parte di tutti gli strumenti di gestione delle risorse, il gestore delle transazioni invia all'applicazione una notifica di operazione riuscita. Se viene segnalato un tentativo di preparazione non riuscito in uno strumento di gestione delle risorse, il gestore delle transazioni invia un comando di rollback a tutti gli strumenti di gestione e segnala all'applicazione che il commit ha avuto esito negativo.

Procedura dettagliata

L'elenco seguente illustra il funzionamento dell'applicazione con DTC per completare le transazioni distribuite.

1. L'istanza di SQL Server partecipa alla transazione DTC. Questa situazione può verificarsi quando nella transazione ci sono più resource manager o se il client richiede che una transazione venga alzata di livello a transazione DTC.
2. Il client esegue alcune operazioni nell'istanza di SQL Server nella transazione DTC.
3. Il client esegue le operazioni di commit e interruzione per la transazione DTC.
   • Se il client esegue l'operazione di interruzione, la transazione viene interrotta immediatamente.
   • Se il client esegue il commit, DTC avvia il protocollo di commit a due fasi chiedendo a tutti i gestori risorse coinvolti nella transazione di preparare la transazione.
4. DTC informa tutti i gestori risorse di eseguire il commit della transazione dopo che tutti i gestori risorse hanno confermato correttamente la fase di preparazione. Se qualcosa impedisce la conferma dell’avvenuta ricezione, DTC annulla la transazione.

Effetti della configurazione di un gruppo di disponibilità per le transazioni distribuite

Ogni entità che fa parte di una transazione distribuita viene chiamata strumento di gestione delle risorse. Esempi di gestori delle risorse includono:

• Un'istanza di SQL Server.
• Un database in un gruppo di disponibilità configurato per le transazioni distribuite.
• Servizio DTC, può essere anche uno strumento di gestione transazioni.
• Altre origini dati.

Per partecipare alle transazioni distribuite, un'istanza di SQL Server si registra presso un DTC. Normalmente, l'istanza di SQL Server si registra con DTC sul server locale. Ogni istanza di SQL Server crea uno strumento di gestione delle risorse con un identificatore di gestione delle risorse univoco (RMID) e lo registra in DTC. Nella configurazione predefinita tutti i database in un'istanza di SQL Server usano lo stesso RMID.

Quando un database è in un gruppo di disponibilità, la copia di lettura e scrittura del database, o replica primaria, può essere spostata in un'istanza diversa di SQL Server. Per supportare le transazioni distribuite durante questo spostamento, ogni database deve agire da strumento di gestione delle risorse separato e deve avere un RMID univoco. Quando un gruppo di disponibilità ha DTC_SUPPORT = PER_DB, SQL Server crea uno strumento di gestione delle risorse per ogni database e lo registra in DTC usando un RMID univoco. In questa configurazione il database è uno strumento di gestione delle risorse per le transazioni DTC.

Per altre informazioni sulle transazioni distribuite in SQL Server, vedere Transazioni distribuite

Gestire le transazioni non risolte

Il risultato delle transazioni attive presente durante la modifica del RMID non può essere recuperato dopo un failover. perché l'RMID usato da SQL Server per l'integrazione e l'RMID usato da SQL Server per il recupero sono diversi. La modifica del RMID può verificarsi nei casi seguenti:

• Modifica DTC_SUPPORT per un gruppo di disponibilità.
• Aggiunta o rimozione di un database da un gruppo di disponibilità.
• Elimina un gruppo di disponibilità.

Nei casi precedenti, se la replica primaria passa tramite failover a una nuova istanza di SQL Server, l'istanza tenta di contattare DTC per determinare l'esito della transazione. DTC non può restituire l'esito perché l'RMID utilizzato dal database per ottenere l'esito delle transazioni in dubbio durante il ripristino non era stato registrato in precedenza. Pertanto, lo stato del database diventa SUSPECT.

Il nuovo log degli errori di SQL Server conterrà una voce simile a quella dell'esempio seguente:

Microsoft Distributed Transaction Coordinator (MSDTC)
failed to reenlist citing that the database RMID does
not match the RMID [xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx]
associated with the transaction.  Please manually resolve
the transaction.

SQL Server detected a DTC/KTM in-doubt transaction with UOW
{yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}.Please resolve it
following the guideline for Troubleshooting DTC Transactions.

L'esempio precedente mostra che DTC non è riuscito a registrare nuovamente il database a partire dalla nuova replica primaria nella transazione creata dopo il failover. L'istanza di SQL Server non può determinare il risultato della transazione distribuita, pertanto contrassegna il database come sospetto. La transazione viene contrassegnata come un'unità di lavoro (UOW) a cui fa riferimento un GUID. Per ripristinare il database, eseguire manualmente il commit o il rollback della transazione.

Eseguire solo uno degli script seguenti:

• Per eseguire il commit della transazione, aggiornare ed eseguire lo script seguente: sostituire yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy con l'UOW della transazione in dubbio dal messaggio di errore precedente, quindi eseguire:

  KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH COMMIT;
  

• Per eseguire il rollback della transazione, aggiornare ed eseguire lo script seguente: sostituire yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy con l'UOW della transazione in dubbio del messaggio di errore precedente, quindi eseguire:

  KILL 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' WITH ROLLBACK;
  

Dopo il commit o il rollback della transazione, è possibile usare ALTER DATABASE per impostare il database online. Aggiorna ed esegui lo script seguente: imposta come nome del database il nome del database sospetto:

ALTER DATABASE [DB1] SET ONLINE;

Per altre informazioni sulla risoluzione delle transazioni in dubbio, vedere Risolvere le transazioni manualmente.

Contenuto correlato

• Transazioni distribuite
• Gruppi di disponibilità AlwaysOn: interoperabilità (SQL Server)
• Transazioni - gruppi di disponibilità e mirroring del database
• Supporting XA Transactions (Supporto delle transazioni XA)
• How It Works: Session/SPID (-2) for DTC Transactions (Come funziona: sessione/SPID (-2) per transazioni DTC)