.SHARP{C#DERS}

.net development, server management, and related topics

Introducing AliaSQL

Last month I blogged about Database Deployer. Since then I added some features, changed the project name, and partnered with Jeffrey Palermo to get the word out on this project. Much of this post is a reiteration of the previous post but updated with the current state of things.

I spent the last few months looking to improve our database change management. For years we have worked off of a common “development” database that we periodically compared with Redgate SQL Compare and generated change scripts. For the most part we have been doing continuous deployment - unless the code corresponded with a database change. In that case it required manual intervention. Messy.

We have gotten used to having tons of nice test data in development to help us test and verify what we are building. The test data we created also gave the testers a nice starting point to see something in action. In order to improve our process we all need to be working off of a local database that is generated with changes nicely committed to source control.

I tried many database change management solutions and they each had some problem that made it less than ideal for my situation.

  • DBUp - Scripts are embedded resources in a DLL.

  • ReadyRoll - Works great until you get to a computer without it installed. Wouldn’t work for an OSS project.

  • Tarantino - SQL SMO dependency, .Net 2.0, extremely complex solution with 26 projects.

  • RoundhousE - In retrospect this one is pretty good. With a full screen of switches and options it just seemed too complicated on first look.

Here were my requirements in no particular order

  • Can do automated database migrations using CC.net and TeamCity

  • No licensing restrictions so we can ship it with an open source project

  • Sometimes its ok to have database changes be ran via a tool but sometimes the DBA wants to run the raw scripts themselves. Database scripts need to be normal sql files that can be generated with SQL Server Management Studio or Redgate SQL Compare and not be embedded in a DLL.

  • Each developer need to have a local copy of the database that is created from scripts that came from source control.

  • Ability to update a database by comparing available scripts with what has already been run.

  • Developers need some test data to work with that should never make its way to production.

  • We have legacy databases that are not currently in the change management process. There needs to be a way to add records to production indicating all scripts have already been ran against it as a sort of baseline.

  • Scripts should run in transactions when possible

  • Ability to add and run scripts without leaving Visual Studio.

About 2 years ago a few of us went to Headspring for an Agile Bootcamp and were introduced to their database change management tool “Tarantino”. It is a nice tool that we have been actively using, but it has some problems. The project has moved from Google Code to BitBucket to GitHub over the last few years and the docs don’t always match how it really works. It is compiled against .Net 2.0 and has dependencies on SQL Server 2008 SMO. We work on .Net 4.5 and SQL 2012 so using Tarantino requires installing a ton of legacy stuff to make it work.

I started off thinking I would fork Tarantino and send a pull request back but after digging at it for a few days I have changed so much it would be too big an ask to send a pull request. Tarantino consists of 25 projects, 2 of which have any connection to the so called “Database Deployer”. There is a Windows GUI version that I could never get to work, an Nant task, and a bunch of other undocumented bits that I frankly have no idea what they are for. I just wanted the code that makes the Database Deployer as seen on Nuget -http://www.nuget.org/packages/tarantino-db-deployer/

I ended up deleting all the other projects, removing the SQL SMO dependency, adding a test data facility, and added a way to bring legacy databases into the change management process.

Lifted from the original Tarantino documentation, here is the problem that Database Change Management is attempting to solve:

  • Most significant business applications rely on at least one relational database for persisting data

  • As new features are developed, database schema changes are often necessary – i.e. new tables, columns, views, and stored procedures

  • Database schema changes and corresponding code changes must always be deployed together

  • While deploying software to a production environment, code files and libraries may usually be deleted or overwritten – Database files, however, must be intelligently manipulated so as not destroy vital business data

The development tools available allow developers to make changes to their environment and do not address the problem of applying those changes to additional environments. (i.e. development, quality assurance, staging, production).

The solution to this problem:

Successful database change management requires that a consistent process be applied by all team members. Without a consistent process than the tools provided in this solution will not provide its full value.

AliaSQL database change management consists of:

  • Each developer using their own local database to do their development work.

  • Each environment using it's own database. i.e. Development, Testing, Staging, Production

  • Each developer maintains his changes locally. When the database changes are ready to commit alongside the application source code, the developer follows these steps:

    • Create a change script that wraps all of the database changes into a single transactional change script. A Tool like Red Gate SQL Compare makes this a 30 second operation. I use a simple powershell against Microsoft's free SQL Package tool to generate change scripts. Here is an example.

    • Save the change script into a folder in your source tree call Update.

    • Commit the change script along with the source code that corresponds to the change.

  • The continuous integration server detects changes to the source control repository than it:

    • It builds the application code.

    • It executes the applications unit tests.

    • Executes the database create task to create a new database with all of the changes that are in source control.

    • Executes the projects integration (data access) tests.

    • Marks the build a success when all the tests pass.

  • Each developer runs the build script locally after receiving new schema changes scripts from the source code repository.

  • The change management managers execute the script using the AliaSQL tool to run the database scripts against the Dev, Test, and staging environments when the environments receive their next update of the source code.

Prerequisites

The following environmental conditions make the use of the database change management process frictionless.

  • An automated build script. This includes compilation, unit tests, integration tests, versioning, packaging, publishing

  • Source Control

  • A continuous integration server that has a separate database instance dedicated to executing schema change scripts and integration tests

  • A team that believes that a little process can go a long way.

  • Separate environments for testers, marketing/content types, and staging.

  • The will to do things better

Some problems we faced

  • We have some full text indexes and the base SQL Server Express doesn’t have full text. You need the massive SQL Server Express with Advanced Services for that.

  • Making test data scripts isn’t very fun.

  • Multiple databases for a single solution adds a level of complexity

Usage

It depends on having a database scripts folder with these 3 folders:
- Create
- Update
- TestData

Example usage:

AliaSQL.exe [Action] [Database Server] [Database Name] [Scripts path]

Create database and run all scripts in Create folder.
Logs to usd_AppliedDatabaseScript

AliaSQL.exe Create .\sqlexpress Demo ./scripts

Run all scripts in Create and Update folders that have not yet been ran. If target database does not already exist it will be created.
Logs to usd_AppliedDatabaseScript

AliaSQL.exe Update .\sqlexpress Demo ./scripts

Drop and recreate database then run all scripts in Create and Update folders.
Logs to usd_AppliedDatabaseScript

AliaSQL.exe Rebuild .\sqlexpress Demo ./scripts

Run all scripts in TestData folder that has yet been ran - expects database to already exist. Normally this would be for development and would never be ran against production. 
Logs to usd_AppliedDatabaseTestDataScript

AliaSQL.exe TestData.\sqlexpress Demo ./scripts

Logs but does not execute all scripts in Create and Update folders that have not yet been ran - expects database to already exist. This is to add the usd_AppliedDatabaseScript table and a record of all scripts to a legacy database.
Logs to usd_AppliedDatabaseScript

AliaSQL.exe Baseline .\sqlexpress Demo ./scripts
 

Get it now

Check it out on Github at https://github.com/ericdc1/AliaSQL and on Nuget at https://www.nuget.org/packages/AliaSQL

I recommend starting with the AliaSQL Kickstarter that creates the Create, Update, and TestData folders and provides the Visual Studio runner, create an empty C# console app then install AliaSQL.Kickstarter from the package manager console:It is on on Nuget at https://www.nuget.org/packages/AliaSQL.Kickstarter

Note that I have Psake set up to build the code, run the (limited) unit tests, create the nuget package, and zip it all up. This is designed to work with Visual Studio 2013 and SQL Server 2012. It should work against SQL Server 2008 and will compile against older Visual Studio versions with a change in the /p:VisualStudioVersion= setting in default.ps1.

Some links that gave me ideas and inspiration

https://code.google.com/p/tarantino/wiki/DatabaseChangeManagement

http://devlicio.us/blogs/rob_reynolds/archive/2011/10/23/roundhouse-intelligent-database-migrations-and-versioning.aspx

http://keithbloom.blogspot.com/2012/05/roundhouse-with-legacy-database.html

Comments (1) -

  • Eric

    6/18/2014 6:59:36 PM | Reply

    Looking forward to checking this out! Found you through the Jeff Palermo blog post...

Pingbacks and trackbacks (1)+

Loading