2

I would like to add change tracking and change data capture in the same table for SQL Server 2017 (v14.0.3030.27). When I enabled Change tracking it worked, also when I enabled Change data capture, it worked well.

But when I enabled both at the same time in the same table, I'm not getting values of change data capture. couldn't see any documentation saying it so.

My plan is to pull the change using Change tracking and fetch the details from Change Data capture table. Any thoughts?

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
Abdul
  • 321
  • 4
  • 22
  • CT and CDC serve different purposes. Use just one! Either you need all intermediate changes (CDC) or you don't (CT). Which is it? – Mitch Wheat Oct 03 '18 at 04:08
  • I suspect you're the first person to ever try to enable both...that's usually a sign your design is wrong! – Mitch Wheat Oct 03 '18 at 04:14
  • @MitchWheat, you may be right, But my aim is to poll a small table every minute (if possible seconds ) to check any change occured on a particular table, if so fetch the full changes from CDC. If you have any other design suggestion please let me know. Thanks – Abdul Oct 03 '18 at 09:50
  • use SqlDependency: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency – Mitch Wheat Oct 03 '18 at 09:51
  • @MitchWheat Thanks for the quick response I will check and get back. – Abdul Oct 03 '18 at 09:54
  • @MitchWheat I'm not using APS.Net Application, the call is from a third party application (Mirth Connect) which is available in the same environment of SQL server. – Abdul Oct 03 '18 at 10:48
  • you don't need to be using ASP.NET – Mitch Wheat Oct 03 '18 at 11:02

1 Answers1

0

Given your extra information "But my aim is to poll a small table every minute (if possible seconds)", I would suggest using SqlDependency to be notified when data changes rather than continuously polling.

SqlDependency is ideal for caching scenarios, where your ASP.NET application or middle-tier service needs to keep certain information cached in memory. SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed.

To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event. For more information about the requirements for creating queries for notifications, see Working with Query Notifications.

With the caveat:

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541