检测sql-server表上的更改

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.

我正在为我的公司开发一个项目,我需要检测另一家公司的桌子上的变化。换句话说,他们将插入/更新一些数据,我需要捕获这些更改。我愿意尝试SQLDependency,但我不确定速度和性能,还需要很多权限。除了这些缺点,我不想错过我需要的任何改变,时间在这个项目中非常重要。

How can I detect these changes with best performance?

如何以最佳性能检测这些变化?

4 个解决方案

#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.

如果您想确保没有错过任何数据,SQL依赖关系将不适合您。 SQL依赖项仅适用于注册接收通知的应用程序范围。这意味着如果您的应用程序因任何原因而关闭,您就会错过一些通知。

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.

您可以启用复制并处理应用程序中的副本数据,并标记已处理的任何记录。

#2

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

您可以查看SQL Server的“更改DataCapture”功能:https://msdn.microsoft.com/en-us/library/cc645937.aspx

#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:

您可以使用SqlDependency类的开源实现 - SqlDependencyEx。它使用数据库触发器和本机Service Broker通知来接收有关表更改的事件。这是一个用法示例:

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.

使用SqlDependecyEx,您可以单独监视INSERT,DELETE,UPDATE并在事件args对象中接收实际更改的数据(xml)。希望这有帮助。

#4

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

由于使用Change Data Capture带来的繁重开销,我建议使用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

以下是使用与变更跟踪一起使用的Sync Framework API的一些链接:https://msdn.microsoft.com/en-us/library/cc305322(v = sql.110).aspx

#1