How to: Use Change Tracking

Article
09/22/2010

[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.]

This tutorial demonstrates how to install and use the Change Tracking feature for the SQL Server Modeling Services. In this tutorial, you will accomplish the following objectives:

Use the Mx.exe tool to install a new Modeling Services database instance with the Change Tracking feature enabled.
Explore the Modeling Services database with Change Tracking enabled.
Enable Change Tracking on a new Modeling Services table.
Query changes recorded by the Change Tracking feature.

Enabling the Change Tracking Feature

The following steps show how to use the Mx.exe utility to install a new Modeling Services database with the Change Tracking feature enabled. After the Modeling Services database is installed, you can explore the resulting changes to the database using SQL Server Management Studio.

To install an Modeling Services database with Change Tracking

On the Start Menu, click All Programs, Microsoft SQL Server Modeling CTP, and open the Microsoft SQL Server Modeling CTP Command Prompt.
Use the following command line to create an empty Modeling Services database named RepositoryCT.
```
mx.exe create /database:RepositoryCT
```
Note

The Mx.exe utility uses integrated Windows security to access the target server. The server in this example defaults to (local), which is the SQL Server 2008 default instance on the current machine. The calling user must have appropriate permissions on the target server to allow for the creation of a new database.
Use the following command to deploy the Base Domain Library (BDL) to the RepositoryCT database. Enable Change Tracking with the RepositoryChangeTracking (rct) property.
```
mx.exe install Repository.mx /database:RepositoryCT /property:rct=+
```
Note

Note that the SQL Server Modeling CTP installs other models, such as the System_Runtime and Microsoft.UML2 models. If you want these models in the new database instances, you can use the same Mx.exe command to install their associated image files (*.mx) in the SQL Server Modeling CTP tools directory.

To explore the new Modeling Services database

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.

Note

Some of the steps in this tutorial require that the user belong to either the RepositoryAdministrator role in the database or the SQL Server sysadmin role.
On the toolbar, click the New Query button. A blank query window should open.
In the blank query window, add the following T-SQL statement. This query looks at the sys.change_tracking_databases system view to return a list of databases using the Change Tracking feature.
```
select DB_NAME(database_id),* from sys.change_tracking_databases
go
```
Press the F5 key to run the query. Confirm that the previously created RepositoryCT database is listed.

Installing the Modeling Services database with Change Tracking adds change tracking for installed Modeling Services tables. To view the tables that use Change Tracking, replace the previous text in the SQL Server Management Studio query window with the following T-SQL statements.

use RepositoryCT
go
select S.name as SchemaName,
   OBJECT_NAME(T.object_id) as TableName, 
   T.is_track_columns_updated_on, T.min_valid_version, 
   T.begin_version, T.cleanup_version
from sys.change_tracking_tables T
inner join sys.tables TT on TT.object_id = T.object_id
inner join sys.schemas S on S.schema_id = TT.schema_id
order by SchemaName, TableName
go

Press the F5 key to run the query. The Results window should show a list of tables with Change Tracking enabled.

Using Change Tracking

A Modeling Services database installed with Change Tracking automatically enables Change Tracking on tables installed by the Modeling Services. However, any tables directly added to the SQL Server will not need to immediately use Change Tracking. The Modeling Services stored procedure, [Repository.Item].[AddChangeTracking], can enable Change Tracking for a specific table. The following procedures also show an example of how to view the captured Change Tracking events. Note the differences between the level of detail that Change Tracking provides versus auditing with Change Data Capture (CDC). Change Tracking reveals the rows that changed since last checked, but unlike CDC, Change Tracking does not provide a detailed history of all changes and the column values for those changes. As a result, it is more lightweight and better suited to synchronizing disconnected copies of Modeling Services table data.

Note

The [Repository.Item].[AddChangeTracking] stored procedure requires that the caller belong to the db_owner fixed database role.

For a user to read the auditing data, the user must be a member of the RepositoryChangeTrackingReader role.

To enable Change Tracking for a new Modeling Services table

In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. This script creates a schema, TestSchema, and a table, PeopleTable.

-- Drop any existing objects.
use RepositoryCT
go

-- First remove any previous tables and schemas.
if (OBJECT_ID('[TestSchema].[PeopleChangeEvents]') is not null)
   drop function [TestSchema].[PeopleChangeEvents]
if (OBJECT_ID('[TestSchema].[PeopleTable]') is not null)
   drop table [TestSchema].[PeopleTable]
go
if (SCHEMA_ID('TestSchema') is not null)
   drop schema TestSchema
go

-- Create the TestSchema schema and PeopleTable table.
create schema TestSchema
go
create table [TestSchema].[PeopleTable] 
   (Id [bigint] not null, Folder [bigint] not null, Name [nvarchar](max) null
   constraint [PK_TestSchema_PeopleTable_Id] primary key clustered ([Id] asc))
go

Press the F5 key to run the query.
In the query window, replace the text with the following statements. This script calls [Repository.Item].[AddChangeTracking] to enable Change Tracking on the [TestSchema].[PeopleTable] table created in the previous step. The @baseName parameter specifies the name of the table without the Table suffix. Any table participating in Change Tracking should follow Modeling Services design patterns for table naming conventions. For more information, see Table Design Patterns.
```
exec [Repository.Item].[AddChangeTracking] @schema='TestSchema', @baseName='People'
```
Note

It is not necessary to explicitly enable Change Tracking for tables created by loading “M” images into the Modeling Services database. The generated SQL statements for this load automatically calls [Repository.Item].[AddChangeTracking] on new tables when Change Tracking is enabled for the target database.
Press the F5 key to run the query.

To view Change Tracking events

In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. This script adds rows to the previously created [TestSchema].[PeopleTable]. It also updates the Name field of row 1 three times. These changes are used to observe associated auditing data for the table.

use RepositoryCT
go

-- Create a test Folder
insert into [Repository.Item].[Folders]
([Name], [Folder]) values (N'CTTestFolder', null)

-- Obtain the Id for the new Folder
declare @CTFolder as int
set @CTFolder = (select [Repository.Item].[PathsFolder]('CTTestFolder'))

-- Insert new rows into the [TestSchema].[PeopleTable] table.
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (1, @CTFolder, N'Person1')
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (2, @CTFolder, N'Person2')
insert into [TestSchema].[PeopleTable]
           ([Id] ,[Folder], [Name]) values (3, @CTFolder, N'Person3')
go

-- Update one row in the table multiple times.
update [TestSchema].[PeopleTable]
set [Name] = N'Person4' where [Id] = 1
go
update [TestSchema].[PeopleTable]
set [Name] = N'Person5' where [Id] = 1
go
update [TestSchema].[PeopleTable]
set [Name] = N'Person6' where [Id] = 1
go

Press the F5 key to run the query.
In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. The prior call to [Repository.Item].[AddChangeTracking] generated a function named [TestSchema].[PeopleChangeEvents]. The following statements use [TestSchema].[PeopleChangeEvents] to return all of the recorded changes. This is done by using the minimum valid version number returned from the SQL Server function CHANGE_TRACKING_MIN_VALID_VERSION.
```
declare @sync_version as bigint
set @sync_version = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('[TestSchema].[PeopleTable]'));

select Id, * from [TestSchema].PeopleChangeEvents(@sync_version) AS CT
```
Although SQL Server 2008 provides separate functionality for viewing Change Tracking data, it is important to use the “ChangeEvents” views generated by the [Repository.Item].[AddChangeTracking] procedure. These views can be called by users in the RepositoryChangeTrackingReader role. The views also provide filtering of the Change Tracking data to show only tracking data for rows in the source tables that the user has permission to see. As with updatable security views, this filtering is based on Modeling Services Folders and user permissions to read data related to those Folders. For more information, see Security Tasks (Modeling Services).
Press the F5 key to run the query. The Results window should show three rows, one for each inserted row in the [TestSchema].[PeopleTable]. The row with an Id of 1 was updated three times in the previous scripts. However, Change Tracking still shows only one row for this identifier. Applications using change tracking use the sys_change_version column to get only the most recent changes. The following steps show this process.
In the previously opened SQL Server Management Studio query window, replace the query text with the following statements. This script records the current database-wide versioning number by calling the SQL Server function CHANGE_TRACKING_CURRENT_VERSION. It then makes changes to the previously created [TestSchema].[PeopleTable]. The [TestSchema].[PeopleChangeEvents] function is then called and passed the stored value of the change tracking version number. This shows all changes made since storing the version number.
```
declare @sync_version as bigint
set @sync_version =  CHANGE_TRACKING_CURRENT_VERSION();

update [TestSchema].[PeopleTable]
set [Name] = N'Person7' where [Id] = 1

select Id, * from [TestSchema].PeopleChangeEvents(@sync_version) AS CT
```
Press the F5 key to run the query. The query should show only one row this time. This is the row that was updated during the query. Applications store Change Tracking version numbers to use in later calls to see the primary key values for only those rows that have changed.

Note

Applications should use the SQL Server function CHANGE_TRACKING_MIN_VALID_VERSION to verify the minimum valid Change Tracking version number for a table. If this is lower than the stored version number, then the application should do a full synchronization by reading the entire table again. Then the application should store the latest version number again using the CHANGE_TRACKING_CURRENT_VERSION function.

Share via