eople receive many notifications daily: weather updates, news updates, updates about stock prices, or a current account balance via a SMS message. But how can you build such notification applications on the Microsoft platform? You have two options: the first and less palatable is to build everything from scratch. The second and more attractive option is to use the SQL Server 2005 Notification Services (SQLNS), which is a new API intended to help you build notification applications.

SQLNS?can generate and deliver notifications to thousands of end users of your application in several different ways and without any regard for the local time of the machine receiving the notification. One of the best known delivery protocols for notifications is e-mail, because people are used to working with emails constantly. But with SQLNS you can not only use e-mail notifications, but also provide notifications via SMS, pagers, or the Windows Messenger client. If none of those options seem palatable, you can also create your own delivery channel.

Architecture
SQLNS is based on four components that form the heart of an SQLNS application. SQLNS applications are always hosted inside the SQL Server or inside a Windows Service. Table 1 describes these four main components.

Table 1. The table lists the four main components required for an SQLNS application and provides a description for each.

	?
Figure 1. The core components of a SQLNS application.

Figure 1 shows how you combine these components in an SQLNS application.

SQLNS executes the following steps whenever it generates a new notification and sends it to registered subscribers:

• Subscribers create subscriptions with a subscription management application, such as an ASP.NET or Windows Forms front end. Subscription management is the only part of an SQLNS application where users or applications are actively involved in the process. You generate subscriptions using a managed assembly shipped with SQLNS.
• The Notification Application collects events, which are processed within the SQLNS application.
• SQLNS uses T-SQL statements to assign events to the subscriptions registered in the first step.
• SQLNS generates new notifications whenever it finds an assignment.
• A Formatter formatsthe new notification, and SQLNS delivers it through a delivery channel to the registered target device of the subscriber.

As you can see from the preceding list of actions, SQLNS applications run completely within SQL Server except for the subscription registration process itself.

SQLNS Configuration Files
To tie all these things and components together everything must be configured through a pair of XML-based configuration files: the Instance Configuration file and the Application Definition file.

To host a SQLNS application you need an instance of SQLNS. An SQLNS instance can host one or more SQLNS applications. The instance also stores subscriber information, which can be shared among all SQLNS applications hosted on an instance. The Instance Configuration File registers each SQLNS application for that instance. Here’s an example of an Instance Configuration file.

            DM               Localhost                                                   StockPrice                                       C:MyPath                                  appADF.xml                                                                     FileChannel                              

Table 2 shows the most important XML elements used in this configuration file.

Table 2. The table shows a description of each main element in an Instance Configuration file.

Within the section, you need an section for each application hosted on this instance. Within each element, you configure the XML elements shown in Table 3

Table 3. The table shows the XML elements needed within each element in the Instance Configuration file.

The Application Definition File
The Application Definition File is more interesting. You use this file to configure the whole SQLNS application structure along with all necessary metadata. Listing 1 shows a minimal Application Definition File.

As you can see from Listing 1, the sample SQLNS application supports only one subscription, named StockPriceSubscription. This subscription contains only one relevant bit information?the StockSymbol?stored in a table column, which gives users of the front-end application the capability of entering the stock codes that they’re interested in. SQLNS uses this configuration file to create all the tables required for the SQLNS application.. You’ll see more about this feature later.

Through the section you can define the structure of the notifications your application generates. That section’s empty in Listing 1, but you’ll define it later. Currently the configuration file defines only that you’re going to use the XsltFormatter to format the notifications. You use the Generator to generate the notifications and the Distributor to deliver those notifications to registered users. The and sections define the computers on which those components are running, making it possible to distribute SQLNS application components over several physical computers for scalability.

Using NSControl.exe
After configuring the SQLNS instance and application you must create all the necessary databases and tables specified in the configuration files. SQLNS provides a command utility tool called nscontrol.exe to simplify the process. Table 4 describes the nscontrol.exe options.

Table 4. The table lists the command-line options available for nscontrol.exe.

For example, to create a new instance of SQLNS you call nscontrol.exe with the Create option and supply the name of the Instance Configuration file as a parameter to the call as follows:

   nscontrol.exe -Create -In Instance.xml 

The In parameter specifies the instance configuration file that nscontrol.exe should process. When you make this call, nscontrol.exe creates a new SQLNS instance and registers the SQLNS application with that new instance. In addition, it creates the instance database DMNSMain and the application database DMStockPrice with the structure specified in the Application Definition File. These database names are created automatically by nscontrol.exe and can’t be customized in any configuration file.

Defining Events
To make use of SQLNS you need to define events that SQLNS will process to produce notifications. SQLNS processes events from an EventProvider, which writes the new events to the event table. You need to define Event classes that process new incoming events. You define the Event classes in the Application Definition file in the section. Listing 2 shows the Event class definition for the sample application.

Listing 2 defines only defining one event, called StockEvt. This event consists of the properties StockCode, ExchangeCode and Price. When you create or update the application using nscontrol.exe, it creates a new event table for use by the SQLNS application. To process events you need one additional component?an Event Provider. Event Providers pass new events to SQLNS for processing. You configure Event Providers through the section in the Application Definition file as illustrated by the following XML fragment. You can place the node anywhere in the configuration file.

                              StockEP                              FileSystemWatcherProvider                              localhost                                            WatchDirectory              C:Events                                       SchemaFile               Schema.xsd                                     EventClassName              StockEvt                             

The section shown above defines an Event Provider named StockEP that uses the built-in FileSystemWatcherProvider. This provider monitors a directory for new files. For example, in the sample application scenario another application periodically places a new file into the WatchDirectory, defined as C:Events. The StockEP provider watches this directory, processes the event, and writes a new record into the event table. After adding the section to the Application Definition file, you must update the SQLNS application using nscontrol.exe.

   nscontrol.exe --Update --in appADF.xml

Because the FileSystemWatcherProvider is a so-called Hosted Provider (the other option is a Non-hosted Provider), you must activate the Service Mode on the SQLNS instance. A Hosted Provider is hosted within a SQLNS instance and a Non-hosted Provider can be hosted in your own application. When you do that, the SQLNS instance runs as a normal Windows Service and monitors the specified directory for new incoming events in the form of XML files.

   nscontrol.exe --Register name DM --server       localhost --service

When you execute this command nscontrol.exe creates and starts a new Windows Service named NS$DM. SQLNS always adds the prefix NS$, which indicates that this is a Windows Service used for SQLNS. You can’t change the service name prefix using configuration. Because the service is a normal Windows Service you can administer it through the standard MMC Services snap-in.

The section shown above also specifies a XSD schema. All new event files written in the event directory must be successfully validated against that XML schema file. Here’s the definition for the XML schema file used in the sample application.

According to that schema, an XML event file can have the following structure:

               MSFT      NYSE      60.54   

SQLNS processes those event files and writes a new record in the application’s event table, NSStockEvtEvents.

Defining Subscriptions
To send out notifications SQLNS must know:

• Who should receive notifications
• What information to send
• The target device to which the notification should be sent

You set up this information using the Subscription Management Objects from a .NET application. The Subscription Management Objects are a set of .NET classes implemented in an assembly shipped with SQLNS. SQLNS stores subscription information in the instance database (not in the application database). This design decision makes it possible to use subscriber information in more than one SQLNS application.

Before you can manage subscriptions you must define a Subscription class in the Application Definition file. You also need to configure a Delivery Channel in the Instance Configuration file. The Subscription class stores information about the event, the target device, and the language in which the notification should be delivered. Listing 3 shows the Subscription class for the sample application.

Using this Subscription class, nscontrol.exe will create a new table when you use it to update the SQLNS instance. The column DeviceName stores the name of the device to which the SQLNS will publish the subscription. The column SubLocale defines the language in which SQLNS sends the notification. Using the StockCode, ExchangeCode, and TriggerVal columns, users can decide which stocks they want to be notified about when a change occurs. For example, a typical notification request might be: “I want to be informed about the stock MSFT (column StockCode) that trades at NYSE (column ExchangeCode) whenever as the stock price exceeds $7.00 (column TriggerVal).”

A Subscription Management application must be able to perform these three tasks:

• Create a new subscriber
• Specify the target device for the new subscriber
• Create a subscription for the new subscriber

To communicate with an existing SQLNS instance you need a reference to it. The sample application accomplishes this using the NSInstance object. This object’s constructor receives a parameter containing the instance name of the SQLNS instance to manage. The SubscriberDevice class represents the subscriber’s target device, and the Subscription class represents a subscription. With all this information in hand, it’s very easy to manage all three tasks. Listing 4 shows the required code in C#. You can use this same code in either an ASP.NET or Windows Forms application.

The most interesting method is AddSubscription. That method creates a new Subscription instance, then uses its SetFieldValue method to set the various properties of the Subscription. SQLNS uses this information to generate notifications from newly received events.

Generating Notifications
After adding subscriptions to the database you can begin generating notifications from new incoming events. Remember that SQLNS generates notifications by comparing new events to the stored subscription information. More accurately, it just performs a simple T-SQL JOIN statement (the Notification Rule) between the Events and the Subscriptions table. The results of this JOIN are the notifications that SQLNS must send to the subscribers.

You define the structures of the resulting notifications through Notification Classes in the Application Definition file, in the section , as shown in Listing 5.

After adding the Notification class, and execute nscontrol.exe to update the SQLNS instance it creates the new notification table within the application database. The last thing you need to add is a Notification Rule. The following example shows where to place the Notification Rule in the Application Definition file.

   SubscriptionClass>                           EvtRule                                                         

For clarity, I’ve included the Notification Rule in a separate code fragment below:

   INSERT INTO StockPriceNotifications      (SubscriberId, DeviceName, SubscriberLocale,       StockCode, ExchangeCode, Price)   SELECT s.SubscriberId, s.DeviceName, s.SubLocale,       e.StockCoce, e.ExchangeCode, e.Price    FROM StockEvt e, StockPriceSubscriptions s    WHERE   e.ExchangeCode = s.ExchangeCode AND       e.StockCode = s.StockCode AND       e.Price > s.TriggerVal

Be sure to escape all special characters (such as “>”) because you’re writing the Notification Rule in an XML file! Alternatively, you could put the Notification Rule in a stored procedure.

The preceding T-SQL statement inserts a new record into the notification table whenever SQLNS receives a matching event, then it hands all generated notifications over to the Formatter. The Formatter handles notification formatting. SQLNS ships with the XsltFormatter used in the sample application, but you’re free to write your own Formatter for special cases. Using the XsltFormatter you specify an XSLT stylesheet that formats the notification. You define the Formatter in the section of the Application Definition file.. Here’s an example.

                              XsltFormatter                                                               XsltBaseDirectoryPath                                                C:Stylesheets                                                                        XsltFileName                                                Tansformation.xslt                                             

After the notifications are formatted the last thing to do is send them to the subscribers. To do that, you specify a Delivery Protocol. SQLNS provides two built-in protocols, SMTP and File, which send notifications through e-mail or write them into a file. Developers often use the File protocol for testing purposes. You configure Delivery Protocols in the Instance Configuration File as follows.

                              FileChannel                              File                                                               FileName                                                C:Notifications.txt                                             

After adding the DeliveryChannel, run nscontrol.exe one last time, and the whole notification application should work. When you put a new event file in the watch directory SQLNS should write the resulting notification in the file C:Notifications.txt.

For further information about SQLNS you should read Shyam Pather’s book Microsoft SQL Server Notification Services. I hope this example has given you an overall introduction to SQLNS. And I urge you to experiment with and modify the sample configuration files to build your own applications.