Il monitoraggio delle modifiche per SQL Server è una tecnologia flessibile e facile da usare per il monitoraggio delle tabelle per inserimenti, aggiornamenti ed eliminazioni. In questo post, discuterò come iniziare con il monitoraggio delle modifiche in SQL Server e mostrerò un esempio di come iniziare con esso.
Change Tracking in SQL Server
Change tracking è un meccanismo leggero per tracciare le righe inserite, aggiornate ed eliminate nelle tabelle monitorate dal change tracking. Il monitoraggio delle modifiche è apparso per la prima volta in SQL Server 2008 ed è stato in tutte le versioni da allora. Ancora meglio, change tracking è disponibile in ogni edizione di SQL Server, anche la free express edition.
In poche parole, ecco come funziona: Nelle tabelle in cui è abilitato il tracciamento delle modifiche, la modifica netta di ogni riga viene tracciata internamente ed è accessibile tramite le funzioni di tracciamento delle modifiche. Ogni modifica ha un ID di versione collegato ad esso e ci sarà un nuovo ID di versione per ogni riga inserita, aggiornata o eliminata. La versione change tracking è un intero a 8 byte (BIGINT) che riflette l’ID di modifica più recente in quel database. È importante notare che la versione di monitoraggio delle modifiche non è specifica della tabella: un’operazione DML in qualsiasi tabella tracciata in ciascun database incrementerà il numero di versione. Il numero di versione sarà sempre sequenziale, ma non sarà necessariamente contiguo all’interno di una singola tabella se è abilitata più di una tabella per il monitoraggio delle modifiche.
Il monitoraggio delle modifiche è abilitato a livello di database. Successivamente, ogni tabella che verrà monitorata deve essere inserita individualmente nel monitoraggio delle modifiche. Ogni tabella da monitorare con change tracking deve avere una chiave primaria, poiché si tratta dell’identificatore a livello di riga utilizzato per segnalare le operazioni DML all’interno di change tracking. Quando abiliti il monitoraggio delle modifiche a livello di tabella, puoi scegliere di tenere traccia delle colonne modificate nell’aggiornamento più recente, il che ti darà maggiore visibilità su ciò che è stato modificato.
Una volta impostato, l’utilizzo di change tracking è un processo relativamente semplice. Ci sono alcune funzioni, in particolare CHANGE_TRACKING_CURRENT_VERSION() e CHANGETABLE(), che possono essere utilizzate per controllare il timbro della versione corrente nel monitoraggio delle modifiche e recuperare l’elenco delle modifiche recenti. Dimostrerò entrambe queste funzioni a breve.
Change Tracking non è Audit Logging
Farò attenzione a non usare le parole audit o logging per descrivere change tracking. Permettetemi di essere chiaro: questo non è un meccanismo di registrazione completo. La cronologia delle modifiche non viene monitorata affatto – il monitoraggio delle modifiche riporta solo il fatto che si è verificata una modifica, ma non mantiene la cronologia delle versioni. Si consideri il caso di una riga di dati con un ID di 1234. Quella riga viene inserita, quindi aggiornata 5 volte e quindi eliminata. Il monitoraggio delle modifiche non mostra la cronologia di inserimento, aggiornamento ed eliminazione; piuttosto, segnalerebbe solo la modifica netta, che l’ID riga 1234 è stato eliminato. Se il processo di caricamento richiede una cronologia di registrazione dettagliata per ogni modifica (piuttosto che solo il delta di tutte le modifiche), dovresti usare qualcosa come change data capture.
Impostazione del monitoraggio delle modifiche in SQL Server
L’attivazione del monitoraggio delle modifiche a livello di tabella è un processo in due fasi. Innanzitutto, deve essere abilitato sul database. Questo può essere fatto attraverso l’interfaccia utente nelle Proprietà del database, nella scheda Change Tracking.
Come mostrato, non c’è molto da configurare quando si abilita il monitoraggio delle modifiche su un database. È sufficiente impostare il valore di monitoraggio delle modifiche su True per impostare il monitoraggio delle modifiche per quel database. Facoltativamente, è possibile modificare anche il valore del periodo di conservazione. Il valore predefinito è 2 giorni, che ho sovrascritto in questo esempio per utilizzare invece 14 giorni. Come con la maggior parte delle operazioni dell’interfaccia utente, c’è un comando T-SQL per fare la stessa cosa. Il comando per impostare il tracciamento delle modifiche su questo database è riportato di seguito.
Dopo questo passaggio, il monitoraggio delle modifiche è abilitato, ma non sta ancora tracciando nulla. Deve ancora essere abilitato per ogni tabella da tracciare. L’interfaccia utente delle proprietà della tabella rende questo molto semplice.
Come mostrato, la semplice modifica del valore di monitoraggio delle modifiche a True consente il monitoraggio delle modifiche per questa tabella. In questo esempio ho anche scelto di tenere traccia delle colonne modificate durante gli aggiornamenti (più su questo in un po’).
L’ultimo passaggio precedente verrebbe ripetuto per ogni tabella da tracciare nel monitoraggio delle modifiche. Una volta abilitato il monitoraggio delle modifiche, tutte le modifiche (inserimenti, aggiornamenti o eliminazioni) a tale tabella verranno memorizzate nella cache del monitoraggio delle modifiche.
Impostazione del tracciamento delle modifiche
Per l’esempio precedente, inserirò, aggiornerò ed eliminerò alcuni dati per dimostrare come accedere ai dati di tracciamento delle modifiche generati per tali operazioni DML. Per riferimento, ecco la struttura della tabella.
Ho mostrato in precedenza come abilitare il monitoraggio delle modifiche per una singola tabella utilizzando l’interfaccia utente. Preferisco usare T-SQL per questa attività, poiché è più facilmente ripetibile. Abilitare il tracciamento delle modifiche per la tabella che ho creato sopra può essere fatto come mostrato qui:
Ricorda che ho menzionato prima che il tracciamento delle modifiche utilizza un ID versione per tracciare la versione corrente delle tabelle tracciate. Quell’ID di versione è il nostro marcatore temporale per rilevare le modifiche. Per recuperare quel valore, c’è una funzione molto semplice: CHANGE_TRACKING_CURRENT_VERSION(). È usato come mostrato di seguito.
Sul mio sistema di test, questo valore è 470 (dal momento che ho eseguito diversi test prima di scrivere). Questo è il punto di partenza e qualsiasi modifica apportata da questo punto in avanti attiverebbe un nuovo numero di versione. Prenderò nota di quel valore e ora apporterò alcune modifiche alla tabella sopra descritta. Inserirò una manciata di righe per mostrare come il monitoraggio delle modifiche visualizza gli inserti.
Dopo aver inserito queste sei righe, controllo nuovamente il valore CHANGE_TRACKING_CURRENT_VERSION() e trovo che il valore sia ora 476. È stato aumentato di 6-uno per riga inserita, che è quello che mi aspetterei.
Utilizzo delle funzioni di tracciamento delle modifiche
Successivamente, utilizzeremo la funzione di tracciamento delle modifiche CHANGETABLE() per mostrare le modifiche nette in questa tabella.
Per abbattere questo:
- CHANGETABLE è la funzione di sistema con valori di tabella che restituirà l’elenco delle modifiche memorizzate in change tracking
- LE modifiche indicano che sto cercando le modifiche poiché la versione specificata
- @ver è la variabile che ho impostato per memorizzare il numero di versione. CHANGETABLE restituirà tutti i risultati che riflettono le modifiche da questa versione. Nota che puoi usare una variabile come ho fatto io, o semplicemente passare un numero scalare (usare il letterale 470 qui avrebbe compiuto la stessa cosa)
Quando eseguo il codice sopra, ricevo il seguente set di risultati.
Questo mi dice che la versione di inserire e/o aggiornare, l’operazione (I, U, o D per inserire, aggiornare o cancellare, rispettivamente), la colonna maschera per le operazioni di aggiornamento (di più su questo momentaneamente), e la chiave primaria della riga interessate da questo cambiamento. Poiché CHANGETABLE () restituisce una tabella, potrei facilmente unire questo risultato alla tabella originale per vedere l’operazione di modifica insieme ai dati correnti in quella tabella.
Questo sarà un po ‘ diverso per un’operazione di aggiornamento. Successivamente eseguirò una dichiarazione di aggiornamento, ma prima, noterò la versione corrente di change tracking (che è ancora 476).
Ora l’istruzione update, che aggiornerà due righe nella tabella:
Ora quando eseguo il codice CHANGETABLE () dall’alto, usando la versione di tracciamento delle modifiche più recente (476) come punto di partenza, ottengo un set di risultati diverso:
Si tratta dei metadati per tutte le modifiche dalla versione 476, che include solo le due righe aggiornate dall’istruzione UPDATE sopra. Si noti che la versione di creazione è null, perché questa modifica era un aggiornamento, non un inserto. Inoltre, il valore SYS_CHANGE_COLUMNS è ora popolato, anche se il valore non ci mostra realmente cosa è cambiato (ancora). Questo è un buon momento per parlare della funzione di tracciamento delle modifiche CHANGE_TRACKING_IS_COLUMN_IN_MASK (). Questa funzione controllerà se la colonna specificata è stata aggiornata dalla versione più recente. La sua sintassi è un po ‘ eccentrica, ma per verificare se il MiddleName è stato aggiornato, la query sarebbe simile a questa:
Onestamente, non lo so che ho mai usato la funzione CHANGE_TRACKING_IS_COLUMN_IN_MASK. È un po ‘ doloroso perché devi eseguirlo per ogni colonna che vuoi controllare. La maggior parte del mio lavoro è nel data warehousing e ho incontrato alcuni casi in cui ho bisogno di sapere esattamente quali colonne sono state aggiornate – voglio solo sapere se la riga è stata aggiornata. Tuttavia, per altri scenari (specialmente in OLTP), posso vedere la necessità di questo.
Ho dimostrato inserti e aggiornamenti. Diamo un’occhiata a come sarebbe un’eliminazione. Ancora una volta, prenderò nota del numero di versione corrente-478-per la prossima operazione. Ora cancellerò una riga di dati:
Dopo aver eliminato una riga, eseguirò di nuovo CHANGETABLE () per vedere quali rapporti di monitoraggio delle modifiche per questa operazione.
Trovo la riga che ho cancellato nell’ultima operazione, con SYS_CHANGE_OPERATION impostato su D (delete):
Ora, ricordate che il numero di versione fa la differenza qui! Il numero di versione passato in CHANGETABLE () è il punto di partenza per tutte le modifiche restituite da tale funzione. Attraverso questo esercizio ho controllato i risultati di tracciamento delle modifiche dopo ogni operazione DML. Tuttavia, posso impostare il numero di versione iniziale su qualsiasi numero di versione valido o semplicemente utilizzare NULL per ottenere tutti i risultati di tracciamento delle modifiche disponibili per quella tabella. Per dimostrare, Ill impostare il valore alla versione 470 – il punto di partenza prima di eventuali aggiornamenti-per mostrare ciò che la storia completa sarebbe simile. Quando eseguo di nuovo CHANGETABLE () usando la nostra versione di tracciamento delle modifiche originale, ottengo quanto segue:
Ci sono un paio di sfumature prevedibili qui. Prima di tutto, la riga che mostra l’ID del record di 1 (che era il record di Phoebe Buffay che ho cancellato) si presenta semplicemente come un’operazione di eliminazione, anche se questa riga è stata inserita e successivamente cancellata dal numero di versione iniziale. Ricorda, è il delta che verrà mostrato-ogni operazione contro quella riga non viene mantenuta nel tracciamento delle modifiche. Per gli ID numerati 2 e 4-che erano le due righe che ho inserito e successivamente aggiornato-SYS_CHANGE_OPERATION mostra un inserto, anche se abbiamo aggiornato entrambi i record dopo l’inserimento. Il tell è che SYS_CHANGE_VERSION e SYS_CHANGE_CREATION_VERSION su queste righe non corrispondono, indicando che la modifica più recente non era l’insert.
Conclusione
Change tracking è un mezzo semplice e leggero di rilevamento delle modifiche in SQL Server. L’utilizzo del change tracking consente una facile identificazione di dati nuovi, modificati e cancellati, eliminando la necessità di confronti a forza bruta. Nel mio prossimo post, guarderò questo da una prospettiva ETL, integrando il monitoraggio delle modifiche in un processo di carico end-to-end.