Detect changes to sql-server table

Question

I am developing a project for my company, and I need to detect the changes in another table of companies. In other words, they will insert / update some data, and I need to catch these changes. I'm ready to try SQLDependency , but I'm not sure about speed and performance, and it needs a lot of resolution. In addition to these shortcomings, I don’t want to miss any changes that I need, time really matters in this project.

How can I detect these changes with better performance?

Answer 1

SQL Dependency will not work for you if you want data not to be skipped. SQL Dependency only works within the registration area of ​​your application to receive notifications. This means that if your application is disconnected for any reason, you have missed some notifications.

You will need to look at something closer to the very level of the database to make sure that you receive all notifications (data changes).

You probably have triggers that update the staging table. Be careful when using triggers. A failure or slow response in your triggers can affect the performance of the original database and operations.

You can enable replication and work with replica data in your application and mark all the records that you have already processed.

Answer 2

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

Answer 3

You can use the open source implementation of the SqlDependency class - SqlDependencyEx . It uses a database trigger and its own Service Broker notification to receive table change events. This is a 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 can track INSERT, DELETE, UPDATE separately and get the actual changed data ( xml ) in the event args object. I hope for this help.

Answer 4

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 APIs, which works with change tracking: https://msdn.microsoft.com/en-us/library/cc305322(v=sql.110).aspx