Detect changes on an sql-server table

Question

I'm developing a project for my company and I need to detect changes on another company's table. In other words, they will insert/update some data and I need to catch these changes. I'm willing to try SQLDependency but I'm not sure about speed and performance also it needs lots of permission. In addition to these drawbacks I do not want to miss any changes I need, time really matters in this project.

How can I detect these changes with best performance?

Answer 1

SQL Dependency will not work for you, if you want to ensure no data is missed. SQL Dependency works only within the scope of your application registering to receive notifications. This means that if your application is down for any reason, you have missed a few notifications.

You will need to look at something closer to the database level itself to ensure that you get all notifications (data changes).

You can probably have triggers that update a staging table. Be careful when you use triggers. A failure or slow response in your triggers could affect the performance of the source database & operations.

You can have replication enabled and work on the replica data within your application and flag off any records that you have already processed.

Answer 2

You can look at the feature "Change DataCapture" of SQL Server : https://msdn.microsoft.com/en-us/library/cc645937.aspx

Answer 3

You can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Hope this help.