SQL CLR: Integrationsmerkmale und Einsatzempfehlungen

13. August 2014

Es ist schon lange her, genauer gesagt im Jahr 2005, als Microsoft mit SQL CLR (dem Quasi-Nachfolger von Extended Stored Procedures) die Nutzung von .NET innerhalb des SQL Servers eingeführt hat. In diesem Artikel möchte ich hauptsächlich darauf eingehen, was Microsoft konkret hinsichtlich der CLR Integration in SQL Server getan hat und was man bei der Nutzung von Assemblies innerhalb des SQL Servers im Hinterkopf haben sollte.

Im persönlichen Projektalltag habe ich den Einsatz von eigenen Assemblies auf Produktionssystemen (mal abgesehen von Open-Source Projekten wie dem Analysis Services Stored Procedure Project) noch nicht so häufig gesehen. Das liegt sicher zum einen an restriktiven Vorschriften hinsichtlich des Server-Betriebsmanagements, aber möglicherweise auch an einer gewissen Vorsicht. Denn einen sporadischen Speicherfehler in einem Produktionssystem zu finden, kann eine heikle Angelegenheit sein. Schauen wir uns also kurz an, wie es um das Thema Security steht.

Microsoft hat sich doch einige Mühe gemacht, die Ausführung von .NET Code innerhalb einer SQL Server Instanz so sicher und zuverlässig wie möglich zu machen. Für die Codezugriffsberechtigungen SAFE und EXTERNAL_ACCESS gibt es eine Assemblyvalidierung beim Erstellen mit CREATE ASSEMBLY, bei der IL-Code geprüft wird und die garantieren soll, dass der Code typsicher ist und bestimmten Sicherheitsrichtlinien entspricht (Näheres dazu hier). Anderenfalls wird das CREATE ASSEMBLY-Statement mit einem Fehler abgebrochen. Im UNSAFE-Modus dagegen, darf auch Unmanaged Code enthalten sein und der Zugriff auf Systemressourcen ist nur durch die Berechtigungen des Aufrufers selbst beschränkt, aber nicht durch SQL Server.

Innerhalb des .NET-Frameworks wurden Methoden, die aus SQL Server Sicht hinsichtlich der Sicherheit problematisch sind, mit HostProtection-Attributen (HPA) versehen. Nutzt man innerhalb der eigenen Assembly eine solche Methode, wird je nach Codezugriffsberechtigung eine HostProtectionException geworfen. Im Folgenden Beispiel hatte ich ExternalAccess verwendet und die BeginExecuteNonQuery-Methode des SqlCommand aufgerufen.

SQLClr_Threading.

Hier noch der zugehörige Methodenausschnitt aus der System.Data.SqlClient.SqlCommand-Klasse:

image

Wollte man BeginExecuteNonQuery trotzdem einsetzen, kann man das natürlich tun. Allerdings muss dann die Assembly im UNSAFE-Modus bereitgestellt werden. Eine Liste der nicht erlaubten HPA gibt es hier.

Ein weiteres Sicherheitsfeature ist, dass SQL Server keine Assemblies aus dem Global Assembly Cache (z.B. mit Assembly.Load("….")) oder von einer sonstigen Stelle lädt, sondern nur diejenigen, die mit CREATE ASSEMBLY in SQL Server als Objekt in binärer Form deklariert wurden. Das Einbinden als BLOB hat zusätzlich den Vorteil, dass beim BACKUP und RESTORE die Assembly mitberücksichtigt wird, d.h. nach dem Wiederherstellen ist auch der Assemblycode wieder verfügbar.

Beim Erstellen einer Assembly in SQL Server wird diese als AppDomain (je Datenbank und Assembly-Eigentümer eine) isoliert. Hat man also 2 Assemblies in einer Datenbank mit 2 unterschiedlichen Assembly-Eigentümern, so werden diese auch in 2 getrennte AppDomain-Container überführt. Somit lässt sich auch der Extrem-Fehlerfall (Entladen der AppDomain) in gewisser Weise steuern. Generell empfiehlt es sich, Funktionen zusätzlich auch nach Codezugriffsberechtigung zu strukturieren. D.h. hat man eine Menge X von Funktionen, die im SAFE-Modus laufen können und eine Menge Y, die auf Ressourcen außerhalb von SQL Server zugreifen, bietet es sich an, diese auch in 2 Assemblies mit SAFE bzw. EXTERNAL_ACCESS-Berechtigungen auszulagern.

Um die Betriebssicherheit eines Servers zu wahren bzw. zumindest eine weitere Hürde nehmen zu müssen, dürfen nur Datenbankadministratoren mit sysadmin- bzw. serveradmin-Berechtigungen die Ausführung von Assemblies erlauben. Um sie erstellen zu dürfen, braucht man mindestens CREATE ASSEMBLY-Berechtigungen. Mindestens deshalb, weil das nur auf solche im Modus SAFE zutrifft. Für das Sicherheitslevel EXTERNAL_ACCESS sind es schon EXTERNAL_ACCESS-Berechtigungen und für UNSAFE führt kein Weg am Systemadministrator (sysadmin-Rolle) vorbei.

Zusätzlich gibt es noch eine Failure escalation policy, die beispielsweise beim Beenden einer SQL Session mit dem Kill-Befehl dafür sorgt, dass der jeweilige Thread sauber beendet wird. Sofern der Thread jedoch in einer kritischen Region besteht und dort ein Lock auf einem Objekt gehalten wird, entlädt SQL Server einfach die komplette AppDomain, sodass ein sauberer Zustand gewährleistet bleibt. Das Entladen der AppDomain kann insbesondere bei Situationen auftreten, in denen SQL Server der Speicher ausgeht (Memory Pressure).

Performance

SQL Server unterscheidet sich als Laufzeithost doch in einem zentralen Punkt besonders von anderen Konkurrenten wie Oracle oder IBM DB2. Während Oracle und DB2 CLR-Code in einem externen Prozess ausführen, geschieht dies bei SQL Server im Serverprozess selbst. Unter Sicherheitsaspekten könnte man natürlich argumentieren, dass die Isolation bei DB2 oder Oracle ein höheres Maß an Sicherheit (zumindest was den Datenbankhost angeht) gewährt, jedoch resultiert aus der Out-Of-Process Ausführung” auch der Nachteil, dass die Performance nicht so optimal ist, wie sie sein könnte und sich ferner auch einige andere Seiteneffekte (wie z.B. Sichtbarkeit von temporären Tabellen oder andere Transaktionsisolationlevel) ergeben.

Durch die Ausführung im SQL Server Hostprozess wird bei bestimmten Operationen eine sehr hohe Leistung erreicht. Insbesondere zeilenbasierte Operationen werden in SQL CLR wesentlich schneller ausgeführt als in Transact-SQL. Sobald jedoch mengenbasierte Aktionen ins Spiel kommen, ist T-SQL deutlich überlegen. Bei CLR Skalarfunktionen (ohne Datenzugriff via SqlConnection) entsteht ein wesentlich geringerer Call-Overhead als bei den Pendants in T-SQL. Sind Datenzugriffe im Spiel (z.B. mit der ContextConnection) kann man grob sagen, dass T-SQL 2-3x schneller ist, weil dort ggf. Daten im Bufferpool liegen und somit wesentlich schneller darauf zugegriffen werden kann.

Wann sollte man die Nutzung von .NET-Assemblies im SQL Server in Erwägung ziehen?

Die Antwort ist, wie so oft…"It depends!".

Bei rechenintensiven oder zeilenweisen Operationen kann der Performancegewinn mit CLR-Code enorm sein. Die charmante Erweiterung von Funktionalität, die nicht standardmäßig in T-SQL verfügbar ist, aber auch der Zugriff auf externe Ressourcen (z.B. Dateien) können den Einsatz von SQL CLR ebenfalls rechtfertigen.

Bei mengenbasierten Operationen sollte man dann aber doch zu (T-)SQL greifen.

Bei Prozessen, die eigentlich in die Middle-Tier-Schicht gehören, sollte man ebenfalls von einer Integration in die Datenzugriffsschicht Abstand nehmen.

Es gibt sicher Situationen, in denen die Entscheidung T-SQL vs. SQL CLR nicht eindeutig abzugrenzen ist. In solchen Fällen ist es empfehlenswert, die beiden Methoden einfach gegenüberzustellen und unter Berücksichtigung des Assembly-Management-Overheads die Art der Implementierung abzuwägen.

Fazit

SQL CLR dient bei genauer Betrachtung und unter Berücksichtigung der Vor- und Nachteile als sinnvolle Erweiterung der nativen Funktionalität von T-SQL. Wenn es mit Maß eingesetzt wird, können Business Anwendungen in bestimmten Szenarien gut davon profitieren.

PS: Unter http://www.sqlsharp.com findet man bereits eine Bibliothek mit allen möglichen Funktionen, die mit SQL CLR umgesetzt wurden und ggf. (nach einem Review!) eingesetzt werden können.

Quellen

1. http://msdn.microsoft.com/en-us/magazine/cc163716.aspx (Critical Regions)
2. http://msdn.microsoft.com/en-us/library/ms131089(v=sql.110).aspx (inkl. Unterseiten)
3. http://www.sqlskills.com/blogs/bobb/category/sqlclr/ (inkl. Unterseiten)