Enterprise Messaging System (EMS)
This is a messaging system based on enterprise wide message specification. By defining message spec the system can use a loosely coupled architecture to inter connect different system, clients and servers.
Enterprise Service Bus (ESB)
ESB is a set of functions which enhances service oriented architecture(SOA). Its build on top enterprise messaging system (EMS) for example a message queue such as IBM MQSeries.
Sql Server Service Broker
Service broker is a set of messaging functionality build in to the SQL Server 2005 to enable asynchronous communication and programming.
Requirement
Want to be able to send and receive messages from SQL Server to ESB/EMS. Be able to do processing with the incoming messages in reliable way.
Solutions
There are number of way to do this -:
- write stored procedure which sends and receive the messages from SQL Server using some kind of vendor specific api. This could be using web service, REST interface, manged or extended stored procedures
- integrate service broker with ESB or EMS either with custom solution or commercial product.
There 100's of vendor for ESB and EMS. But in most cases the commonly used or supported standard is JMS (Java Message Service). That means the safe way to integrate is to use JMS standard so we can be vendor neutral.
Solution 1 - Using the API
Lets look at the first option in which we will try and use the client api to access the JMS service provided by Apache AciveMQ. Please follow the steps to set up the environment.
1) setup java 1.6
2) setup ActiveMQ 5.2.0
Once we have setup this up we should be able to start the server and browse the queue by using the Active MQ Admin.
ActiveQ supports REST type interface to its queue. But its interface is specific to ActiveQ. We will use a stored procedure to make REST calls to JMS provider.
For this we will use sql script available from sqlservercentral.com. We use the script to in database and executed some simple tests to make sure script is working. This might require some permission settings in sql database.
This script contains a stored procedure called usp_httppost. The following line of sql shows the call to this stored procedure.
This script contains a stored procedure called usp_httppost. The following line of sql shows the call to this stored procedure.
exec dbo.usp_httppost
'http://localhost:8161/demo/message/TestQ?type=queue',
'Hello MQ'
The parameters passed are -:
- URL of REST service, here the TestQ is the name of the queue.
- the message we wanted to send
This call will add the message to the TestQ specified in the URL. And the message can be seen from the ActiveMQ Admin.
To receive we need a new procedure which need to be able to do HTTP GET, so we slightly modify that http post script to do a HTTP get. We do the following change
- change the method signature by removing the @post parameter and changing the name to usp_httpget
- change the "POST" to a "GET" in the code
- change the "@Doing = 'Send("' + @post + '")'" to @Doing = 'Send()'
If we do this correctly then we will have a new stored procedure to use. The following SQL code shows the call to do http GET.
exec dbo.usp_httpget
'http://localhost:8161demo/message/TestQ?timeout=1000&type=queue'
The above code just receives messages once, if we want to receive messages contentiously so we can process them like proper message consumer, we have to write a SQL Server agent job. Which will have some code like this, which need to run all the time.
WHILE(1=1)
BEGIN
'http://localhost:8161demo/message/TestQ?timeout=1000&type=queue'
-- process the messages here.
END
But there are number of problems here.
- usp_httppost - creates COM objects from sql server some times this is not allowed or recommended.
- agent procedures and while loops - This going to waste lot of valuable database servers cpu time doing the polling.
- error handling will be problem with this since if the processing of the message has failed you will not be able to rollback since http based REST interface is stateless. There is no support for transaction processing
Solution 2 - Extended Stored Procedure
Another option we have is to create extended stored procedure using C and integrate it with C or C++ API of the messaging service provider. Here also we will have the problem of continuously polling for response from EMS or ESB. Since extended stored procedures are dll and have limitation in use of threads.
Solution 3 - Custom Solution
Custom solution with .net/ java service or demon which transforms the messages between SQL Server and JMS. Its not hard to write such custom solution to the requirement. But have to consider transaction processing and multi threading issues as well as cost and time to develop and maintain such application.
Solution 4 - Service Broker 2 JMS Bridge
Another solution to consider is to use of commercial bridge called service broker to jms bridge (sb2jms bridge) from Jawise. sb2jms bridge is build to do this sort of task. It provides seamless integration between service broker and JMS. The documentation for sb2jms bridge is very easy to understand and its quick to setup.
Another solution to consider is to use of commercial bridge called service broker to jms bridge (sb2jms bridge) from Jawise. sb2jms bridge is build to do this sort of task. It provides seamless integration between service broker and JMS. The documentation for sb2jms bridge is very easy to understand and its quick to setup.
Once sb2jms bridge is setup we use TSQL commands BEGIN DIALOG and SEND commands to send our xml messages to Service Broker, as show here.
DECLARE @msg varchar(MAX)
DECLARE @dh uniqueidentifier;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE SQLServerService
TO SERVICE 'ESBService'
ON CONTRACT ESBContract
WITH encryption = off
SET @msg= '.... ';
SEND ON CONVERSATION @dh MESSAGE TYPE XmlMessage (@msg);
These messages get exchanged to EMS/ ESB by sb2jms bridge. When messages are delivered by ESB/EMS , that messages get exchanged by sb2jms bridge and its placed in to service broker queue. Then a stored procedure can be configured in SQL Server to get activated when messages arrives in the service broker queue. Or TSQL command RECEIVE can be used to receive the messages from the queues and processed in a way required.
DECLARE @msg varchar(MAX)
DECLARE @dh uniqueidentifier;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE SQLServerService
TO SERVICE 'ESBService'
ON CONTRACT ESBContract
WITH encryption = off
SET @msg= '
SEND ON CONVERSATION @dh MESSAGE TYPE XmlMessage (@msg);
These messages get exchanged to EMS/ ESB by sb2jms bridge. When messages are delivered by ESB/EMS , that messages get exchanged by sb2jms bridge and its placed in to service broker queue. Then a stored procedure can be configured in SQL Server to get activated when messages arrives in the service broker queue. Or TSQL command RECEIVE can be used to receive the messages from the queues and processed in a way required.
Advantage here are
- messaging is transactional
- supports conversation type messages supported by Servicer Broker
- resilient with build in monitoring
In the next post will be looking at a possible use of this product for various user cases.
