RoundhousE: Version Your Database

RoundhousE Logo

Versioning your database is as important as versioning your code.  How much time do we waste currently before we are even productive when we have to fix or enhance something in a database?

RoundhousE versions your database how you want. The recommended way is to version based on source control, but that may not be your solution. But for a moment let’s pretend it is.

Why would versioning your database based on source control be a big help? What happens if you get a prod fix (or work order) to fix a view or a stored procedure (sproc)?  How do you know where in source control that is? Do you even know what repository it lives in? How can you even be sure right away that the item you have in source control directly maps to the item in production? Oh right. You can’t. At least not without doing a manual comparison (or with a diff tool).  And if you are not even familiar with the item, how do you get back to it in source control? You ask someone. And then someone else. And so on until you find someone that knows where you can find that view in source control. “Oh….Samsonite! I was WAYYYY off.”

How much time are we using up currently before we are even productive? Probably quite a bit more than we realize. Let’s put it in perspective. You are a new hire today. If I was to point you to a database and tell you to fix a view and update the view in source control, how soon do you think you could be productive? What questions are you going to ask me?

Where is the view in source control? When is this needed by? etc etc

If your company instead used RoundhousE (RH) you could empower the new hire or any developer that suffers from can’t remember stuff (CRS)! Just point them to the database. Let’s take a moment to see what happens as we run the migration.

RoundhousE Versions During Migration

Remember our look at the runner? I want to concentrate in just one area.

Versioning database with version 0.0.0.67 based on http://roundhouse.googlecode.com/svn

Attempting to resolve version from C:\code\roundhouse\code_drop\deployment\_BuildInfo.xml using //buildInfo/version.
Found version 0.0.0.67 from C:\code\roundhouse\code_drop\deployment\_BuildInfo.xml.
Migrating TestRoundhousE from version 0 to 0.0.0.67.
Versioning TestRoundhousE database with version 0.0.0.67 based on
http://roundhouse.googlecode.com/svn.

What exactly is happening here? We are looking into an xml file that contains a version.  RH also asks for the repository the versioning information comes from.  That all gets recorded in the database.

What does that build file look like?

<?xml version="1.0" ?>
<buildInfo>
  <projectName>RoundhousE</projectName>
  <companyName>FerventCoder Software</companyName>
  <versionMajor>0</versionMajor>
  <versionMinor>0</versionMinor>
  <buildNumber>0</buildNumber>
  <revision>25</revision>
  <version>1.0.22.25</version>
  <repositoryPath>http://someotherplace/svn/repositoryname/</repositoryPath>
  <microsoftNetFramework>net-3.5</microsoftNetFramework>
  <msbuildConfiguration>Release</msbuildConfiguration>
  <msbuildPlatform>Any CPU</msbuildPlatform>
  <builtWith>UppercuT v. 0.9.0.216</builtWith>
</buildInfo>

It is the output of an automated build product called UppercuT. Some of you have heard me talk about it before. It records the version in an xml output file to be used by deployment products etc.

Now I have a way to find the query at an exact point in source control history!

What if I don’t use UppercuT? That’s fine, create something in your automated build to create a simple xml file that contains a version. Then pass the file path and xpath for the version info to RH.

Or another way you can get to version is to point to a DLL you have versioned based on source control.

Attempting to resolve assembly file version from C:\code\roundhouse\code_drop\RoundhousE\NAnt\roundhouse.dll.
Migrating TestRoundhousE from version 0.0.0.67 to 0.0.0.67.
Versioning TestRoundhousE database with version 0.0.0.67 based on http://roundhouse.googlecode.com/svn.

RH will automatically pull the file version from the DLL and use it to version the database.

Back To Your Empowerment

So you query the scripts ran table for the view. You notice version_id is 1.

Version Id 1 for vw_Dude.sql

Now you query the version table.

Repository and Version stored in a table. Why didn't I think of that?

Boom. You have a path to a repository. You also have an exact point in source to find the item.  You can now look revision 67 versus the current revision in source control.

You now know exactly where to look. Two steps to productivity. You get that production fix done in no time and we are so impressed we give you a raise on the spot.

Database name is always in source control before the scriptsOne Repository – Multiple Databases

That’s great, but I hit multiple databases when I deploy. Easy as pie. Do you remember last time how we said to always include the database name before the scripts (see the picture to the right)? For each database, it is just another call to RH to run telling it the specific information about the next database. Super simple.

Multiple Repositories – One Database

What if I have multiple repositories that address the same database? We got you covered. If you have more than one repository versioning a database, they will version the database independently.

Found version 1.0.22.25 from C:\code\roundhouse\code_drop\deployment\_BuildInfo.xml.
Migrating TestRoundhousE from version 0 to 1.0.22.25.
Versioning TestRoundhousE database with version 1.0.22.25 based on
http://someotherplace/svn/repositoryname/.

Notice how RH said the prior version was 0. That’s because versioning is based on repository. That repository had never ran against this database before. Now looking in the version table, we see two different repositories with two different versions.

Two repositories. Two different versions

Conclusion

Versioning your database is as important as versioning your code. RoundhousE is a very powerful product for migrating your database (rivaling even some paid alternatives). And it’s free. RoundhousE is just going to keep getting better. So why not give it a try? DBAs approve. It makes auditors smile. I heard it once helped a lady walk again.

kick it on DotNetKicks.com Shout it

Print | posted @ Thursday, November 19, 2009 1:26 AM

Comments on this entry:

Gravatar # re: RoundhousE: Version Your Database
by Ryan Anderson at 12/1/2009 12:09 AM

Brilliant!
Rob I am all in!

RoundHousE has me hooked...!
Comments have been closed on this topic.