Database schema synchronisation with RedGate
By Vincent on Thursday 10 February 2011, 07:45 - Permalink
If you are in the process of setting up a continuous integration system, you might be wondering how to deploy your databases changes to your target environments. It's not an easy question to answer at first. Databases are not really files, neither code.
You could write migration scripts by hand and run them against the environment. It works, this is for instance what I was doing with Ruby On Rails and Capistrano deployment tool. It's is just not so "safe". Conflicts can be detected if you put your migrations files under source control. However it's very difficult to keep track of the version of your database schema and your code. When building you have to make sure you migrate to the right revision for the code you will deploy.
Today I want to do the same with Hudson (our continuous integration server)
on Microsoft SQL servers, and avoid the writing of migration scripts. The
solution is actually to put your database schema under version control, using
SVN for example. Then, your schema becomes files and directories. This means
you can manage versions, see the history, rollback, detect and resolve
conflicts and so forth.
Solution: RedGate
You actually need those tools:
- one to put your database under version control (SVN)
- the other to synchronize (deploy) your databases changes from subversion to your environment (test, production, dev, etc)
The good news is that RedGate offers (not for free though) those tools:
- SQL version control
- SQL compare
Those tools are GUI. But you can use them in command line mode, making it easy to integrate with your continuous integration server.
SQL version control
The process to put your database under version control is pretty straightforward. Install Redgate Version control. It's an add-in for SQL Visual Studio. No need to read any documentation at first, just link your DB with your repository. But here are a few screenshots:
Visual studio with Redgate add-in
Link your database to your repository:
That produces those files on your local file system:
Deploy/migration from SVN
Then here is the portion of code I use in MSBUILD to perform the synchronization:
<Target Name="SyncIntegrationDBSchema" DependsOnTargets="Deploy">
<!-- Update Redgate Compare file -->
<Message Text="Updating Redgate Compare commands files..." />
<CreateItem Include="$(WORKSPACE)\sitecorewebsite\Dev\Src\redgate_commands.xml">
<Output TaskParameter="Include" ItemName="redgate_compare_commands"/>
</CreateItem>
<FileUpdate Files="@(redgate_compare_commands)" Regex="scripts_path" ReplacementText="$(WORKSPACE)\sitecorewebsite\Dev\Dep\runtime\integration_db_schema"/>
<FileUpdate Files="@(redgate_compare_commands)" Regex="target" ReplacementText="$(IntegrationDBTargetDatabase)"/>
<FileUpdate Files="@(redgate_compare_commands)" Regex="server" ReplacementText="$(IntegrationDBTargetServer)"/>
<Message Text="Update Redgate Compare commands files: done!" />
<!-- Sync from scripts to Database -->
<Exec Command='SQLCompare.exe /argfile:$(WORKSPACE)\sitecorewebsite\Dev\Src\redgate_commands.xml' WorkingDirectory="C:\Program Files (x86)\Red Gate\SQL Compare 8" />
</Target>
We use a separate file to provide Redgate with options (redgate_commands.xml):
<?xml version="1.0"?> <commandline> <scripts1>scripts_path</scripts1> <database2>target</database2> <Server2>server</Server2> <include>Table</include> <include>Identical</include> <verbose/> <synchronize/> </commandline>
scripts1: path to your database once checked out from SVN.
Usualy, your CI server will take care of the check out from SVN.
database2: name of the database you want to migrate
server2: name or the server where the database is
seating
include: we want to migrate the Tables. We specify "Identical"
because we don't want the build to fail when the databases are similar (no
change to perform)
synchronize: actually does the work. If you don't specify
this, it will just simulate
verbose: let's make the tool being talkative and actually
print the SQL statements that are being generated.
Result
Here is the output when there is nothing to change (DBs are already in
sync):
SyncIntegrationDBSchema: Updating Redgate Compare commands files... Updating File "D:\esr.sacl\sitecorewebsite\Dev\Src\ESR.SACL\redgate_commands.xml". Updating File "D:\esr.sacl\sitecorewebsite\Dev\Src\ESR.SACL\redgate_commands.xml". Updating File "D:\esr.sacl\sitecorewebsite\Dev\Src\ESR.SACL\redgate_commands.xml". Update Redgate Compare commands files: done! SQLCompare.exe /argfile:D:\esr.sacl\sitecorewebsite\Dev\Src\ESR.SACL\redgate_commands.xml SQL Compare Command Line V8.50.12.20 ============================================================================== Copyright � Red Gate Software Ltd 1999-2010 TRIAL STATUS: You have 11 days left in your trial. Unlicensed Trial Comparing database Scripts.integration_db_schema with database XXXXXXX.ZZZZZZZZZ... Registering data sources Creating mappings Comparing Applying Command Line Items Creating SQL Synchronizing databases (from DB1 to DB2) Summary Information =============================================================================== DB1 = D:\Dev\Dep\runtime\integration_db_schema DB2 = XXXXXXX.ZZZZZZZZZ Object type Name DB1 DB2 ------------------------------------------------------------------------------- Table [dbo].[table1] == == Table [dbo].[table2] == == Table [dbo].[table3] == == Role db_owner == == Role db_accessadmin == == Role db_datareader == == Role db_datawriter == == Role db_ddladmin == == Role db_securityadmin == == Role db_backupoperator == == Role db_denydatareader == == Role db_denydatawriter == == Role public == == Schema db_owner == == Schema db_accessadmin == == Schema db_datareader == == Schema db_datawriter == == Schema db_ddladmin == == Schema db_securityadmin == == Schema db_backupoperator == == Schema db_denydatareader == == Schema db_denydatawriter == == Schema INFORMATION_SCHEMA == == Schema dbo == == Schema guest == == Schema sys == == ------------------------------------------------------------------------------- Details - comparing D:\\Dev\Dep\runtime\integration_db_schema with XXXXXXX.ZZZZZZZZZ ===============================================================================