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

  • Articolo

Si applica a:SQL Server

SQL Server 2017 (14.x) supporta tutte le transazioni distribuite incluse nei database di 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.

Nota

SQL Server 2016 (13.x) Service Pack 2 e versioni successive offrono il supporto completo per le transazioni distribuite in gruppi di disponibilità. Nelle versioni di SQL Server 2016 (13.x) precedenti a Service Pack 2, le transazioni distribuite tra database (ovvero le transazioni che usano database nella stessa istanza di SQL Server) che coinvolgono un database in un gruppo di disponibilità non sono supportate. SQL Server 2017 (14.x) non presenta questa limitazione.

In SQL Server 2016 (13.x) i passaggi di configurazione sono gli stessi di SQL Server 2017 (14.x).

In una transazione distribuita, le applicazioni client funzionano con Microsoft Distributed Transaction Coordinator (MS DTC o DTC) per garantire la coerenza transazionale 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 è in un gruppo di disponibilità, ogni database deve essere il proprio strumento di gestione delle 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 il risultato 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 nell'elaborazione del gruppo di disponibilità a meno che un database non sia anche membro 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 completerà il commit e riconoscerà il commit al chiamante fino a quando il database secondario non riconosce che i record di log sono persistenti nell'archiviazione durevole. Solo a questo punto il database primario dichiarerà il completamento della transazione. In modalità asincrona, non è prevista l'attesa del riconoscimento da parte della replica secondaria ed esiste esplicitamente la possibilità che una piccola quantità di dati vada persa.

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 disponibile all'indirizzo https://support.microsoft.com/kb/3090973.

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  
         )

Nota

Lo script precedente è un esempio semplice di un gruppo di disponibilità e non è progettato per un ambiente di produzione specifico.

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  
      );

Nota

A partire da SQL Server 2016 (13.x) Service Pack 2 è possibile modificare un gruppo di disponibilità per le transazioni distribuite. Per le versioni di SQL Server 2016 (13.x) precedenti al Service Pack 2 è necessario eliminare e ricreare il gruppo di disponibilità con l'impostazione 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 MS DTC.

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 ne richiede il commit o il rollback. Il commit di transazioni distribuite deve essere gestito dal gestore delle transazioni in modo diverso per evitare che, in seguito a un errore della rete, alcuni strumenti di gestione delle risorse eseguano il commit correttamente mentre altri eseguano il rollback della transazione. A tale scopo il processo di commit viene gestito in due fasi, ovvero una fase preparatoria e la fase di commit effettivo. Questo tipo di commit è noto come protocollo 2PC.

  • 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 strumento esegue quindi tutte le operazioni necessarie per rendere la transazione durevole. Tutti i buffer contenenti immagini del log relative alla transazione vengono inoltre trasferiti su disco. Dopo avere completato la fase preparatoria, lo strumento di gestione delle risorse comunica al gestore delle transazioni l'esito, positivo o negativo, della preparazione.

  • Fase di commit

    Se la fase preparatoria ha esito positivo in tutti gli strumenti di gestione delle risorse, il gestore delle transazioni invia comandi di commit agli strumenti di gestione, i quali 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 viene integrata nella transazione DTC. Questa situazione può verificarsi quando nella transazione ci sono più strumenti di gestione delle risorse 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 l'operazione di commit, DTC avvia il protocollo 2PC chiedendo a tutti gli strumenti di gestione delle risorse nella transazione di preparare la transazione.
  4. DTC segnala a tutti gli strumenti di gestione delle risorse di eseguire il commit della transazione dopo la conferma della corretta esecuzione della fase preparatoria. Se non si verificano problemi che impediscono la conferma della corretta esecuzione, DTC interrompe 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 strumenti di gestione delle risorse includono:

  • Un'istanza di SQL Server.
  • Database in un gruppo di disponibilità che è stato 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 viene integrata con un controllo DTC. In genere, l'istanza di SQL Server viene integrata con il controllo DTC nel 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.

Importante

Si noti che il DTC prevede un limite di 32 integrazioni per ogni transazione distribuita. Poiché ogni database all'interno di un gruppo di disponibilità si integra separatamente con il DTC, se la transazione include più di 32 database, è possibile che venga visualizzato l'errore seguente quando SQL Server tenta di integrare il 33° database:

Enlist operation failed: 0x8004d101(XACT_E_TOOMANY_ENLISTMENTS). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MS DTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager.

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 di DTC_SUPPORT per un gruppo di disponibilità.
  • Aggiunta o rimozione di un database da un gruppo di disponibilità.
  • Eliminazione di un gruppo di disponibilità.

Nei casi precedenti, se la replica primaria effettua il failover in una nuova istanza di SQL Server, l'istanza prova a contattare il controllo DTC per identificare il risultato della transazione. DTC non può restituire il risultato perché l'RMID usato dal database per ottenere il risultato delle transazioni in dubbio durante il ripristino non è stato integrato 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 (MS DTC) 
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 il controllo DTC non è riuscito a reintegrare il database 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.

Avviso

Quando si esegue manualmente il commit o il rollback di una transazione, tale operazione può influire su un'applicazione. Verificare che l'azione di commit o rollback sia coerente con i requisiti dell'applicazione.

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'unità di lavoro della transazione in dubbio del messaggio di errore precedente ed 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'unità di lavoro della transazione in dubbio del messaggio di errore precedente ed 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. Aggiornare ed eseguire lo script seguente: impostare il nome del database per il nome del database sospetto:

ALTER DATABASE [DB1] SET ONLINE

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

Passaggi successivi

Transazioni distribuite

Gruppi di disponibilità Always On: interoperabilità (SQL Server)

Transactions - Always On Availability Groups and Database Mirroring (Transazioni: gruppi di disponibilità Always On 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)