SQL Server Magazine

Service Broker, which was a new component in SQL Server 2005, provides many new ways of thinking about database applications. When used in conjunction with Microsoft IIS and other SQL Server technologies, it can provide the core architecture needed for distributed server-side applications, reliable query processing and data collection, large-scale batch processing, and data consolidation for client applications. Let’s look at data consolidation to see how the Service Broker technology can be used to implement a security monitoring and logging solution for any organization.

Components of a Service Broker Solution
A Service Broker solution consists of message types, contracts, queues, and services. Let’s review each of these objects briefly before moving on to the actual Service Broker solution we’re focused on implementing in this article.

A message type is a message category that’s used by an application. Defining a message type ensures that only messages that can be understood by the processing service can be submitted. Message types are similar to email messages in that they have a subject (the message type itself) and a body (the payload of the message). Message types are used in conversations between an initiator service and a target service.

A contract is an agreement between the initiator and the target. The contract defines the message type that can be sent by the initiator and the valid return that can be sent by the target.

The queue is the storage location for the messages that are intended for processing by a specific application. When messages arrive for a specific queue, Service Broker is responsible for placing the messages in that queue. You can think of the queues as storage bins for the messages waiting to be processed.

Finally, the service is the endpoint that the application uses to access the queue. The service specifies the queue and contracts that can be used to store information in and retrieve information from that queue. A service that doesn’t specify a contract can only be an initiator; it can’t be a provider or target.

One additional component is needed: A service program is simply an external application or an internal stored procedure that’s used to process the messages stored in the queues. If you don’t implement a service program, the messages will simply sit in the queue and never be processed.

Implementing the Service
Now, I’ll walk you through the implementation of a centralized security-incident logging solution. You’ll learn how to create message types, contracts, queues, and services, all of which are needed in the solution. You’ll also learn how to create simple Active Server Pages (ASP) code that calls a stored procedure to place information in the security logging queue for automated processing. ASP.NET would be the preferred solution in a production environment; however, I’ll use ASP in this example because it lets me keep the code simple all the way through. Finally, you’ll learn how to read messages from the queue and use stored procedures to process them.

The first thing you need to do is create a database for your incident logs. Create a very basic database named seclog by using the following simple command:

CREATE DATABASE 
  seclog;

ALTER DATABASE seclog
  SET ENABLE_BROKER;

Finally, you must create a table for storing the security incidents after they’re processed by the service program. These commands will work for the simple table you need to create:

USE seclog;

CREATE TABLE incidents
 (id INT PRIMARY KEY IDENTITY(1,1),
  incident varchar(50));

Now that you have the database and table in place, you can begin creating the Service Broker objects. You’ll create them in this order:

1. Message Types
2. Contracts
3. Queues
4. Services

The first step is to define a valid message type. The security logging solution will need to receive a message that contains the incident description to be entered in the incidents table. Call this message RequestMessage. You also need to create another message type for the response, although the actual implementation won’t use it. Call this message ReplyMessage. Listing 1 shows the code to create the message types.

   Prev. page   [1] 2     next page