WCF bietet von Haus aus die Möglichkeit, einen Service in einem Prozess anstatt im IIS zu hosten. Dadurch ist das Hosting wesentlich flexibler, denn zum einen erfordert ein HTTP Hosting nicht zwingend die Installation des IIS, zum anderen stehen auch andere Protokolle wie TCP/IP und Named Pipes zur Vefügung.

Der einzige Haken daran ist, dass ein Prozess, der ohne Admin Rechte läuft, nicht die Rechte hat um einen Service via HTTP zu hosten. Versucht man z.B. einen Windows Service ohne diese Rechte zu starten, erhält man die Fehlermeldung:

Service cannot be started. System.ServiceModel.AddressAccessDeniedException: HTTP could not register URL http://+:7270/[ServiceName]/. Your process does not have access rights to this namespace.

Es gibt aber einen Weg, den WCF Service auch ohne Admin Rechte zu hosten. Unter Windows Vista / 7 / Server 2008 (R2) steht dafür das Tool netsh zur Verfügung. Dadurch ist es möglich, einem Benutzer oder einer Gruppe Rechte auf einen HTTP Namespace, also z.b. „http://+:7270/ServiceName“ zu geben.

Die Syntax dafür lautet

netsh http add urlacl url=http://+:80/MyUri user=DOMAIN\user

Für Windows XP oder Windows Server 2003 übernimmt das Tool httpcfg diese Aufgabe.

Weitere Informationen liefert der Artikel Configuring Namespace Reservations (http://msdn.microsoft.com/en-us/library/ms733768.aspx)

Der einfachste Weg verwalteten Code (Managed Code – .NET Runtime – CLR) und SQL Server zu „verheiraten“ ist die Entwicklungsumgebung Visual Studio (2005 – 2010) zu verwenden. Visual Studio vereinfacht die Entwicklung drastisch, z.B. durch automatisierte Deployment Prozesse. Trotzdem werden wir den Prozess hier „per Hand“ erklären. Natürlich funktioniert dieser Weg auch mit jeder beliebigen .NET Entwicklungsumgebung.

Für jedes unterstützte Stück „Managed Code“ existieren im Namespace Microsoft.SqlServer.Server entsprechende Attribute welche Methoden für den SQL Server verfügbar machen. Dies sind im folgenden:

SqlProcedure 
SqlFunction
SqlUserDefinedAggregate
SqlUserDefinedType
SqlMethod

Auf die zwei häufigsten dieser Attribute möchte ich im folgenden nun kurz eingehen.

SqlProcedure

Die am meisten verwendeten – und nützlichsten – Features in eigenen Projekten sind wahrscheinlich Stored Procedures. Da diese aber meist in T-SQL entwickelt werden lassen sich damit meist nur schwierig komplexere Aufgaben abbilden. Zum Glück lassen sich Stored Procedures auch mit C# (oder einer anderen .NET kompatiblen Sprache) entwickeln. Jedoch gibt es gewisse Voraussetzungen für das entwickeln von „Managed Stored Procedures“.

• Die enthaltende Klasse muss public sein
• Die entsprechende Methode muss public static sein

Das sind alle Anforderungen. Klingt doch gar nicht mal so schlimm, oder? 😉 Um das ganze noch einmal zu verdeutlichen, hier ein Beispiel:

using System;
using Microsoft.SqlServer.Server;

namespace abapguru.SqlServer {
    public class SqlServerIntegration {
        [SqlProcedure("spDoSomething")]
        public static void DoSomething() {
            // Hier passiert was...
        }
    }
}

Hier gibt es also zu „normalen“ statischen Methoden kaum unterschiede. Das SqlProcedure Attribute macht die Methode DoSomething für den SQL Server zugreifbar. Der einzige unterstützte Parameter ist ein String der einen alternativen Namen für die Stored Procedure angibt. Im Normalfall ist dieser immer gleich dem Namen der Methode.

SqlFunction

Funktionen für den SQL Server zu erstellen ist fast so einfach wie Stored Procedures, mit dem einen unterschied dass die Methode einen Rückgabewert benötigt.

using System;
using Microsoft.SqlServer.Server;

namespace abapguru.SqlServer {
    public class SqlServerIntegration {
        [SqlFunction()]
        public static int CalculateSomething(int x, int y) {
            return (x + y);
        }
    }
}

Das Attribute SqlFunction unterstützt folgende Parameter:

• DataAccess
  Bestimmt, ob für die Berechnung auf Daten zugegriffen werden muss oder ob die Berechnung autonom durchgeführt werden kann
• IsDeterministic
  Bestimmt, ob die Funktion immer den selben Wert zurückgibt, unabhängig vom Status des SQL Servers
• IsPrecise
  Bestimmt, ob das Ergebnis „wissenschaftlich präzise“ ist (z.B. für mehrdimensionale Berechnungen nötig)
• Name
  Bestimmt den Namen der Funktion innerhalb des SQL Servers

Assembly in SQL Server einbinden

Angenommen wir haben nun eine fertige Assembly. Um diese innerhalb des SQL Servers verwenden zu können musse diese nur noch installiert eingebunden werden. Dies wird über einen T-SQL Befehl bewerkstelligt.

CREATE ASSEMBLY <NAME>
FROM <PATH>

Beispiel

CREATE ASSEMBLY abapguru.SqlServer
FROM 'C:\Assemblies\abapguru.SqlServer.dll'

Ich denke die Erklärung der Syntax kann ich mir sparen – das ist jedermann logisch oder 🙂

Eine Anmerkung noch: Standardmäßig wird die Assembly mit der „Berechtigung“ installiert, nur „safe Code“ auszuführen. Die CLR Integration hat die Notation von drei verschiedenen Ausführungsberechtigungen; diese werden auf Assemblyebene gesetzt.

• SAFE
  Nur CLR ist ausführbar. Kein Zugriff auf externe Resourcen
• EXTERNAL
  Zugriff auf externe Daten ist erlaubt. Darunter fallen z.B. Dateisysteme, Eventlog, Netzwerk. Kein Zugriff auf „unsafe Code“ or „interop code“.
• UNSAFE
  Keine Limitation, somit Zugriff auf alle Resourcen erlaubt

Diese Ausführungsberechtigungen können beim Importieren der Assembly mit Hilfe des WITH PERMISSION_SET Zusatzes angegeben werden.

CREATE ASSEMBLY <NAME>
FROM <PATH>
WITH PERMISSION_SET=(SAFE|EXTERNAL|UNSAFE)

Ähnlich wie alle DDL Befehle gibt es analog zu CREATE natürlich auch DROP und ALTER. Hier verweise ich allerdings auf die MSDN Dokumentation 😉

Managed Code auf T-SQL mappen

Nachdem die Assembly nun dem SQL Server bekannt ist benötigen wir nur mehr einen letzten, aber wichtigen Schritt. Die in CLR Codierten Funktionen und Stored Procedures müssen nun noch auf äquivalente T-SQL Befehle „gemappt“ werden. Dazu benötigen wir (schon wieder) einige DDL Befehle. Einige Teile davon dürften aber durchaus dem ein oder anderen bekannt sein, der schon seine Erfahrungen mit Stored Procedures gemacht hat.

CREATE PROCEDURE <NAME>
AS EXTERNAL NAME <ASSEMBLY>.<TYPE>.<METHOD>

Beispiel

CREATE PROCEDURE spDoSomething
AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.DoSomething

Dieses kleine Stück T-SQL erstellt eine neue Stored Procedure, allerdings mit Referenz auf das Coding innerhalb unserer eigens erstellten Assembly. Nun können wir mittels EXEC die Stored Procedure aufrufen.

EXEC spDoSomething

Sollte die Stored Procedure parametrisiert sein, können die Parameter (wie gewohnt) in T-SQL spezifizert werden. Achtung! Die Signatur muss hier exakt identisch sein. Beispiel:

CREATE PROCEDURE spDoSomethingWithParams
  @NUMBER INT
AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.DoSomethingWithParams

Ähnlich funktionerit das ganze auch mit Funktionen

CREATE FUNCTION CalculateSomething (
  @X INT,
  @Y INT
) RETURNS INT
AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.CalculateSomething

Auch hier kann, nach absetzen des obigen Codes die Funktion über SELECT aufgerufen werden.

SELECT CalculateSomething(5, 5)

Fehler 6506 vorbeugen

Solltet Ihr den Fehler mit der Nummer 6506 (Could not find Type in Assembly) beim mappen der Funktionen und SPs bekommen, hilft mit ziemlicher sicherheit folgender Trick. Als erstes sollte sichergestellt werden dass die CLR Integration prinzipiell aktiviert ist. Wenn nicht, bitte mit folgendem Befehl aktivieren.

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE

Wenn dieser Schritt erledigt ist und der Fehler euch immer noch nervt hilft folgendes. Aus einem (mir) unerklärlichen Grund muss der Assemblyname im Klassennamen noch einmal auftauchen. Beispiel:

Liefert den Fehler 6506

AS EXTERNAL NAME [abapguru.SqlServer].SqlIntegration.CalculateSomething

Läuft ohne Fehler durch

AS EXTERNAL NAME [abapguru.SqlServer].[abapguru.SqlServer.SqlIntegration].CalculateSomething

Geschafft!

Eine Anmerkung noch am Ende: Bitte extremste Sorgfalt beim Entwickeln von CLR Code welcher in SQL Server integriert wird. Sollte z.B. eine unbehandelte Ausnahme innerhalb des Codings auftreten kann es sein dass der SQL Server die komplette AppDomain entladen muss, was zu enormen Performanceproblemen führen kann.

Bei gelegenheit werde ich noch einen Post zum Thema Performance tippen – mal sehen wie es die Zeit zulässt.