Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / SQL
Article

An Introduction to SQL Server Notification Services

Rate me:
Please Sign up or sign in to vote.
2.58/5 (9 votes)
2 Sep 20026 min read 173K   52   14
This article will provide you with enough information to peak your interest in one of Microsoft’s newest services designed to run under SQL Server 2000, and to take advantage of the .NET initiative.

Introduction

You might be asking “What is SQL Server Notifications Services?”. Well, that is a good question and one that will I hope be asked by lots more people soon. This article is meant to answer that question, and to provide you with enough information to peak your interest in one of Microsoft’s newest services designed to run under SQL Server 2000, and to take advantage of the .NET initiative.

To understand SQL NS, you first need to understand what it is for, or rather the problem it solves. Have you ever designed or worked on any system that was required to let someone or something know when certain conditions existed? For example, (old faithful analogy here) an application that tracks stocks, and the user who wants to be notified when XYZ corp. reaches 150 so he can cash in and buy that new yacht!

Even more common in reality, a system that runs on a schedule and the user who needs to be notified of problems or successful execution. As a career developer I have seen situations similar to these numerous times. Usually it requires either some obtuse custom development, or the purchase of some expensive third party monitoring software. What would be nice is something to hooks right into the core of my software, doesn’t cost a bundle, and is extensible enough that a developer can have a field day with it. Enter SQL Server Notification Services, The newest answer to the issues stated above. Using SQL NS, you can quickly provide notification capabilities to any application.

What it does

Basically, SQL NS takes instructions that are provided by the developers of the service that is needed, and uses those instructions to construct databases that it will use to manage notification. It also creates an instance of a Windows Service with a name that relates to notification application. These pieces of a notification application are all created by SQL NS; you don’t have to know the details of creating them at all. All the developer has to know is how to properly format the Application Definition File or ADF for short. The ADF is basically the instructions that SQL NS will use to create a notification service. I won’t go into too much detail here, but it is very easy to create an ADF and create a new service.

What’s in a notification service?

There are three major pieces to each notification service. Let’s briefly define and explain each.

Events

For a notification to be sent, there has to be some form of trigger. In SQL NS such a trigger is defined as an “Event”. Events can be triggered by certain actions, or they can be in time intervals, it’s up to the designer. All of the events for a notification service can be defined in the ADF for the service.

An example of an event might be that bob wants to know when his flowers were delivered to his girlfriends house, so he subscribes to the notification service that the flower shop provides. The flower shop has defined an event in their service that generates a notification when the delivery person calls in to confirm delivery. Once the event is generated, the notification is created and delivered to bob via SMS messaging.

An example of a timed event might be that a sale person from XYZ corp. needs an updated contact list each morning before he starts his day. An event is created in the notification services that creates a notification with his updated contact list and sends it to his mobile phone in the form of a smart messaging SMS message. With the press of a button, he accepts the message and his contact list is updated.

Events are created in many ways, most commonly they are pulled from SQL databases, or system logs. When the event is defined in the ADF the method for retrieving the event data is also defined.

Notifications

A notification is the actually data that is sent to a subscriber, it is basically a formatted form of the data that was retrieved when the event that goes with this notification was fired. The contents and format of the notifications that a service supports are defined in the ADF.

Microsoft has provided an XSLT content formatter to make our lives easier in respect to notifications. It allows us to apply an XSLT transform to the raw notification data. This allows us to easily prepare a notification for display to a subscriber.

Subscriptions

A subscriber is the actual real end user of the notification service, this is the guy who wants to be notified of his rising stock so he can buy his yacht. When SQL NS creates the notification service, it creates in SQL Server a database that is just for holding subscriber information. To add subscribers, the developer needs to create some sort of a subscription application, this may sound kind of like being left out cold, but that is not the case. The subscription application is left fully to the imagination of the designers of the service, but Microsoft has provided some tools that make it much easier to build. We don’t have to know the full ER model of the subscription database because we can simply utilize the managed components Microsoft has provided for us to add subscribers.

So, simply create an application for gathering the required information from a subscriber, and use the classes provided in the Microsoft.SqlServer.NotificationServices namespace. (note these are .NET classes)

Subscribers can choose the events they want to subscribe to, the format of the notifications they want, and even the media they want notifications sent to, and yes they can even choose more than one.

Of course all these features don’t program themselves, but they can easily be added to a service once you have a good handle on build ADFs. Notification services range from the very simple to the extremely complex. You can build one in a day or in a month. It all just depends on what is required for your situation.

More info…

SQL NS comes supporting two notification delivery mediums, file system and SMTP. More can be added as you need them such as direct SMS support if you need it.

As I stated before, SQL NS is very big and this is just an introduction with hopes that more people will become aware of it, and become interested in the many uses that I see in it’s future. It is still in beta but scheduled to release before 2003.

There is also an API available that allows attaching directly to the service and performing actions on and against it. This is well documented in the supplement of “Books Online” that comes with SQL NS.

SQL NS is fully prepared to allow you to handle subscribers from many different nationalities, if you build your service right you can even send different notifications to the same subscriber is multiple languages.

SQL NS takes full advantage of .NET, in fact it is written in C# itself.

SQL NS is pretty big system, and if you want you can spend a lot of time getting to know the internals of it, or you can spend a small amount of time learning to build services, it’s up to you. But whatever you do you can get a good start by looking here  (<a href="http: www.microsoft.com="" sql="" ns="" overview.asp"="">http://www.microsoft.com/sql/NS/overview.asp)

I hope you got more than a headache reading this, and happy building.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
SQL Sentry, Inc.
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralA lot of questions Pin
Huisheng Chen21-Apr-05 16:45
Huisheng Chen21-Apr-05 16:45 
GeneralNeed Help in NS Pin
sherb26-May-04 1:16
sherb26-May-04 1:16 
GeneralNewbie Badly needed help Pin
Member 25399312-Feb-03 9:25
Member 25399312-Feb-03 9:25 
GeneralRe: Newbie Badly needed help Pin
TonyS26-Oct-04 15:37
TonyS26-Oct-04 15:37 
GeneralTerminology : "triggered"... Pin
Mike Klimentiev9-Sep-02 10:35
Mike Klimentiev9-Sep-02 10:35 
GeneralRe: Terminology : "triggered"... Pin
Jason R. Hall9-Sep-02 11:41
Jason R. Hall9-Sep-02 11:41 
GeneralRe: Terminology : "triggered"... Pin
Anonymous10-Apr-03 15:04
Anonymous10-Apr-03 15:04 
GeneralRe: Terminology : &quot;triggered&quot;... Pin
ursalman8-Oct-03 21:11
ursalman8-Oct-03 21:11 
GeneralThis will be key to .NET's success Pin
Paul Ingles3-Sep-02 22:02
Paul Ingles3-Sep-02 22:02 
GeneralRe: This will be key to .NET's success Pin
Richard Deeming4-Sep-02 3:06
mveRichard Deeming4-Sep-02 3:06 
GeneralRe: This will be key to .NET's success Pin
Paul Ingles6-Sep-02 13:17
Paul Ingles6-Sep-02 13:17 
GeneralRe: This will be key to .NET's success Pin
Nobby Stiles23-Apr-03 4:18
sussNobby Stiles23-Apr-03 4:18 
GeneralRe: This will be key to .NET's success Pin
Paul Ingles23-Apr-03 5:09
Paul Ingles23-Apr-03 5:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.