SQL Server Service Broker – Part 2

In part one we looked at some general Service Broker concepts, so now its time to get our hands dirty and start writing some useful TSQL.

I have done a few presentations on Service Broker recently and often people start asking questions based on a replication metaphor – as in a publisher / subscriber model. This is the wrong way to think about Service Broker and can often lead to incorrect assumptions leading to errors.

Service Broker uses a conversation metaphor and we’ll see various keywords associated with metaphor throughout our implementation.

So, lets get stated with some code.

There are four main objects that we need to create.

• Message Types.
• Contracts.
• Queues
• Services

And once we understand how these four items fit together we are well on our way to having a firm understanding of Service Broker.

But first, lets see what we need to do to activate Service Broker.

And the answer to that is pretty much nothing as when even we create a new database then Service Broker is ready to use by default.

We can however, blow away the existing Service Broker instance and set up a new one with one simply command.

So, lets

• Create a new database and observe that Service Broker is ready for use
• Replace the existing Service Broker instance with a new one
• Observer that the new Service Broker instance has replace the old one

--Start 1
USE [tempdb]
GO

IF EXISTS (SELECT * FROM [sys].[databases] [dbs] WHERE [dbs].[name] = 'SB_Test')
BEGIN
  ALTER DATABASE [SB_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [SB_Test];
END;
GO

CREATE DATABASE [SB_Test];
GO

ALTER AUTHORIZATION ON DATABASE::[SB_Test] TO [SA];
ALTER DATABASE [SB_Test] SET RECOVERY SIMPLE;

--End 1
--------------------------------------------------------------------------------------
--Start 2
/*
Create a new broker - just be be on the safe side
*/
USE [tempdb];
GO
SELECT 
  dbs.service_broker_guid , 
  dbs.is_broker_enabled,
  dbs.user_access_desc,
  dbs.is_honor_broker_priority_on,
  dbs.service_broker_guid,
  *  
FROM sys.databases dbs WHERE dbs.[name] = 'SB_Test';
GO
/*
Creating a new database has SB enabled by default (takes setting from MODEL)
*/
--Need an exclusive lock to alter SB
USE [tempdb];
GO
/*
There are two methods that can be used to create a new Service Broker instance.
This is a destructive command.
Best used in development and testing exercise EXTYREME caution in production environments.
*/
--Method 1
ALTER DATABASE [SB_Test] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

--Method 2
ALTER DATABASE [SB_Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [SB_Test] SET NEW_BROKER; 
ALTER DATABASE [SB_Test] SET MULTI_USER;
----------------------
USE [SB_Test];
GO
SELECT 
  dbs.service_broker_guid , 
  dbs.is_broker_enabled,
  dbs.user_access_desc,
  dbs.is_honor_broker_priority_on,
  dbs.service_broker_guid,
  * 
FROM sys.databases dbs WHERE dbs.[name] = 'SB_Test';
GO
--End 2

Observe that when running the second block of code that there are two SELECT statements from sys.databases and these are separated by two methods of creating a new Service Broker instance. Note that the service_broker_guid is different. This guid unique identifies the Service Broker instance and we’ll take a closer look at it later in the series.

So, the first thing to need is to define some Message Types

CREATE MESSAGE TYPE [http://www.martincatherall.com.au/MessageTypes/MessageOut]
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE [http://www.martincatherall.com.au/MessageTypes/MessageIn]
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML;

When we send messages, we say the Service Broker instance that created the first message is the Initiator and the Service Broker instance that the message is sent to is the Target. Things may get a little more complicated when there are multiple instances involved but for the purpose of this article we will just have an Initiator and a Target.

Next, we are going to need a contract. This simply defines which Service Broker instance (Initiator or Target) can send which message type.

CREATE CONTRACT [http://www.martincatherall.com.au/Contracts/ContractMain]
      ([http://www.martincatherall.com.au/MessageTypes/MessageOut]
       SENT BY INITIATOR,
       [http://www.martincatherall.com.au/MessageTypes/MessageIn]
       SENT BY TARGET
      );

Contracts are immutable, so if a change needs to be made the contract must to dropped and recreated with the new message types in it. Doing this can be fairly involved if done at a later stage as the contract will be bound to other objects, so its best to think this through carefully.

In this article, we’ll simply be setting up service Broker in a single database, but is the messages were being sent between different database then both the Message Types and the Contract would need defining in both the Initiator and Target.

Next, the Queues and Services need to be defined, we’ll have exactly on queue each for both the Initiator and Target and exactly one Service each for both the Initiator and Target.

CREATE QUEUE InitiatorQueue;

CREATE QUEUE TargetQueue;

Now let’s create the Services.

CREATE SERVICE
       [InitiatorService]
       ON QUEUE InitiatorQueue;

CREATE SERVICE
       [TargetService]
       ON QUEUE TargetQueue
       ([http://www.martincatherall.com.au/Contracts/ContractMain]);

And that’s it – we now have or Service Broker infrastructure in place and we can start looking at how we go about starting a Conversation in Part III – Stay tuned….