My team recently decided to start using RoundHouse to automate our database deployments, and I tried searching for documentation specific to using RoundHouse on a legacy database, but I didn’t find much. So I’m sharing here what I learned while setting up RoundHouse to be used on an existing database.

I’m going to keep this post focused specifically on the steps needed to get up and running with RoundHouse on a legacy database. I won’t go into much detail on all the different features RoundHouse has to offer because there’s plenty of other resources for that.

Many RoundHouse tutorials I found recommend creating a separate console project in Visual Studio that executes RoundHouse for the Db deployments. I prefer to just use the command line app provided by RoundHouse instead.

Here are the steps I came up with. I’m not an expert in RoundHouse, so there might be a better way, but this worked for me.

Create the RoundHouse folder

The first step is to create the folder that will contain all the SQL scripts for RoundHouse, and make sure it’s included in the source control for the project. You will also want to create the individual sub-folders that RoundHouse uses. Here are the default folders for RoundHouse:

alterDatabase\
runBeforeUp\
up\
runFirstAfterUp\
functions\
views\
sprocs\
indexes\
runAfterOtherAnytimeScripts\
permissions\

All these folders will be empty to begin with. We’ll populate a few scripts in the permissions folder, but other than that there’s no need to create sql scripts at this time. You’ll only need to start creating scripts when you want to change the database in the future.

Make a backup of the Database

Now you will want to take a backup of the database, but don’t include the data in the backup. It should just contain the schema, sprocs, views, etc. Create another folder called restore and copy the backup file to it.

RoundHouse will skip this backup script during regular deployments. But it will be available to use when we want to stand up the database on a new server.

Create the Permissions scripts

The last step is to create the necessary permission scripts for the database. If the database is located in multiple environments with permissions specific to each environment, then you’ll want to create a separate permission script per environment.

Fortunately RoundHouse makes it easy to create environment specific scripts. It provides a simple naming convention to denote which environment the script is supposed to be applied to.

The naming convention for environment specific scripts is: {EnvironmentName}.scriptName.ENV.sql

So LOCAL.sproc_permissions.ENV.sql will be ran in the LOCAL environment, and TEST.sproc_permissions.ENV.sql will be ran in the TEST environment.

Run RoundHouse

And now you’re ready to perform your first automated database deployment with RoundHouse. So run the RoundHouse console app in the folder you just made and point it to the database you’re using. You can find the exact command line arguments you need in RoundHouse’s documentation.

This first deployment you perform won’t do much because the only scripts it will run are the permissions scripts. But if you look at the database tables, you will notice RoundHouse added 3 new tables that it uses to keep track of the scripts it applies to the database.

Going Forward

Now when you want to make a change to the database, don’t update the database directly, but instead create a script in the ‘up’ folder. And then run RoundHouse and see how it finds the script you added and applies to the database for you.

And that’s really all there is to get up and running on RoundHouse. I was surprised by how little work was actually required to RoundHousify a database, which is another reason why I like RoundHouse!