Under the shared database development model, developers work on the same database simultaneously. SQL Source Control keeps a log of changes made to all shared databases on the server, so you can see who made which change.
By default, the log is saved in
tempdb. However, tbcause
tempdb only stores temporary data, information about who made a change is eventually lost. When this happens, the Changed By column lists the object as changed by Unknown:
To avoid this, you can create a new, dedicated database to log changes in. This makes sure information isn't lost, and database administrators can set appropriate security restrictions.
Step 1: Creating the change log database
You can use this SQL script template to create a dedicated change log database named
ChangeLog. You can modify the script as needed.
Step 2: Editing the config file
After the change log database is created, you need to edit a local config file so SQL Source Control can access it.
- Make sure SQL Server Management Studio is closed.
Go to the SQL Source Control config files folder. By default, this is located at %localappdata%\Red Gate\SQL Source Control 3
Open RedGate_SQLSourceControl_Engine_EngineOptions.xml in a text editor.
Make sure the
EngineOptions versionline reads:
EngineOptions versionline, add:
Ignoring any comments (indicated with
<!->), the final file should look like this::
- Save and close the file.
SQL Source Control will now use the change log database to log changes made to all linked databases.
How to check if changes are being logged
To check if change logging is set up correctly, interact with a linked database.
Afterwards, in the
ChangeLog database, the
RG_AllObjects table should appear. You can inspect the table to see changes appearing in it as changes are made.
Object types not supported by change logging
Changes made to the following object types can't be logged:
- application roles
- full text catalogs
- full text stoplists
- search property lists
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 keys
- DDL triggers
- DML triggers
- event notifications
- extended properties
- partition functions
- partition schemes
- symmetric keys
- table keys
- user-defined data types
- user-defined table types
- user-defined types