Tech News

Notification Services in SQL Server 2005

SQL Server Notification Services is used for developing and deploying applications that generate and send notifications. Notifications are personalized messages sent to a device that subscribes to them. A subscription defines the preference of the subscriber for a specific type of event. For example, a subscriber can specify a preference to the notification application such as “Notify me when new content is added to the Web site.” The notification application in this example is the Web site. The subscriber could be the editor of the Web site or someone responsible for managing its content. The triggering event is the addition of new content. Thus, a notification will be sent to the subscriber when the triggering event occurs. The notification can be scheduled on a weekly, daily, or hourly basis. SQL Server Notification Services can be used to send a notification to a wide variety of devices, such as a subscriber’s cell phone, personal digital assistant (PDA), Windows Messenger or e-mail account.

Terminologies in Notification services

  1. Subscriber is a person or application that subscribes to and receives notifications.
  2. Subscription is a request for specific information to be sent to an e-mail address or a device.
  3. Event is a piece of information that the subscriber may be interested in. The triggering event can be something like an increase or decrease in the price of a stock.
  4. Notification is a message that contains information about the triggering event.
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.
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.


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.


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.

Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x