//
you're reading...
SQL Server

What is SQL Server Notification Services

Notification Services is one of the features included in SQL Server 2005. As the name implies, Notification Services are services which send notifications to the interested entities based on what they would like be notified on. The addition of Notification Services to SQL Server 2005 has inherent advantages as most of the notifications are based on data changes (or additions). Combining Notification Services with SQL Server makes applications more scalable without hurting performance. To put it in simple terms, Notification Services are services that check if any event has occurred on the specified data, check if any entity has subscribed to be notified when that event occurs, and sends the notification to that entity. It looks simple, but the architecture of Notification Services makes it so flexible that you no longer have the pains of implementing features such as polling events, scheduling, formatting and delivery of notifications. These features are built in to Notification Services for easy integration and development.

For example, I would like to be notified by email/SMS or when stock prices of a particular company rise or fall below a pre-determined mark. Another example, I want my cell phone to play a different tune when my flight gets delayed and timings have been changed. There are hundreds of situations we come across and want our lives to move faster than ever. That’s where Notification Services comes into picture.

Architecture

The following terms need an introduction to understand the architecture.

  • Event: An event is an action that occurred affecting the specified data.
  • Subscriber: Subscriber is an entity interested in being notified when an event occurs.
  • Subscription:Subscription is an act by which subscriber describes when and what he wants to be notified as. (Subscriber specifies that he wants to be notified when stock price goes more than $100.)
  • Notification: Notification is a mode/channel of communication. Email, SMS, data files are a few examples.

Notification Services consists four (4) major building blocks: Subscription, Events, Generator, Delivery (notifications). Subscription data can be added using (SMO) Subscription Management Objects to Notification Services application. Events get populated to events table with the help of Event providers. Once events are populated into events table, the generator wakes up (it depends on settings here – generator can be event driven or schedule based) and starts processing rules. The rules are attached with subscriptions and the generator checks to see if any events match them. If matches are found, the generator starts creating notifications and fills the Notifications table. Once notifications arrive in Notifications table, the distributor (again based on scheduling configuration) wakes up and starts processing each notification, formats it and sends them using specified channel.

Adding Subscription Data:

Subscription as explained above is a rule/ set of rules that specify what types of events the subscriber is interested in. Notification Services exposes a set of managed API’s to add subscriber and subscription data.

Subscription management application can be any ASP.NET application or a Windows application which accesses the Notification Services Service (the database) and adds subscriber, subscriber device and subscription information. Subscriber information is generally a unique ID (string usually), where device information includes delivery device type (Cell phone or PC) and device address (ex: phone number). Subscription data depends on the type of Notification Services application. For a stock notifying application, subscription data might include symbol of the company. Microsoft.SqlServer.NotificationServices namespace includes set of objects to add this information.

When configuring a Notification Services application, a developer needs to specify the schema of Subscription table in a configuration file (known as Application Definition File – ADF. Check ADF section for more information). Along with the schema of subscription table, event rules must be specified. These event rules are nothing but SQL statements or Stored procedure calls which populate Notification tables. Generally, these rules are insert statements created by joining subscription and event tables, to fill up notification tables. Each event rule must be mapped to an event class indicating that subscription is based on that event table.

Collecting Events:

Events are collected and inserted into event tables using Event Providers. Notification Services provides FileSystemWatcher, SQL Server and Analysis Services as Standard event providers. Developers can extend and write custom event providers which insert into events table. The structure of the events table should be specified in the ADF file before creating the Notification Services application. Providers also need to be specified in the Providers tag in the ADF file. The FileSystemWatcher event provider watches a particular Windows folder, for a particular type of file. When event data is available, it starts processing the file and puts them into events table.

Note: If event data is expected in XML format, the file schema must be specified in order to be processed.

Notifications Configuration:

The Generator evaluates subscriptions on event data and generates notifications into the Notifications table. The schema of the Notifications table must be specified in the Notifications tag of the ADF file. Each notification class can be attached with a set of content formatters. A content formatter is used when distributor tries to format the notification into an end result (ex: a content formatter can be an XSLT file: the input to XSLT file is an XML of structure <notifications><notification><fields/>..). Finally a notification should be added with at least one delivery protocol (ex: File, SMTP, etc.).

Application Definition File (ADF):

The ADF file is the main input to the Notification Services application. It contains the complete information which is required to create a Notification Services application. The structure of Notification Services includes a Notification Services instance and each Notification Services instance can contain one or more Notification Services application. NSControl is the command line utility to create, update, and delete Notification Services instances and applications. If you use the SQL Server 2005 beta, you can use the SQL Server Management Studio to create Notification Services instances (check MS-Help on how to create Notification Services instance and applications).

To explain how Notification Services work, I am going to take an MS-Help sample and explain the code.

Big Shot Sample:

The Big Shot sample is a simple Notification Services sample application. Theoretically people would be interested in some executive’s (aka “big shot”) activities and would like to be notified when he is free or on a business trip. For example, say I would like to meet Mr. President and I would like to know when he is free for next week. All notifications should be sent to my in-box. Though this sample might seem bit odd (peeping into someone’s personal life), lets assume this big shot is publicly accessible.

Events

The events table should contain Name, Nick-Name, Action and Description. Action indicates the type of action and description explains the action.

<EventClasses>
       < EventClass >
              <EventClassName>BigShotEvents</EventClassName>
              < Schema >
                     < Field >
                           <FieldName>Name</FieldName>
                           <FieldType>nvarchar(30)</FieldType>
                     </ Field >
                     < Field >
                           <FieldName>NickName</FieldName>
                           <FieldType>nvarchar(6)</FieldType>
 
                    </ Field >
 
                    < Field >
                           <FieldName>Action</FieldName>
                           <FieldType>nvarchar(255)</FieldType>
                     </ Field >
                     < Field >
                           <FieldName>Description</FieldName>
                           <FieldType>nvarchar(3500)</FieldType>
                     </ Field >
               </ Schema >
              < IndexSqlSchema >
                     <
SqlStatement>CREATE INDEX BigShotEventsIndex ON BigShotEvents ( NickName )</SqlStatement>
              </ IndexSqlSchema >
        </
EventClass>
</
EventClasses>

The above creates NSBigShotEventsEvents table in the database (when the Notification Services instance is created). Event Provider is the one that inserts into this table. The following part of the ADF specifies the provider configuration.

Providers

This part of the XML creates two (2) event providers for our application. One is FileSystemWatchProvider, which watches on the specified directory. The schemafile argument is required and it should be the same as the BigShotEvents table schema. %_NSSystem_% is a parameter specified in the app.config file. The other provider is the SQL Server provider, which gives us a T-SQL statement to fetch events data. The FileSystemWatchProvider is event based; meaning as soon as data is pasted into the watch folder, the Generator starts its work, where as the SQL Server provider is configured as schedule based. Check the schedule interval tags specified under this host provider. Every 15 seconds the generator executes the select statement and fills the events table with the returned output. So this kind of select statement (without where clause) would fill up the events table and generate notifications that are already generated for every 15 seconds. Avoid these kinds of statements.

<Providers>
      < HostedProvider >
            <ProviderName>FlightEP</ProviderName>
            <ClassName>FileSystemWatcherProvider</ClassName>
            <SystemName>%_NSSystem_%</SystemName>
            < Arguments > 
                  < Argument > 
                        <Name>WatchDirectory</Name>
                        <Value>%_EventsDir_%</Value>
                  </ Argument > 
                  < Argument > 
                        <Name>SchemaFile</Name>
                        <
Value>%_BaseDirectoryPath_%AppDefinition EventsSchema.xsd</Value>
                  </ Argument > 
                  < Argument > 
                        <Name>EventClassName</Name>
                        <Value>BigShotEvents</Value>
                  </ Argument > 
            </ Arguments > 
      </ HostedProvider > 
      < HostedProvider > 
            <ProviderName>BigShotSQL</ProviderName>
            <ClassName>SQLProvider</ClassName>
            <SystemName>%_NSSystem_%</SystemName>
            < Schedule > 
                  <Interval>P0DT00H00M15S</Interval>
            </ Schedule > 
            < Arguments > 
                    < Argument > 
                        <Name>EventsQuery</Name>
                       <
Value>Select Name, NickName, Action, Description From BigShotActions</Value>
                  </ Argument > 
                  < Argument > 
                        <Name>EventClassName</Name>
                        <Value>BigShotEvents</Value>
                  </ Argument > 
            </ Arguments > 
      </ HostedProvider >
</Providers>

Subscriptions:

Our application allows subscribers to poll for events based on the big shot nick-name and action. So the ADF file subscriptions section looks like this.

<SubscriptionClasses>
      < SubscriptionClass >
      <SubscriptionClassName>BigShotSubscriptions</SubscriptionClassNam
      < Schema >
            < Field >
                  <FieldName>DeviceName</FieldName>
                  <FieldType>nvarchar(255)</FieldType>
            </ Field >
            < Field >
                  <FieldName>SubscriberLocale</FieldName>
                  <FieldType>nvarchar(10)</FieldType>
            </ Field >
            < Field >
                  <FieldName>NickName</FieldName>
                  <FieldType>nvarchar(6)</FieldType>
            </ Field >
            < Field >
                  <FieldName>Action</FieldName>
                  <FieldType>nvarchar(255)</FieldType>
            </ Field >
      </ Schema >
      < IndexSqlSchema >
            <
SqlStatement>CREATE INDEX BigShotSubscriptionsIndex ON BigShotSubscriptions ( NickName, Action )</SqlStatement>
      </ IndexSqlSchema >
       < EventRules >
            < EventRule >
                  <RuleName>BigShotSubscriptionsEventRule</RuleName>
                  < Action >
                        INSERT INTO BigShotNotifications(
                          SubscriberId,
                         DeviceName,
                           SubscriberLocale,
                           Name, 
                          NickName,
                         Action,
                         Description
                       )
                        SELECT
                          S.SubscriberId,
                          S.DeviceName,
                          S.SubscriberLocale,
                          E.Name,
                          E.NickName,
                          E.Action,
                          E.Description
                        FROM
                          BigShotEvents E, BigShotSubscriptions S
                        WHERE
                           E.NickName = S.NickName
                        AND
                        (E.Action = S.Action or S.Action IS Null)
                  </ Action >
                  <EventClassName>BigShotEvents</EventClassName>
            </ EventRule >
      </ EventRules >
    </
SubscriptionClass>
</
SubscriptionClasses>

The above indicates what columns the NSBigShotSubscriptionsSubscriptions table includes. General practice is to include device name, subscriber locale (for language support) and any other application specific columns. You can also create indexes on these tables. The EventRules node performs inserts into the Notifications table based on a mapping between Events and Subscriptions table.

Notifications Formatting and Delivery

The Notifications section in the ADF describes the type of formatting and delivery for the notifications. In our example, I have configured to use the XSLT formatter with File and SMTP delivery. Notification Services creates a table named NSBigShotNotificationsNotifications with the specified schema.

<NotificationClasses>
<
NotificationClass>
    <
NotificationClassName>BigShotNotifications</NotificationClassName>
      < Schema >
            < Fields >
                  < Field >
                        <FieldName>Name</FieldName>
                        <FieldType>nvarchar(30)</FieldType>
                  </ Field >
                  < Field >
                        <FieldName>NickName</FieldName>
                        <FieldType>nvarchar(6)</FieldType>
                  </ Field >
                  < Field >
                        <FieldName>Action</FieldName>
                        <FieldType>nvarchar(255)</FieldType>
                  </ Field >
                  < Field >
                        <FieldName>Description</FieldName>
                        <FieldType>nvarchar(3500)</FieldType>
                  </ Field >
            </ Fields >
      </ Schema >
      < ContentFormatter >
            <ClassName>XsltFormatter</ClassName>
            < Arguments >
                  < Argument >
                        <Name>XsltBaseDirectoryPath</Name>
                        <Value>%_BaseDirectoryPath_%AppDefinition</Value>
                  </ Argument >
                  < Argument >
                        <Name>XsltFileName</Name>
                        <Value>NoOp.xslt</Value>
                  </ Argument >
            </ Arguments >
      </ ContentFormatter >
      <DigestDelivery>true</DigestDelivery>
      < Protocols >
            < Protocol >
                  <ProtocolName>File</ProtocolName>
                  < Fields >
                        < Field >
                              <FieldName>Name</FieldName>
                              <FieldReference>Name</FieldReference>
                        </ Field >
                        < Field >
                              <FieldName>NickName</FieldName>
                              <FieldReference>NickName</FieldReference>
                        </ Field >
                        < Field >
                              <FieldName>Action</FieldName>
                              <FieldReference>Action</FieldReference>
                        </ Field >
                        < Field >
                              <FieldName>Description</FieldName>
                              <FieldReference>Description</FieldReference>
                        </ Field >
                  </ Fields >
            </ Protocol >
</
NotificationClass>
</
NotificationClasses>

Any additional settings such as directory paths should be included in the app.config file, which will also have a reference to the ADF file.

Summary

To summarize, Notification Services is such a robust and powerful technology. It will make lot of business processes simple and ease to maintain. What we haven’t covered in this article is Creating Custom Event Provider, Custom Formatters and Custom Delivery.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

Comments are closed.

%d bloggers like this: