Track SQL Server Changes Efficiently

What would happen if a primary key was altered or permissions to a stored procedure were updated without you being aware of it? Tracking SQL Server only with change data capture (CDC) isn’t enough for you to stay on top of changes to your critical databases. Indeed, without reliable, efficient SQL Server change tracking, you simply can’t control what’s going on across your SQL servers.

Native Change Tracking in SQL Server

SQL Server uses a synchronous tracking mechanism, which means that changes are registered once Data Manipulation Language (DML) changes have been committed. The SQL Server change tracking capabilities offered by native tools provide some change tracking functions; for example, you can use change tracking to see who deleted which tables or what rows were changed. But you’ll need to write a set of queries in Transact-SQL to get more detailed change tracking information, such as details about changed data, modified columns or user-created tables — and this task can easily devour your precious time. On top of that, enabling change tracking can quickly fill up your server, so you must constantly run cleanup to avoid running out of disk space, which would cause performance and availability to suffer.

To use SQL Server change tracking, you need to enable change tracking on two nested levels: database change tracking and table change tracking.

Enabling Change Tracking for a Database

For change tracking to work, your database must be at least SQL Server 2005 compatibility level. You can determine your server database version number by running the “SELECT @@VERSION” query. You need to turn change tracking on for each database individually.

To enable change tracking on a database level, you can use either SQL Server Management Studio or run the following T-SQL query:

ALTER DATABASE Accounting2020
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON) 

After change tracking is enabled, you can set a different retention period by modifying the CHANGE_RETENTION and AUTO_CLEANUP parameters.

Enabling Change Tracking for a Table

As with databases, tracking table changes requires enabling tracking on each table separately. Again, you can do this either in SQL Server Management Studio or with a query like this example:

ALTER TABLE Contracts
ENABLE CHANGE_TRACKING 
WITH (TRACK_COLUMNS_UPDATED = ON)

Once change tracking is enabled for a table, information about all rows changed by common SQL statements (SELECT, INSERT, UPDATE, DELETE, etc.) is stored internally.

The TRACK_COLUMNS_UPDATED parameter is used to set change tracking for columns on or off. If it is on, SQL Server will store additional information about which columns were updated. However, since tracking column information adds to storage overhead, this option is disabled by default, and keeping it disabled is recommended for all applications that do not require historical data.

Easy and Efficient Change Tracking with Netwrix Auditor

Unlike native change tracking tools that require you to write queries and manually crawl through data, Netwrix Auditor for SQL Server provides prebuilt dashboards and reports that enable you to easily spot changes and see all the critical details. For example, you can see that a column that was deleted or a user role was modified, along with the before and after values of each change. Interactive search helps you quickly investigate suspicious changes so you can prevent database unavailability and other disruptions.

Netwrix Auditor also makes it easy to keep a close eye on key resources, such as the servers with the most changes and the users making the most modifications. Report subscriptions keep you updated automatically on the schedule you choose. And there’s no more need to constantly worry about cleanup because Netwrix’s two-tiered storage keeps your consolidated SQL Server change data cost-effectively for more than 10 years, while ensuring quick and secure access to it at any time. Even better, Netwrix Auditor uses a non-intrusive auditing mechanism, so it will never degrade database performance.