Article Author: Klaus Aschenbrenner
Introduction
In late 2005 Microsoft released its latest release of SQL Server (SQL Server 2005) and Visual Studio (Visual Studio 2005) to great fanfare. With these releases came many new features that are often written about. Many articles, blogs and postings have appeared discussing and showing samples of using some of the new features. Most of these only discuss or show how to work with a single new feature. This article will take a little different approach and combine several different new features to create a very powerful technique for executing a managed stored procedure asynchronously.
I will start by defining the problem at hand in detail and then proceed to building a solution that uses the following technologies:
- Managed Stored Procedures
- Managed Triggers
- SQL Service Broker
Each of these components will be discussed in depth in the context of solving the problem.
System Requirements
To run the code for this sample you will need to have:
- SQL Server 2005 (any edition) running on Windows 2000 or later
- The .NET Framework version 2.0
- Visual Studio 2005
Installing and Compiling the Sample Code
The sample download for this article contains a Visual Studio 2005 solution (CustomerManagement.sln). This solution implements the managed trigger and the managed stored procedure needed to interact with SQL Service Broker.
To install the sample, you should first create a new database called CustomerManagement on your SQL Server and rebuild the Visual Studio 2005 solution. After this you should run the following 2 SQL scripts:
- 01_Setup_Database.sql
- 02_Setup_ServiceBroker.sql
When you look at these scripts you can see that you must change the directory in which the assemblies are created. You can open and run these scripts directly from SQL Server Management Studio. The first SQL script creates the necessary tables, registers the assemblies, the managed trigger and the managed stored procedure for the Service Broker. With the second SQL script all the necessary SQL Service Broker objects are created and set up.
You will also need to make sure the CLR integration has been enabled. You can do this through the SQL Server Surface Area Configuration as seen in Figure 1.
Figure 1. Enabling CLR support in SQL Server 2005
When all of the steps have completed successfully, you can try to insert a new customer in the provided customer table. If everything runs correctly a text file called c:InsertedCustomers.txt will be created. In this text file you will see the detailed information about the newly inserted customer.
The readme.txt file included with the download also contains detailed instructions on the installation procedure.
The Sample Application
Before I dig into the sample application I want to define the problem that is addressed with the sample solution. Let’s assume you have a table in your database with data in it. Each time a new record is inserted or updated in the table, a text file with additional information about the record must be written to the file system (eg. You may need to process the record in another legacy backend system). Another possible situation could be that you have to call a web service that takes a lot of time when a record is inserted in the table. Or just think of a real world scenario where you must start a workflow (eg. in BizTalk Server or Windows Workflow Foundation) that may take some time to complete.
The quick and dirty solution is to use a trigger that handles the insert or update of the table and writes the file to the file system (or calls the web service). But what if the database is part of a high performance mission critical enterprise application? With the quick and dirty solution you must always make an expensive (in terms of time) synchronous call to write the file or to call the web service. As soon as more records are inserted or updated simultaneously in the table, the performance of your solution starts to degrade. Another drawback is that you probably also have locks on the inserted or updated records, so other writers and readers must wait until the trigger is finished and releases the locks. You can see that this simple and quick approach is not suitable for scalable mission critical enterprise applications.
The solution presented in this article uses a managed trigger that is fired when a record is inserted or updated into a specified table. In this trigger I’m sending a Service Broker message to a Service Broker service that contains all the required information for further processing. As soon as the Service Broker message arrives on the other Service Broker endpoint, the activation feature of the Service Broker is used to start a managed stored procedure that handles the message. Within this managed stored procedure you can then write to the file system or call a web service as needed. The big benefit to using this approach is that the trigger is finished as soon as the Service Broker message is placed on the outgoing queue of the Service Broker. Therefore all the other processing (sending the message, retrieving the message, processing the message, writing to the file system, calling a web service) is done in an asynchronous fashion. Because of this approach, the trigger is finished in a very short time and the required locks on the data are also held as short a time as possible. The result is an asynchronous solution that is suitable for high performance scalable enterprise applications.
A Managed Trigger
Let’s start by first describing how the managed trigger is implemented and how you can access the Service Broker functionality from within managed code. A managed trigger is a trigger that is implemented in a .NET language of your choice that has a static method that does not have any parameters and does not have a return value. You can use the attribute [SqlTrigger] for the method, with this attribute you can specify for which table and for which event the trigger should be registered, as you will see shortly.
When you use the attribute [SqlTrigger] you can automatically deploy the trigger from Visual Studio 2005 to the database. To do this, right click on the project in Visual Studio 2005 and select the command Deploy. This feature is useful during development, but as soon as you have to deploy the trigger (or any other managed component running inside SQL Server 2005) into a production environment, you need to know which T-SQL statements are required for deploying the components. I recommend using the attribute during the development phase but you should also separately create T-SQL scripts with the required T-SQL code for registering the managed objects within SQL Server 2005. You can gather the required T-SQL code by using SQL Profiler during an automatic deployment with Visual Studio 2005. Just run the SQL Profiler during the automatic deployment of the assembly within Visual Studio 2005. Then you can see the created T-SQL statements in SQL Profiler. The following listing shows the C# code needed for implementing the managed trigger.
[Microsoft.SqlServer.Server.SqlTrigger( Name="OnCustomerInserted", Target="Customers", Event="FOR INSERT")]
public static void OnCustomerInserted()
{ using (SqlConnection cnn = new SqlConnection("context connection=true;") { try { SqlCommand cmd = new SqlCommand("SELECT * FROM INSERTED", cnn); cnn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { SqlCommand sendCmd = new SqlCommand(GetServiceBrokerScript( (string)reader1, (string)reader2, (string)reader3, (string)reader4), cnn); reader.Close(); sendCmd.ExecuteNonQuery(); } } finally { cnn.Close(); } }
}
Let’s have a detailed look at the C# trigger code above. The attribute [SqlTrigger] receives the following named parameters:
- Name – specifies the name of the trigger which is used to identify the trigger inside SQL Server 2005
- Target – specifies the target of the trigger, in the sample application this is the name of the table
- Event – specifies for which events the trigger must be fired, in the case of the sample it is the insertion of new records – FOR INSERT
The first step in the trigger is to open a connection to the database. Because the trigger is already running in the context of an active database connection, you can use the connection string context connection=true to retrieve the current connection in which the trigger is fired and executed. In the next step the newly inserted record is read from the table INSERTED. This special table is only available in the context of a trigger when a new record is inserted into the table. The last step is to create a new SqlCommand object and execute it against the current connection. The T-SQL script that is executed through the SqlCommand is taken from the method GetServiceBrokerScript(). This method is shown in the following listing.
private static string GetServiceBrokerScript( string customerNumber, string customerName, string customerAddress, string emailAddress)
{ // Create the request message StringBuilder xmlBuilder = new StringBuilder("<InsertedCustomer>"); xmlBuilder.Append("<CustomerNumber>").Append(customerNumber).Append( "</CustomerNumber>"); xmlBuilder.Append("<CustomerName>").Append(customerName).Append( "</CustomerName>"); xmlBuilder.Append("<CustomerAddress>").Append(customerAddress).Append( "</CustomerAddress>"); xmlBuilder.Append("<EmailAddress>").Append(emailAddress).Append( "</EmailAddress>"); xmlBuilder.Append("</InsertedCustomer>"); // Create the T-SQL statement for sending the Service Broker message StringBuilder sqlBuilder = new StringBuilder("BEGIN TRANSACTION; "); sqlBuilder.Append("DECLAREch UNIQUEIDENTIFIER; "); sqlBuilder.Append("DECLAREmsg NVARCHAR; "); sqlBuilder.Append("BEGIN DIALOG CONVERSATIONch "); sqlBuilder.Append("FROM SERVICE [CustomerInsertedClient] "); sqlBuilder.Append("TO SERVICE 'CustomerInsertedService' "); sqlBuilder.Append("ON CONTRACT [http://www.csharp.at/CustomerManagement/CustomerInsertContract] "); sqlBuilder.Append("WITH ENCRYPTION=OFF; "); sqlBuilder.Append("SETmsg = ‘").Append(xmlBuilder.ToString()). Append("’; "); sqlBuilder.Append("SEND ON CONVERSATIONch MESSAGE TYPE [http://www.csharp.at/CustomerManagement/CustomerInsertedRequestMessage] (msg); "); sqlBuilder.Append("COMMIT;"); // Return the whole T-SQL script return sqlBuilder.ToString();
}
As you can see from the listing above, an XML document is constructed with the given parameters. Finally the whole T-SQL script required for sending a SQL Service Broker message is created and returned to the callee. The next section describes the core concepts of SQL Service Broker.
Sending and Receiving Messages with SQL Service Broker
You have already heard the term SQL Service Broker, but what is a SQL Service Broker? A SQL Service Broker is an asynchronous messaging framework directly built into SQL Server 2005. From an architectural view you can compare it with MSMQ (Microsoft Messaging Queuing) or an XML Web Service. But keep in mind that there are some differences:
- MSMQ always needs a distributed transaction for doing work
- With SQL Service Broker you can handle messages larger than 4 MB
- With XML Web Services you have interop possibilities between platforms
SQL Service Broker has its own database objects in SQL Server 2005. The most important objects are:
- Message Types
- Contracts
- Queues
- Services
- Service Programs
Figure 2 shows the relationship between these objects and how they form a SQL Service Broker solution.
Figure 2. The core SQL Service Broker objects
Let’s have a detailed look at these objects. A message type defines the format and the structure of a message sent with SQL Service Broker. SQL Service Broker supports the following three message types:
- Binary messages
- XML well formed messages
- XML messages validated against an XML schema
In the sample solution I have created two message types: a request message sent from the trigger to the SQL Service Broker service and a response message sent from the managed stored procedure back to the client part of SQL Service Broker. The response message is optional because you can also implement one-way messaging with SQL Service Broker. The following listing shows how you can create message types using T-SQL. This was part of the T-SQL installation script 02_Setup_ServiceBroker.sql, which you have already executed during the installation process of the sample application.
CREATE MESSAGE TYPE [http://www.csharp.at/CustomerManagement/CustomerInsertedRequestMessage] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [http://www.csharp.at/CustomerManagement/CustomerInsertedResponseMessage] VALIDATION = WELL_FORMED_XML
As soon as you have created the message types, you need to create a contract. A SQL Service Broker contract defines in which directions messages are sent between two Service Broker endpoints. You can select from the following three options:
- INITIATOR- the message is sent from the initiating endpoint (the endpoint who starts the conversation)
- TARGET- the message is sent from the target service to the initiating endpoint
- ANY- the message can be sent from both endpoints (like status messages)
In the sample solution I’ve created one contract that contains references to both message types created in the previous step. The following listing creates the contract.
CREATE CONTRACT [http://www.csharp.at/CustomerManagement/CustomerInsertContract]
( [http://www.csharp.at/CustomerManagement/CustomerInsertedRequestMessage] SENT BY INITIATOR, [http://www.csharp.at/CustomerManagement/CustomerInsertedResponseMessage] SENT BY TARGET
)
Once the contract is complete, the next step is to create queues. Queues are used to store messages that are received from endpoints. In terms of SQL Server 2005, a queue is naturally a table. The only difference between a table and a queue is that a queue has a special lock – but nothing else. This special lock implements a lock on a dialog. So as soon as one message from a dialog is processed no other messages from the same dialog can be processed until the first one completes. The following listing demonstrates how queues are created.
CREATE QUEUE [CustomerInsertedServiceQueue]
CREATE QUEUE [CustomerInsertedClientQueue]
The client queue is needed for response messages from the service. Furthermore SQL Service Broker is also using the client queue when an error occurs, SQL Service Broker sends an error message to the initiator of the conversation – the client queue.
When you are sending messages with SQL Service Broker you are not sending these messages directly to the queues. SQL Service Broker has an additional concept called services, a service is an addressable endpoint in SQL Service Broker. Figure 3shows this concept.
Figure 3. Services in SQL Service Broker
In the sample application I’ve created two services, one service for each queue as seen in the listing below.
CREATE SERVICE [CustomerInsertedService] ON QUEUE [CustomerInsertedServiceQueue]
( [http://www.csharp.at/CustomerManagement/CustomerInsertContract]
)
CREATE SERVICE [CustomerInsertedClient] ON QUEUE [CustomerInsertedClientQueue]
( [http://www.csharp.at/CustomerManagement/CustomerInsertContract]
)
As you can see from the listing you must specify each of the contracts that the service supports. In the sample application I must specify the contract I created a few listings back. As soon as the SQL Service Broker knows the contract for a service, it also knows which message types can be sent to and from the service.
The last thing you need to understand when working with Service Broker are the service programs. A service program can be a stored procedure that is automatically activated when a new message arrives in a SQL Service Broker queue. This process is called activiation in SQL Service Broker terms. In the sample application I’ve activated the execution of the stored procedure ProcessInsertedCustomer() on the queue CustomerInsertedServiceQueue. This stored procedure is described in the next section of this article in detail. The following listing shows the complete code that is necessary for the activation of the stored procedure.
ALTER QUEUE [CustomerInsertedServiceQueue] WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = ProcessInsertedCustomer, MAX_QUEUE_READERS = 2, EXECUTE AS SELF )
Now that we understand how the components work together it is easy to understand the SQL Service Broker sending code from the previous section. The necessary T-SQL code was constructed inside the method GetServiceBrokerScript(). Let’s examine the T-SQL script returned from this method.
A new dialog between two SQL Service Broker endpoints is created with the statement BEGIN DIALOG CONVERSATION. Next you need to specify both services with the FROM SERVICE and TO SERVICE clause. It is very important that you specify the receiving service as a string, because this could be a service on a remote SQL Server instance and not only a local service. You must supply the used contract with the ON CONTRACT clause. As soon as you have done all these steps a dialog is opened between the two services. SQL Service Broker returns to you a conversation handle which uniquely identifies the opened dialog. You can use the statement SEND ON CONVERSATION to send the actual XML message on the opened dialog. Here you must supply the message and the message type that should be used. The following listing shows the simplified code needed to send a SQL Service Broker message.
BEGIN TRANSACTION
DECLAREch UNIQUEIDENTIFIER DECLAREmsg NVARCHAR
BEGIN DIALOG CONVERSATIONch FROM SERVICE [CustomerInsertedClient] TO SERVICE 'CustomerInsertedService' ON CONTRACT [http://www.csharp.at/CustomerManagement/CustomerInsertContract] WITH ENCRYPTION=OFF SETmsg = "<MyXmlMessage/>"
SEND ON CONVERSATIONch MESSAGE TYPE [http://www.csharp.at/CustomerManagement/CustomerInsertedRequestMessage] (msg )
COMMIT
As soon as the message is sent and received on the other endpoint the managed stored procedure ProcessInsertedCustomer() is activated and processes the newly received message.
Processing Messages with a Managed Stored Procedure
In the last section I described how you can use SQL Service Broker to build asynchronous messaging facilities directly into your database application. In this section I’ll show you how you can use a managed stored procedure to process a message sent by a SQL Service Broker service. A stored procedure written in managed code must use be a static method without parameters and without a return value. You can use the attribute [SqlProcedure] for an automatic deployment inside Visual Studio 2005
The stored procedure uses the RECEIVE statement to retrieve a SQL Service Broker message from the target queue. As soon as you have read the message from the queue you should send on the active dialog the END CONVERSATION statement in order to end the dialog between the two endpoints. This is very important because otherwise SQL Server will only close the dialog after 64 years! Each open dialog requires some resources on SQL Server. After these few steps the content from the XML message is parsed through some XPath expressions and each piece of information is written to a text file stored on the local file system. At this point you could call a web service or do whatever processing you need to do. It is very important that this be done purely asynchronous, so you have no performance overhead during the insertion of new records. The following listing shows the required code for retrieving the XML message from the SQL Service Broker queue.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ProcessInsertedCustomer()
{ string sql = "RECEIVE conversation_handle, NVARCHAR(MAX">CAST) FROM [CustomerInsertedServiceQueue]"; string message = string.Empty; using (SqlConnection cnn = new SqlConnection("context connection=true;")) { try { cnn.Open(); SqlDataReader reader = new SqlCommand(sql, cnn).ExecuteReader(); if (reader.Read()) { Guid conversationHandle = (Guid)reader0; message = (string)reader1; reader.Close(); new SqlCommand("END CONVERSATION ‘" + conversationHandle.ToString() + "’", cnn).ExecuteNonQuery(); } } finally { cnn.Close(); } } if (message != string.Empty) WriteCustomerDetails(message);
}
As soon as the XML message is read from the queue, the message can be processed and written to the file system as demonstrated in this example. The following listing shows the method WriteCustomerDetails(), which writes the customer details from the message into a text file.
private static void WriteCustomerDetails(string xmlMessage)
{ XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(xmlMessage); using (StreamWriter writer = new StreamWriter("c:InsertedCustomers.txt", true)) { writer.WriteLine("New Customer arrived:"); writer.WriteLine("====================="); writer.WriteLine("tCustomerNumber: " + xmlDoc.SelectSingleNode( "//CustomerNumber").InnerText); writer.WriteLine("tCustomerName: " + xmlDoc.SelectSingleNode( "//CustomerName").InnerText); writer.WriteLine("tCustomerAddress: " + xmlDoc.SelectSingleNode( "//CustomerAddress").InnerText); writer.WriteLine("tEmailAddress: " + xmlDoc.SelectSingleNode( "//EmailAddress").InnerText); writer.Close(); } } <h2 class="redhead0"></pre><!--@END —>
Conclusion
This article has shown how you can combine some of the new advanced SQL Server 2005 programming features to build very powerful and scalable solutions targeted for mission critical enterprise applications.
In the article we covered the core SQL Service Broker objects in order to get our components to talk to each other. We also briefly covered using managed stored procedures to actually process our data asynchronously.
Using this technique provides several advantages over using a synchronous approach:
- Asynchronous execution of trigger tasks
- Very scalable through the activation feature of SQL Service Broker
- You can do just about anything inside a managed stored procedure
I hope that I have inspired you with this article and that you also see the power of the new SQL Server 2005 features. The real power of the new features is when you use them together in order to solve a problem.
