Informazioni su Change Data Capture (SQL Server)

Articolo
06/25/2013

Change Data Capture consente di registrare le attività di inserimento, aggiornamento ed eliminazione applicate a una tabella di SQL Server, rendendo disponibili i dettagli delle modifiche in un formato relazionale facilmente utilizzabile. Le informazioni sulla colonna e i metadati necessari per applicare le modifiche a un ambiente di destinazione vengono acquisiti per le righe modificate e archiviati in tabelle delle modifiche che riflettono la struttura della colonna delle tabelle di origine con rilevamento. Per consentire ai consumer di accedere in modo sistematico ai dati delle modifiche, sono disponibili funzioni con valori di tabella.

Un buon esempio di consumer di dati cui questa tecnologia è destinata è un'applicazione ETL di estrazione, trasformazione e caricamento. Un'applicazione di questo tipo carica incrementalmente dati delle modifiche dalle tabelle di origine di SQL Server in un data warehouse oppure in un data mart. Anche se la rappresentazione delle tabelle di origine all'interno del data warehouse deve riflettere le modifiche in tali tabelle, una tecnologia end-to-end che aggiorna una replica dell'origine non è appropriata. È necessario invece un flusso affidabile di dati delle modifiche strutturato in modo che i consumer possano applicarlo a rappresentazioni di destinazione dei dati diverse. L'acquisizione dei dati delle modifiche di SQL Server fornisce questa tecnologia.

Flusso di dati di Change Data Capture

Nella figura seguente viene illustrato il flusso di dati principale per Change Data Capture.

Flusso di dati di Change Data Capture

L'origine dei dati delle modifiche per la funzionalità Change Data Capture è data dal log delle transazioni di SQL Server. Man mano che alle tabelle di origine con rilevamento vengono applicati inserimenti, aggiornamenti ed eliminazioni, le voci che descrivono tali modifiche vengono aggiunte al log, Il log che viene utilizzato come input per il processo di acquisizione viene letto e le informazioni relative alle modifiche vengono aggiunte alla tabella delle modifiche associata alla tabella con rilevamento. Per enumerare le modifiche visualizzate nelle tabelle delle modifiche in un intervallo specificato, sono disponibili diverse funzioni che restituiscono le informazioni in un set di risultati filtrato. Tale set di risultati viene utilizzato in genere da un processo dell'applicazione per aggiornare una rappresentazione dell'origine in alcuni ambienti esterni.

Informazioni su Change Data Capture e l'istanza di acquisizione

Affinché sia possibile rilevare le modifiche apportate a qualsiasi tabella singola di un database, è necessario che Change Data Capture venga abilitato in modo esplicito per il database. Per eseguire questa operazione, utilizzare la stored procedure sys.sp_cdc_enable_db. Quando il database è abilitato, le tabelle di origine possono essere identificate come tabelle con rilevamento mediante la stored procedure sys.sp_cdc_enable_table. Quando la funzionalità Change Data Capture viene abilitata per una tabella, per supportare la distribuzione dei dati delle modifiche nella tabella di origine viene creata un'istanza di acquisizione associata costituita da una tabella delle modifiche e da una o due funzioni della query. I metadati che descrivono i dettagli di configurazione dell'istanza di acquisizione vengono mantenuti nelle tabelle di metadati di Change Data Capture cdc.change_tables, cdc.index_columns e cdc.captured_columns. È possibile recuperare queste informazioni tramite la stored procedure sys.sp_cdc_help_change_data_capture.

Tutti gli oggetti associati a un'istanza di acquisizione vengono creati nello schema relativo a Change Data Capture del database abilitato. Il nome dell'istanza di acquisizione deve essere un nome di oggetto valido e univoco in tutte le istanze di acquisizione del database. Per impostazione predefinita, il nome è <schema name_table name> della tabella di origine. Il nome assegnato alla tabella delle modifiche associata viene creato aggiungendo _CT al nome dell'istanza di acquisizione. Il nome assegnato alla funzione utilizzata per eseguire una query relativa a tutte le modifiche viene creato anteponendo fn_cdc_get_all_changes_ al nome dell'istanza di acquisizione. Se l'istanza di acquisizione è configurata per supportare net changes, viene creata inoltre la funzione della query net_changes, cui viene assegnato un nome creato anteponendo fn_cdc_get_net_changes_ al nome dell'istanza di acquisizione.

Tabella delle modifiche

Le prime cinque colonne di una tabella delle modifiche di Change Data Capture sono costituite da metadati che forniscono informazioni aggiuntive attinenti alla modifica registrata. Le colonne rimanenti rispecchiano le colonne acquisite identificate dalla tabella di origine nel nome e, generalmente, nel tipo. Tali colonne contengono i dati delle colonne acquisite raccolti dalla tabella di origine.

Ogni operazione di inserimento o eliminazione applicata a una tabella di origine viene visualizzata in un'unica riga all'interno della tabella delle modifiche. Le colonne di dati della riga che costituisce il risultato di un'operazione di inserimento contengono i valori della colonna dopo l'inserimento, mentre le colonne di dati della riga che costituisce il risultato di un'operazione di eliminazione contengono i valori della colonna prima dell'eliminazione. Per eseguire un'operazione di aggiornamento sono necessarie due voci di riga, una per identificare i valori della colonna prima dell'aggiornamento e l'altra per identificare i valori della colonna dopo l'aggiornamento.

Ogni riga di una tabella delle modifiche contiene inoltre metadati aggiuntivi che consentono di interpretare l'attività di modifica. La colonna __$start_lsn identifica il numero di sequenza del file di log (LSN) del commit assegnato alla modifica. Il valore LSN di commit identifica le modifiche di cui è stato eseguito il commit all'interno della stessa transazione e ordina inoltre tali transazioni. La colonna __$seqval può essere utilizzata per ordinare più modifiche eseguite all'interno di una stessa transazione, mentre la colonna __$operation registra l'operazione associata alla modifica in base al seguente schema: 1 = eliminazione, 2 = inserimento, 3 = aggiornamento (prima dell'immagine) e 4 = aggiornamento (dopo l'immagine). La colonna __$update_mask, infine, è una maschera di bit variabile con un bit definito per ogni colonna acquisita. Per le voci relative all'inserimento e all'eliminazione dei dati, tutti i bit della maschera di aggiornamento verranno sempre impostati. Per le righe aggiornate, tuttavia, saranno impostati solo i bit corrispondenti alle colonne modificate.

Intervallo di validità di Change Data Capture per un database

L'intervallo di validità di Change Data Capture per un database è rappresentato dal periodo di tempo durante il quale i dati delle modifiche sono disponibili per le istanze di acquisizione. L'intervallo di validità ha inizio nel momento in cui viene creata la prima istanza di acquisizione e si estende fino al momento corrente.

Se non vengono eliminati in modo periodico e sistematico, i dati inseriti nelle tabelle delle modifiche aumenteranno notevolmente e non sarà più possibile gestirli. Il processo di pulizia di Change Data Capture è responsabile dell'applicazione dei criteri di pulizia basati sulla memorizzazione. Tale processo sposta innanzitutto l'endpoint inferiore dell'intervallo di validità in modo da soddisfare la restrizione relative al tempo e successivamente rimuove le voci della tabella delle modifiche scadute. Per impostazione predefinita, i dati vengono mantenuti per tre giorni.

A livello dell'endpoint superiore, poiché il processo di acquisizione esegue il commit di ogni nuovo batch di dati delle modifiche, le nuove voci vengono aggiunte a cdc.lsn_time_mapping per ogni transazione cui sono associate voci della tabella delle modifiche. Nella tabella di mapping vengono mantenuti sia un numero di sequenza del file di log (LSN) del commit che l'ora in cui è stato eseguito il commit della transazione (colonne start_lsn e tran_end_time, rispettivamente). Il valore LSN massimo presente in cdc.lsn_time_mapping rappresenta il limite superiore dell'intervallo di validità del database. L'ora corrispondente in cui viene eseguito il commit viene utilizzata come base per il calcolo del nuovo limite inferiore da parte del criterio di pulizia basato sulla memorizzazione.

Poiché il processo di acquisizione estrae dati delle modifiche dal log delle transazioni, è presente una latenza predefinita tra il momento in cui viene eseguito il commit di una modifica in una tabella di origine e quello in cui la modifica viene visualizzata nella tabella delle modifiche associate. Mentre tale latenza è in genere bassa, è tuttavia importante tenere presente che i dati delle modifiche non sono disponibili fino a quando il processo di acquisizione non ha elaborato le voci di log correlate.

Intervallo di validità di Change Data Capture per un'istanza di acquisizione

Sebbene l'intervallo di validità per il database e quello per l'istanza di acquisizione singola in genere coincidano, questa situazione non si verifica sempre. L'intervallo di validità dell'istanza di acquisizione viene avviato quando il processo di acquisizione riconosce l'istanza e avvia la registrazione delle modifiche associate nella relativa tabella. Di conseguenza, se le istanze di acquisizione vengono create in momenti diversi, a ciascuna sarà associato inizialmente un endpoint inferiore diverso. Nella colonna start_lsn del set di risultati restituito da sys.sp_cdc_help_change_data_capture viene visualizzato l'endpoint inferiore corrente per ogni istanza di acquisizione definita. Quando il processo di pulizia elimina le voci della tabella delle modifiche, modifica anche i valori di start_lsn affinché tutte le istanze di acquisizione riflettano il nuovo limite minimo per i dati delle modifiche disponibili. Vengono modificate solo le istanze di acquisizione per cui i valori di start_lsn sono attualmente minori rispetto al nuovo limite minimo. Con il tempo, se non viene creata alcuna nuova istanza di acquisizione, gli intervalli di validità per tutte le istanze singole tenderanno a coincidere con l'intervallo di validità per il database.

L'intervallo di validità è importante per i consumer dei dati delle modifiche perché l'intervallo di estrazione per una richiesta deve essere interamente compreso nell'intervallo di validità di Change Data Capture per l'istanza di acquisizione corrente. Se l'endpoint inferiore dell'intervallo di estrazione è minore dell'endpoint inferiore dell'intervallo di validità, alcuni dati delle modifiche potrebbero non essere disponibili a causa di una pulizia eccessiva. Se invece l'endpoint superiore dell'intervallo di estrazione è maggiore dell'endpoint superiore dell'intervallo di validità, il processo di acquisizione non ha ancora eseguito alcuna elaborazione durante il periodo di tempo rappresentato dall'intervallo di estrazione, provocando anche in questo caso la non disponibilità dei dati delle modifiche.

La funzione sys.fn_cdc_get_min_lsn consente di recuperare il valore LSN minimo corrente per un'istanza di acquisizione, mentre sys.fn_cdc_get_max_lsn consente di recuperare il valore LSN massimo corrente. In caso di esecuzione di una query sui dati delle modifiche, se l'intervallo LSN specificato non è compreso tra questi due valori LSN, non sarà possibile eseguire le funzioni della query relative a Change Data Capture.

Gestione delle modifiche nelle tabelle di origine

L'adattamento delle modifiche delle colonne nelle tabelle di origine per cui deve essere eseguito il rilevamento delle modifiche costituisce un problema di difficile risoluzione per i consumer a valle. Sebbene l'abilitazione di Change Data Capture in una tabella di origine non impedisca il verificarsi di tali modifiche DDL, tale funzionalità consente tuttavia di mitigare l'effetto sui consumer consentendo ai set di risultati recapitati restituiti tramite l'API di rimanere invariati anche se la struttura della colonna della tabella di origine sottostante subisce modifiche. Tale struttura fissa viene riflessa anche nella tabella delle modifiche sottostante alla quale accedono le funzioni della query definite.

Per adattare una tabella delle modifiche con struttura della colonna fissa, il processo di acquisizione responsabile del popolamento della tabella delle modifiche ignorerà qualsiasi nuova colonna non identificata per l'acquisizione al momento dell'abilitazione di Change Data Capture per la tabella di origine. Se una colonna con rilevamento viene eliminata, per tale colonna verranno specificati valori Null nelle voci di modifica successive. Tuttavia, se una colonna esistente subisce una modifica relativa al tipo di dati, la modifica viene propagata alla tabella delle modifiche per garantire che il meccanismo di acquisizione non provochi una perdita di dati nelle colonne con rilevamento. Il processo di acquisizione invia inoltre tutte le modifiche rilevate nella struttura della colonna di tabelle con rilevamento alla tabella cdc.ddl_history. I consumer che desiderano ricevere un avviso relativo alle modifiche eventualmente apportate ad applicazioni a valle possono utilizzare la stored procedure sys.sp_cdc_get_ddl_history.

Quando alla tabella di origine associata vengono applicate modifiche DDL, l'istanza di acquisizione corrente continuerà in genere a mantenere la propria forma. È tuttavia possibile creare una seconda istanza di acquisizione per la tabella che rifletta la nuova struttura della colonna. In questo modo il processo di acquisizione è in grado di trasferire le modifiche apportate alla stessa tabella di origine in due tabelle delle modifiche distinte con strutture della colonna diverse. Di conseguenza, mentre una tabella delle modifiche può continuare a essere utilizzata da programmi operativi correnti, la seconda può risultare utile in un ambiente di sviluppo in cui devono essere incorporati i nuovi dati della colonna. Se si consente al meccanismo di acquisizione di popolare entrambe le tabelle delle modifiche contemporaneamente, una transizione da una tabella all'altra può essere eseguita senza perdita di dati delle modifiche. Questa situazione può verificarsi tutte le volte che due cronologie di Change Data Capture si sovrappongono. Quando la transizione viene effettuata, l'istanza di acquisizione obsoleta può essere rimossa.

[!NOTA]

Il numero massimo di istanze di acquisizione che possono essere associate contemporaneamente a un'unica tabella di origine è due.

Relazione tra il processo di acquisizione e l'agente di lettura log della replica transazionale

La logica del processo Change Data Capture è incorporata nella stored procedure sp_replcmds, una funzione del server interna compilata come parte del processo sqlservr.exe e utilizzata anche dalla replica transazionale per raccogliere modifiche dal log delle transazioni. Quando per un database è abilitata solo la funzionalità Change Data Capture, per richiamare sp_replcmds viene creato il processo di acquisizione di SQL Server Agent relativo a Change Data Capture. Se è presente anche la replica, l'agente di lettura log transazionale viene utilizzato da solo per soddisfare le esigenze relative ai dati delle modifiche per entrambi i consumer. Questa strategia consente di ridurre in modo significativo le contese relative al log quando per lo stesso database sono abilitati sia la replica che Change Data Capture.

Il passaggio tra queste due modalità operative per l'acquisizione dei dati delle modifiche viene eseguito automaticamente tutte le volte che lo stato della replica di un database abilitato per Change Data Capture subisce una modifica.

Importante
Per entrambe le istanze della logica di acquisizione è necessario che SQL Server Agent sia in esecuzione affinché il processo venga eseguito.

L'attività principale del processo di acquisizione consiste nell'analisi del log e nella scrittura dei dati delle colonne e delle informazioni relative alla transazione nelle tabelle delle modifiche di Change Data Capture. Per garantire un limite coerente a livello di transazione in tutte le tabelle delle modifiche di Change Data Capture che popola, il processo di acquisizione apre ed esegue il commit della propria transazione a ogni ciclo di analisi. Il processo rileva il momento in cui per le tabelle viene abilitato Change Data Capture e include automaticamente tali tabelle nel set di tabelle per le quali viene eseguito il monitoraggio delle voci di modifica nel log. Analogamente, verrà rilevata anche la disabilitazione di Change Data Capture, provocando la rimozione della tabella di origine dal set di tabelle per le quali viene eseguito il monitoraggio dei dati delle modifiche. Quando l'elaborazione relativa a una sezione del log è completata, il processo di acquisizione segnala la logica di troncamento del log al server, che utilizza queste informazioni per identificare le voci di log idonee per il troncamento.

[!NOTA]

Quando un database è abilitato per Change Data Capture, anche se la modalità di recupero è impostata sul recupero semplice, il punto di troncamento del log non avanzerà finché tutte le modifiche contrassegnate per l'acquisizione non sono state raccolte dal processo di acquisizione. Se il processo di acquisizione non è in esecuzione e sono disponibili modifiche per la raccolta, l'esecuzione di CHECKPOINT non comporterà il troncamento del log.

Il processo di acquisizione viene inoltre utilizzato per gestire la cronologia relativa alle modifiche DDL apportate alle tabelle con rilevamento. Le istruzioni DDL associate a Change Data Capture inseriscono voci nel log delle transazioni del database ogni volta che una tabella o un database per cui la funzionalità Change Data Capture è abilitata viene eliminato o ogni volta che vengono aggiunte, modificate o eliminate colonne di una tabella per cui tale funzionalità è abilitata. Tali voci di log vengono elaborate dal processo di acquisizione, che successivamente invia gli eventi DDL associati alla tabella cdc.ddl_history. È possibile ottenere informazioni sugli eventi DDL che interessano le tabelle con rilevamento mediante la stored procedure sys.sp_cdc_get_ddl_history.

Processi dell'agente di Change Data Capture

A un database abilitato per la funzionalità Change Data Capture sono in genere associati due processi di SQL Server Agent, uno utilizzato per popolare le tabelle delle modifiche del database e uno responsabile della pulizia di tali tabelle. Entrambi i processi sono costituiti da un solo passaggio che esegue il comando Transact-SQL. Il comando Transact-SQL richiamato è una stored procedure definita di Change Data Capture che implementa la logica del processo. I processi vengono creati nel momento in cui per la prima tabella del database viene abilitato Change Data Capture. Mentre il processo di pulizia viene sempre creato, il processo di acquisizione verrà creato solo se non sono state definite pubblicazioni transazionali per il database. Il processo di acquisizione viene creato inoltre quando per un database sono abilitati sia Change Data Capture che la replica transazionale e il processo di lettura log viene rimosso poiché al database non sono più associate pubblicazioni definite.

Sia il processo di acquisizione che quello di pulizia vengono creati utilizzando parametri predefiniti. Il processo di acquisizione viene avviato immediatamente. L'esecuzione è continua e la capacità di elaborazione prevede un massimo di 1.000 transazioni per ciclo di analisi con un'attesa di 5 secondi tra un ciclo e l'altro. Il processo di pulizia viene eseguito quotidianamente alle 02.00. Tale processo consente di memorizzare le voci della tabella delle modifiche per 4320 minuti o 3 giorni, rimuovendo un massimo di 5000 voci con una singola istruzione Delete.

I processi dell'agente di Change Data Capture vengono rimossi quando la funzionalità viene disabilitata per il database. Il processo di acquisizione può essere rimosso anche quando la prima pubblicazione viene aggiunta a un database e le funzionalità Change Data Capture e replica transazionale sono entrambe abilitate.

Internamente, i processi dell'agente Change Data Capture vengono creati e rilasciati tramite le stored procedure sys.sp_cdc_add_job e sys.sp_cdc_drop_job, rispettivamente. Tali stored procedure vengono inoltre esposte in modo che gli amministratori possano controllare la creazione e la rimozione dei processi.

Un amministratore non dispone di alcun controllo esplicito sulla configurazione predefinita dei processi dell'agente di Change Data Capture. A tale scopo viene fornita la stored procedure sys.sp_cdc_change_job che consente di modificare i parametri di configurazione predefiniti. La stored procedure sys.sp_cdc_help_jobs consente inoltre di visualizzare i parametri di configurazione correnti. Sia il processo di acquisizione che quello di pulizia estraggono parametri di configurazione dalla tabella msdb.dbo.cdc_jobs all'avvio. Qualsiasi modifica apportata a tali valori mediante sys.sp_cdc_change_job non avrà effetto finché il processo non viene arrestato e riavviato.

Per avviare e arrestare i processi dell'agente di Change Data Capture, sono disponibili due stored procedure aggiuntive, sys.sp_cdc_start_job e sys.sp_cdc_stop_job.

[!NOTA]

L'avvio e l'arresto del processo di acquisizione non comportano alcuna perdita dei dati delle modifiche, ma consentono solo di evitare che il processo esegua l'analisi del log per inserire voci di modifica nelle tabelle delle modifiche. Una strategia ragionevole per impedire che l'analisi del log aumenti il carico durante i periodi in cui la richiesta è massima consiste nell'arresto del processo e nel successivo riavvio quando la richiesta è diminuita.

Entrambi i processi di SQL Server Agent sono stati progettati per essere sufficientemente flessibili e configurabili in modo da soddisfare le esigenze di base degli ambienti in cui viene utilizzata la funzionalità Change Data Capture. In entrambi casi, tuttavia, le stored procedure sottostanti che forniscono le funzionalità di base sono state esposte in modo che sia possibile applicare ulteriore personalizzazione.

Change Data Capture non funziona correttamente quando il servizio Motore di database o SQL Server Agent è in esecuzione con l'account NETWORK SERVICE. Questa situazione può generare l'errore 22832.