SQL Server Service Broker at a Glance
Introduction
"SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications." This is according to the Technet reference found here.
SQL Server Service broker was introduced in SQL Server 2005. After more than 7 years of its existence SQL Server Service broker remains a not very popular tools to SQL Server developers. Below are cases where service broker can be of great use as well as the step by step guide to implementing a sample solution.
Real Life Scenario
You are an IT professional in a department store industry named “My-mall”. “My-mall” have 100 point of sale(POS) terminals sitting on top of a SQL Server “sales” database. Sales database is owned by “sales Department”. Sales department's critical task is accommodating sales transactions from the customers which are transacting either by payment through credit card, cash, debit card or gift certificates . “My-mall” also have an “inventory” database owned by the “inventory department”. Inventory department's critical task is replenishing stocks in the store in a timely manner as well as maintaining the correct inventory of the product. My-mall also has an accounting database owned by the accounting department. Accounting department's critical task is to maintain the company's financial health.
It is possible to build an a single database solution to accommodate all the three department needs like this in a small scale enterprise but my-mall is a huge enterprise accommodating more than 100000 customers per day. There is also a strong division between the departments although they share the same data in a way.
Throughout the years My-Mall started expanding several branches to different site in the country.
Service Broker to the Rescue
Service broker is ideal for this scenario because it will allow the sales department to process sales without any delay. Amidst processing sales, the sales database can send a message to the inventory database and the accounting database that a sales transaction is being made. The inventory database can also accommodate inventory operation and can send a message back to the sales department if a new product becomes available in the store.
Why not use SSIS?
First of all there is no complex reporting requirement for this solution. There is also no need for complex transformation because this solution can actually be done in a single database on a small scale industry. Although SSIS can work with great accuracy, it is going to hold lock to so many resources in the server.
SSIS would be ideal for consolidating data at the end of the day from different site for executive reporting.
Why not Replication then?
Although transactional replication looks ideal for this scenario it is not. First, database needs to respond critically to business without timeout required. If replication goes wrong somewhere along the line, it can cause delay to the business. The second reason is that, there is a strong divide between departments and ownership of the data is a responsibility of each.
Other Areas Where Service Broker Can Improve on this Scenario
Service Broker can further scale-out the solution by allowing each machine to have its individual database on an extremely large enterprise. This solution will allow the business to respond more rapidly to customer demand.
Service Broker Step By Step
1. First Step is to Create a Database
Create a database by running a create database script.
CREATE DATABASE servicebroker1*
*
2. Know Where Service Broker is
By Inspecting the database in SQL Server management studio you will notice
that service broker can be found inside any user database as shown in the diagram below.
*Figure 1. SQL Server Service broker in SQl Server 2012
*
3. Enable Service broker in a Database
This can be done by setting the "Broker Enabled" property of the database ( as shown in Figure 1) to true or by running the script below.
*
ALTER DATABASE [servicebroker1] SET ENABLE_BROKER
*
4. Create a Message Type?
Next step is to create a message type. To create a message type. Type the following command
CREATE MESSAGE TYPE MyMessageTypes*
*A message type defines the name of a message and the validation that Service Broker performs on messages that have that name. Both sides of a conversation must define the same message types. For more syntax you can refere to books on line.
Figure 2. Creating message types and showing the message type properties
5. Create a Contract
After creating the message types you can now create a contract. A contract defines the message types that are used in a Service Broker conversation and also determines which side of the conversation can send messages of that type. Each conversation follows a contract. The initiating service specifies the contract for the conversation when the conversation starts. The target service specifies the contracts that the target service accepts conversations for. For more info on contracts please consult books-on-line.
Create a contract by running this script.
CREATE CONTRACT MyContract
(MyMessagetypes SENT BY INITIATOR)
Figure 3. Service Broker Contact
6. Create Sender and Receiver Queue
Queues store messages. When a message arrives for a service, Service Broker puts the message on the queue associated with the service
to create the queue you can type the following T-SQL codes. For more information You can refer to this link.
CREATE QUEUE Sender;
CREATE QUEUE Receiver;
*Figure 4. Sender and Receiver Queue
*
7. Create Sender and Receiver Services
A Service Broker service is a name for a specific task or set of tasks. Service Broker uses the name of the service to route messages, deliver messages to the correct queue within a database, and enforce the contract for a conversation. For more information you can consult this link.
CREATE SERVICE SenderService
ON QUEUE Sender (MyContract)
GO
CREATE SERVICE ReceiverService
ON QUEUE Receiver (MyContract)
GO
Figure 5. Service create for both sender and receiver
8. Send a Message to the Queue.
To send a message you need to begin dialog and do a send message on the conversation.
This procedure is case sensitive.
-- Begin Dialog using service on contract
DECLARE @handle uniqueidentifier
DECLARE @message nvarchar(128)
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE SenderService
TO SERVICE 'ReceiverService'
ON CONTRACT MyContract
WITH ENCRYPTION = OFF
-- Send messages on Dialog
SET @message = N'Very First Message';
SEND ON CONVERSATION @Handle
MESSAGE TYPE MyMessagetypes (@message)
SET @Message = N'Second Message';
SEND ON CONVERSATION @Handle
MESSAGE TYPE MyMessagetypes (@message)
SET @Message = N'Third Message';
SEND ON CONVERSATION @Handle
MESSAGE TYPE MyMessagetypes(@message)
*
*
*Figure 6. Sending a message to the conversation. Notice that after Running this there are 3 Messages receive in the receiver Queue.
*
9. View the Message Without Removing it from the Queue
To view the message without removing it from the queue you can select from the message body.
SELECT CONVERT(nvarchar(max),message_body) FROM [dbo].[Receiver]*
*
Figure 7. Viewing the message using select command
10. Receive the Message
To receive the Message issue the "Receive" Command
RECEIVE CONVERT(nvarchar(max), message_body) AS Message
FROM Receiver
*Figure 8. Receiving the message.
*
*Script 1. The completed Service Broker Sample scripts.
*
CREATE QUEUE Receiver
GO--Create Services
CREATE SERVICE SenderService
ON QUEUE Sender (MyContract)
GOCREATE SERVICE ReceiverService
ON QUEUE Receiver (MyContract)
GO-- Begin Dialog using service on contract
DECLARE @handle uniqueidentifier
DECLARE @message nvarchar(128)BEGIN DIALOG CONVERSATION @handle
FROM SERVICE SenderService
TO SERVICE 'ReceiverService'
ON CONTRACT MyContract
WITH ENCRYPTION = OFF-- Send messages on Dialog
SET @message = N'Very First Message';
SEND ON CONVERSATION @handle
MESSAGE TYPE MyMessagetypes (@message)SET @message = N'Second Message';
SEND ON CONVERSATION @handle
MESSAGE TYPE MyMessagetypes (@message)SET @message = N'Third Message';
SEND ON CONVERSATION @handle
MESSAGE TYPE MyMessagetypes(@message)--View Message
SELECT CONVERT(nvarchar(max),message_body) FROM [dbo].[Receiver]--Receive messages
RECEIVE CONVERT(nvarchar(max), message_body) AS Message
FROM Receiver