Der SQL Server liefert standardmäßig verschiedene Werkzeuge, mit denen Änderungen nachverfolgt werden können. Wir stellen die unterschiedlichen Tools kurz vor und erläutern, inwiefern sie für eine Nachverfolgung von DML-Abfragen Insert/Update/Delete geeignet sind und welche Vor- und Nachteile mit ihrer Nutzung einhergehen. Dieser Beitrag erscheint in zwei Teilen.
Im ersten Teil wurde bereits auf zwei Werkzeuge zur Änderungsnachverfolgung eingegangen: das SQL-Server-Transaktionsprotokoll und die SQL-Server-Überwachung. Die beiden Werkzeuge unterscheiden sich stark im Hinblick auf Verwendung und Einrichtung. Wo das Transaktionsprotokoll ohne jegliche Konfiguration nach der Erstellung einer Datenbank automatisch mitläuft, muss bei der SQL-Server-Überwachung erst über die manuelle Einrichtung konkretisiert werden, was überwacht werden soll. Bei der Auswertung hatte dann die SQL-Server-Überwachung deutliche Vorzüge gegenüber dem sehr technisch anmutenden Transaktionsprotokoll.
In diesem Blog beschäftigen wir uns mit drei weiteren Werkzeugen zur Änderungsnachverfolgung:
- SQL Server Change Data Capture
- SQL Server Triggers
- SQL-Server-Änderungsnachverfolgung (Change Tracking)
Beispielabfragen für die Änderungsnachverfolgung
Um alle Methoden der Änderungsnachverfolgung miteinander vergleichen zu können, bietet sich wieder die Tabelle „T_S_User“ aus der Chair-Datenbank an, die bereits im ersten Teil verwendet wurde. Für die Änderungsnachverfolgung werden die DML-Statements (Data Manipulation Language) genutzt, die auf das CREATE TABLE-Statement folgen:
/* Create Table-Statement*/
CREATE TABLE [dbo].[T_S_User](
[Username] [varchar](50) NOT NULL,
[UserID] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, AL-LOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMA-RY]
) ON [PRIMARY]
/*INSERT-Statement*/
INSERT INTO [dbo].[T_S_User]
SELECT 'BC\TEST_2021' AS [Username]
,5 AS [UserID]
/*UPDATE-Statement*/
UPDATE t
SET [Username] = 'BC\Test_2022'
FROM [dbo].[T_S_User] t
WHERE [UserID] = 5
/*DELETE-Statement*/
DELETE t
FROM [dbo].[T_S_User] t
WHERE [UserID] = 5
SQL Server Change Data Capture
Die SQL Server Change Data Capture erzeugt für jede zu überwachende Tabelle eine Protokolltabelle, über die Änderungen nachverfolgt werden können.
Einrichtung
Bevor mit der Einrichtung gestartet wird, sollte über die sys-Tabelle „databases“ abgefragt werden, ob die Datenbank nicht bereits für Change Data Capture (CDC) aktiviert wurde. Im vorliegenden Fall steht der Parameter auf 0 und die Aktivierung hat somit noch nicht stattgefunden.
Abb. 1: Abfrage, ob die Datenbank bereits für Change Data Capture aktiviert wurde
Um die Datenbank für Change Data Capture zu aktivieren, muss der db-Owner oder der sys-Admin auf der Datenbank die sys-Prozedur sys.sp_cdc_enable_db ausführen. Anschließend erscheinen unter „Systemtabellen“ und unter „Gespeicherte Systemprozeduren“ neue Tabellen und Prozeduren im CDC-Schema. Um die Einrichtung der Änderungsnachverfolgung abzuschließen, muss außerdem definiert werden, für welche Tabelle die Change Data Capture aktiviert werden soll. Das geschieht über die sys-Prozedur sys.sp_cdc_enable_table. Der Prozedur müssen dabei zwingend die Parameter für das Schema, den Namen der Tabelle und den Namen der neu erstellten CDC-Rolle mitgegeben werden. Dem Parameter für die Rolle kann auch der Wert NULL mitgegeben werden. Damit wird auf die Erstellung einer eigenen Rolle verzichtet – so können nur der db-Owner und der sys-Admin die Änderungsnachverfolgung einsehen.
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'T_S_User',
@role_name = NULL
Über die sys-Tabelle „tables“ kann, analog zum Aufruf der sys-Tabelle „databases“, geprüft werden, ob die Tabelle bereits für CDC aktiviert wurde.
Abb. 2: Zweite Abfrage, ob die Tabelle für Change Data Capture aktiviert wurde
Mit der Aktivierung der ersten Tabelle für CDC werden zwei Aufträge namens cdc.[DB_Name]_capture und cdc.[DB_Name]_cleanup erstellt. Diese verwalten die Änderungsnachverfolgung. Zudem werden die Systemtabellen um die Tabelle cdc.[Tabelle]_CT erweitert, in der die Änderungen für die spezifizierte Tabelle protokolliert werden.
Abb. 3: Erstellte Aufträge bei Aktivierung der ersten Tabelle für CDC
Abfrage der Protokollierung
Nachdem die Einrichtung abgeschlossen ist, können die Beispielabfragen INSERT/UPDATE/DELETE auf der aktivierten Tabelle ausgeführt werden. Das Ergebnis ist dann in der Protokolltabelle – in diesem Beispiel [cdc].[dbo_T_S_User_CT]) – zu finden:
Abb. 4: Abfrage der Protokolltabelle
Die Protokolltabelle ist sehr technisch aufgebaut. Über die Spalte „_$start_lsn“ (LSN = log sequence number) lässt sich ablesen welche Protokolldatensätze zueinander gehören. Die Spalte „__$command_id“ gibt deren Reihenfolge innerhalb der Transaktion an. Die Spalte „__$operation“ zeigt, welche Transaktion durchgeführt wurde (1 = Delete, 2 = Insert). Die Spalten ohne das Präfix „__$“ sind die aus der originären Tabelle stammenden Spalten. CDC lässt dabei keine Rückschlüsse auf den User zu, der die Änderungen vorgenommen hat. Lediglich durch die Zusammenführung mit der Tabelle [cdc].[lsn_time_mapping] lässt sich die Ausführungszeit auslesen. Das folgende Skript dient der besseren Lesbarkeit der Protokolldatei:
SELECT U.[__$start_lsn] AS TransaktionsID
,TM.tran_begin_time AS Transaktions_Start_Zeitstempel
,U.[__$command_id] AS Reihenfolge_pro_Transaktion
,xaTT.TransaktionsTyp
,U.[Username]
,U.[UserID]
FROM [cdc].[dbo_T_S_User_CT] U
LEFT JOIN [cdc].[lsn_time_mapping] TM
ON U.[__$start_lsn] = TM.start_lsn
CROSS APPLY (
SELECT CASE WHEN U.[__$operation] = 1 THEN 'DELETE' WHEN U.[__$operation] = 2 THEN 'INSERT' END AS TransaktionsTyp
)xaTTDamit ergibt sich folgende Ansicht:
Abb. 5: Ergebnis der Skript-Abfrage der Protokolltabelle
Wie im Transaktionsprotokoll bereits gesehen, wird das Update als ein Delete- und ein Insert-Statement verarbeitetet und protokolliert. (TransaktionsID: 0x0000007A00002F5C0008).
Für jede weitere Tabelle, die in die Änderungsnachverfolgung aufgenommen werden soll, muss die sys-Prozedur sys.sp_cdc_enable_table, wie oben gezeigt, ausgeführt werden. Dabei entsteht für jede überwachte Tabelle auch eine dazugehörige Protokolltabelle. Eine tabellenübergreifende Sicht ist jedoch nicht möglich. Aus der Systemtabelle cdc.change_tables ist ersichtlich, welche Tabellen für die Änderungsnachverfolgung aktiviert worden sind.
Vor- und Nachteile bei der Nutzung von SQL Server Change Data Capture
Die Nutzung der CDC bietet folgende Vorteile:
- Die Einrichtung ist schnell vollzogen.
- Die Überwachung wird auf Tabellenebene vorgenommen. Sie kann damit sehr spezifisch definiert werden und enthält auch die Tabellenspalteninhalte.
Demgegenüber bestehen allerdings auch Nachteile:
- Die Überwachung wird über SQL Server Agent Jobs vollzogen und ist im Fall eines nicht laufenden SQL-Server-Agent-Dienstes nicht aktiv.
- Die Überwachung enthält nur sehr wenige Informationen. Die Information zum User, der die Änderung vorgenommen hat, fehlt.
- Eine Überwachung der gesamten Datenbank wäre mit CDC sehr aufwändig.
SQL Server Trigger
Der SQL Server Trigger als Methode der Änderungsnachverfolgung bietet wohl die meisten Möglichkeiten in der Definition der Überwachung. So viel Individualität kann dann jedoch nur per T-SQL und nicht über einen komfortablen Einrichtungsassistenten definiert werden. Bei den Triggern selbst wird zwischen dem DML-Trigger, dem DDL-Trigger und dem LOGON-Trigger unterschieden. Da im Blog nur auf INSERT/UPDATE/DELETE-Statements eingegangen wird, liegt der Fokus auf den DML-Triggern.
Der grundsätzliche Aufbau des Statements sieht folgendermaßen aus:
CREATE TRIGGER trigger_name
ON { Table name or view name }
[ WITH ]
{ FOR | AFTER | INSTEAD OF }
{ [INSERT], [UPDATE] , [DELETE] }Nachdem die Protokollierungstabelle erstellt wurde, kann mit der Definition des Triggers begonnen werden. In diesem Beispiel definieren wir einen Trigger für alle drei DML-Statements INSERT/UPDATE/DELETE. Über die automatisch erstellten temporären Tabellen „inserted“ und „deleted“ wird geprüft, um welches DML-Statement es sich handelt. Bei einem UPDATE werden beide temporären Tabellen befüllt, wohingegen beim DELETE und INSERT Statement nur die namensgleiche Tabelle befüllt wird. Die Tabellen selbst beinhalten die gelöschten bzw. hinzugefügten Datensätze der Tabelle, auf die sich der Trigger bezieht. Das Skript zum Aufbau der DML-Trigger für die Tabelle T_S_User lautet wie folgt:
CREATE TRIGGER TR_Audit_T_S_User
ON dbo.T_S_User
FOR INSERT, UPDATE, DELETE
AS
DECLARE @TransactionID uniqueidentifier = NEWID()
--Update
IF EXISTS ( SELECT * FROM deleted ) AND EXISTS ( SELECT * FROM inserted )
BEGIN
INSERT INTO [dbo].[T_S_User_Audit]
(
[Username]
,[UserID]
,[Änderung_durch]
,[Änderungsdatum]
,[Transaktionstyp]
,[TransaktionsID]
)
SELECT d.[Username],
d.[UserID],
SYSTEM_USER,
GETDATE() ,
'Update',
@TransactionID
FROM deleted d
INSERT INTO [dbo].[T_S_User_Audit]
(
[Username]
,[UserID]
,[Änderung_durch]
,[Änderungsdatum]
,[Transaktionstyp]
,[TransaktionsID]
)
SELECT i.[Username],
i.[UserID],
SYSTEM_USER,
GETDATE() ,
'Update',
@TransactionID
FROM inserted i
END
--Delete
IF EXISTS ( SELECT * FROM deleted ) AND NOT EXISTS ( SELECT * FROM inserted )
BEGIN
INSERT INTO [dbo].[T_S_User_Audit]
(
[Username]
,[UserID]
,[Änderung_durch]
,[Änderungsdatum]
,[Transaktionstyp]
,[TransaktionsID]
)
SELECT d.[Username],
d.[UserID],
SYSTEM_USER,
GETDATE() ,
'Delete',
@TransactionID
FROM deleted d
END
--Insert
IF EXISTS ( SELECT * FROM inserted ) AND NOT EXISTS ( SELECT * FROM deleted )
BEGIN
INSERT INTO [dbo].[T_S_User_Audit]
(
[Username]
,[UserID]
,[Änderung_durch]
,[Änderungsdatum]
,[Transaktionstyp]
,[TransaktionsID]
)
SELECT i.[Username],
i.[UserID],
SYSTEM_USER,
GETDATE() ,
'Insert',
@TransactionID
FROM inserted i
END
GOAbfrage der Protokollierung
Für die Abfrage der Protokollierung führen wir zunächst wieder die Beispielabfragen auf der Tabelle „T_S_User“ aus. Die zuvor individuell erstellte Audit-Tabelle „T_S_User_Audit“ wird nun über den definiertenTrigger mit den Protokolldatensätzen befüllt. Dies sieht folgendermaßen aus:
Abb. 6: Abfrage der Protokolltabelle
Im Ergebnis erhalten wir sowohl die gewünschten technischen als auch die ursprünglichen Inhalte, die im Trigger-Skript individuell definiert wurden. Um einen Überblick über alle aktiven und inaktiven Trigger zu bekommen, dient die sys-View „triggers“. Hier werden alle Trigger der Datenbank aufgeführt.
Abb. 7: Abfrage der sys-View „triggers“
Vor- und Nachteile bei der Nutzung des SQL Server Trigger
Die Vorteile des SQL Server Triggers zur Änderungsnachverfolgung liegen auf der Hand:
- Trigger bieten den höchsten Individualisierungsgrad der Änderungsnachverfolgungswerkzeuge.
- Die Protokollierung enthält, durch die individuelle Festlegung, alle gewünschten Inhalte.
- Die Protokollierung kann bedarfsgerecht aktiviert und deaktiviert werden.
Einziger Nachteil: Um die Änderungsnachverfolgung einzurichten, sind T-SQL-Kenntnisse vorausgesetzt, da die Trigger nicht über ein Interface definiert werden.
SQL-Server-Änderungsnachverfolgung (Change Tracking)
Das Change Tracking im SQL Server lässt sich relativ einfach einrichten.
Einrichtung
Das Change Tracking im SQL Server kann entweder über die Datenbankeigenschaften oder über das dazugehörige T-SQL-Statement eingerichtet werden. Die Methode über die Datenbankeigenschaften ist sehr komfortabel erreichbar: per Rechtsklick auf die Datenbank und der Auswahl der „Eigenschaften“. Auf der Seite „Änderungsnachverfolgung“ kann das Change Tracking aktiviert werden. Zudem definiert man hier, ob und wann die protokollierten Daten automatisch gelöscht werden sollen.
Abb. 8: Definition der Änderungsnachverfolgung auf Datenbankebene
Mit der Definition der Änderungsnachverfolgung über die Datenbankeigenschaften ist die Funktionalität grundsätzlich für die Datenbank aktiviert. Damit die Änderungen aber tatsächlich protokolliert werden, müssen die gewünschten Tabellen noch aktiviert werden. Diese Einstellung wird in den Tabelleneigenschaften vorgenommen, in der ebenfalls eine Seite „Änderungsnachverfolgung“ enthalten ist. Auch hier kann über das Setzen der Änderungsnachverfolgung auf „true“ die Funktion aktiviert werden. Wichtig ist zu erwähnen, dass die Änderungsnachverfolgung nur bei Tabellen mit Primärschlüssel funktioniert. Damit wäre die Einrichtung bereits abgeschlossen.
Abb. 9: Definition der Änderungsnachverfolgung auf Tabellenebene
Abfrage der Protokollierung
Die Abfrage der Protokollierung geschieht über eine Funktion namens CHANGETABLE. Dieser Funktion muss die jeweilige Tabelle als Parameter übergeben werden, sowie die Änderungsversion, die über einen Zähler alle Änderungen hochzählt (mit dem Wert 0 werden alle Änderungen angezeigt). Die Änderungsnachverfolgung hat jedoch keine Änderungshistorie zu bieten. Das heißt, dass je Primärschlüssel nur die letzte Änderung protokolliert wird. Wenn nun auf ein INSERT zwei UPDATE-Statements auf den gleichen Primärschlüssel folgen, wird lediglich das letzte UPDATE-Statement protokolliert.
Abb. 10: Abfrage der CHANGETABLE-Protokollfunktion
Neben den technischen Feldern (gekennzeichnet über das Präfix „SYS_CHANGE“), wird der jeweils betroffene Primärschlüssel mitgeloggt, sodass, wie im Beispiel zu sehen, die originäre Tabelle gejoint werden kann.
Mit der Abfrage des Beispiel-Statements werden insgesamt zwei Datensätze mitgeloggt. Das hat damit zu tun, dass wir durch das UPDATE-Statement einen weiteren Primärschlüssel generieren und somit schlussendlich für zwei Primärschlüssel den letzten Stand protokolliert haben – in diesem Fall bei beiden das DELETE-Statement, was an der Spalte „SYS_CHANGE_OPERATION“ am „D“ erkennbar ist.
Vor- und Nachteile bei der Nutzung des Change Trackings im SQL Server
Zwei Vorteile fallen bei der SQL-Server-Änderungsnachverfolgung ins Gewicht:
- Bei eingestellter Beibehaltungsdauer besteht keine Gefahr von anwachsenden Logs.
- Die Einrichtung ist schnell vollzogen.
Demgegenüber bestehen allerdings auch zwei Nachteile:
- Es wird lediglich die letzte Änderung je Primärschlüssel protokolliert.
- Das Log enthält sehr wenige Informationen. Es fehlen Angaben zum User, der die Änderung vorgenommen hat, und zum Änderungsdatum.
Fazit
Wie in diesem und im ersten Beitrag zur Änderungsnachverfolgung im SQL Server dargestellt, bietet Microsoft ein breites Spektrum an integrierten Werkzeugen zur Änderungsnachverfolgung. In den meisten Fällen wird die Nutzung von Zusatzsoftware dadurch unnötig.
Welches Tool das Richtige ist, kommt stark auf den Anwendungsfall an: Sollen grundsätzlich alle Änderungen an einer Datenbank nachvollzogen werden können, empfiehlt sich das Transaktionsprotokoll. Dabei kann es sinnvoll sein, Zusatzsoftware zu nutzen, um die Änderungsnachverfolgung benutzerfreundlich zu gestalten. Viele Administratoren benötigen eher eine datenbankweite bzw. serverweite Änderungsnachverfolgung – daher ist das Transaktionsprotokoll nicht zwangsläufig für jeden Datenbank-Nutzer der richtige Ansatz.
Bei individuell zu definierenden Änderungsnachverfolgungen auf Datenbank- oder Objektebene ist die SQL-Server-Überwachung sinnvoll. Sie bietet ein Interface für die Einstellungen der Änderungsnachverfolgung an, welches auch Nutzer ohne SQL-Kenntnisse bedienen können. Zudem kann hier sehr genau festgelegt werden, was überwacht werden soll. Diese Form der Änderungsnachverfolgung bietet sich vor allem an, wenn spezifische Objekte, aber auch die ganze Datenbank detailliert überwacht werden sollen.
Für den höchsten Individualisierungsgrad bei der Änderungsnachverfolgung sind die SQL Server Trigger wohl das beste Mittel. Über die Definition per SQL-Code sind den Überwachungsmöglichkeiten so gut wie keine Grenzen gesetzt. Somit bietet sich diese Form der Überwachung vor allem dann an, wenn man mit den standardisierten Überwachungswerkzeugen an die Grenzen kommt und zudem nur einzelne Objekte überwachen will.
Für jeden Anwendungsfall – vom generischen Ansatz einer Gesamtüberwachung bis hin zur spezialisierten Überwachung einzelner Objekte – gibt es somit ein geeignetes Werkzeug.
