How to Enable Change Tracking in a SQL Server Database Project

By: Ray Barley | Comments (3) | Related: > Change Data Capture

Problem

I use the SQL Server Database Project for developing, maintaining, and publishing/deploying my databases. I need to implement Change Tracking in one of my databases. How can I do that in the SQL Server Database Project?

Solution

The SQL Server Database Project can be used to manage the development and deployment of a SQL Server database. You can download the SQL Server Data Tools for Visual Studio 2012 here.

The SQL Server Database Project has a "publishing" capability which essentially allows you to generate a T-SQL script to deploy your database to a target server. As part of the publishing capability, you can use a Post-Deployment script which is T-SQL code that gets included in the script that is generated when you publish your database. The T-SQL code in the Post-Deployment script gets executed after the database objects have been created; e.g. tables, views, stored procedures, etc. A key point to note about the Post-Deployment script is that it is executed every time you publish your database, even if you are only publishing updates rather than the full database.

In this tip I will start off with how to setup change tracking with just T-SQL code, then perform the following in a SQL Server database project:

Enable change tracking for the database
Enable change tracking for a table in the Post-Deployment script
Setup a publishing profile
Generate a T-SQL script to publish the database

I am going to assume that you are familiar with the SQL Server database project. If you haven't used it before or need a refresher, take a look at the Creating a New Database Project Tutorial. Although it's based on the 2010 version, it will get you up-to-speed quickly.

Setup SQL Server Change Tracking with T-SQL

Change Tracking is a feature that when enabled will keep track of the rows that are inserted, updated or deleted in a table. Change Tracking needs to be enabled at the database level and also enabled on each table that you want to track. If you would like more details on Change tracking, take a look at the tip Using Change Tracking in SQL Server 2008.

Assuming we have a database named MSSQLTIPS_CT_DEMO, run the following T-SQL code to enable change tracking on the database (retain change tracking data for 5 days and automatically clean it up):

 ALTER DATABASE MSSQLTIPS_CT_DEMO
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON);

GO

To see the list of databases that have change tracking enabled, run the following T-SQL code:

SELECT DB_NAME(database_id) [DATABASE_NAME] 
FROM sys.change_tracking_databases;

GO

Assuming we have a table named CUSTOMER, run the following T-SQL code to enable change tracking on the CUSTOMER table (just track the rows that have changed without the column change detail):

ALTER TABLE dbo.CUSTOMER
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

To see the list of tables that have change tracking enabled, run the following T-SQL command:

SELECT OBJECT_NAME(object_id) [TABLE_NAME]
FROM sys.change_tracking_tables;

GO

Perform an insert to generate some change tracking data:

INSERT dbo.CUSTOMER ([NAME]) VALUES ('ABC COMPANY');

GO

Query the change tracking data by running the following T-SQL code:

SELECT ct.ID, ct.SYS_CHANGE_OPERATION, c.[NAME]
FROM CHANGETABLE(CHANGES dbo.CUSTOMER, 0) ct
JOIN dbo.CUSTOMER c ON c.[ID] = ct.[ID];

GO

Sample SQL Server Database Project

In lieu of going through the steps to create a SQL Server database project, I'm providing the following screen shot of the completed database project in the SQL Server Data Tools Solution Explorer:

Enable Change Tracking for the SQL Server Database

To setup change tracking for the database, we will need to set some properties for the project. Right-click on the database project in the Solution Explorer and select Properties; you will see the Project Settings:

Click the Database Settings button, select the Operational tab, and fill in the Change Tracking properties as shown below:

Enable SQL Server Change Tracking for a Table

We need to create a Post-Deployment script in our SQL Server database project that will use T-SQL code to enable change tracking on specific tables when we publish to a target server.

Perform the following steps to add the Post-Deployment script:

Add a Scripts folder to the project: Right-click the project (change-tracking-database-project-sample) in the Solution Explorer, select Add, New Folder; name the folder Scripts
Add a Post-Deployment script to the project: Right-click the Scripts folder in the Solution Explorer, select Add, Post-Deployment script; accept the default file name
Add the following T-SQL code to the Post-Deployment script:

IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_databases 
               WHERE database_id = DB_ID())
BEGIN
     ALTER DATABASE [$(DatabaseName)]
     SET CHANGE_TRACKING = ON
     (CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON);
END
               
IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_tables 
               WHERE object_id = OBJECT_ID('dbo.CUSTOMER'))
BEGIN
     ALTER TABLE dbo.CUSTOMER
     ENABLE CHANGE_TRACKING
     WITH (TRACK_COLUMNS_UPDATED = OFF)
END

Main points about the above code:

The Post-Deployment script defines a DatabaseName variable which is set when you publish or save in a publishing profile
The Post-Deployment script will automatically include a USE [$(DatabaseName)] statement
Since the T-SQL code in the Post-Deployment script is executed every time you publish, you have to enable change tracking only if it's not already enabled
The IF NOT EXISTS code uses the change tracking information tables that I showed in the Setup Change Tracking with T-SQL section above to determine whether change tracking is enabled
The ALTER DATABASE with SET CHANGE_TRACKING = ON isn't required because we specified change tracking for the database in the project settings; I like to put it there so that every time I publish the database, change tracking will get turned on if necessary
Add an ALTER TABLE command for each table that you want to track

Note that you can only have one Post-Deployment script in the SQL Server database project.

Add a Publishing Profile

A publishing profile is saved with the project and stores various settings for the publishing operation. The idea is that you can create a different publishing profile for each of your target environments; e.g. development, testing, production, etc.

Follow these steps to add a publishing profile:

Right click the project in the Solution Explorer and select Publish
Fill in the dialog as shown below:

Main points about the above dialog:

Click the Edit button to specify the database connection properties
Click the Save Profile As button to save the profile in the project; I named mine localhost
Note that in the Target database connection I am using a named instance of SQL Server; e.g. localhost\SQL2012

One thing that I like to do when I'm developing a database is to specify that every time I publish, a new database is created. This keeps my database clean and the contents of the database always match the project exactly. To automatically create a new database each time you publish, click the Advanced button on the Publish Database dialog and check Always re-create database as shown below:

The way that I use publishing is I have separate profiles for development, QA, production, etc. Usually I would only specify Always re-create database in a publishing profile for development.

After you have saved one or more publishing profiles in the database project, you can simply double click it in the Solution Explorer to open it up and publish the database or generate a T-SQL script that you will run at a later time.

Publish the Database

Double click on the saved publishing profile in the Solution Explorer to bring up the Publish Database profile and click the Publish button. The Data Tools Operations window will show messages as the publishing operation proceeds. If there are any errors, click View Results to investigate.

In lieu of publishing immediately, you can click the Generate Script button which will generate T-SQL code that you can run in SQL Server Management Studio (SSMS) or with the SQLCMD utility. The code requires that you enable SQLCMD mode; in SSMS you can do this by clicking Tools, Options, Query Execution, SQL Server, General and checking By default, open new queries in SQLCMD mode. This will take effect with the next query window that you open in SSMS.

One thing I noticed about the publish operation is that if you do not specify Always re-create database, the following code will be included in the T-SQL script:

IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CHANGE_TRACKING = OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'Altering [dbo].[CUSTOMER]...';


GO
ALTER TABLE [dbo].[CUSTOMER] DISABLE CHANGE_TRACKING;
  WITH (TRACK_COLUMNS_UPDATED = OFF)

Essentially if the database exists, the script will turn off change tracking at the database level. However, if the database exists and any tables are enabled for change tracking, the ALTER DATABASE SET CHANGE TRACKING OFF statement will raise an error.

You will probably need to manually edit the T-SQL script and remove the ALTER DATABASE SET CHANGE TRACKING OFF and any ALTER TABLE commands that turn off change tracking for the table.

Next Steps

Download the sample code here and try it out.
Change tracking is a great way to have SQL Server automatically track the rows that have been inserted, updated or deleted in a table.
Take a look at Using Change Tracking in SQL Server 2008 for additional details on change tracking.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips