Change Tracking und Change Data Capture im Vergleich

„Winds of Change“
Change Tracking
und
Change Data Capture im Vergleich
Referentin: Anne Apfel
Business Intelligence Consultant
Was sind Change Tracking
und Change Data Capture
Vorstellung der Verfahren
und Vergleich
Handling und
Funktionsweise
Einsatzszenarien und
Erfahrungen
Change Tracking und
Change Data Capture
Was ist das?
•
Verfahren zum Registrieren und Nachverfolgen von Änderungen
an Daten
 Tracking von DML-Operation
„Wer hat wann was mit meinen Daten gemacht!“
•
mit Release des SQL Server 2008
 Built-In Features
•
typischer Einsatz in der BI
 Incremental Load
 Historisierung von Daten
Change Tracking
Funktionsweise
•
Tracking der Tatsache dass sich Daten geändert haben, aber
nicht wie oft und mit welchen Zwischenwerten
 Speichert Primärschlüssel und Art der Änderung
 keine Historisierung
•
Tracking der Änderungen, sowie Abfrage erfolgt per
Versionsnummer (DB-Transaktion)
•
Versionsnummern gültig für gesamte Datenbank
•
Speichert Nettoänderungen
•
Wird auf Datenbankebene, sowie Tabellenebene aktiviert
Change Tracking
Funktionsweise
•
Änderungen werden in internen Tabellen gespeichert
•
Möglichkeit Auto-Cleanup zu nutzen
•
Zugriff auf Änderungsinformationen mittels spezieller
Funktionen
Auf geänderte Daten selbst, mittels zusätzlichen Join auf
Originaltabelle
•
Voraussetzungen: - Tabelle muss Primärschlüssel besitzen
- empfohlenes Kompatibilitätslevel von min
90 empfohlen (SQL Server 2005)
- Snapshot Isolation ebenso
Change Tracking
•
Funktionsweise
Synchrones Tracking-Verfahren
SELECT * FROM SP.PRODUCT SP
RIGHT OUTER JOIN CHANGETABLE(CHANGES SP.PRODUCT, 300) AS CT
ON SP.ID = CT.ID
6 Zusätzliche Spalten:
- SYS_CHANGE_VERSION
- SYS_CHANGE_CREATION_VERSION
- SYS_CHANGE_OPERATION
- SYS_CHANGE_COLUMNS
- SYS_CHANGE_CONTEXT
- SYS_CHANGE_ID
Change Tracking
Vorteile
•
Keine Eigenentwicklung notwendig
 Verringert Entwicklerzeit
 Keine Änderungen am Datenbankschema notwendig
•
Automatischer Clean-Up Mechanismus verfügbar
•
Change-Tracking-Funktionen ermöglichen einfaches Handling
•
Geringerer Overhead als Eigenlösungen (bspw. Trigger)
•
Verlässliche Resultate bei Überlappenden und
Langzeittransaktionen
•
Über SQL-Server-Managementstudio-Oberfläche konfigurierbar
Change Data Capture
•
•
Funktionsweise
Trackt die Tatsache dass Änderungen am Datensatz passiert
sind und die Änderungen selbst
Bei Update in der Before-Update und After-Update-Version
 Historisierung
•
Aktivierung erfolgt auf Datenbankebene, Tabellenebene und
opt. für einzelne Spalten
•
Fügt neues Schema cdc hinzu
•
Änderungen werden in zugehörigen ChangeTables gespeichert
 enthalten zu trackende
Spalten, sowie weitere
Metadateninformationen
Change Data Capture
Funktionsweise
•
Speichert Änderungen per LSN, die gegen Zeitentabelle
gemappt werden können
•
Speichert Bruttoänderungen, Nettoänderungen können
abgefragt werden
•
Aktivieren von CDC generiert 2
SQL Server Agent Jobs
•
Voraussetzungen: - Enterprise oder Developer Edition
- SQL Server Agent aktiviert
Change Tracking
•
Funktionsweise
Asynchrones Tracking-Verfahren
5 zusätzliche Metadatenfelder:
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask
__$operation  1 = Delete, 2 = Insert, 3 = Before Update, 4 = After Update
Change Data Capture
Vorteile
•
Keine Eigenentwicklung notwendig
 Verringert Entwicklerzeit
 Keine Änderungen am Datenbankschema notwendig
•
Automatischer Clean-Up Mechanismus verfügbar
•
zeigt Daten vor und nach dem Update
•
Geringer Overhead durch asynchrones Verfahren
•
Verlässliche Resultate bei Überlappenden und
Langzeittransaktionen
Change Tracking und
Change Data Capture
Vergleich
Change Tracking
synchron
Change Data Capture
asynchron
Änderungen am Datensatz aus
Ursprungstabelle
interessiert ob sich ein Datensatz verändert
hat, nicht wie
Benötigt keinen SQL-Server-Agent
Snapshot-Isolation empfohlen
Änderungen am Datensatz aus replizierter Tabelle
Clean-Up über Datenbankfeature
Änderungsdaten sofort abfragbar
Clean-Up über Agent Job
Änderungsdaten evt. leicht verzögert abfragbar
Abfrage der Daten anhand Versionsnummer
weniger Ressourcen beim Tracking (Storage)
Abfrage von Daten anhand einer
LogSequenceNummer
mehr Ressourcen beim Tracking (Storage)
mehr Ressourcen bei den SQL-Abfragen
nutzt TempDB
Tracking aller Änderungen
DDL Restriktionen
weniger Ressourcen bei SQL-Abfragen
nutzt TransactionLog
Tracking nur tatsächlicher Änderungen
Keine DDL Restriktionen
interessiert ob sich ein Datensatz verändert hat,
wie und das Ergebnis vor und nach der Änderung
Benötigt SQL-Server-Agent
Snapshot-Isolation nicht notwendig
Wem das noch nicht
reicht…

Links:
Change Tracking
http://msdn.microsoft.com/en-us/library/cc280462(v=sql.105).aspx
Change Data Capture
http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105)
Vergleich und Übersicht
http://msdn.microsoft.com/en-us/library/cc280519(v=sql.105)
Change Tracking
Änderungen an Objekten
Vorgang
Aktivierte Änderungsnachverfolgung
DROP TABLE
Alle Änderungsnachverfolgungsinformationen für die gelöschte Tabelle werden entfernt.
ALTER TABLE DROP
CONSTRAINT
Ein Versuch, die PRIMARY KEY-Einschränkung zu löschen, schlägt fehl. Die Änderungsnachverfolgung muss
deaktiviert werden, damit eine PRIMARY KEY-Einschränkung gelöscht werden kann.
ALTER TABLE DROP
COLUMN
Ist eine gelöschte Spalte Bestandteil des Primärschlüssels, kann die Spalte nicht gelöscht werden, unabhängig
von der Änderungsnachverfolgung.
Ist die gelöschte Spalte kein Bestandteil des Primärschlüssels, kann die Spalte gelöscht werden. Es sollte jedoch
zuerst die Auswirkung auf eine beliebige Anwendung, die diese Daten synchronisiert, verstanden werden. Ist die
Spaltenänderungsnachverfolgung für die Tabelle aktiviert, kann die Spalte trotzdem als Bestandteil der
Änderungsnachverfolgungsinformationen zurückgegeben werden. Die Anwendung ist für die Behandlung der
gelöschten Spalte zuständig.
ALTER TABLE ADD
COLUMN
Wird der änderungsnachverfolgten Tabelle eine neue Spalte hinzugefügt, wird das Hinzufügen der Spalte nicht
verfolgt. Nur die Updates und Änderungen, die an der neuen Spalte vorgenommen werden, werden
nachverfolgt.
ALTER TABLE ALTER
COLUMN
Datentypänderungen einer Nicht-Primärschlüsselspalte werden nicht nachverfolgt.
ALTER TABLE SWITCH
Der Partitionswechsel schlägt fehl, wenn für eine oder beide Tabellen die Änderungsnachverfolgung aktiviert ist.
DROP INDEX oder
ALTER INDEX DISABLE
Der Index, der den Primärschlüssel erzwingt, kann nicht gelöscht oder deaktiviert werden.
TRUNCATE TABLE
Das Abschneiden einer Tabelle kann für eine Tabelle ausgeführt werden, für die die Änderungsnachverfolgung
aktiviert ist. Die mit dem Vorgang gelöschten Zeilen werden von dem Vorgang jedoch nicht nachverfolgt, und die
minimale gültige Version wird aktualisiert. Führt eine Anwendung eine Versionsprüfung durch, ergibt die
Prüfung, dass die Version zu alt ist und dass eine erneute Initialisierung erforderlich ist. Dies ist identisch mit der
Deaktivierung der Änderungsnachverfolgung und der anschließenden erneuten Aktivierung für die Tabelle.
Change Tracking
Funktionen und
Katalogsichten
Funktion
Beschreibung
Ruft Nachverfolgungsinformationen für alle Änderungen an einer Tabelle
ab, die seit einer angegebenen Version vorgenommen wurden, oder gibt
CHANGETABLE (Transact-SQL)
die aktuellen Änderungsnachverfolgungsinformationen für eine
bestimmte Zeile an.
Ruft die minimale Version ab, die zum Abrufen von
CHANGE_TRACKING_MIN_VALID
Änderungsnachverfolgungsinformationen aus einer bestimmten Tabelle
_VERSION (Transact-SQL)
gültig ist, wenn die CHANGETABLE-Funktion verwendet wird.
Ruft eine Version ab, die mit der letzten Transaktion, für die Commit
CHANGE_TRACKING_CURRENT_
ausgeführt wurde, verknüpft ist. Sie können diese Version verwenden,
VERSION (Transact-SQL)
wenn Sie das nächste Mal Änderungen mit CHANGETABLE aufzählen.
CHANGE_TRACKING_IS_COLUM Interpretiert den SYS_CHANGE_COLUMNS-Wert, der von der
N_IN_MASK (Transact-SQL)
CHANGETABLE(CHANGES …)-Funktion zurückgegeben wird.
WITH
Aktiviert die Spezifikation eines Änderungskontexts, z. B. eine AbsenderCHANGE_TRACKING_CONTEXT ID, wenn eine Anwendung Daten ändert.
Katalogsicht
Beschreibung
sys.change_tracking_databases
(Transact-SQL)
Gibt eine Zeile für jede Datenbank in der Instanz von SQL Server zurück,
für die die Änderungsnachverfolgung aktiviert ist.
sys.change_tracking_tables
(Transact-SQL)
Gibt eine Zeile für jede Tabelle in der aktuellen Datenbank zurück, für
die die Änderungsnachverfolgung aktiviert ist.
Change Data Capture
Funktionen
cdc.fn_cdc_get_all_changes_<capture_instance>
( from_lsn , to_lsn , '<row_filter_option>' )
Returns one row for each change applied to the
source table within the specified LSN
sys.fn_cdc_has_column_changed
( 'capture_instance','column_name' ,
update_mask )
Has captured column been updated
cdc.fn_cdc_get_net_changes_<capture instance>
( from_lsn , to_lsn , '<row_filter_option>' )
Returns one net change row for each source row
changed within the specified LSN
sys.fn_cdc_increment_lsn ( lsn_value )
Next LSN
sys.fn_cdc_decrement_lsn ( lsn_value )
Previous LSN
sys.fn_cdc_is_bit_set ( position , update_mask )
Has captured column been updated
sys.fn_cdc_get_column_ordinal
( 'capture_instance','column_name')
Returns the column ordinal of the specified column
sys.fn_cdc_map_lsn_to_time ( lsn_value )
Datetime value from the tran_end_time column in
the cdc.lsn_time_mapping table
sys.fn_cdc_get_max_lsn ()
Maximum LSN from the start_lsn column in the
cdc.lsn_time_mapping system table
sys.fn_cdc_map_time_to_lsn ( tracking_time)
LSN Value from the start_lsn column in the
cdc.lsn_time_mapping table
sys.fn_cdc_get_min_lsn ('capture_instance_name' )
Start LSN from Capture Instance
Change Data Capture
Tabellen
cdc.<capture_instance>_CT
Gibt eine Zeile für jede Änderung an einer aufgezeichneten Spalte in der zugeordneten
Quelltabelle zurück.
cdc.captured_columns
Gibt eine Zeile für jede in einer Aufzeichnungsinstanz nachverfolgten Spalte zurück.
cdc.change_tables
Gibt eine Zeile pro Änderungstabelle in der Datenbank zurück.
cdc.ddl_history
Gibt eine Zeile für jede Änderung an der Datendefinitionssprache (DDL) zurück, die an
Tabellen vorgenommen wurde, die für Change Data Capture aktiviert wurden.
cdc.lsn_time_mapping
Gibt eine Zeile für jede Transaktion zurück, für die Zeilen in einer Änderungstabelle
aufgezeichnet sind. Diese Tabelle wird verwendet, um die Commitwerte der
Protokollfolgenummer (LSN) mit dem Commitzeitpunkt der Transaktion zu verknüpfen.
cdc.index_columns
Gibt eine Zeile für jede Indexspalte zurück, die einer Änderungstabelle zugeordnet ist.
dbo.cdc_jobs (Transact-SQL)
Gibt die Konfigurationsparameter für Change Data Capture-Agent-Aufträge zurück.