This chapter is from the book
This chapter is from the book
This chapter is from the book
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