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?

I think you could use triggers. Have a look at https://msdn.microsoft.com/en-GB/library/ms189799.aspx — panagdu, Apr 17 '15 at 14:02
Just select. We will discuss others depend on project's properties. — Hasan Akgün, Apr 17 '15 at 14:04

score 3 · Accepted Answer · answered Apr 17 '15 at 14:08

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.

score 1 · Answer 2 · answered Apr 17 '15 at 14:00

1

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

answered Apr 17 '15 at 14:00

Pak

2,639
2
21
27

Also look into Change Tracking. It's a lighter weight solution, but will probably do what you need. https://msdn.microsoft.com/en-us/library/bb933875.aspx – Brian Pressler Apr 17 '15 at 14:11
I did but I couldn't find C# side of the technology. – Hasan Akgün Apr 17 '15 at 14:13
There isn't a C# side. It's like querying a classic table or call a stored procedure. You can use Ado .Net if you want or Entity Framework. – Pak Apr 17 '15 at 14:21

score 1 · Answer 3 · answered Apr 22 '15 at 03:03

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.

what is your changesCount parameter here? where do you get it from? — Dominik, Oct 18 '16 at 13:02
@Dominik, this code was taken from a unit test as an example. ChangesCount is an expected value which is hardcoded. You can find this unit test through the link provided in the answer. — dyatchenko, Oct 18 '16 at 13:59

score 0 · Answer 4 · answered Apr 17 '15 at 14:24

With the heavy overhead that comes with using Change Data Capture I would recommend using Change tracking for SQL Server.

Here are some links to using the Sync Framework API that works with Change Tracking: https://msdn.microsoft.com/en-us/library/cc305322(v=sql.110).aspx

Detect changes on an sql-server table

4 Answers4