.Net SQL Server Database Monitoring - Insert, Update, Delete

Question

Does anyone know of a way to monitor table record changes in a SQL Server (2005 or 2008) database from a .Net application? It needs to be able to support multiple clients at a time. Each client will "subscribe" when it starts, and "unsubscribe" when it exits. Multiple users could be accessing the system at once and I want to reflect their changes on the other users client. Then when the client handles the change event, it could update it's local object representing that record. Kind of similar to how Access updates records that are modified are reflected in each form referencing it.

I know microsoft has their Microsoft.SqlServer libraries for interacting with a SQL Server. But I'm not sure which concept applies to what I want to do (or what could be bent to apply to what I want to do). The ones that sound like they might be useful are the Management ones or the Replication one.

In anticipation of someone asking, "why don't you just requery the table occasionally to look for new information?" I have a large number of tables that I want to monitor and that would be a pain in the butt. Plus if I'm looking for something a bit more elegant.

I'm open to suggestions...

Answer 1

To monitor for changes to a SQL table or record in SQL 2005+ you can utilize the SqlDependency class.

It is targeted for use in ASP.NET or a middle-tier service where a single server is managing active subscriptions against a database - not multiple hundreds of clients managing subscriptions. Depending on the maximum number of clients you are referring to you may want to build a cache pooling service that can manage the notification subscriptions against SQL to your clients.

Inside it uses SqlNotificationRequest which uses Service Broker, not Notification Services. Thus, this should work going forward on SQL 2008, etc.

MSDN for SqlDependency

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
  // Assume connection is an open SqlConnection.

  // Create a new SqlCommand object.
  SqlCommand command=new SqlCommand(
    "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", 
    connection);

  // Create a dependency and associate it with the SqlCommand.
  SqlDependency dependency=new SqlDependency(command);
  // Maintain the refence in a class member.

  // Subscribe to the SqlDependency event.
  dependency.OnChange+=new OnChangeEventHandler(OnDependencyChange);

  // Execute the command.
  command.ExecuteReader();
  // Process the DataReader.
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationsEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

"Using and Monitoring SQL 2005 Query Notifications" article that talks you through the steps to set it up in your code (using a web app as example) along with the appropriate SQL permissions that are required to subscribe to Service Broker and so on.

An ASP.NET (web caching) class is also available for web scenarios.

Answer 2

In SQL Server 2008 , you have Change Tracking which you can track when an insert, update or delete occurs. This will track that the event has happened , which record it was and optionally which columns were changed but not the values (before and after). This is typically used to perform bulk processes with known changed data.

In change tracking you set a retention period to hold the changes, typically a period that ensures any processing of the tracked tables is within the retention period.

Alternatively you have (again, in SQL 2008) Change Data Capture which is more granular and records the actual changes. This, however, is an enterprise feature only.

You can easily create a .NET application to run the change tracking or change data capture queries.

Here is a link to further information on both

Change Tracking

Change Data Capture

For the most part, Change Tracking is probably going to be the best option - plus it works in all versions of SQL Server 2008.

If you want to achieve this in 2005 or below, you can basically create tables for each table you want to be tracked and have a trigger to insert values into the tracking table on insert, update, delete. Then have a process to clear this down. This is basically what Change Tracking does for you.