Recently I had the opportunity to play around with RoundhousE, a .NET framework for managing and versioning your database. At its simplest, the project works on pure SQL scripts, so your resident DBA will still be nice and happy with you. However, RoundhousE will take care of all the nasty versioning and change-management so you don’t have to.
In brief, RoundhousE works in the following way:
- You point RoundhousE at a directory on disk (e.g., MyProject/DB)
- RoundhousE creates a database, if it isn’t already present
- RoundhousE looks through that folder for various “special” folders that represent different kinds of objects in your database and executes them as needed.
Getting started is fairly easy. Out of the box it comes with MSBuild and Nant tasks ready to go, but we’re going to conveniently ignore those and look at the console runner. If you know the console runner, then hopefully switching to either of the task versions will be relatively straightforward.
First, download the latest version from the RoundhousE homepage. This guide is written against 2.0.131 – if you are using a newer version and something breaks, do let me know. Once you have the package on your machine, open it up and take a look through it. The project ships with a variety of samples to execute if you’re curious. But save that for another time, and extract RoundhousE/ConsoleApp/rh.exe to a folder of your choice.
The next step is to create a folder on disk for our DB scripts to live in. I usually like <Project>/DB, but it doesn’t really matter. Inside of this folder, you’ll create a directory tree that looks like so: (note that you can omit any folders you don’t need. RoundhousE is cool like that)
The folders functions, permissions, sprocs, and views are all somewhat straightforward. Inside of these directories you will place your SQL scripts that define functions, permissions, sprocs, and views respectively. RoundhousE assumes that the scripts in these directories are essentially safe to re-run anytime the contents change. This means that these SQL scripts will need to be scripted to account for the fact that the functions/permissions/sprocs/up/views may already exist.
There are several ways to do this, but if you have no idea what I’m talking about, then lookie here. For example, you’ll need to write a function like so:
This leaves one last folder to discuss – the up folder. This folder represents scripts that should never, ever, ever be run twice against a database. An example would be a table creation script or anything else that would be stateful and change over time. RoundhousE assumes that any time these scripts change should be a fatal error in your database migration/creation. So once you’ve defined a table creation script, if you need to add or remove columns, you’ll need to create that as an additional ALTER TABLE script.
Regardless, RoundhousE takes all of the folders that it finds and will execute any scripts that are modified since it last ran against your database. For functions, permissions, sprocs, and views that means new or altered scripts. For your up directory, it means any new scripts (and a hard, fatal error on changed scripts).
Once you have this setup, running the rh.exe command-line executable is pretty straightforward. Here are the most important command-line switches:
- -d <DATABASENAME> (Required) – Defines what database this should be called in SQL Server. If it isn’t found, RoundhousE creates it for you. So in the above example, this could be whatever you like.
- -f <PATH_TO_DB_FILES> (Required) – Tells RoundhousE where you are keeping your DB files. In the above example, this would be “-f MyProject\DB”
- -s <SERVERNAME> – Optionally specify what server/instance/pipe to connect to. Otherwise, it uses (local)
- --ni – Normally RoundhousE prompts you for keyboard input when you run it. This shuts it up – good if you are integrating with a build script.
- --env – Optionally specifies to RoundhousE that you are running in a particular environment. It’s a neat feature that deserves a separate blog post.
So for example, when writing this blog post, this is what my rh call has looked like:
If all is going well, that should be it! Be sure to look at the author’s blog, http://ferventcoder.com, for more RoundhousE goodness.
-Scott