In the shared database development model, developers work on the same database simultaneously. By default, information about who makes a change to a shared database is read from the default trace and saved in tempdb. However, because tempdb resets when the server is restarted, information about who made a change is permanently lost. When this happens, the Changed by column lists the object as changed by Unknown:
To prevent this, you can create a new database to permanently log information about 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.
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 they're made.
Object types not supported by change logging
Changes made to the following object types can't be logged:
- application roles
- asymmetric keys
- DDL triggers
- DML triggers
- event notifications
- extended properties
- full text catalogs
- full text stoplists
- partition functions
- partition schemes
- search property lists
- symmetric keys
- table keys
- user-defined data types
- user-defined table types
- user-defined types