Introduction
For all my database related projects, I like to have some way of keeping my db schema in sync with my code and
generally maintaining the db. For the last 4-5 years and 10-15 projects, I have used a custom written tool (with an
innovative name - dbtool), loosely based on Ash Tewari's
DbUpdater.
For the new project, I wanted to try some new approach. Instead of doing SQL incremental change scripts, I've
chosen to go with FluentMigrator. Now, since all my
colleagues and myself are used to dbtool's functionality, I needed to re-implement several things using
FluentMigrator. This is the required functionality:
- Upgrade db - migrate to the latest available version of the database
- Backup db - backup database to a zip file
- Restore db - restore database from zip file
All the functionality should be implemented through MSBuild script, making it easy for use in build environment
like TeamCity,
CruiseControl.NET, Hudson,
etc.
Disclaimer: This isn't going to be a comprehensive article on FluentMigrator, nor MSBuild. For
that, take a look at links at the end of the article, or do a Google search. This article is organized as a set of
problems that I run into while implementing the mentioned functionality, and solutions to those problems.
So, let's begin...
Preparing the solution
Before running migration, we need to have a project with the actual migration code. You can download the source
using the link on top of the article, or browse it on on
GitHub. It contains an empty MVC web
application, migration project, MSBuild scripts and some supporting tools.
Migration project is really simple. It contains Baseline class attributed with
[Migration(0)] and Version001 class attributed with [Migration(1)]. As I
already mentioned, I won't explain the FluentMigrator API here. For that, check the
official documentation.
FluentMigrator and FluentMigrator.Tools NuGet packages are installed to the solution.
You will also need an empty database called FluentMigration in your SQL Server Express instance, or
modify Database connection string in src/Migration.Web/web.config if you want to use
another database name or another database instance.
There are two MSBuild scripts in the \src folder. Migration-initial.msbuild is the
starting (almost) empty version of the build script, and Migration.msbuild is the finished version.
Once we have our solution ready, it's time to start working on those features.
Feature #1 - Upgrade database (run migration)
FluentMigrator has various migration runner tools, for command line, MSBuild and Nant. We'll be using it's MSBuild
task called <Migrate>. Our first version of MSBuild script looks like this:
="1.0"
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
DefaultTargets="Migrate" ToolsVersion="4.0">
<PropertyGroup>
<MigratorTasksDirectory>
$(MSBuildProjectDirectory)\packages\FluentMigrator.Tools.1.0.2.0\tools\AnyCPU\40\
</MigratorTasksDirectory>
<ConnectionString>
Data Source=.\SQLEXPRESS;Initial Catalog=FluentMigration;Integrated Security=True;
</ConnectionString>
</PropertyGroup>
<UsingTask
TaskName="FluentMigrator.MSBuild.Migrate"
AssemblyFile="$(MigratorTasksDirectory)FluentMigrator.MSBuild.dll" />
<Target Name="Migrate">
<Message Text="Starting FluentMigrator migration" />
<Migrate Database="SqlServer2008"
Connection="$(ConnectionString)"
Target="Migration" />
</Target>
</Project>
Cool, we now have everything we need to start, right? Perhaps, but I see some potential problems here.
Problem #1
Since we are using NuGet (I choose to install both FluentMigrator and FluentMigrator.Tools using NuGet), we're kind
of hardcoding to the current version of FluentMigrator (1.0.2.0 in the above script). When we update the
FluentMigrator to the newer version, we also must update the MSBuild file to point to the right folder.
I don't like that, so I chose instead to go with old-school approach and have FluentMigrator.Tools in a separate
subfolder under root\tools. Let's now modify our script to point to the right folder:
<MigratorTasksDirectory>
$(MSBuildProjectDirectory)\..\tools\FluentMigrator\
</MigratorTasksDirectory>
Problem #2
Connection string is hardcoded. Whenever we change application connection string, we need to remember to change it
in MSBuild script also. It would be great if we could just read our current application connection string from
web.config and use that. Well, we can. There's an <XmlPeek> task in MSBuild 4.
An alternative is <XmlQuery> task of
MSBuild Community Tasks library.
Let's add that, and also, let's also reorganize the script a bit:
="1.0"
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
DefaultTargets="Migrate" ToolsVersion="4.0">
<PropertyGroup>
<MigratorTasksDirectory>
$(MSBuildProjectDirectory)\packages\FluentMigrator.Tools.1.0.2.0\tools\AnyCPU\40\
</MigratorTasksDirectory>
<MainProjectDirectory>
$(MSBuildProjectDirectory)\..\Migration.Web\
</MainProjectDirectory>
</PropertyGroup>
<UsingTask
TaskName="FluentMigrator.MSBuild.Migrate"
AssemblyFile="$(MigratorTasksDirectory)FluentMigrator.MSBuild.dll" />
<Target Name="Common">
<XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
Query="/configuration/connectionStrings/add[@name='Database']/@connectionString"
Condition="'$(ConnectionString)' == ''">
<Output TaskParameter="Result" PropertyName="ConnectionString" />
</XmlPeek>
</Target>
<Target Name="Migrate" DependsOnTargets="Common">
<Message Text="Starting FluentMigrator migration" />
<Migrate Database="SqlServer2008"
Connection="$(ConnectionString)"
Target="MyMigration" />
</Target>
</Project>
Since there probably will be some more common things shared between our targets (Migrate,
Backup, Restore), a Common target is added. ConnectionString
property is removed from PropertyGroup and read from web.config in Common
target. Note that the Migrate target now depends on Common target.
OK, that looks better, but it can still be improved.
Problem #3
Team management of connection strings. What if one team member needs to use different database name? And what if
some team members are using SQL Server Express Edition and other team members SQL Server Standard or Developer
edition. Note that the build server could also be considered as a team member. For all those cases,
connection strings would differ, so whenever the working copy is updated, team member would need to check the
connection string and modify it if necessary.
In order to avoid those problems, my team had used separate ConnectionStrings.config files that were
outside of the source control, so each team member had his own version of ConnectionString.config. That
file would then be referenced from web.config or app.config like this:
<connectionStrings configSource="ConnectionStrings.config" />
This worked pretty well for us, but recently I came across another
solution for
this. Ayende's concept is simple. You have a default connection string and machine specific overrides:
<add name="Database" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=FluentMigration;Integrated Security=True;"/>
<add name="Database-MiroslavI5" connectionString="Data Source=.;Initial Catalog=FluentMigration;Integrated Security=True;"/>
<add name="Database-MiroslavLaptop" connectionString="Data Source=.;Initial Catalog=FluentMigration;Integrated Security=True;"/>
The application would first look for machine specific connection string and fall back to default if machine specific
key was not found. This is OK for a small teams with several different machines. For larger teams, these machine
specific connection strings would soon add up. In that case, I would suggest you to use a separated
ConnectionStrings.config file, since it would be easier to maintain.
Since we are using that machine specific trick in our web.config, MSBuild script would also need to be
smart enough to figure out the machine specific or default connection string:
<XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
Query="/configuration/connectionStrings/add[@name='Database-$(ComputerName)']/@connectionString"
Condition="'$(ConnectionString)' == ''">
<Output TaskParameter="Result" PropertyName="ConnectionString" />
</XmlPeek>
<XmlPeek XmlInputPath="$(MainProjectDirectory)\web.config"
Query="/configuration/connectionStrings/add[@name='Database']/@connectionString"
Condition="'$(ConnectionString)' == ''">
<Output TaskParameter="Result" PropertyName="ConnectionString" />
</XmlPeek>
As you can see, we first try machine specific version, using $(ComputerName) MSBuild property, and if
it's not found, then we use the default. Condition="'$(ConnectionString)' == ''" is used to check
whether the connection string is already found or not. The result is saved to ConnectionString
property.
This is it. Now our MSBuild script looks more robust. But if we try to run it now, we'll get an error, since
Migrate task cannot find our migration project dll.
Problem #4
So, we would need to copy migration project dll to the FluentMigrator tools folder. There are several ways we could
do that...
- We could just use MSBuild's
<Copy> task to copy the dll as the FluentMigrator documentation
suggests, but in that case, we would also need to hardcode it's source location. Source could be either
\bin\debug or \bin\release, depending on current build configuration.
- How about using project's post build events to copy output to some
root\build folder, and then we
could have <Copy> task in our MSBuild script? Yes, that would work.
- Instead, I just used Migration project's post build events to copy migration project dll to FluentMigrator tools
folder, like this:
copy "$(TargetDir)Migration.dll" "$(SolutionDir)..\tools\FluentMigrator\"
After that, our MSBuild script runs and executes migration code on the database (providing you have at least an
empty database named the same as in your connection string).
msbuild.exe /target:Migrate Migration.msbuild
Feature #2 - Database backup
Now that we have our migration process ready and running, let's move on to database backup functionality.
Problem #5
There are no SQL running tasks in MSBuild. Fortunately, there are many open source projects with additional tasks.
We're going to use MSBuild Community Tasks.
<MSBuildCommunityTasksPath>$(MSBuildProjectDirectory)\..\tools\MSBuild Community Tasks\Build\</MSBuildCommunityTasksPath>
<MSBuildCommunityTasksLib>$(MSBuildCommunityTasksPath)MSBuild.Community.Tasks.dll</MSBuildCommunityTasksLib>
<BackupFileDirectory>$(MSBuildProjectDirectory)\..\backup\</BackupFileDirectory>
<Target Name="Backup" DependsOnTargets="Common">
<Message Text="Backing up the database to temp folder" />
<MSBuild.Community.Tasks.SqlExecute
ConnectionString="$(ConnectionString)"
Command="BACKUP DATABASE [Migration] TO DISK = N'$(BackupFileDirectory)\Migration.bak' WITH NOFORMAT, INIT, NAME = N'Migration - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
</Target>
In the code above, we have some property fixes for MSBuild Community Tasks (since it's in our \tools
folder and not on default location) and also a new property BackupFileDirectory in our
PropertyGroup. But the database name is hardcoded within the script. And I would like to have a unique
backup file name based on the current time.
Problem #6
To pull out the database name from connection string, we would need to have some kind of regex. Again, MSBuild
doesn't have regex support by default, but it can be achieved using
property functions, a new feature of MSBuild
4.0.
<CreateProperty Value="$([System.Text.RegularExpressions.Regex]::Match($(ConnectionString), `Initial Catalog=([^;])*`))">
<Output TaskParameter="Value" PropertyName="DatabaseName" />
</CreateProperty>
<CreateProperty Value="$(DatabaseName.Replace('Initial Catalog=', ''))">
<Output TaskParameter="Value" PropertyName="DatabaseName" />
</CreateProperty>
This new part creates a property DatabaseName by pulling it from the ConnectionString
property. I couldn't get the plain database name with regex function, so there's a second
<CreateProperty> task that just removes Initial Catalog= string from the
DatabaseName.
Great, now let's get the timestamp and add it to the mix.
<MSBuild.Community.Tasks.Time Format="yyyy-MM-dd-HH-mm-ss">
<Output TaskParameter="FormattedTime" PropertyName="BuildDate" />
</MSBuild.Community.Tasks.Time>
<CreateProperty Value="$(BackupFileDirectory)$(DatabaseName)-$(BuildDate).bak">
<Output TaskParameter="Value" PropertyName="BackupFilePath" />
</CreateProperty>
Again, we are using a task from MSBuild Community Tasks called <Time> and put the formatted time
to a BuildDate property. BackupFilePath is a new property that contains the full path of
the new backup file.
Our <SqlExecute> task now looks like this:
<MSBuild.Community.Tasks.SqlExecute
ConnectionString="$(ConnectionString)"
Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
Now we can try to do backup:
msbuild.exe /target:Backup Migration.msbuild
And... it fails.
Problem #7
We are trying to backup the same database we are connected to. SQL Server refuses to do that. We should connect
using master table. For that, we would need to modify connection string. Here's some more regex magic:
<CreateProperty Value="$([System.Text.RegularExpressions.Regex]::Replace($(ConnectionString), `Initial Catalog=([^;])*`, `Initial Catalog=master`))">
<Output TaskParameter="Value" PropertyName="MasterConnectionString" />
</CreateProperty>
Now we use the new $(MasterConnectionString) property for <SqlExecute> task:
<MSBuild.Community.Tasks.SqlExecute
ConnectionString="$(MasterConnectionString)"
Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
If you run it now, it might work... but then again, it might also fail 
Problem #8
Depending on where you keep your project files, the backup might fail or not. Remember that we put our
BackupFileDirectory under the project's root subfolder \backup? If you keep your project
under your user folder (i.e. C:\Users\Miroslav), then SQL Server doesn't have a permission to create a
file there. SQL Server service usually doesn't run under the current user account, and hence cannot access user's
folder.
OK, so we can't backup directly to user folder, but we can create a backup somewhere else and then simply copy to
our backup destination folder. Let's do that now:
<BackupFileDirectory>$(ALLUSERSPROFILE)\CodeProject\Migration\</BackupFileDirectory>
<BackupOutputDirectory>$(MSBuildProjectDirectory)\..\backup\</BackupOutputDirectory>
The above code goes to PropertyGroup. We replace our old BackupFileDirectory property
with a new value. This time the backup will be done to All Users profile folder (that's C:\ProgramData on Windows 7).
And there's another property - BackupOutputDirectory containing our old
BackupFileDirectory value - our real backup folder. Now we can do backup using those properties:
<MSBuild.Community.Tasks.SqlExecute
ConnectionString="$(MasterConnectionString)"
Command="BACKUP DATABASE [$(DatabaseName)] TO DISK = N'$(BackupFilePath)' WITH NOFORMAT, INIT, NAME = N'$(DatabaseName) - Full Backup - $(BuildDate)', SKIP, NOREWIND, NOUNLOAD, STATS = 10" />
<Message Text="Database backup created" />
<Copy SourceFiles="$(BackupFilePath)" DestinationFolder="$(BackupOutputDirectory)" />
<Delete Files="$(BackupFilePath)" />
<SqlExecute> task remains unchanged. We just copy the backup file from the temp all users folder
to our destination backup folder, and delete the temp one at the end.
To make sure that our backup folders exist, add this to the Common task:
<MakeDir Directories="$(BackupFileDirectory)" />
<MakeDir Directories="$(BackupOutputDirectory)" />
We have a pretty robust backup procedure now. One more thing that could be done. Backup files are usually very big,
and usually are very "compress-able".
Problem #9
Now that we have everything else prepared, zipping the backup shouldn't be an issue. We'll use MSBuild Community
Tasks and it's <Zip> task. And since we need to name our zip file, let's create another property
in Common task:
<CreateProperty Value="$(BackupOutputDirectory)$(DatabaseName)-$(BuildDate).zip">
<Output TaskParameter="Value" PropertyName="BackupOutputPath" />
</CreateProperty>
Now we can use BackupOutputPath property in <Zip> task. Just replace the
<Copy> task we had in the previous step with the <Zip> task.
<MSBuild.Community.Tasks.Zip
Files="$(BackupFilePath)"
WorkingDirectory="$(BackupFileDirectory)"
ZipFileName="$(BackupOutputPath)"
ZipLevel="9" />
That's it. We now have everything that's required for backup. You can try it:
msbuild.exe /target:Backup Migration.msbuild
How about doing backup before each database upgrade / migration? Make your Migrate target depend on
Backup target:
<Target Name="Migrate" DependsOnTargets="Backup">
Great. Two features completed. One more to go.
Feature #3 - Database restore
This isn't something you'll do quite often, but when you need it, an automated restore may come in handy. We already
have all the necessary ingredients for doing restore, so let's just write the target down:
<Target Name="Restore" DependsOnTargets="Common">
<Message Text="Restoring the database from /backup folder" />
<Message Text="Unzipping the backup file $(RestoreFileName).zip" />
<MSBuild.Community.Tasks.Unzip
ZipFileName="$(BackupOutputDirectory)$(RestoreFileName).zip"
TargetDirectory="$(BackupFileDirectory)" />
<CreateProperty Value="$(BackupFileDirectory)$(RestoreFileName).bak">
<Output TaskParameter="Value" PropertyName="BackupFilePath" />
</CreateProperty>
<MSBuild.Community.Tasks.SqlExecute
ConnectionString='$(MasterConnectionString)'
Command="DECLARE @SQL varchar(max); SET @SQL = ''; SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID('$(DatabaseName)') AND SPId <> @@SPId; EXEC(@SQL); RESTORE DATABASE [$(DatabaseName)] FROM DISK = N'$(BackupFilePath)' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10;"
/>
<Delete Files="$(BackupFilePath)" />
<Message Text="Database restored successfully from $(RestoreFileName).zip" />
</Target>
To do restore, we're just reversing the backup steps. Notice how we have a RestoreFileName property
that's not defined in our MSBuild file? That property will need to be defined when calling MSBuild, like this:
msbuild.exe /property:Configuration=Release;RestoreFileName="CleanDatabase" Migration.msbuild /target:Restore
Note that only files within \backup folder are supported. That's enough for our simple case.
Unzipping is done to All Users profile folder again - to allow SQL Server to pick the backup, even if the zip file
itself was under user profile folder.
Database restore is also using <SqlExecute> task. This time, SQL command is a little bigger,
since it contains the code for killing all the connections to the particular database before trying to do restore
operation.
After restoring the database, we remove the temp backup file from the All Users profile folder. Now we can run it:
msbuild.exe /property:Configuration=Release;RestoreFileName="<ExistingBackupName>" Migration.msbuild /target:Restore
Tiding things up
Downloaded code also contains three batch files dbupgrade, dbbackup and
dbrestore, for easier manual running of these targets. It will save us some typing (no need to call
msbuild.exe with additional parameters). Only dbrestore has an additional parameter - the name of
backup file to restore.
Phew, that was longer than expected, but we now have everything we need to maintain our database. Beside the benefit
of having an easy way of running Upgrade, Backup and Restore manually, the scripts can be used in continuous
integration environment. Build server could run them within build tasks and prepare a testing and/or staging
database.
Points of Interest
It was somewhat fun, somewhat frustrating to experience and solve / workaround all those problems, but the final
result looks very usable. This combination is already used in two projects I'm working on. We didn't loose anything
in moving from our custom dbtool to FluentMigrator and MSBuild, and we got the simpler migration code
(C# code instead of SQL scripts). This won't work for everybody or for projects of all sizes, but for a small
projects with smaller databases it would be a good choice.
Once more, the source is available on GitHub,
and through the download link on top of this article.
FluentMigrator:
MSBuild:
History
Initial version
- 2012-06-12: Original article
