Along with this post, please make sure to read Eric Coffman’s very thorough post introducing all of his work on AliaSQL.

Way back in 2006, Kevin Hurwitz and I both worked at a start-up company focused on Sarbanes-Oxley compliance software.  While the business model didn’t quite pan out, we had a killer team, and we created some innovations that have gained widespread adoption even to this day.  Among them are:

• Rich C# Enumerations – Nuget package to simulate Java’s rich enumeration classes
• Onion Architecture – a method for structuring a Visual Studio solution for DDD-based applications
• Tarantino Database Change Management – a build tool for automating database migrations

While any artifacts from 2006 are long-gone, these tools and patterns live on to this day, and many folks have adopted these around the world.  I do have to give credit where credit is due.  In 2007, both Kevin and I were working with Eric Hexter on projects at Callaway Golf Interactive, and Eric material contributed to a large rewrite of the automated database migrations, AND, he was very involved in labeling it as “Tarantino”, honoring the famous movie producer.  And to this day, Tarantino has been widely adopted as a simple and targeted way to perform automated database migrations in continuous integration environments.

Reviewing the problem

In many teams, source control is normal, but databases are left out in the cold.  Perhaps a massive SQL file is exported from time to time as the DDL definition of the database, but deploying database changes across test, staging and production environments is still an issue and is error-prone.

Several common mistakes exist when managing the database change management process.  The first is developers sharing a development database.  The second is developers maintaining local databases that are synced manually.

When sharing a development database, changes to this database have to be a blocking issue.  Working on branches becomes an issue because when a database change happens, at least one developer ends up working with a version of the code that has a problem with the new database change.  The external environmental change ends up wasting the time of at least one team member.

When each developer maintains isolated databases that are synced manually, the team invariably has to have a meeting in order to figure out what the needed database changes are for a given build that needs to be deployed to production.  Having a repeatable QA process is difficult here.

The problem manifests itself when production deployments happen and some database change is left out or performed differently than intended.  This can result in deployment-time troubleshooting and adhoc code-changes or database changes in a heroic effort to salvage the deployment.

The solution

The premise to automated database migrations is to have a process that executes in the exact same fashion in every successive environment so that by the time the production deployment happens, there is no chance of it not working properly.  In addition, the process should not vary based on what feature branch or hot-fix branch in source control is being worked.  And the process should scale to an unlimited number of pre-production and production environments.

One school of thought with database migrations uses a process of examining the target database and then generates appropriate scripts to be run based on the state of the target database.  In fact, Microsoft’s DACPAC in the database project works like this.  From a philosophical level, I don’t like this approach because it doesn’t allow the QA process to vet the scripts that will actually execute from environment to environment, and there is no opportunity to mix in data transforms in between multi-step table transformations, like the merging or splitting of columns.

In addition, I reject migration philosophies that believe roll backs are possible.  Perhaps roll-backs could be performed for completely reversible operations, but as soon as a migration includes a DROP COLUMN operation, a roll-back scenario is broken because there is no way to roll back and reverse the deletion of the data in the column.  In addition, once an install package has failed to properly install, how can one trust it to then faithfully do the right thing in a roll back attempt?

Introducing AliaSQL, the new simple standard in SQL-based database migrations

Right now, you can download AliaSQL (pronounced “ey-lee-us-Q-L”) from Nuget.  Eric Coffman was a Tarantino user for several years and then started encountering issues because Tarantino hadn’t been maintained in a few years.  So he forked the project.  Tarantino was great, and I, and others, poured many hours into it.  It does include much more than just database migrations, and that’s one of the reasons that a new project is warranted – to provide focus.

Tarantino still has a dependency on SQL Server 2008.  SQL Server 2012 isn’t supported, and SQL Azure has some issues.  The SQL SMO dependency was a great idea in its time, but AliaSQL does away with this dependency and achieves broad compatibility as a result.

How to upgrade from Tarantino to AliaSQL

the good news is that AliaSQL is 100% backward compatible with Tarantino database migrations.  This is absolutely intentional.  The process and philosophy of the original Tarantino (which was actually NAnt-script-based) from 2006 is preserved while taking advantage of a significant rewrite to provide more detailed logging, transaction support, and database compatibility.

If you have an application and a build script that currently uses Tarantino, I encourage you to make the simple and trivial upgrade.  You can check out my Iteration Zero sample project to see how easy it is to make the upgrade.  https://bitbucket.org/jeffreypalermo/iteration-zero/commits/45b6d2d67a02d7977ddb8be404dca30364241138  The recommended way to get AliaSQL.exe is from a Nuget search, but you can also directly download just the EXE here.

Then, just updated your build script (a psake build script is shown here).

 task RebuildDatabase -depends ConnectionString {

-        & $base_dirtarantinoDatabaseDeployer.exe Rebuild $databaseServer $databaseName $databaseScripts

+        & $base_diraliasqlaliasql.exe Rebuild $databaseServer $databaseName $databaseScripts

     }

 }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Notice that for an application already using Tarantino, the only change is the path to aliasql.exe.  All other major behavior is exactly the same as well.

AliaSQL differences from Tarantino

Although backward compatibility is excellent, you will immediately notice some key differences:

• Immediate compatibility with SQL Server 2012 as well as automatic compatibility with future versions.  This was accomplished by breaking the SQL SMO dependency.
• Transactions are added:  With Tarantino, when a script failed, the database was in an inconsistent state because transactions were not used.  AliaSQL implements transactions for each SQL file, split by GO lines.  This enables transactional rollback if something goes wrong when executing scripts.
• New TestData command that executes scripts in a TestData folder for the purpose of SQL statements that include test data for non-production environments.
• New Baseline command that initializes an existing database for future management by AliaSQL
• AliaSQL Kickstarter Nuget package that creates a database-specific Visual Studio project to contain SQL scripts and provide a quick console project for the execution of AliaSQL from within Visual Studio

An upgrade illustrated

Before AliaSQL, still running Tarantino.

The automated build after upgrading to AliaSQL:

Notice the augmented logging the specified that a transaction was used.

Conclusion

In closing, go out an download AliaSQL now and upgrade your old Tarantino applications.  It’s a quick, drop-in upgrade, and you’ll be immediately ready to go for SQL Server 2012, SQL Azure, and future versions.

And finally, check out the project documentation and get involved on Github!

Thanks so much Eric Coffman, for grabbing the reins and creating this new tool that continues the heritage of this popular approach to automated database change management/database migrations.