Database Change Management with Tarantino
Seeing as how I have not graced these pages with my presence in quite some time. I figured I better get something going. A little while back my team started working with some outside contractors and our make shift way of keeping changes in sync and under control was simply not cutting the mustard.
Why do we need DB Change management
Okay then Mr. Developer (or Mrs.), Why do we need source control? We need database change management for the same reason we need source control. To help manage the code and dependencies our applications rely on.
Recipes for Disaster:
Team of developers all working/developing off of a central database
Team of developers working/developing off of local database, with no way to keep changes in sync.
So what’s the problem with these recipes? As new features are developed for an application it is very likely that the database will need schema changes to support these features. These could be as simple as new tables or adding a column. If we have schema changes, we have to make sure that the code base for the app is deployed concurrently with the schema changes.
When deploying software to production, code files and libraries can usually be overwritten with a new version. Databases however must be updated intelligently.
So we’ve got a problem, do we have a solution?
Enter Tarantino. I was talking with Jarod F. and he said he had heard some stuff about it, but had never used it so I started digging around and found enough pieces to get it working. So first thing I did was head over to the Tarantino project site. Starting reading around and then i did a little googling and managed to dig up enough information to really wet my appetite and to get it working. Then Eric Hexter was kind enough to give me some time via IM and cleared up several areas for me.
Before I get into the specifics of how to set all this up, I want to explain how Tarantino finds and keeps changes in sync.
- Tarantino is a forward only change management system, you can’t roll back.
- If you use Tarantino, you can’t go make changes by hand manually
- Tarantino uses RedGate SQL Compare if you want, to compare DB Schemas
- Tarantino makes a table in every DB it touches to keep track of what scripts have been executed on that DB
The system is based on a set of conventions which allow incremental changes to the database schema. The conventions are to create two subdirectories in your database scripts folder. Create– this is where your inital schema change scripts go. Update – this is where your change scripts should be placed. They should be named with the following convention ####SCRIPTNAME.sql where #### is the script number with leading zeros. This will ensure the first script 0001_first_schema_change.sql would be executed first. There is a third folder, ExistingSchema, that you will need if you are going to start using Tarantino on an app that is already in development or production.
There are two ways to use Tarantino, command line, and as a NAnt task. I will be showing the NAnt task method, as it has the most examples available on the net.
Project Setup
So what I have done, is created a very simple project. A class library with a Fluent NHibernate configuration, a test fixture to create the database schema, and two simple entities which I have listed below:
public class Order
{
public virtual int Id { get; set; }
public virtual int OrderNumber { get; set; }
public virtual string PONumber { get; set; }
public virtual IList<OrderItem> OrderItems { get; set;}
public Order()
{
OrderItems = new List<OrderItem>();
}
}
public class OrderItem
{
public virtual int Id { get; set; }
public string ItemName { get; set; }
public string Description { get; set; }
}
and my NHibernate and Test Fixture setup:
[TestFixture]
public class DB_Setup_Fixture
{
private Configuration _configuration;
[TestFixtureSetUp]
public void FixtureSetup()
{
_configuration = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2008
.ConnectionString(c =>
c.FromConnectionStringWithKey("testData"))
.UseReflectionOptimizer()
.ShowSql())
.Mappings(m =>
m.AutoMappings.Add(AutoMap.AssemblyOf<Order>()
.Where(x => x.Namespace.EndsWith("Domain"))))
.BuildConfiguration();
}
[Test, Explicit, Category("DBSetup")]
public void Create_DB_Schema()
{
var exporter = new SchemaExport(_configuration);
exporter.Create(true, true);
}
}
The one thing to make note of here, is that my test is set to explicit, and I gave it a category of “DBSetup”. This will be important once we start configuring NAnt and Tarantino.
So now we have a very simple application that we can use to play around with Tarantino. To get Tarantino working, I am using the modified NAnt files that CodeCampServer is using. I just copied over their NAnt folder they have checked into source control. There are two NAnt build files that are required to use Tarantino. Disclaimer: I am a total NAnt noob, this is not my usual build utility, but I figured out enough to make it work.
Common.build
<?xml version="1.0" encoding="utf-8"?>
<project xmlns="http://nant.sf.net/schemas/nant.xsd">
<!-- Database change management -->
<target name="rebuildDatabase" depends="dropDatabase, createDatabase" />
<target name="updateDatabase">
<property name="action" value="Update" />
<call target="manageSqlDatabase" />
</target>
<target name="createDatabase">
<property name="action" value="Create" />
<call target="manageSqlDatabase" />
</target>
<target name="dropDatabase">
<property name="action" value="Drop" />
<call target="manageSqlDatabase" failonerror="false"/>
</target>
<target name="manageSqlDatabase">
<manageSqlDatabase
scriptDirectory="${database.script.directory}"
action="${action}"
server="${database.server}"
integratedAuthentication="${database.integrated}"
database="${database.name}"
username="${database.username}"
password="${database.password}"
/>
<if test="${action != 'Drop'}">
<echo message="Current Database Version: ${usdDatabaseVersion}" />
</if>
</target>
<script language="C#" prefix="migration" >
<references>
<include name="System.IO.dll" />
</references>
<code>
<![CDATA[
[Function("next-migration-number")]
public static string NextMigration(string path ) {
string[] files = System.IO.Directory.GetFiles(path);
int count=1;
if(files.Length > 0)
{
string filename = System.IO.Path.GetFileName(files[files.Length-1]);
try
{
count = Convert.ToInt32(filename.Substring(0, 4));
count++;
if(count%2 == 0)
count++;
}
catch
{
}
}
return string.Format("{0:0000}", count);
}
]]>
</code>
</script>
</project>
This file sets up most of the plumbing that hooks up the Tarantino NAnt targets for use in the nant.build file
NAnt.build
<?xml version="1.0" encoding="utf-8"?>
<!--EXTERNAL_PROPERTIES: usdDatabaseVersion, CCNetLabel-->
<project name="TarantinoSample" xmlns="http://nant.sf.net/schemas/nant.xsd">
<!-- ************** -->
<!-- Initialization -->
<!-- ************** -->
<include buildfile="common.build"/>
<loadtasks assembly="lib\nant\Tarantino.Nant.Tasks.dll" />
<!-- ***************** -->
<!-- Master Properties -->
<!-- ***************** -->
<property name="company.name" value="Tarantino Test"/>
<property name="assembly.unittests" value="TarantinoSample.dll"/>
<!-- Version settings -->
<property name="project.config" value="debug"/>
<!-- Folder references -->
<property name="dir.solution" value="src"/>
<property name="dir.build" value="build" dynamic="true"/>
<!-- Compilation settings -->
<property name="nant.settings.currentframework" value="net-3.5" />
<property name="file.solution" value="${dir.solution}/${project::get-name()}.sln"/>
<!-- Database migration settings -->
<property name="database.script.directory" value="dbChangeScripts" />
<property name="database.server" value="localhost" overwrite="false"/>
<property name="database.name" value="OrderData" overwrite="false"/>
<property name="database.integrated" value="true" overwrite="false" />
<property name="database.username" value="dbuser" overwrite="false"/>
<property name="database.password" value="P@ssword1" overwrite="false"/>
<!-- ********************************** -->
<!-- Database Change Management Targets -->
<!-- ********************************** -->
<target name="reset-database" depends="dropDatabase, createDatabase">
<nunit2 failonerror="true" verbose="true">
<formatter type="Plain" />
<test assemblyname="${dir.solution}/bin/${project.config}/${assembly.unittests}">
<categories>
<include name="DBSetup"/>
<exclude name="*"/>
</categories>
</test>
</nunit2>
</target>
<target name="db-migration">
<delete file="${database.script.directory}/Update/_New_Script.sql" />
<call target="reset-database" />
<call target="create-versioned-database" />
<property name="migrationScriptName" value="${migration::next-migration-number(database.script.directory+'/Update')}_AutoGeneratedMigration.sql"/>
<if test="${file::exists('c:\program files (x86)\red gate\SQL Compare 8\SQLCompare.exe')}" >
<property name="redgate.exe" value="c:\program files (x86)\red gate\SQL Compare 8\SQLCompare.exe"/>
</if>
<if test="${file::exists('c:\program files\red gate\SQL Compare 8\SQLCompare.exe')}" >
<property name="redgate.exe" value="c:\program files\red gate\SQL Compare 8\SQLCompare.exe"/>
</if>
<exec
program="${redgate.exe}"
commandline="/f /v /server1:${database.server} /server2:${database.server} /database1:${database.name} /database2:${database.name}Versioned /scriptfile:${database.script.directory}/Update/_New_Script.sql /exclude:Table:usd_AppliedDatabaseScript"
resultproperty="execReturnCode"
failonerror ="false"/>
<fail if="${execReturnCode != '0' and execReturnCode != '63'}" message="Redgate Compare Failed!"/>
<if test="${file::exists(database.script.directory + '/Update/_New_Script.sql') == false}">
<echo message=""/>
<echo message="---------------------"/>
<echo message="No Migration Required"/>
</if>
<if test="${file::exists(database.script.directory + '/Update/_New_Script.sql')}">
<move file="${database.script.directory}/Update/_New_Script.sql" tofile="${database.script.directory}/Update/${migrationScriptName}"/>
<echo message="Created Migration File ${database.script.directory}/Update/${migrationScriptName}"/>
</if>
</target>
<target name="create-versioned-database">
<tstamp></tstamp>
<manageSqlDatabase
action="Drop"
server="${database.server}"
integratedAuthentication="true"
database="${database.name}Versioned"
username=""
password="" />
<manageSqlDatabase
scriptDirectory="${database.script.directory}"
action="Create"
server="${database.server}"
integratedAuthentication="true"
database="${database.name}Versioned"
username=""
password="" />
</target>
</project>
Inside this file there are a few pieces that need to be configured for use in your own app, these are all listed under “Master Properties”
Change script creation
So without going into too many details, what I have done is created my initial schema using my Unit Test. From here I could script this out to a text file, but what I did was use SQL Compare, comparing my initial schema to an empty database. This gave me my initial SQL script that creates my database.
Just to verify that my initial schema creation script is good, I will have Tarantino create the Versioned database for me using this command:
lib\nant\nant.exe –buildfile:nant.build create-versioned-database
Tarantino then looks in the ExistingSchema folder, runs any scripts found there, then does the same thing for the Updates folder.
In order to test out the change management features, I made a small change to my domain, adding a decimal of cost to the OrderItem class should do the trick. Rebuild my solution, then back to the command line to run my “Create Database Migration Script.bat” file. This file is just running the NAnt task.
What happens then, is NAnt goes out and runs my DBSetup test that is in my project. Which recreates the “OrderData” database. Tarantino then creates the “Versioned” database, then uses SQL Compare to find the changes between the two, and creates a file in the update folder for me named, 0001_AutoGeneratedMigration.sql NOTE: It is important to rename this file before it is committed to source control, or before you apply these changes to a database. Tarantino tracks the scripts that have been run by filename.
Push to Production or Dev
So I have mentioned two databases so far in this post, OrderData and OrderDataVersioned. These two databases contain no data, that’s not to say that they couldn’t, if you had scripts that put it in. But that is not what they were designed to do. Our app.config is pointing to OrderData, so when we ask NHibernate to build the schema, it drops all the tables and recreates them, losing all data. OrderDataVersioned, is Tarantinos database that is used to keep changes in sync. In our development environment, every dev has a third database, we would call OrderDataProd for instance. This database could have sample data and other information in it. Let’s say, we don’t want to lose our data in this DB so what we do is let Tarantino handle the first two databases, then we deploy our schema changes to our development database. We use this same technique to push changes to production, after backups of course.
We can do this easily with the following command:
lib\nant\nant.exe –buildfile:nant.build updateDatabase –D:database.name=OrderDataProd
Now when I tried to run this, my database OrderDataProd did not exist, so I went to SQL manager, created it, and then ran the command again, only to be slammed with another error. Since the database existed, Tarantino did not run the create schema script, only the update, which created another error. The next command will create the database I want, by executing the schema creation script, then applying any updates to it.
lib\nant\nant.exe –buildfile:nant.build createDatabase –D:database.name=OrderDataProd
Bingo! I now have my 3rd database that I can dump data into and start developing on top of. Just to be sure that we can create another change script, and deploy it to this database, I will modify the domain again, by adding a quantity to the OrderItem object. I will leave this step up to you to try out.
Source Code
I know the blog formatting sometimes jacks up code samples a lot so I am making all the source for this post available two ways:
Information Sources
Eric Hexter – Huge Help
Is it possible to achieve the exact same functionality without using Redgate SQL Compare? It gets VERY pricey having to purchase a “Pro Edition” license for each developer and build server that interact with the database. I don’t understand why SQL Compare is $600… least of all why you have to pay MORE to access the “command-line interface”. This seems like an awfully expensive price to pay for (what should be) a simple task to accomplish. Am I wrong? Maybe I am missing something.
@Brad
$600 per-dev would get very expensive in a hurry. Perhaps you could delegate all script creation to a single box, via some service – that way you would only need to purchase a single license. It would be a little more complex, but it would be pretty solid after you set everything up.
-Charles