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.
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!
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
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.
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.
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.
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.
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.
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.
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
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.
simply create an application for gathering the required information from a
subscriber, and use the classes provided in the
namespace. (note these are .NET classes)
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.
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.
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.
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
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.
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.
NS takes full advantage of .NET, in fact it is written in C# itself.
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 (http://www.microsoft.com/sql/NS/overview.asp)
hope you got more than a headache reading this, and happy building.