SQL Server and Notifications

Chaps from Microsoft gave us a presentation of StreamInsight at work the other day.

Following this I started digging around the whole SQL Server notification/event thing: how can an application be notified of a data change instead of polling the database like a drowsy lobotomised lemming?

Service Broker

Messaging infrastructure for SQL Server. To enable it on a database, right-click the database in SSMS, click Properties > Options > Broker Enabled

Service Broker
Servicer Broker Programming

T-SQL has a few constructs dedicated to using the service broker.

Query Notifications

Requires Service Broker to be installed on the current SQL Server instance and enabled for the current database.

Using query notifications
Query Notifications in SQL Server (ADO.NET)

SqlDependency

This is the class you use to implement Query Notifications in a .NET app. It is supposed to be used by server apps (ASP.NET for instance). The documentation warns it is not designed for client apps. It probably doesn’t scale too well.

SqlDependency class
Detecting Changes with SqlDependency (ADO.NET)

Notification Services

SQL Server 2005 only, not 2008. This is a publish/subscribe messaging service.
Notification Services Tutorial
What does Notification Services do?

StreamInsight

Big Diagram
StreamInsight Installation
Download
Deployment model
Concepts

Other Stuff…

• Change Data Capture / Change Tracking

This is more for auditing purposes: allows you to easily query historical changes made to the data without having to write tons of error-prone code in triggers (as I saw being done on an Oracle database by a client I used to work for…)

Data Capture and Change Tracking compared

• WAITFOR

Can be used to delay a statement: block for 2 minutes before running a statement for instance or can be used to schedule a statement to run at a specified time.

Using WAITFOR
WAITFOR (Transact-SQL)