Dit artikel beschrijft hoe een service gemaakt kan worden met SQLServer Service Broker. WCF is tegenwoordig de standaard keuze op het Microsoft platform voor het maken van services, maar dit artikel toont aan dat Service Broker een serieus alternatief kan zijn voor niet publieke services. Dit artikel beschrijft de gebruikte techniek: SQL Service Broker, XML data manipulatie in T-SQL en een C# stored procedures in combinatie met LINQ ter implementatie van de service. Het artikel is van toepassing op SQLServer 2005 en 2008.
Het artikel is gebaseerd op de praktijksituatie van de salarisverwerker SDB Groep in de zorgsector. Voor deze dienstverlener is een rekenservice gerealiseerd waarmee alle salariscomponenten opnieuw worden uitgerekend zodra het werkrooster van een zorgverlener wordt gewijzigd. De overheid streeft naar zoveel mogelijk handen aan het bed tegen zo laag mogelijke kosten, dus inzicht in de loonkosten helpt een zorginstelling om de zorgverlening optimaal te plannen. Aan het eind van elke salarisperiode worden de rekenresultaten als invoer gebruikt voor de salarisverwerking.
Figuur 1 geeft de oplossing schematisch weer. Een gebruiker bewerkt een werkrooster via de webapplicatie. Dat resulteert in een database transactie om het gewijzigde rooster weg te schrijven. Als gevolg daarvan moeten er salariscomponenten opnieuw berekend worden. Dat wordt gedaan door asynchroon een rekenservice aan te roepen die is geïmplementeerd als een C# stored procedure in een andere database op een andere server om de on-line database zo min mogelijk te belasten. In de tweede helft van dit artikel zal de oplossing stap voor stap worden toegelicht, maar nu volgt eerst enige uitleg van Service Broker.
Fig. 1: Schematische weergave van de oplossing
Wat is Service Broker?
Service Broker is een wat minder bekend onderdeel van SQLServer dat sinds versie 2005 aan het product is toegevoegd. Menig lezer zal ooit een tabel met een statusveld hebben geïntroduceerd om dit via een SQLServer Agent job te laten pollen om daarmee een stored procedure te starten zodra gedetecteerd wordt dat er records zijn die aan bepaalde conditie voldoen. Dat is een scenario dat nu beter met Service Broker kan worden opgelost.
Service Broker biedt de mogelijkheid om asynchroon services te laten uitvoeren via message queueing. Een service wordt geïmplementeerd met een stored procedure die gekoppeld is aan een queue. Een queue is in feite een speciaal soort tabel met een vooraf gedefinieerd schema, waarbij de belangrijkste velden het berichttype en het bericht zelf zijn. Een service kan geïnitieerd worden door een bericht naar een service (lees: queue) te sturen vanuit een database transactie. Het verwerken van het bericht uit de queue wordt vervolgens in een nieuwe transactie gedaan door een via Service Broker geïnitieerde stored procedure. Met andere woorden: Service Broker biedt de mogelijkheid tot het asynchroon laten uitvoeren van database transacties.
Service Broker biedt de mogelijkheid om asynchroon services te laten uitvoeren via message queueing
Het SELECT statement kan worden gebruikt om in een queue te kijken, maar T-SQL heeft ook uitbreidingen ondergaan. Je hebt b.v. de statements CREATE QUEUE, SEND en RECEIVE om respectievelijk een queue aan te maken en berichten op een queue te plaatsen dan wel er vanaf te halen. Enkele voorbeelden volgen in de tweede helft van dit artikel.
Er is locking van toepassing op berichten in de queue, zodat een bericht pas zichtbaar wordt nadat de versturende transactie gecommit is. Service Broker kan ervoor zorgen dat een stored procedure automatisch gestart wordt zodra een bericht op de queue wordt vrijgegeven. Dat doet Service Broker heel efficiënt. Er wordt niet voor elk nieuw bericht telkens een nieuwe stored procedure instantie gestart. Er wordt pas een extra instantie gestart als blijkt dat de reeds actieve instanties het tempo waarmee berichten arriveren niet kunnen bijhouden. De database administrator kan het maximum aantal instanties per queue configureren, zodat er niet ongewenst veel resources gebruikt worden.
De verzendende en ontvangende service kunnen in dezelfde database geïmplementeerd zijn, maar ook in verschillende databases en zelfs op een andere SQLServer, zoals in figuur 1 te zien is. Een service kan bovendien worden geïmplementeerd op verschillende SQL Servers tegelijk. Een bericht zal dan round-robin worden verzonden naar een van de servers om daar afgehandeld te worden. Een service kan hoog beschikbaar worden gemaakt door simpelweg de database hoog beschikbaar te maken met behulp van server clustering of database mirroring.
Waarom Service Broker?
De business casus vereist dat de rekenservice asynchroon maar gegarandeerd wordt uitgevoerd. Asynchroon omdat het werk van de planner niet gehinderd mag worden door het feit dat gekozen is om bij het opslaan van een rooster alles opnieuw te laten berekenen, en gegarandeerde verwerking omdat het om echt geld en om echte verlofdagen gaat.
Eén van de eigenschappen van Service Broker is dat het lezen van de queue of het schrijven naar de queue in dezelfde database transactie kan plaatsvinden als het manipuleren van de data in de tabellen van de database. Vergelijk dat eens met een WCF service die via het MSMQ transport ook gegarandeerde message verwerking kan bieden, maar alleen in combinatie met de Distributed Transaction Coordinator. Dat geeft een veel minder goede performance dan Service Broker. Vanwege de extra benodigde componenten buiten SQLServer is deze oplossing ook moeilijker te configureren en te beheren.
Het doorslaggevende argument om voor Service Broker te kiezen boven WCF met MSMQ is dat de berekeningen gestuurd worden door verschillende database tabellen. Als er iets wijzigt in een van die tabellen moeten berekeningen opnieuw worden gedaan. Met Service Broker is dat eenvoudig te realiseren door een bericht naar een service te sturen vanuit een database trigger.
Service Broker heeft extra het concept van een dialoog
Maar Service Broker heeft nog meer voordelen ten opzichte van WCF met MSMQ. Wat Service Broker extra heeft ten opzichte van andere messaging systemen is het concept van een dialoog. Als twee services met elkaar moeten gaan communiceren moet vooraf eerst een contract worden gedefinieerd. Dat beschrijft welke berichttypen er tussen twee services uitgewisseld kunnen worden en met welke berichttypen een dialoog kan beginnen. Service Broker dwingt af dat een dialoog tussen twee services altijd voldoet aan het contract. De ontvangende service kan er dus op rekenen dat ze altijd berichttypen ontvangt die ze kan verwerken. Service Broker garandeert bovendien dat er maar één service instantie tegelijk actief kan zijn met verwerking van berichten van dezelfde dialoog. Daarmee worden problemen met volgordelijkheid van verwerking voorkomen. Een dialoog zou bijvoorbeeld kunnen bestaan uit het eerst versturen van een order header, gevolgd door het versturen van één of meer orderregels. Zonder de garantie die Service Broker biedt zou het kunnen zijn dat de eerste orderregel al wordt verwerkt door een tweede service instantie terwijl de eerste service instantie nog bezig is met de verwerking van de header. Berichten van verschillende dialogen kunnen wel parallel door verschillende service instanties worden verwerkt.
In sommige situaties is de garantie van volgordelijke verwerking binnen een dialoog nog niet voldoende. Kijk b.v. eens naar de praktijk van de salarisverwerker, waar een planner twee keer achter elkaar een berekening voor dezelfde zorgverlener in dezelfde salarisperiode kan starten door twee wijzigingen achter elkaar uit te voeren op hetzelfde werkrooster. Elke berekening start een nieuwe dialoog met de rekenservice. De salarisverwerker moet er zeker van kunnen zijn dat alleen het rekenresultaat van de laatste situatie wordt bewaard. Dat is mogelijk met het Service Broker concept Conversation Group. Berichten van verschillende dialogen die onderdeel worden gemaakt van dezelfde Conversation Group, zullen gegarandeerd door één service instantie tegelijk worden afgehandeld. Dat werkt op basis van een Conversation Group lock, die een Service Broker service automatisch vraagt zodra ze het eerste nieuwe bericht van een Conversation Group leest. Er is maar één service instantie die deze exclusieve lock kan krijgen. Andere berichten binnen dezelfde Conversation Group blijven verborgen voor andere service instanties totdat de ene service instantie geen berichten voor die Conversation Group meer in de queue vindt en de lock loslaat. Er kunnen daarna nog meer berichten arriveren binnen de dezelfde conversation Group. De eerste de beste service instantie die daarvan het eerste nieuwe bericht te pakken krijgt, plaatst opnieuw een lock en gaat er mee aan de slag. Een Conversation Group bestaat uit niets anders dan een unique identifier. De oplossing voor de salarisverwerker is dus om een Conversation Group Id per combinatie van medewerker en salarisperiode in de database te administreren en te zorgen dat elke dialoog met de juiste Conversation Group Id wordt geïnitieerd.
Is SQL Server wel geschikt om complexe berekeningen te doen?
SQLServer is erg goed in setsgewijze datamanipulaties met behulp van T-SQL, maar berekeningen die meer vergen dan een paar aggregatie functies kunnen beter in een procedurele taal worden geschreven. De SQLCLR komt daarbij goed van pas. Stored procedures kunnen in C# worden geschreven en de .Net assembly kan in de database worden ondergebracht. De SQLCLR kan de code uitvoeren alsof het een T-SQL procedure is. Procedurele code is dus krachtig. Maar aan de andere kant: berekeningen op basis van een verzameling ingeplande diensten zijn toch ook weer setsgewijs. Het zou mooi zijn als LINQ in de database gebruikt zou kunnen worden zodat setsgewijze expressies gecombineerd kunnen worden met ingewikkelde rekenkundige expressies.
Door CLR is SQL Server geschikt voor berekeningen
SQLServer 2005 weet niets van het bestaan van LINQ, maar LINQ is niets anders dan een compiler truc die gewone CLR 2.0 compatible code oplevert met referenties naar een assembly uit het .Net 3.5 Framework. En de SQLServer CLR is compatible met de CLR 2.0. Dus het enige wat moet gebeuren om LINQ te ondersteunen vanuit de SQLServer CLR is het registreren van de betreffende .Net 3.5 Framework assembly in SQLServer. Listing 1 laat zien dat dit eenvoudig is.
use master
go
-- CLR ondersteuning aanzetten
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
go
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2008%'
BEGIN
-- Trustworthy setting is nodig om UNSAFE assemblies
-- te mogen gebruiken.
-- Een assembly is UNSAFE als hij afhankelijk is
-- van een assembly die niet op de trusted list staat.
ALTER DATABASE [$(TargetDB)] SET TRUSTWORTHY ON
END
go
USE [$(TargetDB)]
go
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2008%'
BEGIN
-- Om LINQ in de database te kunnen gebruiken is
-- onderstaande .NET 3.5 assembly nodig.
-- Omdat deze assembly nog niet op de trusted assembly
-- list staat van SQL Server 2005,
-- is de enige optie om hem in UNSAFE mode te gebruiken.
-- N.B. de $ parameter vereist dat SSMS in SQLCMD
-- mode staat (zie de button
-- met het rode uitroepteken op de toolbar)
IF NOT EXISTS(
SELECT 1
FROM sys.assemblies
WHERE name = 'System.Core')
BEGIN
CREATE ASSEMBLY [System.Core]
FROM '$(ProgramFiles)\Reference\Assemblies\
Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
END
END
go
Listing1: Registreren van de assembly met LINQ Extension methods in SQLServer 2005
Ten eerste moet de SQLCLR worden aangezet. Vervolgens kan de assembly worden geregistreerd. De registratie van de .Net 3.5 assembly is niet nodig voor SQL Server 2008, omdat deze de benodigde assembly standaard op zijn vertrouwde lijst met assemblies heeft staan. Dit is niet het geval voor SQL Server 2005 en daarom moet de assembly als UNSAFE worden gemarkeerd. Een database administrator die daar huiverig voor is,
moet zich realiseren dat deze settings alleen nodig zijn op de SQL Server instantie die de rekenservice host. En dat is natuurlijk een andere dan waarin alle productiedata zit. Die instantie draait zelfs op een andere server om de primaire database server niet te belasten met complexe berekeningen. Dus nog een extra SQL Server licentie erbij? Nee hoor, een gratis SQL Server Express Edition is voldoende mits de limiet van 1 CPU geen probleem is. SQL Service Broker werkt ook volledig op een SQL Server Express Edition, mits tenminste een Standard Edition in een dialoog betrokken is. En de SQL Server met de productiedata is dat.
Vervolgens is de rekenservice in een aantal stappen gemaakt.
- Startpunt is een XML Schema met de definitie van een drietal berichten. Ten eerste het vraagbericht met daarin alle data die nodig is voor een berekening. Ten tweede het antwoordbericht voor de rekenresultaten en ten derde een foutbericht mocht de berekening om een of andere reden niet lukken.
- Op basis van het XML Schema is met behulp van het utility XsdObjectGen [ref1] een C# objectmodel gegenereerd. Alle berekeningen zullen geschreven worden op dit objectmodel.
- De data wordt aangeleverd als een XML stream. Om de XML om te zetten in een instantie van een objectmodel en vice versa zijn XML (de)serializers nodig. Het .Net framework kan dynamisch XML (de)serializers genereren, maar de SQLCLR ondersteunt geen dynamisch gegenereerde code, dus in plaats daarvan zijn de serializers van te voren uitgegenereerd met behulp van het utility XgenPlus [ref2].
- Tenslotte is met behulp van Visual Studio 2008 een C# database project gemaakt. Dit project type bevat o.a. een template voor het maken van een CLR stored procedure. Daarbij moet bij de eigenschappen van het project gekozen worden voor .Net 3.5 als doelplatform; LINQ behoort dan tot de mogelijkheden.
Listing 2 toont de C# stored procedure met de code om de XML-stream naar het object model te converteren en vice versa. Daarbij wordt gebruik gemaakt van de gegenereerde XML serializer classes in de namespace SDB.Mplus.Rekenserver.ObjectModel.Serializers. Het T-SQL type xml mapt op het .Net type System.Data.SqlTypes.SqlXml. Nadat deze “plumbing” code is geschreven kan de daadwerkelijke implementatie van de rekenlogica getypeerd worden uitgeschreven tegen het objectmodel dat op basis van het XML schema gemaakt is.
using System;
using System.Data.SqlTypes;
using System.Xml;
using System.IO;
using System.LINQ;
using SDB.MPlus.RekenServer.ObjectModel;
using Helper =
SDB.MPlus.RekenServer.ObjectModel.Serializers;
public partial class StoredProcedures
{
[Microsoft.SQL Server .Server.SqlProcedure]
public static void BerekenLoonfactoren(SqlXml vraag,
out SqlXml antwoord)
{
// Creer het objectmodel uit de xml van de vraag
XmlReader reader = vraag.CreateReader();
Helper.LoonfactorBerekeningVraag.
LoonfactorBerekeningVraagSerializer vraagSerializer=
new Helper.LoonfactorBerekeningVraag.
LoonfactorBerekeningVraagSerializer();
LoonfactorBerekeningVraag vraagObject =
vraagSerializer.Deserialize(reader)
as LoonfactorBerekeningVraag;
// Bereken het antwoord
LoonfactorBerekeningAntwoord antwoordObject =
BerekenLoonfactorenImplementatie(vraagObject);
// Converteer het antwoord naar xml
Helper.LoonfactorBerekeningAntwoord.
LoonfactorBerekeningAntwoordSerializer
antwoordSerializer = new Helper.
LoonfactorBerekeningAntwoord.
LoonfactorBerekeningAntwoordSerializer();
MemoryStream antwoordStream = new MemoryStream();
antwoordSerializer.Serialize(antwoordStream,
antwoordObject);
antwoordStream.Flush();
antwoordStream.Position = 0;
antwoord = new SqlXml(antwoordStream);
}
/// <summary>
/// Type-safe implementatie van de LoonfactorBerekening
/// </summary>
private static LoonfactorBerekeningAntwoord
BerekenLoonfactorenImplementatie
(LoonfactorBerekeningVraag vraag)
{
// Implementatie code weggelaten
return new LoonfactorBerekeningAntwoord();
}
}
Listing2: C# stored procedure met XML serializatie
Om een idee te krijgen hoe vervolgens LINQ op het objectmodel leidt tot leesbare en dus onderhoudbare code is in listing 3 een code fragment weergegeven. Deze expressie had nog prima in T-SQL uitgeschreven kunnen worden, maar het gaat om het idee.
int aantalDagenGewerkt =
(from d in vraag.DienstCollection.Cast<Dienst>()
from tb in d.TijdblokCollection.Cast<Tijdblok>()
where (tb.DienstType.TeltMeeInGewerkteUren
&& !tb.IsZiek)
select d.Datum.Date).Distinct().Count();
Listing 3: Een berekening op setsgewijze data met behulp van LINQ
Nadat de C# stored procedure is voltooid en tot een library assembly is gecompileerd, moet hij in SQL Server worden gedeployed. Listing 4 toont hoe dat moet. Eerst moet de assembly worden geregistreerd en vervolgens kan een T-SQL stored procedure wrapper om de C# static methode worden gemaakt. Omdat deze assembly afhankelijk is van de .Net 3.5 System.Core assembly en deze door SQL Server 2008 wel standaard wordt vertrouwd, wordt bij het registreren van de assembly onderscheid gemaakt tussen het benodigde permission level in SQL Server 2005 en 2008.
IF @@VERSION LIKE 'Microsoft SQL Server 2008%'
BEGIN
CREATE ASSEMBLY
[SDB.MPlus.RekenServer.LoonfactorBerekening]
FROM '$(RekenserverAssemblyDir)\ SDB.MPlus.RekenServer.LoonfactorBerekening.dll'
WITH PERMISSION_SET = SAFE
END
ELSE IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
BEGIN
CREATE ASSEMBLY
[SDB.MPlus.RekenServer.LoonfactorBerekening]
FROM '$(RekenserverAssemblyDir)\ SDB.MPlus.RekenServer.LoonfactorBerekening.dll'
WITH PERMISSION_SET = UNSAFE
END
go
CREATE PROCEDURE dbo.BerekenLoonfactoren
(
@Vraag xml
, @Antwoord xml OUTPUT
)
-- [assembly name].[fully qualified type name].
-- [method name]
AS EXTERNAL NAME
[SDB.MPlus.RekenServer.LoonfactorBerekening].
[StoredProcedures].[BerekenLoonfactoren]
go
Listing 4: Registratie van de assembly en de C# stored procedure in SQL Server
Om deze stored procedure asynchroon via Service Broker te kunnen aansturen moet een aantal Service Broker artifacts worden gemaakt, zoals in de inleiding van dit artikel is beschreven. Listing 5 toont een gedeelte van de code aan de server zijde; het aanmaken van een aantal berichttypen, een contract, een queue en een service. Er is geen ruimte om alles te laten zien. Wat bijvoorbeeld ontbreekt, is het maken van een Service Broker endpoint waarmee SQL Server berichten zal ontvangen, alsmede de routes waarmee een bericht naar de juiste service wordt geleid. Dit is wel beschreven in de SQL Server Books Online [ref3]
IF NOT EXISTS(
SELECT 1 FROM sys.service_message_types
WHERE name = '/Rekenserver/LoonfactorBerekening/Vraag')
CREATE MESSAGE TYPE [/Rekenserver/LoonfactorBerekening/Vraag]
VALIDATION = WELL_FORMED_XML
IF NOT EXISTS(
SELECT 1 FROM sys.service_message_types
WHERE name = '/Rekenserver/LoonfactorBerekening/Antwoord')
CREATE MESSAGE
TYPE [/Rekenserver/LoonfactorBerekening/Antwoord]
VALIDATION = WELL_FORMED_XML
IF NOT EXISTS(SELECT 1 FROM sys.service_message_types
WHERE name = '/Rekenserver/Fout')
CREATE MESSAGE TYPE [/Rekenserver/Fout]
VALIDATION = WELL_FORMED_XML
IF NOT EXISTS(
SELECT 1 FROM sys.service_contracts
WHERE name =
'/Rekenserver/LoonfactorBerekening/DataContract')
CREATE CONTRACT
[/Rekenserver/LoonfactorBerekening/DataContract]
(
[/Rekenserver/LoonfactorBerekening/Vraag]
SENT BY INITIATOR
, [/Rekenserver/LoonfactorBerekening/Antwoord]
SENT BY TARGET
, [/Rekenserver/Fout]
SENT BY TARGET
)
IF NOT EXISTS(
SELECT 1 FROM sys.service_queues
WHERE name = 'LoonfactorBerekeningRekenQueue')
CREATE QUEUE dbo.LoonfactorBerekeningRekenQueue
-- Rekenserver moet berichten kunnen ontvangen
GRANT RECEIVE ON dbo.LoonfactorBerekeningRekenQueue
TO RekenserverUser
-- lokaal service endpoint aanmaken
IF NOT EXISTS(
SELECT 1 FROM sys.services
WHERE name =
'/Rekenserver/LoonfactorBerekening/RekenService')
BEGIN
CREATE SERVICE
[/Rekenserver/LoonfactorBerekening/RekenService]
AUTHORIZATION RekenserverUser
ON QUEUE dbo.LoonfactorBerekeningRekenQueue
(
[/Rekenserver/LoonfactorBerekening/DataContract]
)
END
-- MPlus moet messages kunnen versturen naar
-- de reken service
GRANT SEND ON
SERVICE::[/Rekenserver/LoonfactorBerekening/RekenService]
TO MPlusUser
Listing 5: Registratie van Service Broker artifacts
Vervolgens moet er een stored procedure worden geschreven die geactiveerd wordt als er een bericht in de queue verschijnt. Listing 6 toont deze stored procedure. De stored procedure is zo geschreven dat hij niet voor elk afzonderlijk bericht hoeft te worden geactiveerd. Als hij eenmaal is opgestart, blijft hij berichten uit de queue lezen zolang die er nog zijn (WHILE @@ERROR = 0). Het lezen van een bericht gebeurt via het RECEIVE statement. Met het nieuwe WAITFOR statement kan de maximale wachttijd worden geregeld. In het voorbeeld wordt de stored procedure beëindigd zodra er gedurende één seconde lang geen nieuwe berichten meer in de queue verschijnen. Elk bericht wordt apart transactioneel afgehandeld. Het daadwerkelijk afhandelen van het bericht wordt overgelaten aan de C# stored procedure waarvoor in listing 4 een T-SQL wrapper gemaakt is.
-- xml type methoden vereisen deze setting:
SET QUOTED_IDENTIFIER ON
go
-- Deze stored procedure is bedoeld als
-- "activating" stored procedure
-- voor de queue dbo.LoonfactorBerekeningQueue.
-- Op deze queue komen alle verzoeken binnen voor
-- een loonfactorberekening.
CREATE PROCEDURE dbo.LoonfactorBerekeningRekenQueueReader
AS
BEGIN
IF @@TRANCOUNT > 0
BEGIN
RAISERROR('dbo.LoonfactorBerekeningRekenQueueReader
mag niet vanuit een transactie gestart worden', 16, 1)
ROLLBACK TRANSACTION
RETURN 1
END
WHILE (@@ERROR = 0)
BEGIN
DECLARE @DialogHandle UNIQUEIDENTIFIER
DECLARE @RequestMessage xml
DECLARE @ResponseMessage xml
DECLARE @MessageType sysname
BEGIN TRANSACTION
BEGIN TRY
WAITFOR
(
RECEIVE TOP(1)
@DialogHandle = conversation_handle,
@RequestMessage = message_body,
@MessageType = message_type_name
FROM dbo.LoonfactorBerekeningRekenQueue
), TIMEOUT 1000
IF @@ROWCOUNT = 0
BEGIN
-- Geen berichten meer in de queue
ROLLBACK TRANSACTION
BREAK;
END
-- Voor testdoeleinden:
-- SELECT @MessageType as RequestMessageType,
-- @RequestMessage as RequestMessage
IF @MessageType =
N'/SDB/MPlus/Rekenserver/
LoonfactorBerekening/Vraag'
BEGIN
EXEC BerekenLoonfactoren
@RequestMessage,
@ResponseMessage OUTPUT;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE
[/SDB/MPlus/Rekenserver/
LoonfactorBerekening/Antwoord]
(@ResponseMessage);
END
ELSE IF @MessageType =
N'http://schemas.microsoft.com/SQL/
ServiceBroker/EndDialog'
BEGIN
-- Service initiator heeft de dialoog beeindigd.
-- Dan doen wij dat hier ook.
END CONVERSATION @DialogHandle;
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF XACT_STATE() = -1
BEGIN
-- huidige transactie is niet committable
-- Dit zou nooit mogen gebeuren. Gebeurt het
-- wel, dan wordt de foutmelding in de SQL Server
-- log geschreven.
PRINT ERROR_PROCEDURE()
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
ROLLBACK TRANSACTION
END
BEGIN TRANSACTION
-- Verwerk de fout, desnoods buiten
-- de oorspronkelijke transactie om
DECLARE @FoutBericht xml;
WITH XMLNAMESPACES (DEFAULT
'http://www.sdbnieuws.nl/MPlus/Rekenserver/2008')
SELECT @FoutBericht =
(
SELECT
ERROR_NUMBER() 'ErrorNumber'
, ERROR_SEVERITY() 'ErrorSeverity'
, ERROR_STATE() 'ErrorState'
, ERROR_PROCEDURE() 'ErrorProcedure'
, ERROR_LINE() 'ErrorLine'
, ERROR_MESSAGE() 'ErrorMessage'
FOR XML PATH ('RekenserverFout')
);
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [/SDB/MPlus/Rekenserver/Fout]
(@FoutBericht);
END CONVERSATION @DialogHandle;
COMMIT TRANSACTION
IF XACT_STATE() = 1
BEGIN
-- huidige transactie is wel committable
COMMIT TRANSACTION
END
END CATCH
END; -- WHILE
END
GO
GRANT EXECUTE
ON dbo.LoonfactorBerekeningRekenQueueReader
TO RekenServerUser
go
Listing 6: Stored procedure om berichten van de queue te verwerken
Tenslotte moet de stored procedure aan de queue worden gekoppeld en dan is de rekenservice is klaar voor gebruik. Listing 7 laat zien hoe dat moet.
ALTER QUEUE dbo.LoonfactorBerekeningRekenQueue
WITH ACTIVATION
(
STATUS = ON,
PROCEDURE_NAME = [LoonfactorBerekeningRekenQueueReader],
MAX_QUEUE_READERS = 4,
EXECUTE AS 'RekenserverUser'
)
Listing7: Stored procedure aan de queue koppelen.
Daarbij kan worden gekozen om de stored procedure automatisch te laten starten zodra er een bericht op de queue verschijnt (ACTIVATION = ON). Als één instantie van de stored procedure het tempo waarmee berichten op de queue verschijnen niet kan bijhouden, zal Service Broker een extra instantie starten tot het aangegeven maximum (MAX_QUEUE_READERS). Omdat de stored procedure asynchroon worden uitgevoerd, is er geen client connectie en moet er expliciet voor een security context worden gekozen m.b.v. EXECUTE AS.
De rekenserver implementeert de service, maar aan de initiërende kant is ook nog een aantal zaken vermeldenswaardig, zoals het initiëren van een nieuwe dialoog als onderdeel van een Conversation Group (zie listing 8). De dialoog wordt gestart WITH RELATED_CONVERSATION_GROUP. Zoals hiervoor al is beschreven is dit om te garanderen dat twee achtereenvolgende berekeningen van hetzelfde rooster in dezelfde volgorde worden afgehandeld, zodat altijd het resultaat van de meest recente berekening zal worden bewaard.
DECLARE @RekenserverDialogId uniqueidentifier;
DECLARE @ConversationGroupId uniqueidentifier;
DECLARE @DienstverbandID int;
-- niet getoond wordt hoe deze variabele een waarde krijgt
DECLARE @PeriodeID int; -- idem
DECLARE @Vraag xml;
-- Maak een conversation group voor deze combinatie
-- van periode en dienstverband. Dit voorkomt dat
-- twee Rekenserver opdrachten voor dezelfde
-- combinatie elkaar gaan 'inhalen'
EXEC Rekenserver.LoonfactorBerekening_ConversationGroupMaken
@PeriodeID = @PeriodeID
, @DienstverbandID = @DienstverbandID
, @ConversationGroupID = @ConversationGroupId OUTPUT
-- Maak een dialog voor het versturen van de vraag
BEGIN DIALOG CONVERSATION @RekenserverDialogId
FROM SERVICE [/Rekenserver/LoonfactorBerekening/DataService]
TO SERVICE '/Rekenserver/LoonfactorBerekening/RekenService'
ON CONTRACT [/Rekenserver/LoonfactorBerekening/DataContract]
WITH RELATED_CONVERSATION_GROUP = @ConversationGroupId;
-- Stel de vraag samen
EXEC Rekenserver.LoonfactorBerekening_VraagMaken
@DienstverbandID = @DienstverbandID
, @PeriodeID = @PeriodeID
, @MessageID = @RekenserverDialogId
, @Vraag = @Vraag OUTPUT
IF @Vraag IS NOT NULL
BEGIN
-- Verstuur de vraag
SEND ON CONVERSATION @RekenserverDialogId
MESSAGE TYPE [/Rekenserver/LoonfactorBerekening/Vraag]
(@Vraag);
END
ELSE
BEGIN
-- Er valt niks te versturen
END CONVERSATION @RekenserverDialogId
END
Listing 8: Start van een nieuwe dialoog als onderdeel van een conversation group
Er is nadrukkelijk aandacht nodig voor foutafhandeling bij een Service Broker applicatie
Bij een Service Broker applicatie moet nadrukkelijk aandacht besteed worden aan foutafhandeling. Als het verwerken van een bericht van de queue vijf keer achter elkaar tot een rollback van de transactie leidt, zal Service Broker automatisch de queue disablen en dan stopt de verwerking volledig. Om dit te voorkomen moet de code zo geschreven worden dat het bericht linksom dan wel rechtsom kan worden verwerkt. En simpelweg committen van de transactie is niet altijd een optie; SQL Server staat niet toe dat de consistentie van de database in het gedrang komt. Listing 9 toont het foutafhandelingsgedeelte van een queue reader stored procedure. Met de functie XACT_STATE() wordt bepaald wat de mogelijkheden zijn.
CREATE PROCEDURE
Rekenserver.LoonfactorBerekening_DataQueueReader
AS
BEGIN
IF @@TRANCOUNT > 0
BEGIN
RAISERROR(
'Rekenserver.LoonfactorBerekening_DataQueueReader
mag niet vanuit een transactie gestart worden', 16, 1)
ROLLBACK TRANSACTION
RETURN 1
END
WHILE (@@ERROR = 0)
BEGIN
DECLARE @DialogHandle uniqueidentifier
DECLARE @ConversationGroupId uniqueidentifier
DECLARE @Message xml
DECLARE @MessageType sysname
DECLARE @Foutmelding varchar(255)
BEGIN TRANSACTION
BEGIN TRY
WAITFOR
(
RECEIVE TOP(1)
@ConversationGroupId = conversation_group_id,
@DialogHandle = conversation_handle,
@Message = message_body,
@MessageType = message_type_name
FROM Rekenserver.LoonfactorBerekeningDataQueue
), TIMEOUT 5000
-- code met verwerking van het bericht is weggelaten
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (1205)
BEGIN
-- Er is een herstelbare fout opgetreden;
-- Probeer hetzelfde bericht nogmaals te verwerken
-- N.B. Error 1205 = deadlock
ROLLBACK TRANSACTION
CONTINUE
END
IF XACT_STATE() = -1
BEGIN
-- Huidige transactie is niet committable
-- Dit zou nooit mogen gebeuren. Gebeurt het wel,
-- dan wordt foutmelding in SQL Server log geschreven
SET @Foutmelding = 'Fout ' +
CONVERT(varchar(10), ERROR_NUMBER())
+ ' op regel ' + CONVERT(varchar(10),
ERROR_LINE()) + ' van procedure '
+ ERROR_PROCEDURE()
PRINT @Foutmelding
PRINT ERROR_MESSAGE()
ROLLBACK TRANSACTION
BREAK
END
ELSE
BEGIN
-- Huidige transactie is wel committable;
-- leg de foutmelding vast
UPDATE Rekenserver.LoonfactorBerekeningLog
SET Foutmelding = ERROR_MESSAGE()
WHERE SsbDialogHandle = @DialogHandle
-- breek de dialoog af
END CONVERSATION @DialogHandle;
COMMIT TRANSACTION
END
END CATCH
END; -- WHILE
END
Listing 9: Queue reader stored procedure
Tenslotte zal getoond worden hoe makkelijk het is sinds SQL Server 2005 om met XML-data te werken.
Om XML-data te genereren is het SELECT statement met FOR XML PATH clause de meest flexibele variant. Een dataveld kan als ofwel een XML-attribuut dan wel als een XML-element worden opgenomen. Het wordt een attribuut als de alias met een @ begint. Het maken van geneste XML-elementen is met deze variant ook heel eenvoudig. Begin gewoon een geneste subselect op de plaats waar het subelement moet komen. De TYPE subclause in de SELECT zorgt ervoor dat de data als XML-data wordt gezien en niet als tekst. Als een XML-element dieper genest moet worden dan de (sub)select die het dataveld selecteert, kan met een relatieve XPath-expressie de nesting expliciet gestuurd worden; vandaar de naam FOR XML PATH. Listing 10 toont beide technieken. Let vooral op het dieper geneste Salaris element. De XML-namespace wordt bepaald via de WITH XMLNAMESPACES clausule.
DECLARE @Vraag xml, @MessageID uniqueidentifier;
SET @MessageID = NewId();
WITH XMLNAMESPACES
(DEFAULT 'http://www.sdbgroep.nl/MPlus/Rekenserver/2008'),
SELECT @Vraag = (
SELECT @MessageID '@Id', CURRENT_TIMESTAMP '@Timestamp',
(
SELECT DienstverbandID '@Id',
(
SELECT CaoCodeID '@Id'
, CaoCodeCode 'Code'
, CaoCodeIs4Weken 'IsVierWeken'
, CaoCodeNaam 'Naam'
, CONVERT(int, CaoCodeWerkweekUren) 'WerkweekUren'
FROM dbo.tblCaoCode
WHERE CaoCodeID = dv.fkCaoCodeID
FOR XML PATH ('Cao'), TYPE
)
, dv.DienstverbandDagenPerWeek 'DagenPerWeek'
, CONVERT(char(10), dv.DienstverbandDatumInDienst, 120)
'DatumInDienst'
, CONVERT(char(10), dv.DienstverbandDatumUitDienst, 120)
'DatumUitDienst'
, ISNULL(NULLIF(dv.DienstverbandParttimeUren,0),
cao.CAOcodeWerkweekUren) 'ParttimeUren'
, dv.DienstverbandGarantieToeslag
'Salaris/@GarantieToeslag'
, gb.CAOInpassingCodeBedrag
'Salaris/@GarantieTredeBedrag'
, dv.DienstverbandLoonToeslag
'Salaris/@LoonToeslag'
, dv.DienstverbandSalaris
'Salaris/NominaalSalaris'
FROM …
WHERE …
FOR XML PATH ('Dienstverband'), TYPE
),
…
Listing 10: XML genereren met SELECT FOR XML PATH
Listing 11 toont een bijpassend XML-fragment.
<LoonfactorBerekeningVraag xmlns="http://www.sdbgroep.nl/MPlus/Rekenserver/2008"
Id="300B830A-EA57-DD11-82FF-000C294CDAD3"
Timestamp="2008-07-22T14:31:05.487">
<Dienstverband
xmlns="http://www.sdbgroep.nl/MPlus/Rekenserver/2008"
Id="3062">
<Cao
xmlns="http://www.sdbgroep.nl/MPlus/Rekenserver/2008"
Id="100">
<Code>4</Code>
<IsVierWeken>0</IsVierWeken>
<Naam>CAO Ziekenhuizen</Naam>
<WerkweekUren>36</WerkweekUren>
</Cao>
<DagenPerWeek>4.00</DagenPerWeek>
<ParttimeUren>23.00</ParttimeUren>
<Salaris GarantieToeslag="121.00" LoonToeslag="0.00">
<NominaalSalaris>1234.5600</NominaalSalaris>
</Salaris>
<Soort>PT</Soort>
</Dienstverband>
</LoonfactorBerekeningVraag>
Listing 11: Xml naar aanleiding van de SELECT uit listing 10.
De gegenereerde XML wordt vervolgens naar de rekenservice gestuurd en die komt uiteindelijk met een XML-antwoord. Dat XML-antwoord kan vrij eenvoudig weer tot een resultset worden omgevormd met behulp van de ingebouwde functies op het XML-datatype (zie listing 12). Met de nodes() functie wordt een XML-nodelist gemaakt. Vervolgens worden de waarden uit elke node geselecteerd met de value() functie. De resultset kan tenslotte gewoon worden gejoined aan andere tabellen.
DECLARE @Antwoord xml;
SET @Antwoord = '
<LoonfactorBerekeningAntwoord xmlns="http://www.sdbgroep.nl/MPlus/Rekenserver/2008"
Id="300B830A-EA57-DD11-82FF-000C294CDAD3"
Timestamp="2008-07-22T14:31:05.487">
<VariabeleMutatie>
<DienstverbandId>5031</DienstverbandId>
<KaartSoort>5</KaartSoort>
<LoonfactorCode>093</LoonfactorCode>
<PeriodeId>463</PeriodeId>
<Waarde>4</Waarde>
</VariabeleMutatie>
<VariabeleMutatie>
<DienstverbandId>5031</DienstverbandId>
<KaartSoort>5</KaartSoort>
<LoonfactorCode>094</LoonfactorCode>
<PeriodeId>463</PeriodeId>
<Waarde>1</Waarde>
</VariabeleMutatie>
</LoonfactorBerekeningAntwoord>';
WITH XMLNAMESPACES
('http://www.sdbgroep.nl/MPlus/Rekenserver/2008' as rs)
SELECT
vm.KaartSoort
, vm.Waarde
, vm.DienstverbandId
, lf.LoonfactorID
, vm.KostenPlaatsId
, vm.KostenSoortId
, vm.PeriodeId
FROM
(
SELECT
vm.value('(rs:DienstverbandId)[1]', 'int')
as DienstverbandId
, vm.value('(rs:KaartSoort)[1]', 'int')
as KaartSoort
, vm.value('(rs:KostenSoortId)[1]', 'int')
as KostenSoortId
, vm.value('(rs:KostenPlaatsId)[1]', 'int')
as KostenPlaatsId
, vm.value('(rs:LoonfactorCode)[1]', 'varchar(3)')
as LoonfactorCode
, vm.value('(rs:PeriodeId)[1]', 'int')
as PeriodeID
, vm.value('(rs:Waarde)[1]', 'numeric(7,2)')
as Waarde
FROM
@Antwoord.nodes(
'/rs:LoonfactorBerekeningAntwoord/rs:VariabeleMutatie')
VariabeleMutaties(vm)
) as vm
INNER JOIN dbo.Loonfactor lf
ON lf.LoonfactorCode = vm.LoonfactorCode
Listing 12: Het omzetten van een XML-fragment tot een relationele resultset
Conclusie
In dit artikel hebben we laten zien dat SQL Server 2005 of 2008 een uitstekend platform is om services te bouwen die dicht tegen de database aanliggen. Service Broker heeft geen op webservice standaard gebaseerde interfaces zoals WCF, dus is niet geschikt als publieke service. Maar de transactionele, betrouwbare en zeer snelle services die gemaakt kunnen worden met Service Broker zijn uitstekend geschikt als service achter de schermen. De SQLCLR maakt het mogelijk om die services gewoon met het vertrouwde en krachtige .Net framework te realiseren. De nieuwste T-SQL mogelijkheden sluiten daar naadloos op aan.
Referenties