CLR im SQL Server nutzen (Teil 1)
Posted by sprinter | Filed under Coding, Programmierung
Seit Version 2005 kann der SQL-Server Funktionalitäten in Form von .NET-Komponenten integrieren. codingfreaks zeigt, wie das funktioniert. Im ersten Teil dieser neuen Serie gehen wir auf die grundlegenden Konzepte ein und erstellen eine erste benutzerdefinierte Funktion.
Teil 2
Lange Zeit waren die Fronten zwischen Anwendungsentwicklern und Datenbank-Admins klar gezogen. Die einen stellen Tabellen, Sichten und (wenn man Glück hatte) Prozeduren bereit, die anderen (also die Programmierer) nutzten diese und führten komplexere Aufgaben dann anwendungsseitig im Code aus. Wunderbar! Natürlich nicht wunderbar, denn Daten mussten erst aufwendig vom Server auf den Client gezogen werden, damit komplexere Aufgaben darauf ausgeführt werden konnten und die DBAs hatten sich mit Dingen herumzuschlagen, die sie eigentlich nicht so gut konnten.
Seit Version 2005 des Micrsoft SQL Server gibt es nun die Möglichkeit, Komponenten im Visual Studio zu entwerfen und diese dann auf einem SQL Server bereit zu stellen. Der Programmierer erstellt seinen Code wie immer, nur dass er ihn nicht als EXE laufen lässt, sondern auf dem SQL-Server installiert.
Bedingungen
Bevor wir mit den eigentlichen Arbeiten beginnen können, muss das Arbeitsumfeld geklärt sein. Auf der Entwicklungsmaschine sollte ein Visual Studio 2008 laufen. Bei einer normalen Installation steht dann auch der SQL Server 2005 in der Express Edition zur Verfügung. CLR-Komponenten können derzeit in den SQL-Server-Version 2005 und 2008 genutzt werden. Die folgenden Beispiele sowie das Video-Tutorial nutzen Visual Studio 2008 mit SQL-Server 2005 und wir werden die fertige Datenbank dann auf einer virtuellen Maschine in einem SQL Server 2008 einsetzen.
Datenbank anlegen
Schritt 1 sollte das Erstellen einer Beispieldatenbank auf der Entwicklungsmaschine sein. Im Visual Studio nutzen wir dazu einfach den Server Explorer (Ansicht -> Server Explorer). Ein Rechtsdklick auf “Datenverbindungen” und ein anschließender Klick auf “Neue SQL Server-Datenbank erstellen…” bringt ein Fenster, in dem man unter Servername “.\SQLEXPRESS” auswählt und dann unter “Neuer Datenbankname:” einen Wert seiner Wahl einträgt. Die Schritte sind in den folgenden Abbildungen widergegeben.
Das Ergebnis ist, dass die neue Datenbank erstellt wurde und nun unter “Datenverbindungen” aufgelistet wird.
Projekt erstellen und Eigenschaften des Projektes
Nun ist es an der Zeit, ein Projekt für die Entwicklung der CLR-Komponenten anzulegen. Dazu wird im Visual Studio ein neues Projekt angelegt. Im entsprechenden Fenster gibt es unterhalb von C# (bzw. Visual Basic) den Treeview-Eintrag “Datenbank”. Wird dieser ausgewählt, bietet Visual Studio den Projekttyp “SQL Server-Projekt” an (siehe Screenshot rechts).
Nach Eingabe eines Projektnamens und einem anschließenden Klick auf “OK” erstellt das Visual Studio das Projekt und fügt die nötigen Verweise ein. Noch bevor der Projektbaum gezeigt wird, öffnet sich ein Dialog, mit dem man die Datenbankverbindung auswählen kann, die für das Projekt genutzt werden soll. Das ist der Grund dafür, warum wir im vorherigen Step diese Arbeit bereits erledigt haben.
Der entsprechende Eintrag der zuvor erstellten Verbindung kann jetzt ausgewählt werden und nach einem weiteren “OK” wird die Frage nach dem Aktivieren des SQL/CLR-Debugging beantwortet. Für unser erstes HowTo benötigen wir das nicht und wählen deshalb die “Nein”-Option.
Das Ergebnis des Assistenten enttäuscht auf den ersten Blick gestandene Visual-Studio-Nutzer ein wenig. Außer einem Ordner “Test Scripts” und einer darin enthaltenen SQL-Datei ist nicht viel zu sehen. Das muss auch nicht sein, denn die Implementierung verbirgt sich hier elegant im Hintergrund.
Interessant ist an dieser Stelle bereits ein Blick in die Projekteigenschaften. Hier erkennt man ein zusätzliches Tab “Datenbank”, in dem die Optionen für die Datenbankverbindung eingestellt werden können. Zum jetzigen Zeitpunkt hat Visual Studio natürlich die Einstellungen der durch uns gewählten Verbindung eingetragen. Interssant kann hier die Option “Assemblybesitzer” sein. Lässt man das Feld leer, wird der aktuelle Windows-Benutzer als Besitzer angenommen. Es kann durchaus wichtig sein, hier einen anderen Wert einzutragen, wenn man direkt gegen das Produktiv-System arbeitet, bei dem der eigene Windows-Account keine entsprechenden Rechte hat. In TSQL gibt es eine Methode, die diesen Wert äquivalent benutzer:
CREATE ASSEMBLY assembly_name AUTHORIZATION owner_name
“owner_name” entspricht dem Wert “Assemblybesitzer” in den Projekteinstellungen.
Das außerdem noch dazu gekommene Tab “Bereitstellen” bietet die Option “Code bereitstellen” an. Dadurch werden die Programmcodes, die noch geschrieben werden letztlich auf den gewählten Server übertragen.
Die AssemblyInfo.cs birgt keinerlei Überraschungen.
Erstellen von CLR-Komponenten
Nun wird es langsam Zeit, endlich .NET-Komponenten für den SQL-Server zu erstellen. Das Geheimnis liegt in den verfügbaren hinzufügbaren Komponenten. Ein Rechtsklick auf den Projektnamen im Projektmappen-Explorer und die Auswahl des der Menu-Option “Hinzufügen” bringt Licht ins Dunkel (Screenshot rechts).
Insgesamt 5 verschiedene Typen von SQL/CLR-Elementen lassen sich derzeit entwickeln:
- Benutzerdefinierte Funktionen (user defined functions = UDF)
- Gespeicherte Prozeduren (stored procedures = SP)
- Aggregate
- Trigger
- Benutzerdefinierte Typen (user defined types = UDT)
Benutzerdefinierte Funktionen
Eine UDF ist ein Logikelement innerhalb des SQL-Servers, das Berechnungen kapselt. Dabei kann eine UDF auf Datenbankinhalte zugreifen (Tabellenwertfunktionen) oder einfach nur Berechnungen ausführen (Skalarwertfunktionen). Tabellenwertfunktionen ändern Tabelleninhalte und geben oft nichts zurück (außer einer Erfolgsmeldung) wohingegen Skalarwertfunktionen meist daran zu erkennen sind, dass sie berechnete Werte zurückgeben. UDF können als Funktionen in T-SQL genutzt werden. Viele bereits in SQL-Server eingebaute Systemfunktionen nutzt man, ohne es sich bewusst zu machen. Zu erkennen sind Funktionen in T-SQL an der Nutzung von Klammern. Ein Beispiel:
SELECT DATEADD("d", 1, GETDATE()) AS Tomorrow
Diese einfache Zeile TSQL benutzt bereits 2 in SQL-Server eingebaute Funktionen (DATEADD() und GETDATE()). An der Benutzung der Klammern (ähnlich wie bei C# auch) erkennt man sie hervorragent.
Wie man ebenfalls gut erkennen kann, werden Funktionen, und damit UDF auch, innerhalb von TSQL-Anweisungen genutzt. Sie sind also nicht per EXEC aufrufbar.
Funktionen können Eingangsparameter haben (DATEADD), oder auch nicht (GETDATE) und besitzen somit, wie in C# z.B. auch, eine Signatur.
Eine eigene CLR-Funktion stellt mit diesem Wissen keine große Hürde mehr dar. Nehmen wir an, wir brauchen eine Funktion, die den Hash-Wert eines Strings zurückgibt. Um dies umzusetzen, wählen wir in dem bereits erwähnten Kontext-Menu die Option Hinzufügen -> Benutzerdefinierte Funktion. Im allseits bekannten Fenster wird nun noch der Name der Funktion “GetStringHash” eingetragen. Das Ergebnis ist folgender Klassen-Rumpf:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetStringHash()
{
// Fügen Sie hier Ihren Code ein.
return new SqlString("Hello");
}
};
Interessant sind hier zwei Dinge. Zum einen handelt es sich um eine partielle Klasse, die statische Methoden nutzt, zum anderen wurde die Methode GetStringHash() mittels des SqlFunction-Flag-Attributes als eine im SQL-Server verfügbare Funktion gekennzeichnet. Dass die Methoden statisch sein müssen, leuchtet ein, denn innerhalb des SQL-Servers werden bei der Ausführung keine Instanzen der Klasse “UserDefinedFunctions”. Die Klasse ist übrigens deshalb als partial gekennzeichnet, weil es möglich ist, mehrere einzelne Klassen für benutzerdefinierte Funktionen zum Projekt hinzuzufügen. Innherhalb des SQL-Servers werden diese aber zu einer Klasse zusammengefasst.
Zeile 13 des Scripts macht natürlich derzeit noch keinen Sinn und wir müssen der Methode noch ein wenig mehr Sinn geben:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetStringHash(SqlString strInput)
{
HashAlgorithm hasThis = HashAlgorithm.Create("SHA1");
if (hasThis != null)
{
byte[] abytTmp = Encoding.UTF8.GetBytes(strInput.ToString());
byte[] abytResult = hasThis.ComputeHash(abytTmp);
return (SqlString)Convert.ToBase64String(abytResult);
}
return string.Empty;
}
Zunächst einmal soll die UDF einen Eingabeparameter entgegen nehmen. strInput ist hier nicht vom Typ string, sondern SqlString, also der entsprechende Datentyp innerhalb des SQL-Servers. Wenn es uns gelingt, in Zeile 4 einen Hash-Algorithmus zu erstellen, wird in Zeile 9 wiederum ein SqlString mit dem Base64-kodierten Hash-Wert zu strInput zurück gegeben. Ansonsten gibt Zeile 11 einen Leer-String zurück.
Um die Funktionaltität zu testen, wird zunächst das Projekt erstellt, um die Gültigkeit des Codes zu ermitteln. Achtung: Erstellen erfolgreich heißt hier lediglich, dass die C#-Synthax und -Nutzung regelkonform ist. Es heißt nicht, dass eine Komponenten auch im SQL-Server problemlos läuft!
Nachdem dieser Test erfolgreich durchlaufen ist, wird unter “Erstellen” die Option “Projektmappe bereitstellen” gewählt. Durch diese Operation wird die Assembly erstellt und der SQL-Server-Instanz hinzugefügt. Die notwendigen TSQL-Kommandos führt das Visual Studio gleich aus. Den Erfolg dieser Bereitstellung kann man im Server-Explorer nachvollziehen (siehe Screenshot oben). Unterhalb von “Funktionen” sollte die GetStringHash nun auftauchen. Ein Klick auf das +-Symbol davor zeigt uns bereits, dass der Eingabeparemeter ebenfalls richtig erkannt wurde.
Um den Funktionstest durchzuführen, wählen wir durch einen Rechtsklick auf die Verbindung die Option “Neue Abfrage”. Dies öffnet ein Fenster, in dem wir TSQL-Kommands absetzen können. Das angebotene Fenster zur Auswahl von Objekten für den visuellen Designer kann man getrost schließen, um dann im unteren Editor-Fenster folgenden Befehl von Hand einzugeben:
SELECT dbo.GetStringHash(N'Hallo') AS Hash
Den dbo-Teil fügt das Abfragefenster selbst ein und das N vor dem ‘Hallo’ sorgt dafür, dass der string als Unicode-String betrachtet wird. Das Ergebnis sollte ein Base64-String sein (Base64 hat den Vorteil, dass das Ergebnis für uns lesbarer erscheint. Normalerweise würde man einen Hash nicht in Base64-umrechnen).
Wer gegen einen echten SQL-Server arbeitet und womöglich beim Ausführen Fehlermeldungen á la “Schalten Sie clr enable ein” enthält, muss auf dem entsprechenden SQL-Server noch den Befehl
SP_CONFIGURE 'clr enable', 1
gefolgt von
RECONFIGURE
ausführen. Dieser Prozeduraufruf schaltet die SQL-Server-Instanz für das Ausführen von CLR-Code frei.
Es sollte nicht vergessen werden, dass der SQL-Server bereits von Haus aus viele Funktionen bereitstellt. Anstatt das Rad also immer wieder neu zu erfinden, lohnt ein Blick in den Baum “Systemfunktionen” unterhalb von Funktionen.
Resumé und Ausblick
Wie man sieht, ist das erstellen solcher CLR-Objekte für den SQL-Server sehr einfach. Einfach heißt aber nicht immer auch sauber! Man sollte immer im Hinterkopf behalten, dass die Nutzung dieses Features einige Gefahren in sich birgt, die vor allem aus Anwendungs-Design-Gesichtspunkten zum Nachdenken anregen sollten:
- Code, der eigentlich in der business logic angesiedelt sein sollte, wandert ganz leicht in die database-Schicht ab.
- Die Performance mancher Funktionen kann als SQL/CLR-Komponente schwächer sein, als z.B. bei IIS-gehosteten COM+-Komponenten.
- Die Verteilung der CLR-Komponenten muss sehr genau geplant und ausgeführt werden.
- Unit-Tests werden erschwert.
- DBAs können meist nichts mit Hochsprachen anfangen und die Wartung der Datenbank splittet sich ungewollt auf.
- Nicht jeder DBA wird glücklich über .NET-Blackboxes auf seinem System sein – ein gut geplantes und umgesetztes Sicherheitskonzept sollte also absolute Pflicht sein
- Der Wechsel auf andere Datenbank-Backends (z.B.) wird schlicht unmöglich gemacht!
Sicher gibt es noch weitere Bedenken gegen den Einsatz, aber in reinen .NET-Umgebungen spielt dieser Einsatz ganz erhebliche Trümpfe aus. Wer schonmal die unübersichtlichen Packages von Oracle durchstöbert hat oder unglücklich über die magere Programmierunterstützung in MySQL war, der wird seine wahre Freude an SQL/CLR haben.
In den weiteren HowTos dieser Reihe widmen wir uns den anderen Objekttypen sowie Fragen des Deployments, der Nutzung von Exceptions und der Sicherheit.
Dezember 20th, 2011 at 19:33
[...] Teil 1 [...]