Introductie SQL Server Service Broker

Met de komst van SQL Server 2005 heeft de .NET ontwikkelaar een hele set van nieuwe functionaliteiten en tools aangereikt gekregen om schaalbare database applicaties te ontwikkelen. In eerdere artikelen hebben we onderwerpen als SQL Server CLR integratie en SQL Server XML integratie al besproken. Samen met SQL Webservices, Notification Services en Service Broker vormen deze functionaliteiten het antwoord van Microsoft om gedistribueerde, asynchrone database applicaties te ontwikkelen.

Een voorbeeld waar SQL Server Service Broker ingezet kan worden is een webwinkel. Denk maar eens aan een webwinkel als Amazon of Bol.com; je bestelt een boek of een ander artikel en je krijgt prompt een melding dat je bestelling in behandeling is. Vervolgens krijg je daarna nog berichten wat de status van je bestelling is. Dit zijn allemaal asynchrone acties welke prima door Service Broker uitgevoerd kunnen worden.

In dit artikel gaan we kort in op de verschillen met de andere messaging systemen die ons ter beschikking staan en wat de voordelen van Service Broker zijn. Verder bespreken we de Service Broker architectuur aan de hand van enkele terminologieën. Tot slot laten we aan de hand van het Service Broker objectmodel (ServiceBrokerInterface) een voorbeeld zien hoe we Service Broker kunnen inzetten door middel van een .NET applicatie.

Wat is SQL Server Service Broker?

Zoals aangegeven is SQL Server Service Broker het antwoord van Microsoft om gedistribueerde, schaalbare database applicaties te ontwikkelen.

Het antwoord van Microsoft om gedistribueerde, schaalbare database applicaties te ontwikkelen.

Wat moeten we echter met nog een messaging systeem als we ook al producten als MSMQ en Biztalk hebben en straks Windows Workflow Communincation?

Allereerst wordt Service Broker niet neergezet als een messaging systeem. Als je Service Broker vergelijkt met MSMQ, dan zijn de grootste verschillen dat MSMQ voor verschillende Windows platformen geschikt is, terwijl Service Broker alleen werkt met SQL 2005. Een voordeel van Service Broker ten opzichte van MSMQ is, is dat Service Broker long running transactions aan kan en dat Service Broker grotere berichten kan verwerken. Verder geldt dat bij Service Broker dat het verwerken van een bericht van de queue en een bijbehorende database actie in één transactie gedaan kunnen worden. Dit is niet het geval bij MSMQ. We spreken dan van een two-fase commit.

Als we Service broker vergelijken met Biztalk, dan is de overeenkomst dat beide aan reliable messaging tussen SQL Server instanties doen. Daar houdt de vergelijking op, want Biztalk kan nog veel meer zoals orchestration, state bewaren, gebruik maken van verschillende transporttypes, etc. Wil je meer dan alleen berichten versturen tussen SQL Server 2005 instanties, dan is Biztalk de juiste optie. Hoeft de applicatie echter alleen maar berichten te versturen tussen twee SQL Server 2005 databases, dan is Service Broker een optie.

Windows Communication Framework verzorgt een nieuwe communicatie-infrastructuur die rondom de webservices architectuur is gebouwd. Advanced Webservices binnen Windows Communication Foundation verzorgen zaken als security, reliability, transactionele messaging en interoperabiliteit. Het programmeermodel is gebouwd rondom het Microsoft .NET Framework. In 2006 wordt naar verwachting BizTalk uitgeleverd met een WCF adapter en ook SQL Server zal een WCF adapter uitleveren. De volgende versie van SQL Server zal naar verwachting uitgeleverd worden met een WCF Service broker.

Nu we weten wat de verschillen zijn tussen Service Broker en de hierboven genoemde alternatieven, kunnen we ons meer richten op de voordelen van Service Broker. Hieronder volgen enkele voordelen van Service Broker in willekeurige volgorde.

Verregaande database integratie. Omdat Service Broker onderdeel van SQL Server is, maakt het ook gebruik van de standaard SQL functionaliteit zoals backup/restore, profiling, etc. Instellingen die voor de database gelden, gelden ook voor Service Broker. Dit betekent dat we geen nieuwe tools hoeven te leren.

Internal activation. Hierbij wordt de queue continu bewaakt door Service Broker. Als gesignaleerd wordt, dat berichten verwerkt moeten worden, dan zorgt activation ervoor dat een stored procedure het bericht verwerkt. Indien tijdens een piekbelasting een stored procedure het werk niet alleen aan kan en er dus meerdere berichten staan te wachten voor verwerking, dan zorgt activation ervoor dat er meerdere stored procedures opgestart worden om de berichten te verwerken.

Transactional messaging. SQL Server Service Broker garandeert dat een bericht precies éénmaal verwerkt wordt, ook als het netwerk tijdens een transactie uit de lucht gaat.

Reliable message transport. Service Broker garandeert dat een bericht aankomt als het verstuurd wordt van broker locatie A naar broker locatie B. Dit geldt ook als de twee locaties op verschillende SQL Server instanties staan.

Ben je niet van plan Service Broker te gebruiken in je applicatie, dan is de kans groot dat je Service Broker onbewust toch gebruikt

Andere voordelen van Service Broker kun je vinden in SQL Server 2005 books online. Ben je niet van plan Service Broker te gebruiken in je applicatie, dan is de kans groot dat je Service Broker onbewust toch gebruikt. Service broker wordt namelijk gebruikt voor Notification Services, Query Notifications en voor Database Mail.

SQL Server Service Broker Architectuur

Conversation

De basis van het berichtverkeer binnen Service Broker wordt gevormd door een conversation. Een conversation is een geordende uitwisseling van berichten. Service Broker onderkent twee type conversations; een dialoog en een monoloog. Monologen zijn niet beschikbaar in de eerste versie van Service Broker, dus blijven de dialogen over. Een dialoog is een conversatie tussen twee endpoints. Een endpoint kan gelijk gesteld worden aan een queue; een endpoint kan zowel berichten versturen als berichten ontvangen. De endpoint die begint met het verzenden van het eerste bericht wordt ook de ‘initiator’ genoemd. De endpoint die het eerste bericht ontvangt wordt het ‘target’ genoemd. Als eenmaal het eerste bericht verzonden is, kunnen zowel de initiator als het target berichten versturen en ontvangen.

Queue

Een queue is een tijdelijke opslagplaats voor berichten. Een queue heeft dezelfde structuur als een table, maar normale TSQL statements kunnen we niet uitvoeren op een queue. Service Broker onderkent twee type queues: user defined queues en transmissie queues. User defined queues zijn de queues die door de ontwikkelaar aangemaakt worden om berichten van A naar B te verzenden. Een transmissie queue is een interne queue die Service Broker gebruikt om de betreffende berichten te verzenden. Voordat een bericht verzonden wordt, wordt deze namelijk in de transmissie queue geplaatst en daar bewaard totdat een bevestiging terug gekomen is dat het bericht goed ontvangen is. Het gebruik van de transmissie queues is voor de gebruiker transparant; deze merkt hier niets van.

-- create queues and services
CREATE QUEUE InitiatorQueue
  WITH ACTIVATION
    (STATUS=ON,
     PROCEDURE_NAME=SDNStoredProc,
     MAX_QUEUE_READERS=5,
     EXECUTE AS SELF
    )
GO

CREATE QUEUE TargetQueue
GO

Listing 1: Voorbeeld creatie Queue

Zoals je ziet heeft de ‘Create Queue’ syntax verschillende argumenten. Wat deze argumenten precies betekenen, staat beschreven in de SQL Server 2005 books online. Als de status van een queue ‘OFF’ is, dan worden er geen berichten meer naar deze queue gestuurd. Berichten die dan naar deze queue gestuurd worden, worden in de transmissie queue geplaatst en pas verwerkt als de status van de queue weer ‘ON’ is. In het eerste voorbeeld creëren we een queue waarbij een bepaalde stored procedure geactiveerd wordt als er een bericht binnen komt. Er worden in het voorbeeld maximaal vijf instanties geactiveerd van deze stored procedure tijdens de piekbelasting.

Message Type

Berichten die van queue A naar queue B verzonden worden, moeten van een bepaald message type zijn. We kunnen niet zomaar elk willekeurig bericht naar een queue verzenden. Het is mogelijk om een validatie op een bericht te plaatsen. Er zijn vier validatie mogelijkheden, van ‘NONE’, ofwel geen validatie, tot ‘VALID_XML WITH SCHEMA COLLECTION’, waarbij het bericht aan een vooraf gedefinieerde XSD schema moet voldoen. Het datatype van het bericht zelf is varbinary(MAX), dit betekent dat een bericht van alles mag zijn en dat het maximaal twee gigabyte groot kan zijn. Het is interessant te weten dat validatie aan de ontvangende kant gebeurt en niet aan de zendende kant.

-- define two message types with XML validation
CREATE MESSAGE TYPE RequestMsg
VALIDATION = WELL_FORMED_XML
GO

CREATE MESSAGE TYPE ResponseMsg
VALIDATION = WELL_FORMED_XML
GO

Listing 2: Voorbeeld creatie Message type

Contract

Een contract is een overeenkomst tussen twee endpoints, waarin aangegeven wordt welke message types in een conversatie gebruikt mogen worden. Het contract geeft aan welk message type door de initiator gebruikt wordt en welk message type door het target gebruikt wordt.

-- define a contract between the two sides
CREATE CONTRACT SDNContract
 (RequestMsg SENT BY INITIATOR,
  ResponseMsg SENT BY TARGET)
GO

Listing 3: Voorbeeld creatie Contract

Service

Een service is een verzameling opdrachten voor het verwerken van berichten. De service definieert de queue die de berichten ontvangt en het contract waar de berichten aan moeten voldoen. We definiëren een service en gebruiken niet direct een queue om zo onafhankelijk te zijn van de infrastructuur waar Service Broker op draait. Door services te gebruiken in plaats van queues is het mogelijk om bijvoorbeeld een queue te verplaatsen naar een andere machine zonder dat de programmatuur aangepast hoeft te worden. Een conversation gebeurt altijd tussen services en niet tussen queues.

CREATE SERVICE SDNServiceTarget
 ON QUEUE InitiatorQueue
 (SDNContract)
GO

CREATE SERVICE SDNServiceInitiator
 ON QUEUE TargetQueue
 (SDNContract)
GO

Listing 4: Voorbeeld creatie Service

Samenvatting architectuur

Hierboven hebben we schijnbaar allemaal verschillende termen besproken die we bij SQL Server Service Broker gebruiken, maar wat is nu de samenhang tussen deze verschillende termen?

Allereerst hebben we een queue waarin we berichten kunnen plaatsen. Berichten kunnen we alleen in een queue plaatsen door middel van een service. Een service bevat een contract waarin aangegeven is, waar het bericht aan moet voldoen dat door de queue verzonden wordt. Schematisch ziet de SQL Server Service Broker architectuur er als volgt uit:

Fig. 1: SQL Server Service Broker Architecture

SQL Server Service Broker Objectmodel

Nu we weten hoe de architectuur van SQL Server Service Broker eruit ziet, gaan we een voorbeeld maken. We doen dit met behulp van het objectmodel dat bij Service Broker geleverd is. Als SQL Server geïnstalleerd wordt, staat bij de voorbeelden het objectmodel waaraan je kunt refereren als je een .NET applicatie ontwikkelt.  Dit objectmodel wordt dus niet officieel ondersteund door Microsoft maar gewoon als voorbeeld (Service Broker Interface) meegeleverd.

Het objectmodel staat op de locatie ‘C:\Program Files\Microsoft SQL Server\90\Samples\Engine\ServiceBroker\ServiceBrokerInterface\cs’

Als we de solution ‘ServiceBrokerInterface’ voor SQL Server Service Broker laden, dan zien we de volgende bestanden in de solution:

Fig. 2: ServiceBrokerInterface

Als we Service Broker Interface bestuderen, dan zien we het volgende. Er is één bestand waarin attributen gedefinieerd worden die we meegeven aan onze eigen broker methoden. Verder is er een bestand waarin de Service Exception gedefinieerd wordt. Ten slotte zijn er een aantal bestanden voor het creëren van een message, een service en een conversatie. We hebben ook nog een bestand ‘Broker Application’, maar deze is verder niet ingevuld.

Op basis van dit objectmodel ontwikkelen we een Windows applicatie, zodat we een bericht kunnen versturen naar een service die het bericht verder verwerkt in de database. Als de service het bericht verwerkt heeft, stuurt deze weer een bevestiging terug naar de Windows applicatie.  Verder maken we gebruik van de services, queues en message types die we hierboven gedefinieerd hebben. Allereerst ontwikkelen we dus de service applicatie.

Service applicatie

We maken een klasse ‘SDNDemoService’ die erft van de klasse ‘Service’. Verder definiëren we twee methoden en een stored procedure in deze klasse. De stored procedure doet niets anders dan de service creëren en opstarten. De methode ‘InsertMessage’ wordt geactiveerd als de SDNDemoService een bericht binnen krijgt onder een bepaald contract (SDNContract) en een bepaald messagetype (RequestMsg).  Het contract en het message type worden gedefinieerd in de attributen die bij deze method horen. Hieronder volgt de sourcecode voor de service applicatie.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
using Microsoft.Samples.SqlServer;
using System.Data.SqlClient;

public partial class SDNDemoService : Service
{
   public SDNDemoService(SqlConnection conn):
     base("SDNServiceTarget", conn)
  {
    WaitforTimeout = TimeSpan.FromSeconds(10);
  }

  // Message Handlers
  // Implement BrokerMethod to handle your message
  [BrokerMethod("SDNContract", "RequestMsg")]
  public void InsertMessage(Message msgReceived,
                            SqlConnection connection,
                            SqlTransaction transaction)
  {
    // Handle the message in the database
    TextReader reader =
      new StreamReader(msgReceived.Body);
    string sql =
      "insert into dbo.sdndemo ([MESSAGE]) VALUES ('" +
      reader.ReadToEnd() + "')";
    SqlCommand comm =
      new SqlCommand(sql, connection, transaction);
    comm.ExecuteNonQuery();
    // Sent message to the initiator
    MemoryStream body =
      new MemoryStream(Encoding.ASCII.GetBytes(""));
    Message msgSend = new Message("ResponseMsg", body);
    Conversation conversation = msgReceived.Conversation;
    conversation.Send(msgSend, connection, transaction);
    conversation.End(connection, transaction);
  }

  // Implement BrokerMethod to handle EndConversation messages
  [BrokerMethod(Message.EndDialogType)]
  public void EndConversation(
    Message msgReceived,
    SqlConnection connection,
    SqlTransaction transaction)
  {
    Conversation conversation =
      msgReceived.Conversation;
    conversation.End(connection, transaction);
  }

  // Implement BrokerMethod to handle
  // EndConversation messages
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void SDNStoredProc()
  {
    Service service = null;
    SqlConnection conn = null;
    try
    {
      // Get the SqlConnection from
      // the in-proc managed provider
      conn = new
        SqlConnection("context connection=true");
      conn.Open();

      // Create an instance of our DemoService
      service = new SDNDemoService(conn);
      service.FetchSize = 1;

      // Use default message loop for fetching messages
      service.Run(true, conn, null);
    }
    catch (ServiceException svcex)
    {
      Conversation conversation =
        svcex.CurrentConversation;
      if (conversation != null)
      {
        conversation.EndWithError(
          1,
          "Ending dialog with error: " +
            svcex.InnerException.Message,
        svcex.Connection,
        svcex.Transaction);
      }

      SqlTransaction transaction = svcex.Transaction;
      if (transaction != null)
        transaction.Commit();
    }
    catch (SqlException sqlex)
    {
    }
    finally
    {
      conn.Close();
    }
  }
};

Listing 5: Implementatie Service applicatie

Dit completeert de service applicatie. We hebben een service gedefinieerd plus een methode die aangeroepen wordt als er een bericht binnenkomt. Tevens hebben we een stored procedure gedefinieerd die de service ‘SDNDemoService’ aanroept. Deze stored procedure zien we ook weer terug bij de declaratie van de queue ‘InitiatorQueue’.

Client applicatie

We hebben nu nog een applicatie nodig die een bericht verstuurt naar onze service applicatie. We ontwikkelen hiervoor een Windows applicatie waarin we een textbox definiëren voor het invoeren van een bericht en een listbox voor het tonen van de voortgang van de berichtverwerking. Hieronder volgt de source code:

private void btnSentInitiator_Click(
  object sender, EventArgs e)
{
  lstInitiator.Items.Add(
    "Connecting to SQL Server ServiceBroker");
  // Create a connection and open it
  conn = new SqlConnection(
           "Initial Catalog=SDNServiceBroker; Data
           Source=localhost;Integrated Security=SSPI;");
  conn.Open();

  // Begin a transaction
  tran = conn.BeginTransaction();

  // Our Service instance
  Service client = null;
  client = new
    Service("SDNServiceInitiator", conn, tran);

  // We will receive one message at a time
  client.FetchSize = 1;

  Conversation dialog = null;
  dialog =
    client.BeginDialog(
      "SDNServiceTarget", null, "SDNContract",
      TimeSpan.FromMinutes(1), false, conn, tran);
  lstInitiator.Items.Add("Begin dialog");

  // Create an request message
  string msg =
    "" +
    txtMessageInitiator.Text +
    "";
  MemoryStream stm = new
    MemoryStream(Encoding.ASCII.GetBytes(msg));
  Microsoft.Samples.SqlServer.Message request = new
    Microsoft.Samples.SqlServer.Message(
      "RequestMsg", stm);

  // Send the message to the service
  dialog.Send(request, conn, tran);
  lstInitiator.Items.Add(
    "Message sent of type '" + request.Type + "'");
  lstInitiator.Items.Add("Message: " + msg);
  // Message isn't sent until transaction is committed
  tran.Commit();
  lstInitiator.Items.Add("Transaction committed");

  // Begin another transaction
  tran = conn.BeginTransaction();

  lstInitiator.Items.Add("Waiting for Response....");

  // set a WaitforTimeout using TimeSpan class
  client.WaitforTimeout = TimeSpan.FromSeconds(5);
  if (client.GetConversation(dialog, conn, tran)==null)
  {
    lstInitiator.Items.Add(
      "No message received –
      Ending dialog with Error");
    dialog.EndWithError(
      1, "no response within 5 seconds.", conn, tran);
    tran.Commit();
    conn.Close();
    return;
  }

  Microsoft.Samples.SqlServer.Message response = null;
  // Receive the message
  response = dialog.Receive();
  if (response.Body != null)
  {
    lstInitiator.Items.Add("Message contains: ");
    reader = new StreamReader(response.Body);
    lstInitiator.Items.Add(reader.ReadToEnd());
  }

  lstInitiator.Items.Add("Ended Dialog");
  // End the conversation
  dialog.End(conn, tran);
  // Remember to commit again
  tran.Commit();          
  // Close the connection
  conn.Close();
}

Listing 6: Implementatie Client applicatie

Zowel de service applicatie als de client applicatie hebben een referentie naar het ServiceBrokerInterface. Beiden hebben ook een using statement m.b.t. het ServiceBrokerInterface. Als we beide applicaties compileren en de client applicatie testen zien we hieronder het resultaat:

Fig. 3: Client applicatie

Conclusie

We hebben een introductie gehad van SQL Server Service Broker. Met deze introductie hebben we nu meer inzicht in de terminologie en in het objectmodel dat bij Service Broker hoort. Ook hebben we meer inzicht in de verschillen tussen Service Broker en zijn concurrenten en de sterke punten van Service Broker. De voorbeelden hebben ons meer inzicht gegeven hoe we met Service Broker kunnen programmeren, maar we hebben echter maar een klein deel van Service Broker behandeld. Onderwerpen die we niet behandeld hebben, zijn bijvoorbeeld security en poison message afhandeling.

Het grote nadeel van Service Broker is dat Service Broker alleen maar in een SQL Server 2005 omgeving werkt, dit in tegenstelling tot de directe concurrenten van Service Broker. Toch heeft SQL Server Service Broker potentie, zeker als je kijkt naar de voordelen zoals activation en het gegeven dat Service Broker ervoor zorgt dat berichten gegarandeerd precies éénmaal worden afgeleverd in de juiste volgorde.