Using SQL Server Notification Services
By Peter A. Bromberg, Ph.D.
Printer - Friendly Version
Peter Bromberg
SQL Server Notification Services is a platform for developing and deploying applications that generate and send notifications to users. Notifications are personalized, timely messages that can be sent to a wide variety of devices.

Notifications reflect the preferences of the subscriber. The subscriber enters a subscription to express an interest in information. For example, "notify me when there is a new article on eggheadcafe.com about .NET" or "notify me when the strategy document my team is writing is updated."

A notification can be generated and sent to the user as soon as a triggering event occurs. Or, a notification can be generated and sent on a predetermined schedule specified by the user. The user's subscription specifies when the notification should be generated and sent to the user.

Notifications can be sent to a wide variety of devices. For example, a notification can be sent to a user's cellular phone, personal digital assistant (PDA), Microsoft Windows Messenger, or e-mail account. Because these devices often accompany the user, notifications are ideal for sending high-priority information.



Notification Services consists of:

  • A simple yet powerful Notification Services programming framework that enables you to quickly create and deploy notification applications. The programming framework is based on XML and Transact-SQL.
  • A reliable, high-performance, scalable engine that runs notification applications. The engine is built on the Microsoft .NET Framework and Microsoft SQL Server 2000.

How Does Notification Services Work?

A Notification Services application collects events and subscriptions, generates notifications, and then distributes the notifications to external delivery services, such as a Simple Mail Transfer Protocol (SMTP) server. Notification Services accomplishes these tasks as follows:

  • Notification Services stores subscriber and subscription data in SQL Server databases. Using the subscription management objects, part of the Notification Services API, you can create a custom subscription management application to manage subscriber and subscription data.
  • Using event providers, Notification Services collects event data and stores the event data in the application's database. For example, the file system watcher event provider monitors a directory for XML event data. Using this event provider, you can drop XML event files in the directory, and the event provider reads the XML events and submits them to the application database.
  • Event providers can be run by the event provider host component, or can run independently of Notification Services. For more information, see Event Collection Architecture.
  • The generator matches subscriptions and events and generates notifications. The generator runs on a regular interval defined for the application, ensuring that subscriptions are evaluated on a regular basis. The application developer writes Transact-SQL queries that determine how subscriptions are evaluated and what information goes into the notifications.
  • The distributor formats notifications and sends them to subscribers using one or more delivery services. The application developer specifies the transformation from raw data to a formatted notification using a content formatter such as XSLT.
  • The Notification Services platform uses the NS$instance_name service to run notification applications. This service, the primary component of the Notification Services engine, runs the three internal functions: the event provider, the generator, and the distributor.

To send notifications, a Notification Services application must have information about subscribers, the information that the subscribers are interested in, and information about where to send the information. Subscription management is the process of managing this subscriber, subscription, and subscriber device data.

Subscription management is handled by a custom subscription management application written by the application developer. This application, which can be a Web application or standard Microsoft Windows application, writes the subscriber, subscription, and subscriber device data to the proper databases. You use subscription management objects supplied with Notification Services to simplify the application development process.

To get started with SQL Server Notification Services, download the Release version here, and run the installer program.

Installing and running Sample Applications

The easiest way to learn how to use SQL Server Notification Services is to install and examine the provided sample applications. After you install SQL Server Notification Services, your Start Menu will contain a "Samples" subgroup under the main Microsoft SQL Server Notification Services Item, with instructions on how to set up the samples.

The SetupSamples.cmd batch file usage is as follows:

Usage: SetupSamples.cmd
ServiceUsername ServicePassword
SqlServerInstance
[ServiceSqlServerUserName] [ServiceSqlServerPassword]
[SqlServerAdminName]

ServiceUsername and ServicePassword: User name and password the
Notification Services Windows service runs under. This account must
have the permissions outlined in the documentation. If you change the
user name or its associated password, you must run NSControl Register
to apply the changes.

Note: Since passwords can contain non-alphanumeric characters,
it is a good idea put passwords in quotation marks.

SqlServerInstance specifies the name of the SQL Server instance that
is hosting the sample databases. If using the default SQL Server
instance, specify the computer name; otherwise, specify both the
computer name and instance name in the form computer\instance.

ServiceSqlServerUserName and ServiceSqlServerPassword: Optional. If you
use SQL Server authentication to connect to SQL Server, this is the
SQL Server user name and password that the service uses to access
the sample databases. If you do not specify ServiceSqlServerUserName,
the account specified by ServiceUserName is used to connect to SQL Server.

SqlServerAdminName is the account used by SetupSamples to run SQL Server
tools, such as osql. Specify this parameter only if your SQL Server
administration account uses SQL authentication. If this value is not
supplied, Windows authentication is used.

Example 1: Run the service under a domain account. The service will access a
named instance of SQL Server using SQL authentication. OSQL will
use the sa account.
SetupSamples "domain\serviceaccount" "serviceaccountpassword" MACHINE\InstanceName "sqluser" "sqluserpassword" sa

Example 2: Run the service under a local account. The service will access the
default instance of SQL Server using integrated authentication, as
will OSQL:
SetupSamples "MACHINE\serviceaccount" "password" yourpasswd


NOTE: The documentation on how to run the "SetupSamples.cmd" is particularly sparse, and unless you are very familiar with batch files, you may want to create a separate "Test.bat" file containing the arguments to the SetupSamples.cmd" batch file. For a default instance of SQL Server, here is one that worked for me:

SetupSamples "machineadmin" "password" %COMPUTERNAME% "machineadmin" "password" "machineadmin" "password"

You will still get prompted for the password again from OSQL, however as long as the "machineadmin" account you use is also a valid login account in SQL Server, has Admin privileges to the Master database, and carries "GRANT" permissions, the setup should complete fine.

The only other action you will need to do is start the Windows Service for the sample you wish to test (e.g., "NS$StockInstance") , and navigate to the sample test page for that sample to test it out.

The samples include a sample subscription management application which allows you to configure events and their parameters (e.g., alert when MSFT goes over $60 and send user a report at 4:30 PM EST) and enable subscribers to subscribe to different events. The methods of notification are flexible and highly configurable.

Samples Workflow

All the sample code is in Visual Studio .NET Projects, making the SQL Server Notification Services infrastructure easier to understand and debug. Each sample has a specific workflow to illustrate the potential uses of the service.

Here is an abstraction of the workflow for the Stock Notification Service project:

  • On the subscription management page, you enter a stock symbol to be tracked. Clear the "Include this stock in a daily summary" check box, specify a trigger price for the stock, and then click Alerts Signup. This will create an event-driven subscription.
  • Select the "Include this stock in a daily summary check box" , specify a time zone and time, and then click Alerts Signup. This will create a scheduled subscription.
  • On the event submission page, submit stock price data for the stock you have entered.
    This page uses the WebEventCreator event provider to write the event data to the application database.
  • The event chronicle rule adds and updates stock values in the chronicle table, so that each stock's highest value for the day is recorded.
  • For the event-driven notifications, the Notification Services generator processes the subscription event rule, which joins the event chronicle data with subscription data, based on the following conditions:
     --The stock price in the event chronicle table is greater than or equal to the stock price in the subscriptions table.
     --The stock symbol in the event chronicle table is equal to the stock symbol in the subscriptions table.
  • For the scheduled notifications, the Notification Services generator processes the subscription scheduled rule at the time specified for the scheduled subscription. Subscription and event chronicle data is matched based on the stock symbol in the chronicle table matching the stock symbol in the subscription table. The sum of each stock value is generated to provide the notification information.
  • A notification is created for each match found.
  • The Notification Services distributor formats notifications using the Notification Services XSLT content formatter and the Application.xslt file. By default, the Notification Services distributor sends all notifications using the HttpLoggerChannel delivery channel, which uses the custom HttpLogger delivery protocol.

    HttpLogger posts the notification data to the URL specified in the HttpLoggerChannel delivery channel definition in the appConfig.xml file. The default URL is http://localhost/NSSamples/Stock/HttpLogger.aspx. The code for this page is available in Samples\Stock\Subscribe\HttpLogger.aspx.cs.
  • You can test the HttpLogger.aspx page by loading it in your browser and clicking Test Post.
  • You can change the subscriptions to use the File delivery protocol by editing the imgbtnAlertsSignup_Click method in Default.aspx.cs. If the File delivery protocol is used, the notifications are written to InstallPath\Microsoft Notification Services\VersionNumber\Samples\Stock\Test\Notifications\FileNotifications.txt.
  • The sample code does not provide a method to update a subscriber's delivery information. Once a subscriber has been entered in the database you must remove that subscriber to update her delivery preference. The update functionality could be added to the user interface for a production application.

Testing the Samples

In order for actual notifications to complete the delivery process based on the protocol(s) you want, you must configure and build the CustomDeliveryProtocol project first.

You can generate sample notifications by using the test data and the RunSample.cmd script. To do this, you can open a command prompt window, navigate to the specific sample subdirectory (InstallPath\Microsoft SQL Server Notification Services\VersionNumber\Samples\Stock), and then run the script. This action loads subscriber, subscription, and event data into the system; generates notifications based on it; and then formats the notifications and writes them to a text file. Some notifications are written to the HttpLogger.aspx page, and some are written to InstallPath\Microsoft Notification Services\VersionNumber\Samples\Stock\Test\FileNotifications.txt. The delivery of notifications using the HttpLogger fails if the CustomDeliveryProtocol project has not been built.

A successful test script run of the Stock Notification Service would look like the following:

Verifying that the instance is registered
Microsoft Notification Services Control Utility (Enterprise) 2.0.2114.0
Copyright (C) Microsoft Corporation 2002. All rights reserved.

Instance "StockInstance":
Database Server: PETER
Notification Services version: 2.0.2114.0
SQL Username: Encrypted
SQL Password: Encrypted
Verifying that the instance databases exist

Creating event directory and deleting any existing event files

Creating notifications directory and deleting existing notifications

Deleting any existing subscribers
Password:
(19 rows affected)
(0 rows affected)
(17 rows affected)

Starting the service
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.


Enabling the instance so subscribers can be inserted and
notifications can be generated
Microsoft Notification Services Control Utility (Enterprise) 2.0.2114.0
Copyright (C) Microsoft Corporation 2002. All rights reserved.

Status of instance "StockInstance":

Subscribers: Enabled

Application: Stock
Hosted event providers
StockEP (PETER): Enabled

Non-hosted event providers
WebEventCreator : Enabled

Generator
StockGen1 (PETER): Enabled

Distributors
StockDist1 (PETER): Enabled

Subscriptions: Enabled

Windows services
NS$StockInstance (PETER): The service is running

Loading subscribers and subscriptions using VBScript
Subscribers successfully added.
Subscriptions successfully added.

Loading events in the system by copying XML file
1 file(s) copied.

Give the service 60 seconds to generate notifications...

 

Your "Raw" notifications (Before they are formatted and sent out over the configured protocols such as SMTP, Messenger, etc. would look like this:

POST /NSSamples/Stock/HttpLogger.aspx HTTP/1.1
Content-Length: 467
Content-Type: text/html;charset=iso-8859-1
Expect: 100-continue
Host: localhost

subject=Stock notification: Nov 23 2002 3:56PM
subscriberid=stephanie
escaping=yes
Text=<html>
<body>
<b>AWKS</b> is now trading at: <b>$67.68</b><br />
Click here to see the quote detail:
<a href="http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&amp;Symbol=AWKS">
http://moneycentral.msn.com/scripts/webquote.dll?ipage=qd&amp;Symbol=AWKS</a><br /><br />
<i>Thank you for using SQL Server Notification Services.</i><br /><br /></body>
</html>

Summary

SQL Server Notification Services is an advanced, highly programmable workflow generation, subscription, notification and management system utilizing the proven framework of SQL Server 2000 and the .NET Framework. Developers now have a platform that will make a multitude of business case problems much more easy to solve in a reliable, productive manner and in such a way as to potentially reduce the total cost of ownership of these solutions. Developers would be well - advised to learn and study this new platform carefully, and to become competent in its use as quickly as possible.


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.