May include but is not limited to: change tracking, database audit specification, CHANGETABLE
There are two tools in SQL Server serving the purpose of auditing data changes. The first is Change Tracking, which you can find in every version, the other is Change Data Tracking, can be found in Enterprise Edition.
The difference in a nutshell: using CDC, you can see what’s changed and how, in an asynchronous manner (there’s a latency involved). With CT, you can see that there’s a change occurred, but can’t see the changes themselves, and do so in a synchronous manner.
You can imagine the benefits and drawbacks: CDC uses much more resources, while providing more information. Change Tracking doesn’t provide historical data, while still indicate the type of change made. Now let’s see both techniques in detail.
The first thing to do with CT is to enable it. You must define that you need this functionality at the database level. You’d write a syntax like this:
ALTER DATABASE MyDataBase
SET CHANGE_TRACKING = ON
Continue reading “Track data changes”