Advantages and Disadvantages on Change Tracking

  • Article

Some weeks ago a customer asked me for options to Audit a specific situation. I decided to evaluate Change Tracking, a new feature in SQL 2008. The conclusion is that Change Tracking is not auditing feature and we shouldn't try to use it as one, so I want to share some of my findings. SQL Server 2008 has other features that track changes in some way that you can evaluate depending on your needs:

-        SQL Auditing

-        C2 Audit Mode

-        SQL Traces (Profiler)

-        Change Tracking

-        Change Data Capture

-        Custom code (triggers, additional columns in tables, additional code in sp’s, etc.)

-        Third Party tools

In this case we decided to try Change Tracking. Change tracking allows you to track every time a row or column is modified. To learn how to implement it, you can refer to https://msdn.microsoft.com/en-us/library/cc280462.aspx, here I want to mention the advantages and limitations I have found using this feature.

Advantages

-        Very easy to implement

  • Doesn’t require application changes (unless you want to add a change context)
  • Only requires configure database and objects.

-        High performance implementation because is embedded in SQL Server and it isn’t something separated.

-        You are able to identify the history of changes, not only, the last change.

-        You can add context information to every DML if required (requires code change)

-        You can identify which columns changes (if configured) not only which row.

-        It doesn’t block the transaction log because the operation is synchronous, this means, that there is no risk that sometimes the log cannot be truncated because change tracking is blocking the transaction log.

-        It is useful to identify the rows and/or columns that have changed and need to be moved to the Data Warehouse.

-        You have auto cleanup. You don’t need to worry about deleting the information collected; it is deleted automatically based on the retention period.

Disadvantages

-        It doesn’t track time of the change.

-        It doesn’t track security context that executed the change.

-        It doesn’t save how the data changed, only tracks that a change was made.

-        Depending on the amount of changes it will add some overhead to the server, although, this doesn’t seem to be significant.

-        Because the operation is synchronous it will add some time to the transaction time. How much, depends on the amount of changes you are doing in your transaction.

 

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”