How to Enable Change Tracking in a SQL Server Database Project

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

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

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

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

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

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:

1. 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
2. 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
3. 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:

1. Right click the project in the Solution Explorer and select Publish
2. 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.

Ray Barley

Ray is a Principal Architect at IT Resource Partners focused on SQL Server and Business Intelligence. He is a Microsoft Certified Solutions Expert (MCSE) in Business Intelligence, a MSSQLTips.com BI Expert and Ambassador. He is also a co-author of the book: SharePoint 2010 Business Intelligence Unleashed.

• MSSQLTips Awards: Champion (100+ tips)