Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Notification Services

SQL Server 2008 Notification Services

  • Comments 7
  • Likes

Notification services is one of the deprecated features in SQL Server 2008, essentially it is supported for now but will not be in the next release of SQL Server. If you are using notification services currently and you are looking to upgrade to SQL Server 2008 then you can get it from here.

But why deprecate the feature? Basically not many people were using and it wasn’t seen as being easy to use or flexible enough.  But what are your migration options if you are using it?

That’s going to depend on what you are using it for:

  • Alerting of system health. A good alternative here would be use the policy management features in SQL Server 2008 and either schedule them (which is built into the UI) or set them to OnDemand: notify if the policy supports that (as per this post of mine.
  • Audit.  You can trap audit details to file, or the event logs (including the security log)in SQL Server 2008.  From there you could run a report on a schedule to show you any issues or use the capabilities of the event logs.  Change Tracking can do similar things for reporting on changes to actual data
  • Performance.  Extended events and the Data collection elements of SQL Server 2008 will allow you to trap detailed telemetry of what's happening to your database and the wider context of what’s happening server.

Having collected the information you want to track, the challenge then is to get this information back to you when things go wrong.  This could simply be a case of making use of an agent job to do a test to see if there’s a problem, and then to conditionally running a reporting services report of the problem(s), or a send mail procedure to do it that way.  DDL triggers might also be an option, so there are lots of options but no obvious single thing to take your existing setup and migrate it to any of the above I’m afraid.

So I would be interested in the comments this generates, and as ever if you have some ideas or an issue with this then Microsoft Connect is the forum for that.

Comments
  • Great post, explained really well and I could really understand. Thank you.

  • Andrew,

    Great post.  I'll post Microsoft Connect as well, but wanted to pick your brain for a moment if you'll allow it.

    I have used SQL Notification to handle cache expiration notifications (or rather updating remote cache's when data changes at the source).  This was a great technique that insured data did not become stale.

    Do you have any ideas or have you seen any suggestions for how to achieve this type of "data changed event" notification in SQL Server 2008?

    Thanks,

    Robert

  • Robert

    Back form my holidays tomorrow(29 Jan), so can I give you a call to discuss?

    Andrew

  • would it be possible to share your response to Robert's query regarding remote cache management via notification service please?

  • Malcolm

    Robert didn't get back to me, so again feel free to call me e-mail me etc. as basically I need to know what you want ot use NS for

    Andrew

  • Andrew, I assume the SqlNotificationRequest class in ADO.NET is dependent upon Notification Services. can you confirm? And What is the replacement approach for a .NET app to get notified of new rows in a Change Tracking table? (Effectively, getting notified of changes in the tracked table). can you post something to http://stackoverflow.com/questions/877273/change-notification-with-sql-server-2008 ?

  • Useful post...thanks.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment