Get Started Using SQL Server 2005 Notification Services
Because of the complex configuration options required to write SQL Server 2005 Notification
Services (SQLNS) applications, it can be difficult to get started, but after you've learned the concepts
and ideas behind SQLNS, you'll find yourself able to write great applications that are both scalable
and extensible using common interfaces.
People 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.
Comp Description
onent
Subscr A subscriber is a person or an application who
iber wants to receive notifications.
Subscr A subscription is a request for a notification
iption and can be generated from an application
through the SQLNS API. A typical example is
the change of a stock price.
Event An event is an information, in which a
subscriber is interested in. As soon as a stock
price has changed an event can be generated
that's processed within SQLNS.
Notific The notification component is a notification
ation which is send to the registered subscriber
through a defined delivery channel.
Figure 1. The core components of a SQLNS
Figure 1 shows how you combine these components application.
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.
<?xml version="1.0" encoding="utf-8" ?>
<NotificationServicesInstance
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/Con
figurationFileSchema">
<InstanceName>DM</InstanceName>
<SqlServerSystem>
Localhost
</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>
StockPrice
</ApplicationName>
<BaseDirectoryPath>
C:\MyPath
</BaseDirectoryPath>
<ApplicationDefinitionFilePath>
appADF.xml
</ApplicationDefinitionFilePath>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>
FileChannel
</DeliveryChannelName>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>
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.
XML element Description
InstanceName The name of this SQLNS instance.
SqlServerSystem The name of the SQL Server that hosts the SQLNS instance database.
DeliveryChannel Describes the several delivery channels supported by this SQLNS instance.
Within the <Applications> section, you need an <Application> section for each application hosted
on this instance. Within each <Application> element, you configure the XML elements shown in
Table 3
Table 3. The table shows the XML elements needed within each <Application> element in the
Instance Configuration file.
XML element Description
ApplicationName Name of the SQLNS application hosted on this SQLNS instance.
BaseDirectoryPath Base directory of the SQLNS application. Here are stored all
application relevant information like the Application Definition File.
ApplicationDefinitionFilePat Name and path to the Application Definition File that configures the
h SQLNS application.
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 <NotificationClass> 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 <Generator> and <Distributors> 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.
Option Description
Create Creates a new instance of SQLNS.
Delete Deletes an existing instance of SQLNS.
Disable Disables a SQLNS instance.
Enable Enables a SQLNS instance.
Register Registers a new instance of SQLNS.
Unregister Unregisters a instance of SQLNS.
Update Updates an existing instance of SQLNS.
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 <EventClasses> 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 <Providers> section in the
Application Definition file as illustrated by the following XML fragment. You can place the
<Providers> node anywhere in the configuration file.
<Providers>
<HostedProvider>
<ProviderName>
StockEP
</ProviderName>
<ClassName>
FileSystemWatcherProvider
</ClassName>
<SystemName>
localhost
</SystemName>
<Arguments>
<Argument>
<Name>WatchDirectory</Name>
<Value>C:\Events</Value>
</Argument>
<Argument>
<Name>SchemaFile</Name>
<Value>Schema.xsd</Value>
</Argument>
<Argument>
<Name>EventClassName</Name>
<Value>StockEvt</Value>
</Argument>
</Arguments>
</HostedProvider>
</Providers>
The <Providers> 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 <Providers> 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 <Providers> 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.
<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql=
"urn:schemas-microsoft-com:mapping-schema">
<xsd:element
name="event"
sql:relation"StockEvt">
<xsd:complexType>
<xsd:sequence>
<xsd:element
name="StockCode"
type="xsd:string" />
<xsd:element
name="ExchangeCode"
type="xsd:string" />
<xsd:element
name="price"
type="xsd:decimal"
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
According to that schema, an XML event file can have the following structure:
<?xml version="1.0" encoding="utf-8" ?>
<event
xmlns="http://tempuri.org/
XMLSchema.xsd">
<StockCode>MSFT</StockCode>
<ExchangeCode>NYSE</ExchangeCode>
<Price>60.54</Price>
</event>
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 <NotificationClasses>, 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>
<EventRules>
<EventRule>
<RuleName>EvtRule</RuleName>
<Action>
<!—Insert Notification Rule here -->
</Action>
</EventRule>
</EventRules>
</SubscriptionClass>
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
<NotificationClass> section of the Application Definition file.. Here's an example.
<NotificationClass>
<ContentFormatter>
<ClassName>
XsltFormatter
</ClassName>
<Arguments>
<Argument>
<Name>
XsltBaseDirectoryPath
</Name>
<Value>
C:\Stylesheets
</Value>
</Argument>
<Argument>
<Name>
XsltFileName
</Name>
<Value>
Tansformation.xslt
</Value>
</Argument>
</Arguments>
</ContentFormatter>
</NotificationClass>
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.
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>
FileChannel
</DeliveryChannelName>
<ProtocolName>
File
</ProtocolName>
<Arguments>
<Argument>
<Name>
FileName
</Name>
<Value>
C:\Notifications.txt
</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
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.
