SQL Server 2005 makes it easy to set up asynchronous
distribute applications by integrating a queueing mechanism into the database. It
combines the ideas of MSMQ with the stability, reliability and functionality of
a relational database server. In this code walkthrough we investigate how to
set up a simple application on this service broker. Gradualy we introduce
additional aspects of the service broker, complicating the initial simple
setup.
The application we are creating is one where employees can
request vacation days. Before a vacation is granted, certain rules need to be
checked (minimal staff availability, important deadlines, enough vacation days
left, approval by supervisor,…). Because of this, a person submitting a request
cannot do so synchronously: she will submit a request, and then wait minutes,
hours or even days before the request is accepted or rejected.
Setting up such an application is far from trivial. A first
problem is that the vacation request must travel reliable from the client to
the server: the client needs to be sure the server gets the request once, and
exactly once. Also, we must be able to guarantee that while sending the
message, nobody can read the message on the network: we don’t want that the
messages your docter sends to the hospital to update your medical record are
intercepted by your life insurance company. Another problem is that must make
sure that if the server (or client) crashes during the processing of your
request, your vacation request doesn’t get lost. More advanced problems are
caused by the problem that the ‘dialog’ in which you request your vacation can
last a few days. What if in the mean time the server handling these requests
needs to move to a different machine (“hey guys, the new server arrived”)? You
don’t want to loose your vacation because of that.
Other problems have to do with processing requests on the
server. We want to process requests as soon as possible. So in May we may need
more processing power than in September. But on the other hand, this server is
probably doing other things --- and sometimes more important things --- than
processing vacation requests. So we must be able to close down the vacation
request processing at times we need the resources for other tasks, and
reactivate this processing at more quiet times.
In order to set up our vacation application, we must create
a service where we can post vacation requests. From there, the requests will be
forwarded to the service which processes the requests. This latter service
will, upon completion of the processing of the request, send the answer back to
the requesting service. In real life, these two services will run on two
different machines: the client machine contains the requesting service,
the server contains the processing service. To save ourself the trouble of
setting up the communication between two SQL Server instances, we will
initially set up this application on one single server.
In order to set up a service, you need to have queues where
messages can be stored (in our case, one on each server), and a message type,
which describes the type of message that can be sent or received by a service.
Finally, we need contracts which specify which messages can be sent in which
direction.
Client setup
Let’s start by creating the necessary components for the
client computer. Notice that setting up the infrastructure needs only to be
done once; you can compare it with the creation of tables in regular databases.
In this exercise, we will first create a new database for these client objects;
the remainder of the statements in this section need to run in this newly
created database.
CREATE DATABASE [BrokerClient]
Since we initially want to ignore the security setup, we label our database as trustworthy, in that way we allow impersonation on this database.
ALTER DATABASE BrokerClient SET TRUSTWORTHY ON
We first start with the message types. These are database
objects new to SQL Server 2005. To create them, we simply use the create
statement. At this time, there is no graphical tool included in SQL server to
setup any of the broker related objects.
CREATE MESSAGE TYPE
[www.u2u.be/demo/sql05/VacationRequest]
CREATE MESSAGE TYPE
[www.u2u.be/demo/sql05/VacationReply]
Question: why do we use such complex names for the
message type objects?
Next, we create a contract, which specifies which messages
can be sent by a dialog initiator, which messages can be sent by a dialog
destination, and which messages can be sent by both parties. Notice that a
contract only specifies the direction of a message, not the order in which
messages are sent. Contracts are once more database objects, so we create them
with a create statement:
CREATE CONTRACT [www.u2u.be/demo/sql05/VacationContract]
(
[www.u2u.be/demo/sql05/VacationRequest] SENT BY
Initiator,
[www.u2u.be/demo/sql05/VacationReply] SENT BY
Target
)
Before we can define the service, we must first create the
physical storage where messages for that dialog are going to be stored. These
are queues, which in reality are ‘just’ hidden tables with additional behaviour
on them. This is clear when you look at the syntax of the create queue
statement: just like the create table statement you can specify on which
filegroup this queue needs to be stored. However, totally different from
tables, when creating a queue, we cannot specify the column names and types:
these are predefined. Also notice that a queue is not bound to any message
types or contracts. Let’s go ahead and create the queue to store the outgoing
requests and the incoming responses:
CREATE QUEUE [VacationQueue]
Question: why don’t we use a complex name for this queue?
With all the previous preparations, we are now ready to set
up the service on our client. A service is just the combination of a queue and
one or more contracts:
CREATE SERVICE
[www.u2u.be/demo/sql05/VacationRequestService]
ON QUEUE [VacationQueue]
(
[www.u2u.be/demo/sql05/VacationContract]
)
This concludes the setup for the client of our application.
To see if all the necessary objects are created, unfold the relevant folders in
the object explorer in the management studio:
Server setup
On the server, we need to know the same message types and
contract as on the client: the server needs to accept a message sent by the
client, and vice versa do the messages sent by the server be received by the
client. Because of that, some of the objects created on the client also need to
be created on the server, namely message types and contracts. We first create
the database on the server, and then created the necessary objects:
CREATE DATABASE BrokerServer
ALTER DATABASE BrokerServer SET TRUSTWORTHY ON
-- Broker Server setup
USE BrokerServer
-- create message types for sending and
receiving
CREATE MESSAGE TYPE
[www.u2u.be/demo/sql05/VacationRequest]
CREATE MESSAGE TYPE
[www.u2u.be/demo/sql05/VacationReply]
-- create contract: only initiators may request
vacation, not the server :-)
CREATE CONTRACT
[www.u2u.be/demo/sql05/VacationContract]
(
[www.u2u.be/demo/sql05/VacationRequest] SENT BY
Initiator,
[www.u2u.be/demo/sql05/VacationReply] SENT BY
Target
)
Of course we also need a queue to store messages at the
server. And our server also needs a service, which will be the receiving side
of a vacation request dialog. Because of that, we also have to create the
necessary queue and service at the server side:
-- create queue for incomming requests and
outgoing responses
CREATE QUEUE [VacationServerQueue]
-- combine queue and contract(s) in a service,
the cornerstone of a conversation
CREATE SERVICE [www.u2u.be/demo/sql05/VacationResponseService]
ON QUEUE [VacationServerQueue]
(
[www.u2u.be/demo/sql05/VacationContract]
)
Now the infrastructure is ready, and we can start to use the
services.
Let’s talk
Now it is time to get some action. If you want to exchange
messages you first have to start a dialog. As soon as the dialog is started,
both parties can send messages over this dialog. It is not necessary that after
every message from one side the other side send a message: one party in a
dialog can send as many messages as it wants, as long as they satisfy the
contract(s) specified on the service.
Before we can start sending messages, we have to setup a
conversation. At this time, dialogs are the only supported conversations. To
help us create a conversation and send a message, we create a stored procedure
at our client database, in which we specify that a dialog must be finished with
5 days (432000 seconds):
CREATE PROC dbo.RequestVacation(@message
varchar(2000))
AS
declare @handle uniqueidentifier
begin tran
begin dialog @handle
from service
[www.u2u.be/demo/sql05/VacationRequestService]
to service
'www.u2u.be/demo/sql05/VacationResponseService'
on contract
[www.u2u.be/demo/sql05/VacationContract]
with lifetime = 432000, encryption = off
;SEND ON CONVERSATION @handle
MESSAGE TYPE
[www.u2u.be/demo/sql05/VacationRequest]
(@message)
commit
GO
Question: in the BEGIN DIALOG statement, why is the from
service a sysname, and the to service a string?
We can startup such a dialog for instance from within a WinForms
application that runs on the client machine. For instance, you can create an
application as shown here, where people can enter there request and click
‘Send’. Of course in real life a less ‘basic’ GUI will be needed J.
The implementation of the Click handler on the Send button
is as follows:
VB.NET
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As New SqlConnection("Data
Source=.;Initial Catalog=BrokerClient;Integrated Security=True")
Dim cmd As SqlCommand =
con.CreateCommand()
cmd.CommandText =
"dbo.RequestVacation"
cmd.CommandType =
Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("message",
TextBox1.Text)
Try
con.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Your request
is submitted at the client database. Please check for a response in the next 5
days")
Catch ex As Exception
MessageBox.Show("Sorry,
processing of your request failed: " + ex.Message)
Finally
If con.State =
Data.ConnectionState.Open Then
con.Close()
End If
End Try
End Sub
End Class
If we run this small application while both the BrokerClient
and the BrokerServer database are available, we see that the message
immediately travels to the BrokerServer queue: check this queue by running this
statement in a query window at the BrokerServer (which clearly shows that a
queue is also a table):
SELECT * FROM dbo.VacationServerQueue
This returns one record, because there is one message in the
queue. Notice that this select statement doesn’t remove the message from the
queue; it is more like a ‘peek’ statement, but a peek statement which allows
you to see all entries, not just the one on top of the queue.
Exercise: convince yourself that the message is only at
the BrokerServer database and no longer on the BrokerClient database by
querying the queue at the latter database as well. Also check the
sys.transmission_queue, which stores messages ‘on their way’
We now have a message delivered to the BrokerServer
database, but of course we will have to process it over there. In real life,
this processing will probably consist of starting new dialogs with subsystems
(staff schedule checker, staff administration, supervisor approvement), and
processing the replies from those systems. In this first Service Broker setup,
we keep things simple: every vacation request is granted, and is granted
immediately.
We saw that reading messages from a queue was just a select.
If we want to read and remove the message, it requires the RECEIVE statement,
which is comparable to the select statement in its syntax. Finally, we also have
to end the (server side of the) conversation with an end conversation
statement. For easy use, we throw all these statements in one large stored
procedure, which needs to be created and run in the BrokerServer database:
CREATE PROC ProcessVacationRequest
AS
DECLARE @handle uniqueidentifier
DECLARE @message varchar(2000)
DECLARE @type sysname
BEGIN TRAN
;RECEIVE TOP(1) @handle = conversation_handle
, @type = message_type_name
, @message =
convert(varchar(2000),message_body)
FROM dbo.VacationServerQueue
IF @type =
'www.u2u.be/demo/sql05/VacationRequest'
BEGIN
print 'They want some vacation because ' +
convert(varchar(2000),@message)
-- we reply
;SEND ON CONVERSATION @handle
MESSAGE TYPE [www.u2u.be/demo/sql05/VacationReply]
('Go ahead, enjoy some vacation')
-- update some internal, regular tables
-- ...
-- and end our part of the conversation
END CONVERSATION @handle
END
-- what if the other side already asked to end
the conversation
-- or raised an error? Then they don't want to
go on vacation,
-- so we just end the conversation
ELSE IF @type =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' or
@type =
'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
END CONVERSATION @handle
ELSE
-- oh ohw, we received even another type of
message, something
-- must be wrong (maybe conversation timed
out?), lets quit!
BEGIN
print 'we quit because we received an ' +
convert(varchar(2000),@type)
END CONVERSATION @handle
WITH ERROR = 33 DESCRIPTION = N'received wrong
type'
END
COMMIT
GO -- end proc
Go ahead and run this procedure. You will see that the
message is removed from the queue, and a new message is sent to the
BrokerClient queue.
Exercises
1. We
don’t live in an ideal world: mimic server or network failures by detaching the
BrokerServer database, and start a new dialog from the WinForm app. What
happens? Check both queues at the BrokerClient (Vacation queue as well as
sys.transmission_queue. Where does the message live? Check the
transmission_status column at the transmission queue!
Then reattach BrokerServer. Does this solve the problem? Check once more the
transmission_status column.
Restore the database in its original state by making it again trustworthy, and
by enabeling the broker functionality (chech the ALTER DATABASE statement in
BOL). What happens now with the messages?
2. We
do not want to run the stored procedure at the server side by hand. Investigate
the ALTER QUEUE statement in BOL and change the queue at the BrokerServer so
that it automatically starts up the stored procedure when a new message
arrives.
3. We
need to receive the answer at the client and close the conversation there as
well. Create a stored procedure for this, and test all this.