DBAs often struggle with setting up automated tasks for managing databases such as taking backups, maintaining warehouses, re-building indexes and generating scheduled reports just to mention a few. While Service Broker is seen mostly as a framework for developers to build reliable messaging applications, it can be used very well by DBAs for doing routine maintenance jobs asychronously. Roger Wolter's recent article on Microsoft TechNet Magazine (Spring 2005) talks about a simple batch scheduling system built using Service Broker. You can access it here:

Async Lifestyle: Manage Your Tasks With Service Broker

[Attachment: CommodityExchange.zip]

We studied auctions in our e-commerce and AI classes at U of M. In one of our assignments we were asked to configure an agent that played the Trading Agent Competition (TAC) which involved a variety of auctions for buying or trading airline tickets, hotel reservations and entertainment tickets. The agents participating in the competition talked to the auction server using a binary TCP protocol. In the real world, the protocol would require to have reliability and security and the app would require to scale to thousands of agents trading at any given time.

Of particular interest to me were the entertainment ticket auctions. These were traded using continuous double auctions where agents could both sell as well as buy entertainment tickets. Continuous double auctions (or CDAs) are very common in exchanges such as the stock market. Agents submit bids of the form (i, q, p). If q > 0, this indicates that the agent is willing to buy q units of item i for a unit price no greater than p. If q < 0, then it means that the agent is willing to sell -q units of item i for a unit price no lesser than p. The auctions clear continuosly, i.e. bids match immediately if possible; otherwise they remain standing in the auction. Price quotes can be issued anytime using a snapshot of the standing bids. The price quote for an item i is specified as the bid price (i.e. the price of the highest standing buy bid) and the ask price (i.e. the price of the lowest standing sell bid). For example, consider the standing bids for some commodity (Gas) as follows:

1. (Gas, 10, 2.07)
2. (Gas,   5, 2.23)
3. (Gas,   6, 2.45)
4. (Gas,  -7, 2.53)
5. (Gas,  -3, 2.67)

The price quote for Gas is ask price 2.53 and bid price 2.45. Now if someone places a bid (Gas, -12, 2.14), it will immediately match bids 2 and 3 and the unmatched part (Gas, -1, 2.14) will remain standing.

To illustrate the power of SQL Server 2005 in running asychronous tasks and providing reliable message delivery using Service Broker, I decided to implement a very simple CDA server using just SQL Server 2005. The auction service runs as an internally activated stored proc. While it could have been done in just T-SQL, I wanted to dogfood the ServiceBrokerInterface library as well and so I wrote the stored proc in C#.

The attached file contains two solutions -- an updated version of the ServiceBrokerInterface library and the sample called CommodityExchange. CommodityExchange comprises of two projects -- ExchangeService, the internally activated CLR stored proc and TradingAgent, a Winforms client to participate in the auction.

Various agents that want to participate in the auction implement an [agent_service]. [agent_service]s talk to the [exchange_service] using the [auction_contract] contract which defines three message types -- [submit_bid_message], [retract_bid_message] sent by the [agent_service]s and [clear_bid_message] sent by exchange service. In order to participate in the auction, [agent_service]s begin a conversation with the [exchange_service]. The conversation handle uniquely identifies the agent's session. Agents can submit or retract bids of the form (i, q, p) to the [exchange_service]. The stored proc that processes these messages is activated automatically by SQL Server. The stored proc executes the business logic governing CDAs, i.e. it attempts to match bids immediately in price order and store unmatched bids in a standing bids table. When bids are matched, the [exchange_service] sends notification messages to the agents involved. If the stored proc cannot keep up with the rate of incoming messages, SQL Server will activate multiple instances of the stored proc to run in parallel (configured to a max of 4), thus allowing the app to scale.

You can try the sample by building the solutions and running the install.cmd script. The script sets up the [exchange_service] in a database called [exchange_db] and also sets up two sample agent services [alice_agent_service] and [bob_agent_service] in their corresponding databases [alice_db] and [bob_db]. Public key certificates of service owners are exported and exchanged in order to use full dialog security.

Having built webapps in Java and PHP, I have been curious about ASP.NET. So I decided to teach myself some basic ASP.NET programming by building a useful webapp. A common problem that I've faced going for dinners/movies/shopping with other single friends is splitting bills and keeping track of who owes who how much. It is typical in the plastic money era for us not to have any cash on ourselves. So when they bring the check it is quite tedious to request it being charged to 8 different credit cards with varying amounts. We end up with just one or two of us paying on behalf of others who try to remember the small debts they owe. Three or four such transactions per week and an ever growing social network of friends makes tracking these debts pretty hard.

So I built a webapp called 'Paisa' (hindi for 'Money') to try to solve this problem. Members can charge other members the amounts owed and clear payments received. They can also split a bill among multiple payers with custom ratios. Transactions added send an automatic e-mail to the people involved.

It is a pretty simple app that runs on ASP.NET and uses SQL Server 2005 (express edition should be fine) as backend. It uses Database Mail, a feature of SQL Server 2005 for queuing e-mail messages and delivering them via an SMTP server.

If you think this is handy, send me an e-mail and I can give you the source code so that you can customize it for yourself.

Today, we did a short presentation on Service Broker for a customer who is interested in using the Service Broker technology. Their current application uses a large backend SQL Server database that is accessed from several mid-tier servers. In one of their scenarios, requests are queued to an MSMQ message queue and an NT service periodically reads these requests from the queue and performs some database operations on the backend. Switching from MSMQ to SQL Server 2005 Service Broker would give them the immediate advantages of persisting messages along with the data in the database and thus provide a single backup/restore solution. And they also get the exactly once in-order semantics of the conversations, internal activation to get rid of the polling NT service, etc.

MSMQ and Service Broker being fundamentally different in their architecture, we realized that proposing a solution from a Service Broker angle seems like a huge change for someone thinking in terms of a single MSMQ queue. So here is a look at incrementally migrating a simple spooling MSMQ application to a Service Broker.

Our application consists of a single queue that is being used for spooling requests. Two processes 'reader' and 'writer' respectively dequeue and enqueue messages to it. There could potentially be multiple writers enqueuing requests. Implementing multiple readers in the MSMQ case can be tricky is messages are correlated and must be fetched in order (But in Broker land, this is trivial due to our conversation semantics).

To implement this solution using SQL Server 2005, both the reader and writer programs connect to a database (for example using ADO.NET). So we begin by creating a database 'spoolerdb':

-- create the database

--

use master;

go

if (exists (select * from sys.databases where name='spoolerdb'))

      drop database spoolerdb

go

create database spoolerdb;

go

Next we create a queue (which is a new database object in SQL Server 2005) that will hold our request messages:

use spoolerdb;

go

create queue spool;

go

In order to identify endpoints that can initiate or be the target of messages, we create yet another type of database objects called services and bind them to the same queue. The services currently only support the [DEFAULT] contract for sake of simplicity, but can be extended in the future:

create service writer on queue spool (

      [DEFAULT] );

go

create service reader on queue spool (

      [DEFAULT] );

go

The overall architecture of the system would look something like the figure below:

The red dashed lines represent the operations that we want to achive -- the writer wants some way to enqueue messages into the queue and the reader wants to dequeue messages from the queue. The layer that sits between the programs and the physical queue is a logical layer comprising of Service Broker constructs.

The writer can enqueue messages by executing the following T-SQL batch:

-- Writer logic (DML)

--

declare @dh uniqueidentifier;

begin transaction;

    begin dialog @dh

        from service writer

        to service 'reader', 'current database'

        with encryption = off;

    send on conversation @dh ('My first message');

    send on conversation @dh ('My second message');

    send on conversation @dh ('My third message');

    end conversation @dh;

commit transaction;

The reader can loop to receive messages from the queue. Here is a loop entirely in T-SQL that receives messages into a temp table and runs a cursor over it. If the message type is not the special end_dialog message type then it prints the message to the output.

-- Reader logic (DML)

--

declare @qview table (

      message_type_name sysname,

      dh uniqueidentifier,

      message_body varchar(max));

declare @message_body varchar(max);

declare @message_type_name sysname;

declare @dh uniqueidentifier;

declare qc cursor for select * from @qview;          

while (1=1)

begin

      begin transaction;

            delete from @qview;

            waitfor (

                  receive message_type_name, conversation_handle, message_body

                  from spool into @qview), timeout 2000;

            if (not exists (select * from @qview))

                  break;

            open qc;

            fetch next from qc into @message_type_name, @dh, @message_body;

            while @@fetch_status = 0

            begin

                  if (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

                        end conversation @dh;

                  else

                        print @message_body;

                  fetch next from qc into @message_type_name, @dh, @message_body;

            end

            close qc;

      commit transaction;

end

deallocate qc;

commit transaction;

Of course, the logic can be broken into multiple batches and executed from the reader program, for example a CLR program that uses ADO.NET. It can be seen that we use abstractions such as services and dialogs for SENDing and RECEIVing messages. Services are named endpoints that can initiate or be the target of a message exchange called conversations. A two-way peer-to-peer conversation is known as a dialog.

While this simple solution works great for quickly migrating an MSMQ app to Service Broker, you will soon realize that having separate queues for the initiator and target service is a good idea even if you don't initially perceive the writer to read messages or the reader to write messages. But that is optional.

Postedby rushidesai | 1 Comments    

Anyone who has written a web application has probably experienced the cost of doing a database round trip for fetching a result set everytime a web user loads a page. In fact, handling a page request may involve more than one queries (imagine 20 drop-down boxes, each of which must be populated). A fix to this problem is caching frequently requested result sets in memory to avoid the database round trip. The cache can be invalidated periodically get a  more recent version of the actual data. This technique seems to work fine if the data is not updated frequently and the application does not really need the result sets to be fresh. What would be ideal in this case is for the database to notify the cache when it gets dirty so that the application can fetch the updated result set on demand. Most database engines have the infrastructure for making this possible and it is used for maintaining materialized views. SQL Server 2005 provides a mechansim to hook into this plumbing in order to allow external applications to be notified when views are invalidated. The feature is called Query Notification.

The figure above shows typical interactions for the desired behaviour:

1. Gerald sends a web-request to the web-server.
2. Page handler performs a SELECT query on the database.
3. Database returns a result set.
4. a. Web-server generates a response page and sends it back to Gerald.
   b. Web-server stores the result set in its cache.
5. Ivan updates the table which would affect the result of the SELECT query above.
6. Database fires a query notification.
7. Web-server issues the SELECT query again.
8. Database returns the new result set.
9. Web-server updates the cache with new result set.
10. Gus requests the same web-page.
11. Page handler finds cached version of the result set.
12. Web-server generates a response page and sends it back to Gus.

Query notifications use Service Broker underneath to send notification messages to a subscribed service. You can subscribe for query notifications and have a custom message be sent back in the body of the notification when the query's results are invalidated. The notification message looks something like this:

<qn:QueryNotification

  xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"

  Type="change" Source="data" Info="insert">

    <qn:Message>http://rushi.desai.name/catalog.aspx?Category=Software</qn:Message>

</qn:QueryNotification>

The <qn:Message/> element contains the custom payload and could be a URL, a GUID or anything you like. I tried to create a sample web application that caches result sets and uses query notifications for maintaining coherency. I have built web-apps in the past using PHP and Java Servlets, but ASP.NET is completely new territory for me. So I'm sure there are better ways of doing this. While looking at ASP.NET and ADO.NET, I realized there are at least half a dozen ways of using query notifications -- from the simplest that require no lines of code, just some declarations:

    <form id="form1" runat="server">

        <asp:GridView ID="grdItems" Runat="server"

DataSourceId="SqlDataSource1"/>

        <asp:SqlDataSource ID="SqlDataSource1" Runat="server"

      EnableCaching="true"

SqlCacheDependency="CommandNotification"

            ConnectionString="<%$ ConnectionStrings:mySqlServer %>"

            SelectCommand="SELECT i.Name as Item, i.UnitPrice as UnitPrice, c.Name as Category FROM dbo.Item i JOIN dbo.Category c ON i.CatId=c.CatId ORDER BY i.Name"

        />

</form>

to actually implementing the service that receives the notifications. This article by Bob Beauchemin goes into the details of using Query Notifications in ADO.NET 2.0.

 But for our sample, we do not use ASP.NET’s Cache object just in order to illustrate how the plumbing works underneath. I want to cache the result set in the Application’s context and so I add it to the Application.Contents hashtable. We begin with the Global.asax file that actually implements the caching logic as below:

<script runat="server">

    void Application_Start(Object sender, EventArgs e)

    {

        Refresh();

    }

    void Refresh()

    {

        using (SqlConnection conn = new SqlConnection(

                  ConfigurationSettings.ConnectionStrings[

"mySqlServer"].ConnectionString))

        {

            // Construct the command to get Items data

            SqlCommand cmd = new SqlCommand(

                  "SELECT i.Name as Item, i.UnitPrice as UnitPrice, " +

"c.Name as Category FROM dbo.Item i JOIN " +

"dbo.Category c ON i.CatId=c.CatId ORDER BY i.Name",

conn);

            // Create a dependency on this query.

            SqlDependency dependency = new SqlDependency(cmd);

            // Register the event handler which will be called

// when the base tables are updated.

            dependency.OnChanged +=

                new OnChangedEventHandler(Dependency_OnChanged);

            conn.Open();

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = cmd;

            DataSet ds = new DataSet("MyDataSet");

            adapter.Fill(ds, "Items");

            lock (Application)

            {

                Application.Contents["ds"] = ds;

                // To ensure that object is not garbage collected

                Application.Contents["dep"] = dependency;

            }

        }

    }

    private void Dependency_OnChanged(object sender,

        SqlNotificationEventArgs e)

    {

        Refresh();

    }

</script>

 
The key idea is to create a SqlDependency object with the appropriate event handler to bind to the SqlCommand. When the command is executed, the user subscribes to the query notification. SqlDependency uses a default stored proc that is internally activated. The stored proc uses a TCP or HTTP channel to actually fire an event in the client process. In our event handler, we re-fetch the result set and register another SqlDependency.

 The front-end webpage could simply use the object from the cache as follows:

<%@ Page Language="C#" ClassName="qncache_aspx" Debug="true" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

<script runat="server">

    private DataSet ds;

    public void Page_Load()

    {

        DataSet ds;

        lock (Application)

        {

            ds = (DataSet)Application.Contents["ds"];

        }

        if (ds != null)

        {

            grdItems.DataSource = ds.Tables[0];

            grdItems.DataBind();

        }

    }

</script>

<html>

<head>

    <title>Output Cached Items</title>

</head>

<body>

    <form id="form1" runat="server">

        <asp:GridView ID="grdItems" Runat="server" />

    </form>

</body>

</html>

Having seen implementations of MSMQ, SMTP and UDP transport providers for WSE, I was sure someone would come up with one for Service Broker as well. I played around with this for a bit but only got to implement the sender side. Mike Taulty recently released a Service Broker sample transport provider. I haven't tried it myself yet but I will post my comments on this soon.

Going forward, it will be possible to overlay Indigo on top of Service Broker in a similar fashion, using the Service Broker technology as a full duplex, transacted, secure and reliable channel. Anyone up for building a Service Broker Channel for the Indigo CTP bits?

• CREATE_TABLE
• DROP DATABASE
• Audit_Login
  

use eventsnotifdb
go

create queue ClientEventNotificationQueue
go

create service ProcessEventNotifications
on queue ClientEventNotificationQueue
([http://schemas.microsoft.com/SQL/\
  Notifications/PostEventNotification])
go

create event notification testnotif
on database with fan_in for CREATE_TABLE
to service 'ProcessEventNotifications', 'current database'
go

create table A (a int)
go

receive message_type_name, convert(xml, message_body) from ClientEventNotificationQueue
go

I found this article on MSDN about a new programming language called Cω (C-omega) that was quite interesting. It is a language created by Microsoft Research that looks a lot like C# but has new constructs that help programmers to access and process XML and relational data. Some of the cool features that grabbed my attention were special streams type, XPath and SQL like querying capability and anonymous structures for semi-structured data. You can find out more about this cool new language on the research group homepage. They also have a shared-source preview compiler which I'm going to experiment with soon. Will post more about this later.

In a previous post, I described the basic routing architecture of Service Broker. What I did not describe was a special facility that allows you to exploit external mechanisms for finding routes. For example, an enterprise may use Active Directory or LDAP for mapping service names to network locations; or an industrial consortium may host web-services that do the mapping. Service Broker allows you to define special ‘exit routes’ to find services using external mechanism. You can create this ‘exit route’ in a broker by defining and implementing a special service called he Broker Configuration Notification or BCN service. When the router cannot match the target service with a specified named route, it first tries the BCN service. If no BCN service is defined, only then does it use the last resort routes. (Refer to Part 1 about precedence).

If a route cannot be found, Service Broker internally begins a dialog with the defined BCN service and sends a request message containing the service_name and optionally the broker_instance of the target service being looked up. The message would look something like this:

<MissingRoute xmlns="...">
 <SERVICE_NAME>Nile/Finance/ExpenseService</SERVICE_NAME>
</MissingRoute>

You are free to implement the BCN service in any way you like. It could be a stored proc that looks up a table or an external program that talks to Active Directory. But the contract is that the BCN service upon fetching the route information CREATEs a route and ends the dialog. When the dialog ends, the Service Broker service assumes that a route must have been created and attempts to route the message from the sending service.

The figure above shows the sequence of events and messages exchanged by the broker and the BCN service to find a route.

I have been asked more than once if SQL Server Express has the Service Broker feature and if there are any limitations on the use. SQL Server 2005 Express Edition is a version of SQL Server 2005 that is available for free (strategically being released to compete against the free databases like MySQL). This version of SQL Server does come with Service Broker but has certain limitations.

There are no restrictions on creating Service Broker objects such as services, queues, message types and contracts. You can also have services begin dialogs and exchange messages within the same SQL Server instance, i.e. within the same broker/database as well as between brokers/databases hosted on the same server instance. Further, you can also use internal activation to kick-off stored procedures. The only limitation is when dialogs span SQL Server instances. In that case, at least one of the brokers on the route from the initiating service to the target service must be a full version (i.e. Standard or Enterprise Edition). So either the initiating service, the target service or one of the forwarders should be on a full/paid version of the product.

What this means is that you can use Express for building asychronous database applications within the same SQL Server instance without any problem. In an enterprise scenario with large backend machines running the full version, you can use Express at the mid-tier as forwarding brokers (routers) as well as clients at the front end.

To allow conversations across SQL Server instances, Service Broker provides a binary adjacent transport protocol. Brokers can communicate directly from initiating service to target service or use multiple intermediate forwarding brokers. In order to identify brokers uniquely, they carry a GUID property called broker_instance. (In SQL Server 2005, each database takes on the role of a broker). When a service begins a dialog with a remote service, the user only specifies the target service name and optionally a broker_instance GUID. We denote the target of a dialog using a tuple (service_name, broker_instance). The service name does not directly identify a network location. The routing infrastructure provides the mechanism for finding a network path to the remote service.

You can create/drop/alter routes that map service_name and broker_instance to a network address. We denote a route using a tuple (service_name, broker_instance, address, mirror_address). We classify the types of route definitions as follows: [Note: ‘*’ means not specified and hence match-all]

The address is typically a URL of the form:

   tcp://hostname|ip_address[:port]

But there are two special cases for last_resort routes. You can specify the address to be ‘LOCAL’ which indicates that the router should try to find a locally deployed broker. You can also specify ‘TRANSPORT’ which means the router should parse the service_name to extract a fully-qualified network location, eg> tcp://rushi.desai.name:4256/MyService.

When the initiating service sends the first message on the dialog, the broker attempts to resolve the route based on a routing policy. The following precedence rules are used for matching routes:

Broker Configuration Notification service or BCN is a mechanism for doing external route lookup and is explained in a future post.

By default, Service Broker creates a route (*, *, LOCAL) when a database is created. The helps map service_name to local services when no other matching routes are present (and BCN is not configured).

Typical scenarios

Migration – Disassociating names from locations enables the DBA to physically move a service without changing the application.

Forwarding – If there are thousands of front-end brokers initiating dialogs with a few backend target brokers, the overhead of managing thousands of connections could put too much load on the backend servers. The routing mechanism allows you to deploy mid-tier forwarding brokers which simply forward messages to next-hop broker without persisting them. The intermediate brokers are ‘forward-only’ and not ‘store-and-forward’ and do not participate in acknowledgement and retry.

Load-balancing – If a single broker cannot take the load of incoming requests, the services may be replicated on multiple brokers. A load-balancing router would have definitions for all the backend brokers. The router picks a route uniformly by hashing the conversation handle. A route chosen is sticky so that all messages belonging to a particular conversation will always be forwarded to the same remote broker.

Mirrored routes – For high-availability, databases may be mirrored using database mirroring. Service Broker can talk to mirrored brokers using a mirrored route. In such a case, you must define a ‘full’ route with the network addresses of both principal as well as mirror. The initiating broker monitors the state of the mirrored brokers and sends messages to the principal when available. On failover, it automatically switches to sending messages to the new broker seamlessly.

Disclaimer: The information presented here may be incorrect or out-of-date. The purpose of the post is only to give a general idea about typical usage scenarios. Refer to Books Online in your SQL Server 2005 for an exhaustive list of routing rules and policies