Track data

SVA 116 Reputation points
2024-07-08T15:56:29.8366667+00:00

Hi

How can I track the data changes of a heavily used table in AG without performance impact in SQL 2017 and SQL 2022

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2024-07-08T21:21:32.05+00:00

    Your requirements are very vague, and there are a multitude of options. Which is the best is very much "it depends". Options include:

    • Transactional Replication. (To get all changes.)
    • Change Data Capture. (To get all changes.)
    • Change Tracking. (If you only want to track that something has changed, but not get all changes.)
    • Having a trigger that writes to the target table (Probably not a good idea if the table is written frequently.)
    • Trawling for changes by using a rowversion column or a LastUpdated column. (Akin to Change Tracking, but more prone to miss changes.)
    • I've probably forgotten something.

    Beside the impact on the system, one thing is also to consider the skillset in the organisation. Change Data Capture could very well be the best choice technically, but if your org has never used CDC before, there is a hurdle.)

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,161 Reputation points
    2024-07-08T18:38:13.32+00:00

    Everthing has performance impact.

    What exactly do you expect/looking for?

  2. MikeyQiaoMSFT-0444 1,755 Reputation points
    2024-07-09T08:35:24.9533333+00:00

    Hi,SVA

    Manage Change Tracking(Lightweight and simple)

    Retrieve change information through system functions and views.

    change data capture (CDC)

    Create capture tables and record data changes into these tables, allowing you to view all inserts, updates, and deletes in specific tables. Suitable for large-scale data synchronization and change tracking.

    To record the changes update, delete to a history table for future use

    1. Triggers: You need to create a history table and set up DELETE, UPDATE, and INSERT triggers. However, this can consume server performance.
    2. Transaction Logs: Use third-party software to read the content of the transaction logs.
    3. Audit (Recommended): Record database operations in detail in an audit log.

    Best Regards,

    Mikey Qiao

    If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

    0 comments