Chapter 53. SQL Server Audit, change tracking, and change data capture

 

Aaron Bertrand

SQL Server 2008 introduces three new features for tracking data and object changes within the database: change tracking, change data capture, and SQL Server Audit. Based on the marketing information alone, it might be difficult to determine which, if any, would be useful for your specific scenario. In this chapter, I will compare the features, outline their pros and cons, and try to help you to decide which solution or solutions might work best in your environment.

What are these solutions used for?

Since shortly after the very first database was persisted to disk, database administrators everywhere have been asking questions like, “Who did what to my data, and when did they do it?” It is in our nature to be curious about the way users utilize our systems in general. In some specific cases, it is important to track down exactly when a piece of data was changed, and who was responsible for the change.

Before talking about the new features introduced in SQL Server 2008, it will be useful to briefly outline some of the key motivations for implementing an auditing or change tracking solution in the first place.

What do people do now?

How does SQL Server 2008 solve these problems?

Comparison of features

Summary

About the author