In diesem Blog Eintrag geht es wie im vorherigen Eintrag „Loggen von Berechtigungen“ um das Protokollieren von Änderungen. Hier werden dafür aber jegliche Struktur Änderungen an der Datenbank protokolliert. So kann ein Datenbank Administrator prüfen, welche Änderungen an der Datenbank durchgeführt wurden.
In diesem Beispiel soll zusätzlich eine Prüfung implementiert werden, die ermöglicht eine Checksumme zu erstellen die bspw. Software Herstellern erlaubt zu prüfen, ob jegliche Anpassungen in einem Änderungsscript erfolgreich durchgeführt wurden.

In der folgenden Tabelle wird dann jegliches XML erfasst, was der DDL Trigger erfassen soll. Zusätzlich wird hier auch die Checksumme gespeichert:
CREATE TABLE dbo.logDbTracking (
uid uniqueidentifier default newid() not null primary key,
xmltext XML NULL,
checksumme INT NULL,
datum DATETIME DEFAULT GETDATE() NOT NULL,
);
Nun wird der Trigger erstellt. Dieser Trigger erfasst mehrere DDL Ereignisgruppen:

• DDL_TABLE_EVENTS
  CREATE, ALTER, DROP TABLE
• DDL_VIEW_EVENTS
  CREATE, ALTER, DROP VIEW
• DDL_INDEX_EVENTS
  CREATE, ALTER, DROP INDEX, beinhaltet auch Volltext Indizes
• DDL_FUNCTION_EVENTS
  CREATE, ALTER, DROP FUNCTION
• DDL_PROCEDURE_EVENTS
  CREATE, ALTER, DROP PROCEDURE
• DDL_TRIGGER_EVENTS
  CREATE, ALTER, DROP TRIGGER

Der Trigger schreibt in die Tabelle fü alle erfassten Events das XML wie auch aus dem XML eine erstelle Checksumme.
CREATE TRIGGER TR_DBChangeTracking
ON DATABASE
FOR
DDL_TABLE_EVENTS, DDL_VIEW_EVENTS,
DDL_INDEX_EVENTS, DDL_FUNCTION_EVENTS,
DDL_PROCEDURE_EVENTS, DDL_TRIGGER_EVENTS
AS
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @xmlData XML
SELECT @xmlData = EVENTDATA()
DECLARE @cmdText NVARCHAR(MAX)
SET @cmdText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(MAX)')

INSERT INTO dbo.logDbTracking
(xmltext, checksumme)
VALUES (
@xmlData,
CHECKSUM(@cmdText)
);
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT OFF

Nun werden jegliche strukturellen Änderungen an der Datenbank entsprechend protokolliert. Aus jedem protokollierten TSQL Statement wird in der Tabelle eine Checksumme gebildet. So erhält man die Möglichkeit einen Abgleich über die Vollständigkeit durchzuführen.

Hinweis: Oben stehendes Script funktioniert sowohl auf einem Microsoft SQL Server 2005 wie auch unter 2008 (R2).

Download:

• Download Script