Wie kann unter Verwendung des Microsoft SQL-Servers ein kundenspezifisches Regelwerk zur Datenbankentwicklung eingeführt und automatisiert durchgesetzt werden? Dieser Blogbeitrag zeigt einen Lösungsansatz mit Hilfe von SQL Server DDL-Triggern, deren Funktionsweise und technische Umsetzung anhand eines konkreten Praxisbeispiels. Es werden die Technik, der Nutzen und ein Codebeispiel dargestellt. Das dargestellte Codebeispiel ist ein allgemeingültiger Vorschlag, basierend auf der bisher im Bissantz Consulting gelebten Namensgebung im Entwicklungsprozess von Kundenprojekten.
Wer unter uns Datenmodellierern kennt das nicht: Man wirft nach einigen Tagen oder Wochen einen Blick auf „seine“ Datenbank, möchte sich einen Überblick über den Entwicklungsstand verschaffen und dann das: ein „Wildwuchs” von SQL-Objekten tut sich auf. Dabei beruhen doch unser Modellierungsansatz und der dahinterliegende Automatismus des DeltaMaster Modelers auf einer klaren Namenskonvention.
Alle generierten Tabellen werden T_DIM_ oder T_FACT_ benannt, die zugehörigen Prozeduren P_DIM_ oder P_FACT. Gleiches gilt natürlich auch für angelegte Sichten (engl.: View); diese beginnen immer mit V_XYZ. Diese einheitliche Nomenklatur hat sich über Jahre bewährt. Steht der ursprüngliche Kollege aus dem Projekt gerade nicht zur Verfügung, kann eine notwendige Anpassung oder Fehleranalyse mit nur minimaler Rüstzeit von einem Kollegen übernommen werden. Dies ist einer der Garanten für unseren zweifelsohne hervorragenden Kundensupport.
Letztlich ist diese Datenschicht aber die letzte von mehreren Stufen auf dem Weg von den Rohdaten hin zu einem mehrdimensionalen Datenmodell. In diesem Blogbeitrag wird ein sehr nützliches SQL-Werkzeug gezeigt, welches einmal abgestimmte einheitliche Regeln zur Namensgebung in unseren Datenbanken konsequent durchsetzt. Und das vollautomatisch!
Das Regelwerk zur Namensgebung soll auf jeglichen Vorstufen (oft als Staging Area bezeichnet) Anwendung finden. Der SQL-Server übernimmt dabei die Rolle der „Code-Polizei“, er wird zur automatischen Überwachung dieser Regeln eingesetzt und gibt direkt Rückmeldung bei einem Verstoß.
Abbildung 1: Relationale Data Warehouse Architektur
Die nützliche Funktionalität dazu bietet der Microsoft SQL-Server unter dem Begriff DDL-Trigger. DDL steht für Datendefinitionssprache (engl.: Data Definition Language). Dabei können unterschiedliche Reaktionen auf sogenannte DDL-Ereignisse ausgeführt werden. Ereignisse bestehen meistens aus T-SQL Anweisungen, die mit den Schlüsselwörtern CREATE, ALTER, DROP, GRANT oder DENY beginnen. Eine Übersicht über die unterstützten Ereignisse können im MSDN nachgelesen werden.
DDL-Trigger im Allgemeinen können für folgende Aufgaben verwendet werden:
- Verhinderung von Änderungen am Datenbankschema
- als Reaktion auf eine Änderung soll ein Ereignis ausgeführt werden
- Aufzeichnung von Änderungen im Datenbankschema
Grundsätzlich werden DDL-Trigger in zwei Typen unterschieden: zum einen DDL-Trigger für T-SQL (die-se Variante wird im Folgenden Beispiel verwendet) und CLR-DDL-Trigger (dabei wird als Reaktion auf ein DDL-Ereignis eine CLR-Methode oder Funktion aus einer programmierten Assembly ausgeführt).
Darüber hinaus können DDL-Trigger in zwei Gültigkeitsbereiche unterteilt werden, entweder auf eine einzelne Datenbank bezogen oder für den gesamten MS SQL-Server.
DDL-Trigger sind bereits seit SQL-Server Version 2005 integrierter Bestandteil. Die zum Auslesen der der Ereignisse verwendete EVENTDATA() Funktion und der Datentyp XML wurden jedoch erst mit Versi-on 2008 eingeführt.
Praxisbeispiel
In Kundenprojekten wird von den verschiedenen Projektmitarbeitern an der Entwicklung der relationa-len Datenbank gearbeitet. Auf folgende Regeln bei der SQL-Programmierung hat man sich geeinigt:
- Kein benutzerdefiniertes SQL-Objekt soll im Datenbankschema dbo angelegt werden dürfen.
- Alle Tabellen mit den Rohdaten sollen mit T_Import_XYZ benannt werden.
- Alle Hilfstabellen sollen mit T_D_ oder T_S_ beginnen.
- Ein Verstoß gegen eine oder mehrere dieser Regeln soll dem Anwender direkt angezeigt wer-den.
Zur Durchsetzung dieses kleinen Regelwerks legen wir ein eigenes Schema an und konfigurieren an-schließend einen neuen DDL-Trigger. Der Trigger im Allgemeinen besteht aus zwei Teilbereichen, zum einen das Regelwerk, auf das reagiert werden soll und die resultierende Reaktion. Optional kann die Meldung an die Benutzer mit spezifischen Informationen (z.B. sprechende Fehlermeldung beim Eintreten des Ereignisses, Hinweise auf externe Links) angereichert werden.
Erstellung neues Datenbankschema
Für die Entwicklung des weiteren SQL-Codes und zur differenzierten Berechtigungssteuerung legen wir ein neues Datenbankschema „DataStaging“ an. Besitzer dieses Schemas ist zunächst das Systemkonto db_owner (für dieses Beispiel ausreichend), in größeren Projektumgebungen könnte hier schon nach Entwicklergruppen (Kunde, Bissantz, Partner) unterschieden werden.
CREATE SCHEMA [DataStaging] AUTHORIZATION [db_owner]
GONach erfolgreicher Ausführung können wir nun mit der eigentlichen Definition des Regelwerks beginnen.
Definition DDL-Trigger (Regelwerk)
CREATE TRIGGER BC_Namenskonvention
ON DATABASE
FOR
CREATE_TABLE
,CREATE_VIEW
,CREATE_PROCEDURE
,CREATE_FUNCTION
AS
BEGIN
--Variablen für Prüfungen und Meldungstexte
DECLARE @ServerName VARCHAR(50)
DECLARE @Schema VARCHAR(50)
DECLARE @Table VARCHAR(50)
DECLARE @ObjectType VARCHAR(50)
DECLARE @Command VARCHAR(50)
--Auslesen der aktuellen Ereignisinformationen
SELECT @ServerName =
eventdata().value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(100)')
SELECT @Schema =
eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(100)')
SELECT @Table =
eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
SELECT @ObjectType =
eventdata().value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(100)')
SELECT @Command =
eventdata().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'
, 'varchar(100)')
--Regel 1., keine benutzerdefinierten Objekte im Schema dbo zugelassen
IF @Schema = 'dbo'
BEGIN
PRINT
'Es wurde versucht, das Objekt ' + @ObjectType + ' ' + @Table +
' im Schema ' + @Schema + ' anzulegen! ' +CHAR(13)+CHAR(10)+
'Laut Vereinbarung dürfen Objekte nur in benutzerdefinierten
Schemas angelegt werden!' +CHAR(13)+CHAR(10)+
' '
ROLLBACK TRANSACTION
END
--Regel 2. und 3., Prüfung Namenkonvention Tabellenname
IF (@ObjectType = 'TABLE' AND @Table NOT LIKE 'T[_]Import[_]%' )
AND (@ObjectType = 'TABLE' AND @Table NOT LIKE 'T[_]D[_]%' )
AND (@ObjectType = 'TABLE' AND @Table NOT LIKE 'T[_]S[_]%')
BEGIN
PRINT
'Verletzung der Namenskonvention für den Objekttyp '
+@ObjectType +CHAR(13)+CHAR(10)+
'Der aktuelle Objektname lautet: ' +@Schema+ '.'
+@Table +CHAR(13)+CHAR(10)+
'Der Tabellenname muss mit T_Import_ , T_D_ oder T_S_ beginnen, bitte korrigieren Sie den Namen.' +CHAR(13)+CHAR(10)+
' '
ROLLBACK TRANSACTION
END
END
GOAn dieser Stelle ein paar Erläuterungen zum T-SQL-Code:
In diesem Beispiel wird der DDL-Trigger „BC_Namenskonvention“ angelegt. Die Gültigkeit wird auf eine einzelne Datenbank gesetzt. Wenn im Kundenprojekt vereinbart wird, dass die Namenskonvention auf dem gesamten System für alle Datenbanken gelten soll, so muss lediglich ON DATABASE durch ON ALL SERVER ersetzt werden. Warum die Syntax hier „ALL Server“ und nicht „ALL Databases“ ist bleibt Microsofts Geheimnis.
Eventdata()
Nach der Variablendeklaration folgt ein interessanter Abschnitt unter Nutzung der EVENTDATA() Funk-tion. Laut der Microsoft Dokumentation liefert die Funktion verschiedene Informationen über SQL-Server und Datenbankereignisse in Form eines XML-Datensatzes zurück. Für die CREATE TABLE Anwei-sung sieht das XML-Ergebnis von EVENTDATA() zum Beispiel wie folgt aus:
Erweitert man die EVENTDATA()-Funktion um die Methode .Value(), dann erhält man in TSQL die Mög-lichkeit, einzelne Bestandteile des XML Resultats anhand des Knotennamens zu extrahieren. Die allge-meine Syntax wird oft auch als xQuery Methode bezeichnet. Weitere Details dazu finden Sie in den Links am Ende dieses Beitrags.
Die Funktionalität wird in der Trigger-Definition im Abschnitt „Auslesen der aktuellen Ereignisinformati-on“ angewendet, um die Anforderung 4 aus dem Praxisbeispiel umzusetzen.
Regelprüfungen
Die Regelprüfungen setzen auf dem Abschnitt „Eventdata()“ auf und nutzen die soeben beschriebene Funktionalität, dass die Änderungen am Datenbankschema (und eine CREATE, ALTER oder DROP Anweisung ist nichts anderes) differenziert zur Laufzeit abgefragt werden können. Die Prüfung an sich ist dann wieder reines transaktionales SQL, somit ist praktisch jede (Kunden-)Anforderung abbildbar.
Empfehlung: Gehen Sie nicht zu restriktiv mit dieser Thematik um, denn je mehr Sie einschränken, um so zeitaufwändiger wird auch die Weiterentwicklung. Treibt man es auf die Spitze, sind in der Konsequenz sicherlich auch verlängerte Entwicklungszeiten und -kosten messbar. Dennoch kann etwas Disziplin auch uns Datenmodellierern nicht schaden, einheitliche Namen helfen Zeit und Kosten zu sparen. Schauen wir uns jetzt die „Code-Polizei“ im Test an:
Abbildung 2: DDL-Trigger Ergebnis
Wie im Trigger definiert, treten bei Ausführung der CREATE-Anweisung zwei Fehler gleichzeitig auf. Durch die personalisierte Fehlerbeschreibung ist direkt jedem Entwickler klar, welche Regelungen bestehen und was ist dem auszuführenden Code anzupassen ist, um ihn erfolgreich ausführen zu können.
Ausblick / Erweiterungen
Wie bei so vielen Themen hat Microsoft auf die hier vorgestellte Funktionalität ein ganzes Framework in SQL-Server aufgesetzt. Das Stichwort heißt SQL-Server Policies. Man findet diese Umgebung zur Re-geldefinition im SQL Server Management Studio (kurz: SSMS) auf Serverebene unter dem Punkt Verwaltung.
Abbildung 3: MS SQL-Server Policies
Man erkennt in der Namensgebung von Microsoft schon die Ähnlichkeiten zu den in diesem Blogbeitrag dargestellten Bestandteilen eines DDL-Triggers. Kein Wunder, denn im Hintergrund wird bei der Konfi-guration von Bedingungen und Richtlinien technisch mit Unterstützung einer grafischen Oberfläche und einigen Systemprozeduren ein Trigger erzeugt.
Vorteil an diesem relativ umfangreichen Framework ist, dass man neben der Protokollierung der abgesetzten SQL-Anweisungen auch erweiterte Informationen in der Meldung an den Benutzer zurückgeben kann. Das könnte z.B. ein Link in das Intranet auf das Entwicklungshandbuch sein. Aus meiner Erfahrung reicht es aber völlig aus, den hier vorgestellten Ansatz in die jeweilige Kundendatenbank zu implementieren. Durch die reine Verwendung von T-SQL ist man sehr flexibel in der Anpassung und Erweiterung des Regelwerks, ohne umständlich zwei unterschiedliche grafische Oberflächen bedienen zu müssen.
Fazit
Es kann nur empfohlen werden, eine grundlegende Ordnung bei der Namensgebung der relationalen Objekte auf Datenbankebene auf- und durchzusetzen. Letztlich ist es nur die konsequente Fortführung des bereits durch den DeltaMaster Modeler beschrittenen Weges.
Unbedingt zu empfehlen ist der Einsatz der „Code-Polizei“, sobald mehr als ein Entwicklungsteam auf der gleichen Datenbank parallel programmiert. In diesem Fall können alle Entwickler einer Firma auf ein komplettes Schema berechtigt werden, was die Zugriffsteuerung vereinfacht. Viel wichtiger in einem solchen Umfeld ist aber sicherlich die einheitliche Namensgebung und deren Bedeutung.
Literatur/Links
Microsoft Developer Network, DDL Trigger
https://msdn.microsoft.com/de-de/library/ms175941.aspx
DDL Optionen:
https://msdn.microsoft.com/de-de/library/bb522542.aspx
EVENTDATA Abfrage:
https://msdn.microsoft.com/en-us/library/ms173781.aspx
value()-Methode
https://msdn.microsoft.com/de-de/library/ms178030.aspx
Beispiel inklusive Logging
https://sqlandme.com/tag/eventdata-xml/
