SQL CLR: Integrationsmerkmale und Einsatzempfehlungen

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)

Data Analysis Expressions (DAX) in SSRS

In SSRS besteht auf den ersten Blick erst einmal keine Möglichkeit, DAX einsetzen zu können. In der Regel ist das auch nicht nötig, denn mit MDX kann man das tabulare Modell wie einen SSAS Cube abfragen. Aber welchen Grund gibt es dann DAX zu verwenden und wie geht das in SSRS?

Nun, in Szenarien, bei denen ROLAP-Zugriffe notwendig sind, hat man bei BISM Tabular nur die Möglichkeit mit DirectQuery oder mit T-SQL zu arbeiten. T-SQL ist bei vielen BI-Berechnungen (gerade was Time Intelligence angeht) eher weniger das Mittel der Wahl. Daher bleibt nur DirectQuery übrig, und das können wir nur mit DAX-Befehlen steuern. Mit dem folgenden Kniff kann man DAX auch in SSRS verwenden.

Dazu wird ein Dataset erstellt, dass auf einer Microsoft SQL Server Analysis Services-Datasource ausgeführt wird.

image

Im Query Design muss man jetzt auf eine Data Mining Query umschalten (Command Type DMX).

image

Nun wechselt man dann vom Designmode in den CommandMode…

image

und wo normalerweise eine DMX-Query landet, kann nun auch eine DAX-Query eingegeben werden.

image

Zugegeben, die Vorgehensweise ist auf den ersten Blick speziell, aber sie funktioniert. Auch Parameter können wie gewohnt (@ParameterName) verwendet werden.

Debuggen des MDX-Scripts

Eine häufig auftretende Aufgabenstellung beim Designen von SSAS-Cubes ist neben den Calculated Measures das sog. Scoping, also das gezielte Überschreiben von Zellwerten eines Cubes oder Cubeausschnitts, meist in einem fachlichen Kontext. Wie mittels des MDX Debuggers die Wirksamkeit und Korrektheit der Scopes überprüft werden kann, soll dieser kurze Artikel zeigen.
Innerhalb des MDX Skripts kann es mit zunehmenden fachlichen Anforderungen schon einmal unübersichtlich werden. Wer #Regions sucht, sucht leider vergebens, aber das nur am Rande. Mit Hilfe des Einzelschrittmodus kann man einigermaßen gut prüfen, welche Auswirkungen die jeweiligen Scoping-Anweisungen haben. Es gibt auch noch einen anderen Kniff, dazu aber später.
Zunächst navigiert man zur Scope-Anweisung im MDX Skript, die getestet werden soll, setzt einen Breakpoint (F9) und startet den Debug-Modus.
01_StartDebugging
Während des Debuggings können bis zu 4 MDX-Statements deklariert werden.
02_DeclareMDX
Für jedes einzelne MDX-Resultset kann man dann sehen, wie sich das formulierte Scope auswirkt. In diesem Beispiel wird der Betrag des Measures “Sales Amount Quota” für jedes Quartal des Fiskaljahres 2008 um 35% erhöht und das Ergebnis den entsprechenden Quartalen des Fiskaljahres 2009 zugeordnet.
Die betroffenen Zellen werden standardmäßig gelb hervorgehoben (deaktivierbar im Kontextmenü), sodass BI Entwickler diese sowohl bei kleinen als auch bei größeren Resultsets schnell erkennen können.
03_Scoping
Für das nächste Scoping wird das 2. MDX mit anderem Kontext genutzt. Die Quartalswerte des Fiskaljahres 2006 werden linear auf die darunterliegenden Monate verteilt.
04_Scoping2
Zusätzlich kann (wie auch im Cube Browser) der Security Context gewechselt und die im Cube definierten Rollen getestet werden.
Wie anfangs erwähnt kann man das Scoping auch noch mit einer anderen Methode testen, indem direkt hinter der Scope-Anweisung ein Formatierungsbefehl folgt, der die Änderungen erkennbar macht.
In diesem Beispiel wurde der Scope-Befehl um die Back_Color-Property ergänzt:

Scope 

( 

[Date].[Fiscal Year].&[2009],

[Date].[Fiscal].[Fiscal Quarter].Members,

[Measures].[Sales Amount Quota]

);    


This = ParallelPeriod                             

( 

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember 

) * 1.35; 


Back_Color(This) = RGB(0,255,0);

Schickt man nun im Management Studio ein simples MDX ab, kann man die Änderungen, die das Scope-Kommando vornimmt, sehr gut nachvollziehen. Wichtig hierbei ist die Angabe der Cell-Properties BACK_COLOR und FORMATTED_VALUE.
05_ScopingBackgroundColor
Fazit:
Das nicht allzu bekannte MDX-Script-Debugging bietet gute Möglichkeiten Berechnungen innerhalb von SSAS-Cubes zu testen. Trotzdem vermisst man Überwachungsfunktionen oder hilfreiche Performance-Indikatoren, die man jedoch zumindest teilweise mit den Query Processing Events im SQL Profiler tracen kann.
Insofern sind Debugging-Möglichkeiten für MDX-Scripts vorhanden, jedoch im Vergleich zu denen bei der .NET-Entwicklung recht bescheiden ausgeprägt.

SQL Server 2012: Sequences

In diesem Artikel möchte ich das neue Feature “Sequences”, das bereits durch einen Kollegen im Artikel Neue T-SQL Funktionen im SQL Server 2012 (Teil 1/4) vorgestellt wurde, noch etwas ausführlicher erläutern.

Wie gehe ich mit Sequences um und was passiert “behind the scenes”?

 

 

Anlegen & Abfragen

-- Listing 1

CREATE SEQUENCE [dbo].[SeqCounter] AS INT-- Zulässig: Alle Integer-Datentypen (Std.: BIGINT)

START WITH 1                             -- Ohne START WITH wird beim Minimalwert begonnen

INCREMENT BY 1                           -- Hinweis: Inkremente können auch negativ sein

MINVALUE 1
MAXVALUE 10

Zusätzlich zum Abrufen per NEXT VALUE FOR hat man auch die Möglichkeit ganze Wertebereiche mit der Stored Procedure sp_sequence_get_range abzurufen. Hierzu möchte ich aber auf den entsprechenden MSDN-Artikel verweisen.

Sequences ändern

ALTER SEQUENCE [dbo].[SeqCounter]
RESTART WITH 10        -- nächsten Wert auf 10 einstellen
NO CYCLE               -- NO CYCLE = Bei Überschreitung von 10 wird eine Exception ausgelöst
                       -- CYCLE    = Bei Überschreitung von 10 wird vom MINVALUE neu
begonnen

Nach Ausführung des Kommandos

SELECT NEXT VALUE FOR [dbo].[SeqCounter]

erscheint wie erwartet der Wert 10, der mit RESTART WITH festgelegt wurde.

Wenn ich das Kommando erneut ausführe, wird durch die Option NO CYCLE folgende Exception ausgelöst:

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘SeqCounter’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Hätten wir statt NO CYCLE die Option CYCLE verwendet, wäre das Ergebnis 1 ausgegeben worden.

Sequences löschen

Wenn wir die Sequence nicht mehr benötigen, kann sie mit dem DROP-Statement entfernt werden.

-- Löschen eines Sequence-Objekts
DROP SEQUENCE [dbo].[SeqCounter]

Welche Sequences gibt es?

Über den INFORMATION_SCHEMA-Katalog (INFORMATION_SCHEMA.SEQUENCES) oder über den Systemkatalog (SYS.SEQUENCES) lassen sich alle im aktuellen Datenbankkontext angelegten Sequences abfragen.

Alternativ hat das SQL Server-Team Sequences als neuen Knoten unter “Programmability” bzw. “Programmierbarkeit” einer Datenbank aufgenommen. Dort kann man div. Einstellungen mit Hilfe der GUI vornehmen.

Transaktionales Verhalten

Sequences wissen nichts von Transaktionen, d.h. wenn man ein ROLLBACK durchführt, sind die innerhalb des Transaktionsblocks abgerufenen Sequence-Werte “verbraucht”, sodass ohne separates Eingreifen eine Lücke in der sequentiellen Werte-Reihenfolge entsteht.

Caching

Die Caching-Option ist der größte Unterschied zu gewöhnlichen IDENTITY-Columns. Während bei Identitätsspalten per Default 20 Werte im Cache liegen (dieser Wert ist fest, differiert u.U. jedoch von Version zu Version), lässt sich das bei SEQUENCES nach individuellem Bedarf steuern. Standardmäßig wird ein Cache von 50 Werten verwendet, sofern weder die Option CACHE noch NO CACHE angegeben wird.

Mit Angabe der CACHE-Option hat man somit direkten Einfluss auf I/O-Vorgänge. Aber wie funktioniert das genau?

Hier ein kleines Beispiel (wir starten bei 10 und stellen den Cache auf 100 Werte ein)

ALTER SEQUENCE [dbo].[SeqCounter]
RESTART WITH 10
MINVALUE 10
MAXVALUE 1000000 CACHE 100

Wenn der erste Wert angefordert wird startet der SQL Server mit 10, vermerkt aber in der Systemtabelle sys.sequences den Wert 110 (aktueller Wert plus CACHE Einstellung) – er behauptet also, dass er bereits 100 Werte mehr vergeben hat. Sobald ein neuer Wert von der Sequence abgerufen wird, liefert der SQL Server diesen aus dem Zähler im Speicher, also 11, 12, …, bis 110. Damit ist die Grenze des gecachten Bereichs überschritten. Sobald der nächste Wert abgerufen wird wiederholt der SQL Server das Vorgehen: Er liefert zwar den nächsten Wert, schreibt aber wiederum den um 100 (basierend auf der CACHE-Einstellung) größeren Wert in die Systemtabelle.

Diese Vorgehensweise vermindert im Gegensatz zu Identities die Anzahl der Schreibzugriffe auf Metadaten und kann somit (insbesondere bei Massenverarbeitungsläufen) die Performance steigern.

Übrigens:

John Huang schreibt in seinem Blog, dass das Festlegen von großen CACHE-Werten kaum Performancevorteile bringt, da die wenigen Schreibvorgänge irgendwann nicht mehr wirklich ins Gewicht fallen.

Sollte der CACHE-Wert größer sein als MAXVALUE, wird der CACHE-Wert automatisch auf MAXVALUE reduziert.

Was passiert bei unerwartetem Herunterfahren der Datenbank-Engine?

Wenn die Datenbank-Engine per Service-Stop beendet wird, läuft alles “geregelt” ab, d.h. SQL Server schreibt dann den zuletzt vergebenen Wert (unabhängig der Cache-Einstellung) auf die Platte, sodass bei Neustart der nächste Wert vergeben werden kann und keine Sequence-Gap entsteht.

Im Desaster-Fall (Stromausfall oder “jemand deinstalliert SQL Server”…ok, dann ist sowieso alles weitere egal…aber man könnte schon von einem Desaster sprechen Winking smile) hat SQL Server u.U. keine Möglichkeit mehr diese Säuberungsarbeiten durchzuführen. D.h. im Klartext…die Werte gelten als “abgerufen”, sodass Lücken entstehen, die man aber auch manuell durch das RESTART WITH-Kommando “geradebiegen” könnte, sofern sinnvoll/gewünscht. Die Gefahr, dass Werte doppelt vergeben werden, besteht jedoch nicht.

Im Übrigen haben IDENTITIES hier einen Vorteil. Beim Hochfahren der Datenbank-Engine prüft SQL Server u.a. auch die zuletzt vergebenen Identitätswerte. Das stellt sicher, dass keine IDENTITY-Gaps durch technische Probleme entstehen können.

Security

Da es sich bei Sequences um “normale” Datenbankobjekte handelt, können hierauf auch Berechtigungen gesetzt werden. Dies kann man entweder über das eben erwähnte Dialogfeld oder klassisch über GRANT….TO… erledigen.

Einschränkungen

Sequences können nicht innerhalb von benutzerdefinierten Funktionen, Views, Unterabfragen, CTEs oder abgeleiteten Tabellen verwendet werden.

Ferner bekommt man Fehlermeldungen, sobald das verwendete Statement DISTINCT, EXCEPT, INTERSECT oder UNION verwendet. Eine Ausnahme davon bildet UNION ALL.

Es gibt noch eine Reihe weiterer Limitationen, die jedoch fast alle damit zu tun haben, dass sie nicht innerhalb von Aggregationen verwendet werden dürfen (z.B. PARTITION BY, GROUP BY…usw.).

Fazit

Sequences können bei richtiger Konfiguration performanter ablaufen und sind tabellenübergreifend einsetzbar. Sie lassen sich innerhalb einer Tabellendefinition als DEFAULT-Wert einbinden und können jederzeit modifiziert werden. Diese Vorzüge kombiniert mit höherer Kontrolle machen SEQUENCES zu einer sinnvollen Alternative, deren Einsatz jedoch hinsichtlich der vorhandenen Einschränkungen abgewogen werden muss.

Offizielle Dokumentation: http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx