Take Away

Both Notification Services and the Service Broker are more about the programming constructs than administration. Other than the security implications and a few more Performance Monitor objects and counters, there is not a lot for the DBA to do in the system from day to day.

I thought it might be useful to work through a simple example of a Service Broker application because it is the one that is the most accessible from standard T-SQL statements. Creating your own application helps you to understand the process, and you can even use this simple program as a starting point for a more advanced application.

I will keep the comments down to what I think you should know along the way, because seeing the entire application process laid out is useful to understanding the process quickly. You should be familiar with the terms I explained in the chapter to follow along. If you are interested in extending this system, create the application on your test system just as I have here. Then review the T-SQL statements that make up the process to expand what the system can do.

Service Broker Example

In this section, I create a simple example of adding an employee to a database. The requirement is for a junior human resources worker to be able to add an entry into the company's employee system. Because her manager wants to review the entry prior to the employee receiving a permanent ID number, we have decided to create an application that takes her entry and places it in a "holding" table until it is reviewed.

On my test system, I created a database called ServiceBrokerExample that has one table called Employee. That table has one column called EmployeeInfo, with an xml column setting, because that is what my application expects. Here is the code for all that in case you want to try it on your system:

 Create the database
CREATE DATABASE ServiceBrokerExample
GO

 Create the table
USE [ServiceBrokerExample]
GO
CREATE TABLE [dbo].[Employee](
      [EmployeeInfo] [xml] NULL
) ON [PRIMARY]
GO

With that all set, I begin the process by creating a message type for the system. I instruct the type to check to make sure my data is in proper XML format, but in production you will also often reference a full XML schema document:

 Create the Message Type
CREATE MESSAGE TYPE
 [ServiceBroker/Example/Employee/AddEmployee]
 VALIDATION = WELL_FORMED_XML
GO

I then create a contract for the system, for each party to use. You can see that it uses the AddEmployee message type that I just created:

 Create the Contract
CREATE CONTRACT
 [ServiceBroker/Example/Employee/AddEmployeeContract]
 ([ServiceBroker/Example/Employee/AddEmployee]
 SENT BY INITIATOR
 )
GO

Here are my "postal workers" that read the data from the queue and insert it into the database. I am using an XML conversion function to move the data along and place it into the database. There are two stored procedures here: one to do the inserts, and the other to check and empty the queue. Do not let the complexity stop you; read through each section line by line to see what's happening here:

 Create the Insert SP
CREATE PROCEDURE [dbo].[AddEmployee]
      @MB xml
AS
INSERT INTO Employee(EmployeeInfo)
VALUES (@MB)
GO

And now the stored procedure that reads the queue:

 Create the Update SP
CREATE PROCEDURE [dbo].[ProcessEmployee]
AS
BEGIN
 BEGIN TRAN
  DECLARE @CH uniqueidentifier
  DECLARE @MB varbinary(max)
 dequeues the message
  WAITFOR
  (
  RECEIVE TOP(1) @CH = conversation_handle, @MB =
message_body
  FROM EmployeeQueue
  ),
  TIMEOUT 1500
  process the message

  EXECUTE ProcessEmployee @MB
  END CONVERSATION @CH
COMMIT TRAN
END

With the workers in place, I need to set up the "Post Office"the queue that will store all the data. When I create the queue, I assign the service program (in this case, my stored procedure) that is assigned to process it:

 Create the Queue
CREATE QUEUE EmployeeQueue
 WITH STATUS = ON,
 ACTIVATION
 (
 PROCEDURE_NAME = ProcessEmployee,
 MAX_QUEUE_READERS = 5,
 EXECUTE AS SELF
 )
GO

I am almost there. Now I create the service that responds to the requests from the conversations; I will also tie that to the contract I created earlier:

 Create the Service
CREATE SERVICE AddEmployeeService
 ON QUEUE [EmployeeQueue]
 ([ServiceBroker/Example/Employee/AddEmployeeContract])
GO

The system is now ready, and I can examine all the objects using the SQL Server Management Studio. With the server ready for Service Broker conversations, I can set up a full sample event. I am only sending a snippet of the code I would really use as the XML document, but this snippet makes the code easier to read:

 Begin the dialog
DECLARE @CH uniqueidentifier
DECLARE @EmployeeName XML
SET @Employeename = '<name>Buck</name>'

BEGIN DIALOG CONVERSATION @CH
 FROM SERVICE AddEmployeeService
 TO SERVICE
'[ServiceBroker/Example/Employee/AddEmployeeService]'
 ON CONTRACT
[ServiceBroker/Example/Employee/AddEmployeeContract];

 SEND ON CONVERSATION @CH
    MESSAGE TYPE
[ServiceBroker/Example/Employee/AddEmployee] (@EmployeeName)
GO

To check the results, I query the dynamic management views I mentioned earlier, as well as the destination table:

 Look at the results
SELECT * FROM sys.dm_broker_activated_tasks
SELECT * FROM sys.dm_broker_connections
SELECT * FROM sys.dm_broker_forwarded_messages
SELECT * FROM sys.dm_broker_queue_monitors
GO

SELECT *
FROM
Employee
GO