Introduction:

As a part of my SQLServer2005 learning curve, the focus for now is Service Broker. I first heard about SQL Server Service Broker during introduction sessions for Yukon.(codename for what later has been named SQL Server 2005). Many developers were looking forward on the integration of CLR in SQL Server, meaning they wouldn't have to learn and use SQL. We all know by now that's a myth. The second thing developers liked was message queuing also had been integrated. Let's just say they were blinded by the light. But indeed, it is message queuing. But it isn't the MSMQ they have known.

The reason why I was considering service broker was that I introduced a system of raising SQL Server alerts to launch SQLAgent jobs. (help to tighten use of cmdshell or sp_start_job). Maybe I could ship those jobs of to another (existing) application server, in stead of the actual db-server, so the dbserver could reclaim the full dedicated server's capabilities.

So what is Service Broker?

Simply said: "SQL Server to SQL Server message queuing"

How hard can it be?

How to use this article

Let me start with mentioning I use SQL2005 Std (and EE) with SP1.

With this article, you'll find the solutions I used with SQL Server Management Studio. Download them and keep them at hand, you'll find a solution per subject mentioned later on in this adventure.

Each solution contains a sequence of scripts per phase and per broker-party. You'll notice that script-sequence 004, the execution, doesn't change sequence number. This is on purpose so everyone can refer to it without being confused over a sequence number. Also, this way, you can experiment with the kind of error messages SQL Server raises, when and where they are raised.

Building the whole sequence per party is, in my opinion, very welcome when finding scripts regarding new stuff. So I'd like you to run the scripts on a test server, one step a time, because the scripts may result in messages for you to move or copy files from one server to another.

Just keep in mind always to start with the cleanup steps, because each solution is supposed to stand by itself. Take good care to connect to the correct instance when you test the multi-instance solutions.

When you get to use a "routing" script (sequence 007) check the create route statement so it points to your correct test server. If you're using firewalls, you may need to open the ports you've picked when creating the endpoints (listener_port).

I hope you'll enjoy my adventure and get a good reference in the scripts I've provided.

First things I did for service broker exploration:

• Search the forums to find out if something is too buggy to start with in Service Broker. Like always, Google or MSN Search are your friends.
• Search the forums for some more examples. Since I went to the SQLPass2006 convention in Seattle, finding an example has been the easy part.

  (session AD405 "Message in a bottle")

• Read BOL (yes, I do) in a diagonal fashion.
• Ouch! ...XML...well, not really needed, but it makes life so much easier. So have a glimpse at it!

I wanted to drive the car, I didn't want to become a car technician. Casual Ferrari-drivers just drive the car and especially don't touch the nuts and bolts under the hood, right? That was my aim!

Phase one: a simple LOCAL service broker scenario.

This is a piece of cake, because most examples you'll find are implemented in the local SQL Server instance. First of all the elementary concepts need to be known. Also elementary, but you have to know some terminology, what's their function and why they are used.

• How do we call the participants
• message types
• Contracts
• Queues
• Services
• BEGIN DIALOG CONVERSATION
• Send message
• Receive message
• End conversation
• ...and find out that there is no easy GUI-setup path for service broker!

I tested my first SSB application with sender and receiver in a single database. The example I used was the one provided by Bob Beauchemin in his SODA presentation. (scripts not included with my solutions but you can find them at http://www.sqlskills.com/resources/conferences/200510SODA.zip )

Copy/Paste of the example just did the trick. It was also nice to see that you don't need to stop / start SQL Server to enable you first SSB-service. (Unlike with SQLAgent alerts for sql7.0 and sql2000)

Now it was worth to investigate more since I had seen the light.

At that time, surfing the web, I stumbled onto Klaus Aschenbrenner who asked for a SSB-newbie to co-read the book he was writing about SSB. (http://www.csharp.at/blog/) I contacted him and he did send me his first couple of chapters. This was actually the first time I co-read chapters of a book, and I enjoyed doing the co-reading and send him my comments. I learned a bunch of things that I normally wouldn't even consider digging into when throwing a first glimpse to a product, because it also handles nuts and bolts.

There are also a couple of nice articles on the web regarding "when to use the various asynchronous technologies". It makes sense to have some selection criteria about which technology to use, so have a look.

Phase two: sender and receiver in separate databases.

Secondly I tested a SSB application with sender and receiver in separate databases. For this example I started with a scenario I found with a presentation I attended at Flexcom-Azlan at the end of 2005. http://www.azlan.be. My thanks to them for allowing me to elaborate on their example.

This scenario has a concept of parties called Airline and TravelOffice. Because these names generate typical scenarios with most people, regarding "asynchronous processing", I thought it would make life easy to elaborate on that.

Basic scenario: An airline-company provides an SSB-service "TravelOfficeBooking" composed of an "OpenBooking", "SeatBooking", "CloseBooking" and a "BookingConfirmation" method.

You can find this test in my solution in folder 01_1_ALZDBA_ServiceBrokerSequence_Local. When I run the scripts from the solution, I just run them in sequence, actually building the situation one step at a time, each time the same step for each database. Working this way clears out the parallels and the differences at each side of the service broker services.

The most important thing I had to do is to alter the database that contains the initiator because it has to have the "trustworthy ON" property set for local usage.

So within the same instance of SQL Server, things still work fine by default, provided you performed the previous alter database.

Phase three: sender and receiver in separate instances.

My third test was now to detach the initiating database and move it to a second instance of SQL Server. I installed that second instance on the same physical box. (Or should I say operating system, because nowadays virtualisation is hot) For the record, all my SQL Server instances are being served using a windows domain account and have builtin\administrators revoked.

This time there is no need for the database to be trustworthy because that setting is only used within the SQL Server instance. Guess what. It didn't work anymore!

This urged the need for DMV's and queries to find out what's going on and where things got stuck.

Btw: I published my Troubleshooting Service Broker (SSB) script at www.SQLServercentral.com

Why didn't it work?

Transport security.

You can find this test in my solution in folder 02_1_ALZDBA_ServiceBrokerSequence_Transport. If you go outside of your SQL Server instance, you need to setup transport security and routing. This thing is secured by default, so time needed to be spent to check out the options.

You might imagine transport security to be the choice of your package-delivery-server like DHL, UPS, ABX,.... Without dialog security you would be giving them your love letters in an open envelope or in transparent foil. With dialog security on the other hand, you'd hand your package-delivery-server an envelope or a vault containing your encrypted message.

My choice was to always use SSL-certificates when setting up transport security. Don't be frightened by the word SSL, you get it from SQL Server itself! Why SSL-certificates?

Because with SQL Server 2005 you can create your own SSL certificates, so you don't have to hassle outside of your beloved and known software. I won't go into strategies or politics regarding this security-choice. That's something you'll have to determine yourself, but it makes it easy for me during this experiment.

Like the word says, Transport security is all about transport. There is only transport when you go from one SQL Server instance to another.

Because we have a standard for composing a sa-password, I thought it might be opportune to also use a standard to compose the password used for the master database master key. This key is then used to generate the certificate to be exchanged between the instances for transport security. With SQL Server 2005 this part is handled in the master databases of the involved instances.

It is mandatory to give SEND authority on the service to PUBLIC if you do not implement dialog-security!!

You can only have one Service Broker endpoint per SQL Server instance, so before you rollout an SSB application, play with it so you learn what things to keep in mind and which decisions need to be taken.

Since you only have one Service Broker endpoint, and the transport security concern inter-instance communication through the endpoint, you need to tell the endpoint it has to use your certificate. In my experiment's case resulting in:

CREATE ENDPOINT BrokerService
       STATE = STARTED
       AS TCP (LISTENER_PORT=55552)
       FOR SERVICE_BROKER (
AUTHENTICATION= CERTIFICATE [AirlineServiceBROKERCommunicationCertificate]
, ENCRYPTION=SUPPORTED
)

I also decided to create a login per connecting instance because this gives me an excellent opportunity for auditing.

So after creating the SSB-endpoint that uses the communication-certificate, certificates need to be exchanged. Need it to be mentioned "what's in a name" ...

There was still a route to be created at each side of the transport. This route resides at database level, meaning it is no other databases business to know who you route your messages to. Because I was only setting up transport security at this time, I needed to grant send authority to public for my local SSB-service in the userdatabases.

I need to emphasize that I did look over the impact of this last line a number of times because as a DBA, I never grant anything to public. So never say never ...!

GRANT SEND ON SERVICE::[AirlineBookingService] TO PUBLIC ;-- airline db
GRANT SEND ON SERVICE::[TravelOfficeBookingService] TO PUBLIC --TtravelOffice db

Things started to work again.

Dialog security: You can find this test in my solution in folder 02_2_ALZDBA_ServiceBrokerSequence_AnonymousDialog

So how about dialog security? As you can imagine by now, as long as your message stays within the same instance of SQL Server, it is considered to be secure. Dialog security is about encrypting the content of your message. By itself this has nothing to do with transport, but you'll need transport security to get to your counterpart instance.

So dialog security actually is an additional security measure which lives its own life, but needs transport. Like when you send an encrypted letter using a package-delivery-server. A dialog resides in the userdatabases of the instances, so dialog security is a user databases procedure.

Just like with the setup of transport security, my choice was to always use SSL-certificates when setting up dialog security. Just keep in mind there are other options to be explored. So you also need a user database master key to encrypt the SSL-certificate you create to support dialog security. Then also telling the service to encrypt dialogs using a specific certificate:

CREATE USER [TravelOfficeServiceUser] WITHOUT LOGIN;
-- This defers from Transport security! This way the dialog gets encrypted 
-- by the certificate bound to the service USER !
Alter AUTHORIZATION ON SERVICE::[//wonderland.world/Broker/TravelOfficeBookingService] 
       to [TravelOfficeServiceUser] ;
go
CREATE CERTIFICATE [TravelOfficeDialogCertificate]
        AUTHORIZATION [TravelOfficeServiceUser]
        WITH SUBJECT = 'Certificate for the TravelofficeService service',
             START_DATE ='11/30/2006',
 EXPIRY_DATE = '12/31/2050';
go

Exchanging these dialog certificates and implementing in both userdatabases and off course telling the sprocs to begin the dialog using encryption and off we go ... NOT. (Kind of slang my teenage daughter uses these days)

CREATE BEGIN DIALOG @dialog
     FROM SERVICE ..
     TO SERVICE 
     ON CONTRACT 
     WITH ENCRYPTION = ON;

One little bit I overlooked was that you also have to create a remote service binding at initiator side when using dialog security!

CREATE REMOTE SERVICE BINDING [AirlineServiceBinding]
    TO SERVICE '//wonderland.world/Broker/AirlineBookingService'
    WITH USER = [ProxyAirlineServiceUser] , ANONYMOUS = ON  ;

With my scripts, you'll also find a solution using full dialog security (anonymous=off). You can find this test in my solution in folder 02_3_ALZDBA_ServiceBrokerSequence_FullDialog. In that case there are dialog certificates to be exchanged at both sides of the conversation.

Phase four: Forwarding.

Ok, so now I had tested a simple scenario with two participators. Hey, I have heard you could move your db to another server without informing your counterpart. So I focused on forwarding.

Once again I started off with the example where only transport security was implemented. Easy does it, so off we go.

The scenario is simple: all you do is detach the database, move it to another SQLServer2005, attach it like you would when moving any user databases (users & stuff). All you have to do is to setup transport security from the original server to the new server, setup the forwarding and off course also altering the existing route to your forwarding server.

Setting up forwarding is actually quit easy, because it is handled in the original master and msdb. You have to tell the endpoint for service broker (master db) that message forwarding is enabled. In msdb you provide the forwarding details by providing the routes (forward and backward).

You can find this test in my solution in folder 03_1_ALZDBA_ServiceBrokerSequence_Transport_Forwarding

Guess what..... It worked!

It got a bit tricky when I tried it with the scenario where I implemented dialog security. Well it was actually the same as with the example with only the transport security implemented, but this time it no longer worked. You can find these tests in my solution in folders 03_2_ALZDBA_ServiceBrokerSequence_AnonymousDialog_Forwarding and 03_3_ALZDBA_ServiceBrokerSequence_FullDialog_Forwarding

And I had to actually use the SQL Server-errorlog to figure out why it didn't work. It stated it couldn't decrypt the message because of lack of a key to open the database. Which certificate or key? The database's master key!

I had to provide the new server the knowledge how to open the old database's master key to decrypt the dialog. Because I encrypted the database originally using a password, I used that same password to tell the new server how to open the database master key.

EXEC sp_control_dbmasterkey_password @db_name = N'Airline', 
@password = N'P@ssword', @action = N'add';

Phase five: Multiple initiators.

Did you notice I didn't have to change the service broker identifier during my entire test? I didn't had to perform alter database set new_broker with rollback immediate because I was always moving my database. So I didn't clone it, but always nicely removed it from its original location and brought it back up at a new location.

Now I wanted to have two travel offices using my airliner. I started off the easy way and copied the scripts of the travel office db and executed them on a new database. So basically only the server connection changed for the copied scripts to the new SQL instance.

At travel office side, it are two exact implementations. You can find this test in my solution in folder 04_1_ALZDBA_ServiceBrokerSequence_Transport_MultipleClients

At airliner side, I had to figure out how service broker would know where to send a message to. Trial and error was the way to go. I just implemented the routes like before, only giving them a unique name.

Create ROUTE TravelOffice
    WITH
    SERVICE_NAME = '//wonderland.world/Broker/TravelOfficeBookingService',
    ADDRESS = 'TCP://myserver1:55551' ;
go
CREATE ROUTE TravelOffice2
    WITH
    SERVICE_NAME = '//wonderland.world/Broker/TravelOfficeBookingService',
    ADDRESS = 'TCP://myserver2:55551' ;
go

Of course, I started off sending a first message from the first travel office. The one that worked before. And again, it worked.

Then I did send a message form the new travel office. And things got stuck. I seemed like the messages were just sitting there, waiting to be processed.

I started off from zero once more, but now I started sending the first message from the second travel office, and guess what it worked ?!?!?!?!

At least until I send a message from the first travel office, after that the second travel office didn't receive any answers anymore. Pondering how to uniquify the routes, my attention got toward the broker_instance parameter of the "create route"-statement.

Create ROUTE TravelOffice
    WITH
    SERVICE_NAME = '//wonderland.world/Broker/TravelOfficeBookingService',
    /* TO BE SUPPLIED */    BROKER_INSTANCE = '056BD77E-8117-41AF-B094-73D74744D6FF' ,
    ADDRESS = 'TCP://myserver1:55551' ;
go
CREATE ROUTE TravelOffice2
    WITH
    SERVICE_NAME = '//wonderland.world/Broker/TravelOfficeBookingService',
    /* TO BE SUPPLIED */    BROKER_INSTANCE = 'AD7DA8DD-E237-4924-864F-7868D450C44C' ,
    ADDRESS = 'TCP://myserver2:55551' ;
go

And, yes, now it works fine. And in my opinion even faster than before!

My conclusion

I learned a lot and wanted to share, just to give everyone the chance to experiment the copy/paste way with this powerful out of the box solution of our beloved SQL Server 2005. Combined with the obvious advantages for messages using XML, SSB can be very powerful.

The things that need to be figured out before implementing it into production:

1. Elaborate on security.
2. Naming conventions for type, queues, services,...
3. Guidelines for transport security
4. Guidelines for dialog security
5. Is using SSL certificates the way to go?
6. Selection criteria for SSB to be used.
7. XML. We have to learn it anyway because it makes our performance related life more meaningful figuring out bottlenecks, execution plans,..
8. How to handle poison messages. Detection, notification and solution.

There are user efforts to build a GUI for SSB administration: e.g. SSB Admin - a GUI for SQL Server 2005 Service Broker

For more info regarding the nuts and bolds of service broker I gladly redirect to Klaus Aschenbrenner's book, which is still in the publishing process at this time.

Other resources:

• SQL Server 2005 Books Online
• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp
• http://www.sqlservicebroker.com
• http://www.sqlskills.com/blogs/bobb/ (Bob Beauchemin)
• http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&slcid=us