SQL Server Metadata Services Change Tracking
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
The SQL Server Modeling Services uses two SQL Server 2008 features to support change tracking:
Change Data Capture (CDC).
Change Data Capture (CDC) provides detailed operational auditing for changes to database tables, whereas Change Tracking is a lighter-weight mechanism for application synchronization strategies.
The following sections provide more details for how Modeling Services uses both CDC and Change Tracking.
Auditing with Change Data Capture
Change Data Capture records all data changes to targeted tables, including the operation that changed the data and the column values that changed. CDC requires the Partner, Developer, or Enterprise editions of SQL Server 2008.
There are two prerequisites for using CDC auditing in the Modeling Services database:
SQL Server Agent must be running, because CDC uses SQL Server Agent jobs as part of its implementation.
Change Data Capture must be enabled for the Modeling Services database. This happens when the database is installed. For more information, see How to: Use Operational Auditing with Change Data Capture.
Administrators and Modeling Services tools enable CDC on specific tables using the [Repository.Item].[AddAuditing] stored procedure. The following T-SQL example demonstrates the use of this stored procedure on a user-created table named HRApplication.EmployeesTable.
exec [Repository.Item].[AddAuditing] @schema=N'HRApplication', @baseName=N'Employees'
The first two parameters accept the target table's schema and table's base name, defined here as the table name without the appended word "Table". In this example, defaults are used for the remaining parameters.
Note
[Repository.Item].[AddAuditing] requires that tables use the Modeling Services's naming convention for tables—the word "Table" must be appended to the base table name. For more information, see Table Design Patterns.
The [Repository.Item].[AddAuditing] stored procedure creates two functions for securely accessing the changes. Both functions show changes between commit log sequence numbers (LSN). Log sequence numbers identify and order committed database transactions in the SQL Server transaction log. The first generated function is named by appending the word "Changes" to the table's base name (for example, HRApplication.EmployeesChanges). This function returns all changes between two LSN values and an optional row filter. The second function is named by appending the word "NetChanges" to the table's base name (for example, HRApplication.EmployeesNetChanges). This function shows only the net change from the beginning LSN value and ending LSN value. These functions actually call CDC-generated functions cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance>, applying Modeling Services security checks to the results. These security checks are similar to those made in the Modeling Services's updatable views; they limit the returned changes to items accessible by the user and based on Modeling Services Folders. Both the Modeling Services-generated change functions as well as the CDC-generated change functions accept the same parameters and return the same change information. For more information about the usage of the Modeling Services’s change functions, see cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance>.
One challenge in using auditing is that examining changes by log sequence numbers does not directly answer the common question of when a change occurred. To scan for changes based on date and time, the datetime value must be converted to an LSN value using the sys.fn_cdc_map_time_to_lsn system function. The reverse process uses the sys.fn_cdc_map_lsn_to_time system function to convert an LSN value to a datetime value.
The following query shows an example of retrieving all changes in the past 15 minutes for an audited table named Employees in the HRApplication schema.
declare @min_lsn as binary(10)
declare @max_lsn as binary(10)
declare @begin_time as datetime
declare @min_lsn_time as datetime
set @begin_time = DATEADD(mi, -15, GETDATE())
set @min_lsn = sys.fn_cdc_get_min_lsn('Capture_HRApplication_Employees')
set @max_lsn = sys.fn_cdc_get_max_lsn()
set @min_lsn_time = sys.fn_cdc_map_lsn_to_time(@min_lsn)
if @min_lsn_time > @begin_time
set @begin_time = @min_lsn_time
set @min_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time)
if (@min_lsn is not null)
begin
select sys.fn_cdc_map_lsn_to_time(__$start_lsn) as StartTime,
case __$operationwhen 1 then N'delete'
when 2 then N'insert'
when 3 then N'update before update'
when 4 then N'update'
end as Operation,
Id, Name
from HRApplication.EmployeesChanges(
@min_lsn, @max_lsn, 'all')
end
else
begin
print 'No changes during the last 15 minutes'
end
The previous example uses DATEADD to find the time 15 minutes prior to the current time and stores this in the @begin_time variable. Then CDC functions sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn are used to determine the minimum and maximum log sequence numbers (LSNs) for the capture table, Capture_HRApplication_Employees. sys.fn_cdc_map_lsn_to_time then converts the minimum LSN value, @min_lsn, to a datetime value. This is the date and time of the earliest change stored in the audit table. This is necessary to see whether the minimum valid change time was less than 15 minutes earlier.
For more information about how to use operational auditing in the Modeling Services database, see How to: Use Operational Auditing with Change Data Capture.
Synchronization with Change Tracking
SQL Server 2008 also implements a Change Tracking feature that Modeling Services-based applications can use for data synchronization. Unlike auditing with CDC, Change Tracking does not record a detailed history of all row changes with the column values for each change. Instead, Change Tracking focuses on synchronization scenarios, recording only the primary key for each changed row in the target table. For example, if a single row were updated five times, CDC auditing would record five rows in a tracking table along with all column values for each changed row. Change Tracking would enter a single row in a tracking table for all five changes, recording only the value of the primary key for the row that changed along with information about the change. The purpose of Change Tracking is to inform a synchronizing application of which rows in a target table have changed since the last synchronization. The application can then use the tracked primary key value to look up the latest version of those rows from the database. One use of this feature could be to efficiently update an application user-interface that displays Modeling Services table data. A refresh of the user-interface would need only to update the rows of the table that changed since the last synchronization.
Modeling Services provides the [Repository.Item].[AddChangeTracking] function for adding change tracking to a target table. The following T-SQL statement uses this procedure to enable change tracking on a table named EmployeesTable in the HRApplication schema.
exec [Repository.Item].[AddChangeTracking] @schema = 'HRApplication',
@baseName = 'Employees',
@primaryKeyColumns = N'[Id] bigint'
This stored procedure takes the schema name, HRApplication, the base table name without the table suffix, Employees, and the primary key columns for the table, N'[Id] bigint'. The procedure enables change tracking on the table and creates a function for accessing change events on the table. The function uses the table’s base name and appends the word “ChangeEvents” (for example, HRApplication.EmployeesChangeEvents).
Note
[Repository.Item].[AddChangeTracking] requires that tables use the Modeling Services's naming convention for tables—the word "Table" must be appended to the base table name. For more information, see Table Design Patterns.
After it is enabled, SQL Server creates an internal tracking table to record changes to the target table. SQL Server maintains an incrementing data version number that increases with each change to the database. This data version number is critical to the functionality of Change Tracking. Change Tracking records the current data version number at the time of each row change. Applications query the current version number by using the CHANGE_TRACKING_CURRENT_VERSION function as shown in the following T-SQL query.
select CHANGE_TRACKING_CURRENT_VERSION()
In a typical scenario, an application initially stores the current data version number after reading all rows from the tracked table. When later synchronizing with the database, the application passes the stored version number to the Modeling Services-created ChangeEvents function for the table. This function internally calls SQL Server’s CHANGETABLE function, but it also applies security filters to the change events returned as defined in the Modeling Services database. These functions return a table that contains the primary keys of all rows that have changed since the last synchronization (rows in the Change Tracking tables that have a data version number higher than the one that was passed to the ChangeEvents function for that table). The application then uses these primary key values to query the database, obtaining the latest versions of only the rows that have changed since the last synchronization. The following query demonstrates the use of the ChangeEvents function.
select * from HRApplication.EmployeesChangeEvents(@sync_version)
The preceding query assumes that a bigint variable @sync_version was previously assigned a data version number from the last synchronization. The query returns a list of primary key values for the rows that have changed in the EmployeesTable table in the HRApplication schema.
For more information about how to use Change Tracking in the Modeling Services database, see How to: Use Change Tracking.