Sql Server Notification Services

Sql Server 2005 is more than just a relational database management system (RDBMS). It also contains a reporting engine called Reporting Services (SSRS), a data warehousing system called Analysis Services (SSAS), an engine for selectively pulling data from various sources called Integration Services (SSIS), and a subscription based notification engine called Notification Services (although I've not seen this notation, I presume we can say SSNS for short?).

Each of these items are a technology unto themselves and take time to learn and master. Recently I started with Readify and my first job was to do a proof of concept for an internal project. It involved Notification Services and I was able to learn some cool stuff to share with you.

SSNS is available in all versions of Sql Server 2005 ; all my development was done with Developer which costs around $80AUD. The core components of a SSNS instance are as follows.

Subscribers
Users must be in the subscriber list to add subscriptions. This is essentially a 'User' table in SSNS.

Subscriber Devices
Users can receive their notifications in different ways. For example I might get a full detailed notification in my email, just the 1 line title on my MSN messenger, and a summary of all of the days alerts on my PDA.

Subscriptions
A subscriber can subscribe to a particular field in an event. For example, they may want to receive notifications when someone posts a comment on their blog that contains swear words. So the subscription uses a SQL query to look for key words in the blog comment, and if it finds them, the user will be notified.

Events
Events are the things occurring that may or may not cause notifications to go out. In the above example, the Event is the comment being posted. Every comment that gets added to the blog causes an event in Notification Services, but only the events with swear words in the comment field will cause notifications to go out.

Notifications
These are the content that are sent to users. They occur as a result of an event matching a subscription. They are sent on a channel such as email, file, service, etc. The notification is formatted with XSLT, and can use event data as necessary.

How It Works
So there's no real programming involved here. Essentially its all declarative via 2 XML structures. The first simply specifies the applications; its kind of like a solution in Visual Studio. It specifies what applications will exist, and also allows you to specify parameters for your XML files. Then you need 1 XML file per application. This file will contain XML schemas of your Events, Subscriptions, and Notifications. It also allows you to specify how notifications can be sent, and how events can be submitted.

Don't I Get To Write Code?
Yes you do. The API is very open to fiddle with. When you upload your XML files to SSNS it will create 2 databases, with lots of tables and stored procedures. The good news is that you don't have to touch those items. If you maintain your schemas in your XML files, it will ensure the tables are created correctly.

However, some of the stored procedures are there for you to call; particularly around submitting subscribers, devices, subscriptions, and events. So you could call those sprocs, or you could use the managed API for Notification Services. This is just an assembly you can add a reference to in your application. For example, create a windows app that provides the interface for logging in and adding/removing subscriptions. With a few quick method calls you have connected to your SSNS application and not had to write any SQL! I can't stress enough how easy it is to use this API. You can do everything you need, and its very logical to use.

Pulling It Together
Ok so think about this example. I want to receive email updates whenever anyone adds a comment on my blog that mentions 'Notification Services'. So I create an event schema that allows for a comment ID and content to be sent to SSNS. Then I create my notification schema with just the comment data field (since I don't need the ID in my email). Thirdly I create a subscription schema where I specify a field called 'SearchPhrase' and an SQL statement that inserts data into the Notification schema by searching the Event comment data for the search phrase.

Now I just need to specify how the event data gets into the event schema in SSNS. I have lots of options here:

• My comment code just calls a sproc, so I can change that sproc on the server to include a call to insert the event data into SSNS via a sproc
• I could add a trigger on the comments table in my database such that when data is added, it will also add that data to SSNS via a sproc
• In my .Net code I could just call the sproc to insert the comment data after I have added it to the database
• Or I could use the API mentioned earlier to add the subscription so I don't have to worry about nasty sprocs!

Oh I need to add a subscription too; since I now know SSNS well enough, I can just insert this into the right table; I don't plan on subscribing/unsubscribing continuously.

More Resources
Check out the MSDN tutorial:
http://msdn2.microsoft.com/en-au/library/ms170337.aspx

Or here is the MSDN main stop:
http://msdn2.microsoft.com/en-au/library/ms172483.aspx

And the API reference:
http://msdn2.microsoft.com/en-au/library/microsoft.sqlserver.notificationservices.aspx

And please, no swear words in the comments. :P