Knowledge base

Logging changes to shared databases in SQL Source Control

Date: 27/07/2012
Product: SQL Source Control

Note: Change logging is only available in SQL Source Control version 3.1 and above.

When working on a shared database, developers work on the same database simultaneously (see Database development models). This can cause confusion about which developer made each change. To fix this, SQL Source Control keeps a log of changes made to all shared databases on a server.

By default, the log is saved in tempdb. Because tempdb only stores temporary data, information about who made a change is eventually lost. When this happens, the Changed By column lists 'Unknown' as the user who made the change.

To avoid this, we recommend you create a new database named to log changes in. This makes sure information isn't lost, and database administrators can set appropriate security restrictions.

Creating the change log database

You can use the following SQL script template to create the change log database. You can modify the script as needed.

USE master
EXECUTE ('CREATE DATABASE CHANGELOG')

ALTER DATABASE CHANGELOG SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE CHANGELOG SET ANSI_NULLS OFF
ALTER DATABASE CHANGELOG SET ANSI_PADDING OFF

ALTER DATABASE CHANGELOG SET ANSI_WARNINGS OFF
ALTER DATABASE CHANGELOG SET ARITHABORT OFF
ALTER DATABASE CHANGELOG SET AUTO_CLOSE OFF
ALTER DATABASE CHANGELOG SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE CHANGELOG SET AUTO_SHRINK OFF
ALTER DATABASE CHANGELOG SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE CHANGELOG SET READ_WRITE
ALTER DATABASE CHANGELOG SET RECOVERY SIMPLE
ALTER DATABASE CHANGELOG SET MULTI_USER
ALTER DATABASE CHANGELOG SET PAGE_VERIFY CHECKSUM
ALTER DATABASE CHANGELOG SET DB_CHAINING ON

EXECUTE ('USE CHANGELOG IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name=''guest'') EXECUTE sp_grantdbaccess guest')

Editing the config file

After the change log database is created, each member of your team must edit a local config file so SQL Source Control can access it.

Note: Every member of your team must follow these instructions to edit the config file on their computer for SQL Source Control to log their changes.

1. Make sure SQL Server Management Studio is closed.
2. Go to the SQL Source Control config files folder. By default, this is located in %localappdata%\Red Gate\SQL Source Control 3
3. Open RedGate_SQLSourceControl_Engine_EngineOptions.xml in a text editor.

   Note: this file may not exist in some installations. If it doesn't exist in your installation:

   a. Create an .xml file named RedGate_SQLSourceControl_Engine_EngineOptions.xml in the folder.

   b. Open the file in a text editor and paste this into it:

   <?xml version="1.0" encoding="utf-16" standalone="yes"?>

   <EngineOptions version="3" type="EngineOptions">

   </EngineOptions>

   c. Continue reading from step 5 below.

4. Make sure the EngineOptions version line reads:

   <EngineOptions version="3" type="EngineOptions">

5. Below the EngineOptions version line, add:

   <TraceCacheDatabase>CHANGELOG</TraceCacheDatabase>

   Ignoring any comments (indicated with <!>), the final file should look like this:

   <?xml version="1.0" encoding="utf-16" standalone="yes"?>

   <!--comment-->

   <EngineOptions version="3" type="EngineOptions">

   <TraceCacheDatabase>CHANGELOG</TraceCacheDatabase>

   </EngineOptions>

   Note: The example above does not include any extra lines you may have included. For example, you may have included additional lines to set up SQL Source Control with TFS2012 or tfs.visualstudio.com.

6. Save and close the file.

SQL Source Control will now use the change log database to log changes made to all linked databases.

To check if change logging is set up correctly, interact with a linked database. In the CHANGELOG database, the RG_AllObjects table should appear. You can inspect the table to see changes appearing in it.

Note:

• The change log database will only be used by SQL Source Control to save information about changes to linked databases. It will not be used for any other purpose.
• Recovery must be set to simple mode.
• Each developer must have read and write access to the database.
• The change log database will contain details of changes made to all databases linked to SQL Source Control. Users will see the names of modified objects, but not the data itself. If these names contain sensitive information, consider restricting access instead of using the guest role.
• You can delete the change log database, but history about changes will be permanently deleted.

Server types not supported by change logging

SQL Source Control cannot log changes made to databases on Azure or SQL Express servers.

Object types not supported by change logging

Changes made to the following object types cannot be logged:

• Application Role
• Full Text Catalog
• Full Text Stoplist
• Search Property List

Changes made to the following object types can't be logged in the current version of SQL Source Control, but may be supported in future versions:

• Asymmetric Key
• Certificate
• Constraint
• DDL Trigger
• DML Trigger
• Event Notification
• Extended Property
• Partition Function
• Partition Scheme
• Role
• Schema
• Symmetric Key
• Table Key
• User
• User-defined Data Type
• User-defined Table Type
• User-defined Type

Note: Changes made to static data cannot be logged.