Data Management

E-mail notifications made easy with SQL Server Notification Services

See how Microsoft's new toolkit makes building e-mail notification into your applications as easy as editing an XML template.

Have you ever bought something on eBay or put a price watch on a stock through an online trader? The e-mail notifications you get of price changes, sports scores, and auction bids are awfully convenient, and features like this are usually requested functionality in any widely used system. However, providing notification functionality means writing a lot of internal code, and retrofitting notifications onto an existing application can require an almost total rewrite.

Microsoft recently released a toolkit you can use to add notification functionality to an application with relatively little fuss. SQL Server Notification Services is a generic framework for providing user notification of particular events, built upon SQL Server 2000 and the .NET Framework. The Notification Services framework is complete enough to pretty much use out of the box, but it’s also very extensible and customizable. Let’s examine the structure of the Notification Services framework, and then we'll take a general look at how you’d leverage it in an application.

Notification Services architecture
Notification Services deals in three main concepts: events, subscriptions, and notifications, and it uses several different components to manage the process of notifying users. The whole thing is controlled by a set of NT services, one for each Notification Services instance running on a server.

Events generically represent any real-world or virtual occurrence. An event could be triggered when the inventory amount of a particular product drops below a certain level, when an order is shipped to a customer, or even when a particular file or other shared resource is modified. You can also build regularly scheduled events that don’t depend on a particular condition. The data for an event—which can take the form of a COM object, .NET class, XML document, or a SQL Server stored procedure—is generated by an Event Provider. Notification Services has two built-in kinds of Event Provider:
  • The File System Watcher keeps an eye on a designated system directory and watches for new XML files. When it finds a new file, it generates an event based on the data the file contains. You’d use the File System Watcher to hook into events from external sources.
  • The SQL Server Event Provider uses a specific SQL query to watch for changes to a particular table’s data and generates events based on the results of that query. Obviously, this provider is useful for generating events based on your application’s data.

Subscriptions are rules defining the types of events in which a given user is interested. The Subscription Management Objects API provides a generic interface for managing subscriptions. A Notification Services component, called the Generator, runs periodically to match events with subscribers.

Notifications are messages regarding a specific event, sent to a subscriber or subscribers, via any method. Notification Services includes support for notifications via e-mail, over HTTP, and by modifying a disk file. You can also provide your own notification method, such as instant messaging. There’s a Distributor component that handles formatting the data from an event into a notification using XSLT, or any custom method you provide.

SQL Server figures prominently
As you’d expect from the framework’s full name, SQL Server plays a prominent role in a Notification Services implementation. Incoming data from events is stored in a set of tables in a database. The Generator checks for relevant subscriptions by running a set of parameterized stored procedures against these tables. The Distributor extracts event data to fulfill any relevant subscriptions found by the Generator.

Having SQL Server do the bulk of the work is advantageous in that Notification Services will scale as well as SQL Server itself scales. Also, by using the SQL Server Event Provider, you can retrofit notification functionality into an existing application without touching much, if any, of that application’s code: The connection lies in the database itself.

Building a Notification Services application
Using the built-in components, the process of building a Notification Services app is fairly straightforward. You’ll need some kind of front end that allows users to manage their subscriptions: A Web app would probably make the most sense. The front end uses the subscription management API, which is exposed as both .NET classes and COM interfaces, to register users for notification of various events.

You’ll also need to create a few XML configuration files to define the service. In the config file, you specify the name of the service, the name of the hosting SQL Server, the notification channel the service should use, and some basic information about the application. The application definition file, or ADF, configures the SQL Server tables and procedures needed to support the service and controls a variety of performance settings, such as how often the Generator and Distributor run, how many items they process per run, rules for logging, how the service should behave if it falls behind in event checking or notifications, and how often old event and notification data should be deleted.

And that’s it. All you do then is create and register your new service with the NSControl Create and NSControl Register commands. If you want to use custom event providers or notification channels, there’s more to it, of course, but using the built-in components, you’re likely to spend more time building the subscription management application than building the actual service.

More information at MSDN
SQL Server Notification Services doesn’t really do anything new; developers have been writing notification applications of all stripes for years. It does, however, make creating these apps remarkably easy, and it uses technology that most Microsoft developers are already very familiar with. If you’re interested in learning more, Microsoft’s MSDN Web site has a full section of resources for getting started with Notification Services, including a walk-through showing you how to create a simple stock price notification service.
0 comments

Editor's Picks