Dieser Beitrag bietet einen Überblick über die vom SQL Server standardmäßig ausgelieferten Werkzeuge zur Änderungsnachverfolgung. Er stellt unterschiedliche Tools vor und erklärt, welche für eine Nachverfolgung von Data-Manipulation-Language-Abfragen (DML) Insert/Update/Delete geeignet sind und welche Vor- und Nachteile diese Tools haben. Der Beitrag erscheint in zwei Teilen. Im ersten Teil erläutern wir die Änderungsnachverfolgung mit dem SQL-Server-Transaktionsprotokoll und der SQL-Server-Überwachung.
Der SQL Server verfügt über eine Vielzahl an integrierten Möglichkeiten zur Nachverfolgung von Änderungen. Die Bandbreite der zur Verfügung stehenden Werkzeuge ist groß und vielfältig: Einige können sehr individuell auf die Nachverfolgungsbedürfnisse angepasst werden, andere sind wiederum sehr starr in ihren Konfigurationsmöglichkeiten. Ein weiteres Unterscheidungsmerkmal zwischen den Werkzeugen ist die Detailstufe, in der die Änderungsnachverfolgung möglich ist. Je nach Anwendungsgebiet kann also das eine oder das andere Tool besser geeignet sein.
Um herauszufinden, welches Werkzeug das richtige ist, werden im Folgenden unterschiedliche Tools und ihre Vor- und Nachteile vorgestellt. Dabei wird unser Blick auf die Werkzeuge von der Frage bestimmt, mit welchem der Tools Änderungen über DML-Abfragen in Form von „Insert/Update/Delete“ nachverfolgt werden können.
Übersicht der Werkzeuge zur Änderungsnachverfolgung
Für die Änderungsnachverfolgung wurden die folgenden Werkzeuge ausgewählt. Diese sind grundsätzlich Teil der SQL-Server-Installation und müssen nicht als Drittanbieter-Werkzeug ergänzend installiert werden:
- SQL-Server-Transaktionsprotokoll (Transaction Log)
- SQL-Server-Überwachungen (Audit)
- SQL Server Change Data Capture
- SQL Server Triggers
- SQL-Server-Änderungsnachverfolgung (Change Tracking)
In diesem Beitrag widmen wir uns dem SQL-Server-Transaktionsprotokoll und der SQL-Server-Überwachung. Drei weitere Tools werden im nächsten Beitrag dieser zweiteiligen Reihe näher erläutert.
Beispielabfragen für die Änderungsnachverfolgung
Um alle Methoden der Änderungsnachverfolgung vergleichen zu können, bietet sich die Tabelle „T_S_User“ aus der Chair-Datenbank an. Die hierauf folgenden DML-Statements werden in Form von Insert/Update/Delete ausgeführt:
/*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] = 5SQL-Server-Transaktionsprotokoll (Transaction Log)
Das Transaktionsprotokoll wird automatisch bei der Erstellung einer Datenbank angelegt.
Einrichtung
Beim Anlegen der Datenbank über das Management Studio werden immer zwei Datenbankdateien auf dem Dateisystem abgelegt: die primäre Datendatei sowie die Log-Datei (Suffix „_Log“), in der sämtliche Transaktionen protokolliert werden. Damit ist das SQL-Server-Transaktionsprotokoll initial automatisch eingerichtet und muss nicht manuell erstellt werden.
Abb. 1: Einrichtung einer Datenbank im Management Studio
Abfrage der Protokollierung
Zur Abfrage der Log-Datei kann die SYS-Funktion „fn_dblog“ genutzt werden. Diese verfügt über zwei Parameter, mit deren Hilfe der Start und das Ende über die Log Sequence Number (LSN) angegeben werden kann. Hier bietet es sich an, als Default „NULL“ zu verwenden, um erst einmal keine Einschränkung des Logs vorzunehmen. Die Filterungen können über die WHERE-Bedingung auch später vorgenommen werden.
SELECT *
FROM sys.fn_dblog (NULL, NULL)Das Ergebnis der Abfrage des Transaktionsprotokolls ergibt ein technisches Bild, bestehend aus ID-Spalten ohne sprechende Bezeichnungen.
Abb. 2: Abfrage des Transaktionsprotokolls
Die Protokollierung lässt sich so nicht wirklich lesen. Daher gibt es auch einige Third-Party-Tools auf dem Markt, die die Log-Datei sprechend auslesen. Um auch ohne ein weiteres Programm Informationen aus dem Transaktionsprotokoll ziehen zu können, kann folgendes Skript verwendet werden:
SELECT xaU.[UserID]
,U.[Begin Time]
,L.[Operation]
,L.[Context]
,L.AllocUnitName
,U.[Transaction Name]
,O.[name]
,O.[type_desc]
FROM sys.fn_dblog (NULL, NULL) L
INNER JOIN (SELECT * FROM fn_dblog (NULL, NULL) WHERE [Operation] = N'LOP_BEGIN_XACT') U
ON L.[transaction ID] = U.[Transaction ID] –- Start der Transaktion
INNER JOIN sys.system_internals_allocation_units AU
ON L.[AllocUnitId] = AU.[allocation_unit_id]
INNER JOIN sys.partitions P
ON P.[partition_id] = AU.[container_id]
INNER JOIN sys.objects O
ON P.[object_id] = o.[object_id]
CROSS APPLY (
SELECT SUSER_SNAME (U.[Transaction SID]) AS UserID
) xaU
WHERE L.[Operation] IN ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
AND O.[name] = 'T_S_User'
AND xaU.UserID = /*Domäne\User*/Das Ergebnis für unsere Beispielabfrage über diese Änderungsnachverfolgungsmethode ergibt vier Datensätze mit Informationen zum User („UserID“), dem geänderten Objekt („name“) sowie zu der Transaktionsart („Transaction Name“).
Abb. 3: Ergebnis der Skript-Abfrage des Transaktionsprotokolls
Interessant dabei ist, dass das Log für das Update aus zwei Datensätzen besteht (einer „LOP_DELETE_ROWS“- und einer „LOP_INSERT_ROWS“-Operation). In diesem Fall verarbeitet der SQL Server das Update-Statement intern als ein Delete- und ein Insert-Statement.
Vor- und Nachteile bei der Nutzung des Transaktionsprotokolls
Die Änderungsnachverfolgung mit dem Transaction Log bietet drei Vorteile:
- Da das Transaktionsprotokoll automatisch bei jeder Datenbank aktiv ist, ist keine weitere Einrichtung für dieses Tool notwendig.
- Alle Datenbank-Transaktionen werden protokolliert.
- Es ist kein gesondertes Logging notwendig, das zu Lasten des Speicherplatzes und der Performance gehen würde.
Allerdings lässt sich die Log-Datei nicht ohne tieferes Wissen über die Systemobjekte auslesen. Nachteil der Verwendung des SQL-Server-Transaktionsprotokolls ist also, dass zum Auslesen der Log-Datei Third-Party-Tools notwendig sind, um Änderungen wie gewünscht nachverfolgen zu können.
SQL-Server-Überwachung (Audit)
Diese Methode der Änderungsnachverfolgung muss im ersten Schritt auf SQL-Server-Ebene eingerichtet werden. Dies kann über das Management Studio Interface oder per T-SQL durchgeführt werden.
Einrichtung
Für die Einrichtung über das Management Studio muss unter „Sicherheit“ → „Überwachungen“ im Kontextmenü „Neue Überwachung“ ausgewählt werden. Hier wird anschließend der Überwachungsname angegeben und der Pfad, auf dem die Protokolldateien abgelegt werden sollen.
Abb. 4: Neue Überwachung auf SQL-Server-Ebene einrichten
Die neu erstellte Überwachung sollte nun in der Liste der Überwachungen auftauchen. Diese ist standardmäßig zunächst inaktiv und muss über das Kontextmenü aktiviert werden.
Abb. 5: Aktivierung der Überwachung
Alternativ zur Einrichtung der Abfrage über das Management Studio lässt sich das Erstellen und Aktiveren der Überwachung per T-SQL mit folgendem Skript durchführen:
CREATE SERVER AUDIT [Audit-TEST]
TO FILE
( FILEPATH = N'C:\Temp\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'b92b3306-0d16-40a7-b1d8-0f4de3e59383'
)
ALTER SERVER AUDIT [Audit-TEST] WITH (STATE = ON)
GOIn beiden Fällen wurde mit der Einrichtung der Überwachung auf SQL-Server-Ebene das Grundgerüst für die Protokollierung definiert.
Im nächsten Schritt ist zu spezifizieren, was überhaupt auf Datenbank-Ebene überwacht werden soll. Um dies zu definieren, muss auf der gewünschten Datenbank (im vorliegenden Fall die „Chair“) unter „Sicherheit“ → „Datenbank-Überwachungsspezifikation“ eine neue Datenbankspezifikation über das Kontextmenü angelegt werden. (Auch auf SQL-Server-Ebene ist eine Spezifikation möglich. Diese kann unter „Sicherheit“ → „Serverüberwachungsspezifikation“ vorgenommen werden.)
Abb. 6: Einrichtung einer Datenbank-Überwachungsspezifikation
Nun muss aus dem Dropdown-Menü die zuvor angelegte Überwachung ausgewählt werden. Im Anschluss daran kann diese näher spezifiziert werden. In unserem Fallbeispiel sollen die DML-Statements aller User auf der Datenbank „Chair“ überwacht werden. Daher werden die in Abbildung 6 dargestellten Einstellungen vorgenommen. Alternativ könnte auch ausschließlich die Tabelle „T_S_User“ für die Überwachung genutzt werden, indem als Objektklasse „OBJECT“ und als Objektname „T_S_User“ ausgewählt werden. Bei „Prinzipalname“ sollte „public“ angegeben werden, um die Transaktionen aller User überwachen zu können. Ansonsten können auch explizit User oder Rollen ausgewählt werden.
Nach der Anlage der Überwachungsspezifikation muss diese erst wieder über das Kontextmenü aktiviert werden, damit die Protokollierung startet.
Abfrage der Protokollierung
Die Protokollierung muss bei dieser Methode ebenfalls über eine SYS-Funktion abgefragt werden. Diese heißt in diesem Fall „fn_get_audit_file“ und hat drei Parameter. Der erste Parameter gibt den Pfad der Überwachungsdatei an, der ausgelesen werden soll. Die anderen beiden Parameter können auf den Default „NULL“ gesetzt werden, um die Daten nicht weiter einzuschränken.
SELECT *
FROM sys.fn_get_audit_file ('C:\temp\audit\*.sqlaudit', NULL, NULL)
Abb. 7: Abfrage des Überwachungsprotokolls
Über die Wildcard in der Pfadangabe, werden alle Audit-Dateien aus dem Pfad ausgelesen. Das ist zu empfehlen, wenn man nicht nur eine bestimmte Datei auslesen will.
Das Abfrageergebnis sieht bereits „sprechender“ aus als bei der Abfrage des Transaktionsprotokolls. Um die Informationen jedoch klarer auslesen zu können, empfiehlt sich auch hier eine Erweiterung der Abfrage:
SELECT
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, event_time), DATE-NAME(TzOffset, SYSDATETIMEOFFSET()))) AS event_time
,af.session_server_principal_name
,af.server_instance_name
,af.host_name
,af.database_name
,af.schema_name
,ct.class_type_desc AS ClasstypeDescription
,af.object_name
,a.name AS ActionDescription
,af.statement
FROM sys.fn_get_audit_file ('C:\temp\audit\*.sqlaudit', NULL, NULL) af
LEFT JOIN (SELECT DISTINCT action_id,name from sys.dm_audit_actions) a
ON af.action_id = a.action_id
LEFT JOIN (SELECT DISTINCT class_type, class_type_desc FROM sys.dm_audit_class_type_map) ct
ON af.class_type = ct.class_type
ORDER BY af.event_time DESCDas Ergebnis der Abfrage zeigt gut, welcher User wann, über welche Maschine, in welcher Datenbank und in welchem Objekt welche Änderung vorgenommen hat.
Abb. 8: Ergebnis der Abfrage des Überwachungsprotokolls
Vor- und Nachteile bei der Nutzung der SQL-Server-Überwachung
Die Nutzung der SQL-Server-Überwachung zur Änderungsnachverfolgung bietet folgende Vorteile:
- Es können sehr genaue und spezifische Überwachungen definierten werden.
- Die Überwachungsdatei ist bereits von vornherein gut lesbar.
- Die Überwachung kann bedarfsgerecht aktiviert und deaktiviert werden.
Allerdings hat diese Methode zur Änderungsnachverfolgung zwei Nachteile:
- Die Änderungsnachverfolgung muss erst eingerichtet werden. Der Aufwand hierfür ist allerdings überschaubar.
- Es sind zusätzliche Überwachungsdateien notwendig, die zu Lasten des Festplattenspeichers gehen.
Vorschau
Im zweiten Teil dieser Reihe wird auf drei weitere Werkzeuge zur Nachverfolgung eingegangen, die der SQL Server standardmäßig anbietet. Dies sind folgende:
- SQL Server Change Data Capture
- SQL Server Triggers
- SQL-Server-Änderungsnachverfolgung (Change Tracking)
Nach einer Darstellung der jeweiligen Funktionsweisen sowie der Vor- und Nachteile dieser Werkzeuge ziehen wir außerdem ein Resümee, welches der fünf Werkzeuge für welchen Einsatzzweck am sinnvollsten ist.
