Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 281

Per_id SYS_CHANGE_
OPERATION

SYS_CHANGE_
VERSION

SYS_CHANGE_COLUMNS

21 U 8 0x00000000021000

22 I 6 NULL

23 I 6 NULL

This completes the process to design the CT functionality on an existing SQL Server instance
and database.

How it works...

The Change Data Capture (CDC) and Change Tracking (CT) functionality is used to detect the
net row changes having a minimum storage of data with less overhead. CDC works with all
three types of database recovery models and all CDC operations for simple or bulk-logged
recovery-modeled databases are fully logged. The key ingredient for CDC is the transaction log
I/O, as it will significantly grow when CDC is enabled in a database. Furthermore, log records
will stay active until CDC has processed them as per the feature. So, in an environment such
as a highly-transactional database or a very large database environment, the log file can
be exponential and the log space cannot be reused as long as CDC scan job completes the
processing of log records.

In this recipe, we have demonstrated the change data retention period as 48 hours with an
AUTO clean-up process. Once the data is populated and a few DML operations are performed,
the change tracking system tables will have data to show the results.

The designated column level changes are tracked by using the TRACK_COLUMNS_UPDATE
option on dbo.WEF_Passwords table. To obtain the changes and operation of the change-
tracked status, the sys.CHANGE_TRACKING_TABLES system catalog view is used. In
addition to the previous system catalog view, a system function is used to retrieve change
tracking data; the system function is the CHANGE_TRACKING_CURRENT_VERSION , which
returns the version number from the last committed transaction for the table.

Implementing Policy-Based Management
features

One of the best administrative features for DBAs is Policy-Based Management (PBM) that is
introduced in SQL Server 2008. PBM has three components such as policy management,
explicit administration, and evaluation modes that contain the concepts of targets, facets,
and conditions.