RoundhousE DB Migration: Action Shots

I just started the appetites with my last post on RoundhousE. RoundhousE itself has a migration sample to help you get familiar with how it works. You can get to that by using a subversion client to download the source. When you download the RoundhousE source from SVN and run build.bat, you get a code_drop folder. Go in there and look at the deployment folder for a sample run.

Prerequisites: Locally installed (and running) SQL Server 2008

Run That Bad Boy

When you get into the code_drop folder, take a look below at the screenshot. Run LOCAL.DBDeployment.bat. It will run the through a NAnt deploy of RoundhousE and create a database called TestRoundhousE.

Deploy DEPLOY! 

When you run LOCAL.DBDeployment.bat, this is the output (provided you have SQL Server 2008 installed):

All kinds of total sweetness

Running RoundhousE on (local) (TestRoundhousE). Looking in C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE for sql scripts.
Executing RoundhousE against contents of C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE.
Creating TestRoundhousE database on (local) server if it doesn't exist.
Creating RoundhousE schema if it doesn't exist.
Creating [RoundhousE].[Version] table if it doesn't exist.
Creating [RoundhousE].[ScriptsRun] table if it doesn't exist.
Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\NAnt\RoundhousE.dll.
Migrating TestRoundhousE from version 0 to 0.0.0.59.
Versioning TestRoundhousE database with version 0.0.0.59 based on http://roundhouse.googlecode.com/svn.
Running 0001_CreateTables.sql on (local) - TestRoundhousE.
Running 0002_ChangeTable.sql on (local) - TestRoundhousE.
Running vw_Dude.sql on (local) - TestRoundhousE.

Take a look here. It gives you feedback about where it is running and where it is looking for sql scripts.  Then it attempts to resolve the version based on a Dll’s version (can also do it through an xml file from the build containing a version).  It will create the database if it doesn’t exist (does not require action create, it’s smart about that).  Then RoundhousE will create it’s tables (one for tracking versions, and one for tracking what scripts have run).  Then it’s going to look through folders recursively and find sql files to run.

What happens when I run it a second time?

For real?!

Running RoundhousE on (local) (TestRoundhousE). Looking in C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE for sql scripts.
Executing RoundhousE against contents of C:\code\roundhouse\code_drop\deployment\scripts\..\..\db\TestRoundhousE.
Creating TestRoundhousE database on (local) server if it doesn't exist.
Creating RoundhousE schema if it doesn't exist.
Creating [RoundhousE].[Version] table if it doesn't exist.
Creating [RoundhousE].[ScriptsRun] table if it doesn't exist.
Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\NAnt\RoundhousE.dll.
Migrating TestRoundhousE from version 0.0.0.59 to 0.0.0.59.
Versioning TestRoundhousE database with version 0.0.0.59 based on http://roundhouse.googlecode.com/svn.
Skipped 0001_CreateTables.sql either due to being a one time script or finding no changes.
Skipped 0002_ChangeTable.sql either due to being a one time script or finding no changes.
Running vw_Dude.sql on (local) - TestRoundhousE.

The second time it runs it is only going to rerun items that are stateless, items that do not contain data, like functions, views and stored procedures.

FUTURE ENHANCEMENT: RoundhousE will only rerun stateless items if they have changed.  That will cut your migration time way down for databases that are heavy one the stored procedures side.

RoundhousE and SQL Server

What does that goodness give you? Let’s crack open SSMS (Sql Server Management Studio).

 I'm sold!

You get two tables (schemas and tables names are completely configurable): Version and ScriptsRun.

Versioning a database...how come I never thought of that?

Looking a the version table, I have the idea of both a source repository and a version. What does that mean? It means RoundhousE knows that you may have multiple repositories that hit the database and that’s cool.

I think I just creamed in my drawers! 

The ScriptsRun table here captures some really interesting information. There is script_name, the id of the version it is associated with, and then there is the text_of_script. Whoa…the text of the actual script I ran?! That’s awesome! But ….why?! We do this for two reasons. You now have an audit of what actually ran. This makes both DBAs and auditors happy. RoundhousE also has a goal in mind of versioning back down. This becomes extremely simple if we keep track of those things.

What happens if I start running bigger and bigger scripts? It’s a minimal impact. I tested this with a 15MB production level insert script for running an initial load of data and it ran like a champ. My database also only grew about 4MBish larger than the original database without recording the script. So the impact is minimal.

The next column we’ll focus on is one_time_script. This column is what tells you automatically whether this script is located in a one time run folder (the up folder in this case) or if it is a stateless item that will not cause data loss or error to run again and again.

Okay next column: text_hash – what is that? That is how RoundhousE can very quickly determine if there have been changes to the script. Which is a good transition into the next section.

RoundhousE is Smarter

What happens if someone changes a DDL/DML file that is meant to run only once? Add even one extra space in the file and this is what you get:

OMG WTF dude?!

RoundhousE encountered an error:
System.Exception: 0001_CreateTables.sql has changed since the last time it was run. By default this is not allowed - scripts that run once should never change. To change this behavior to a warning, please set warnOnOneTimeScriptChanges to true and run again. Stopping execution.

It is configurable to set this to a warning, but the important thing to note is that RoundhousE is going to try to help your developers see that they made changes to something that won’t run. That way they are not making changes to files that should not change.

Go get the source and give it a whirl.

Next up – RoundhousE Configuration. Same bat time. Same bat channel.  And remember, “There is no charge for awesome.”

 

 

kick it on DotNetKicks.com Shout it

 

Print | posted @ Friday, November 13, 2009 1:17 AM

Comments on this entry:

Gravatar # re: RoundhousE DB Migration: Action Shots
by Nik De Clercq at 2/17/2010 4:14 AM

In the GettingStarted document you asked to let you know if the sample runs on SQLExpress 2008. Well it does.
The only change I had to make was to the property server.database which I had to change to "(local)\SQLExpress" because SQL Express on my PC is installed as a named instance.
Gravatar # re: RoundhousE DB Migration: Action Shots
by Robz at 2/18/2010 8:02 PM

This is awesome! Thanks Nik!
Comments have been closed on this topic.