Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Enable Change Tracking in a SQL Server Database Project


By:   |   Read Comments (2)   |   Related Tips: More > 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:

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:

Project Settings

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

Change Tracking properties

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:
Publish Profile

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:

Advanced Publish Settings

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.


Last Update:






About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, August 28, 2014 - 9:52:53 AM - Raymond Barley Back To Top

Change tracking isn't designed to provide the information on who made a change since it only records the primary key value of every row that is inserted, updated or deleted.  I use it to determine what changed; e.g. I have a relational data warehouse that is used to populate a cube.  I just need to know what rows have been inserted, update or deleted since the last time I processed the cube.  Change tracking works great in this case.

When I have to maintain a history of whop changed what I do it the old fashioned way with a trigger that records the action and who performed it in a change history table.  I know alot of people aren't fans of triggers but I think this is one case where a trigger is perfect. 

Maybe change data capture could be used since you can specify the columns that you want to track and it could provide the appropriate level of detail.

 


Wednesday, August 27, 2014 - 8:48:13 PM - Jeff Bennett Back To Top

Ray,

 

Have you figured out how to link Update/Delete/Insert records in the CHANGETABLE(CHANGES dbo.tablename, 0) with the associated login that made the change?

 

Thanks

Jeff Bennett

St. Louis, MO


Learn more about SQL Server tools