Change Tracking in the Data Warehouse
Today we have a feedback from readers about the use of Change Data Capture and Change Tracking features in SQL Server 2008 and later.
Thomas
I wouldn’t encourage anyone to use Change Tracking without emphasizing that they should be on the latest CU of whatever version of SQL they are running. For instance for SQL Server 2008 R2 Cumulative Updates prior to CU 6 have a much higher incidence of syscommittab errors. After CU 6 you are quite a bit safer, and if the databases you are working with are in a controlled server environment (Where the server will rarely experience a ungraceful shutdown), you should be ok.
If you experience a Syscommittab error you will need to be able to establish a DAC connection in single user mode to recover your database, so be prepared if you are running it on SQL Express editions. Most of the time, you can continue operations after you have this error, but you will not be able to do a backup until it is resolved, so you want to be ready to fix them when they happen.
For most these errors may be infrequent, but I’m running Change Tracking on 1200+ servers and they are too frequent for me.
Phil
Really enjoy the newsletter and the work you are doing. Change Tracking and Change Data Capture (CDC) are 2 technologies I have recently been looking into. My interest lies in the BI/Data Warehousing subject area. Recent training on the Kimball methodology (focused on the Microsoft stack) did touch on CDC since data warehouses of have any significant size have to be loaded incrementally, which calls for some knowledge of what changed in the source system(s). My understanding is that CDC necessitates Enterprise edition but Change Tracking is in the lower editions (Enterprise is not in my picture at the moment).
Also Change Tracking as I understand can give you what changed down to the column level, just not what the historical value was (that requires CDC). So to utilize Change Tracking in my DW application, I might need to maintain at least current state of columns in a staging version of the source system(s). That is best practice anyway.
Definitely interested in any comments you might get from those using either technology feature in a BI/DW setting.
Editor:
As I understand the purpose of Change Tracking it does track change of any column within a table, but does not maintain any sort of history regarding what has changed. Instead, a unique code is maintained on a per record basis. When any data column is modified in a row, the code is changed in the history for that row of that table. Nothing regarding the contents is maintained except the primary key.
This is useful primarily in optimistic concurrency. Then you have the ability to confirm that no other user has modified a row in a table since you retrieved it. If the code is the same as when you prepare to update then you can be sure you are not overwriting changes made by another process. If it is different, your application can now begin some sort of reconciliation process.
So, while the change of the code on a row represents the fact that any column or columns in that row were modified, it does not track to specifically which ones, or what values were.
I think the best take away from this discussion is that both these technologies are not restricted to an OLTP database. They also work with data warehouse structures as well.
Thanks for the input everyone. If you want to add to this or any SSWUG discussion please send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Is your manager legitimately harsh?
There is a fine line between behaviors by a manager that is legitimately harsh and within normal management practices and behavior that could be considered harassment. For example: How do you determine whether reprimanding an employee in front of his or her peers is harassment? Are there certain criteria that apply to conduct that are considered to be harassment? Can yelling at an employee in front of his or her peers be harassment? What determines whether or not it is? How likely is it that an employee who is disciplined under legitimate circumstances will complain of harassment? How does an employer deal with this situation?
Featured White Paper(s)
Achieve an astounding return on investment with Toad® for Oracle
read more)