Versioning legacy database changes

With Entity Framework's model-first approach, keeping track of database changes suddenly became much easier. And as we move steadily away from stored procedures, another part of the puzzle has been solved.

But a quick reality check is in order. Many developers - I'll go out on a limb and say most developers - still work with legacy systems. Systems that are not built with EF and do not have automatic database migrations.

In the past, I've seen some solutions trying to overcome this. They usually involved large and fragile documents, and required (several) people to remember to update them. Because of the pain involved, database changes were avoided when possible, and tests were often suffering from it.

And in the end, the worst case scenario: things break at the worst possible moment. While deploying to the production environment.

Roundhouse to the rescue! Roundhouse lets you keep database migrations as SQL files, happily living where your code lives. It requires some special folders and a little tinkering, but most databases should be able to fit.

It's a command line tool that invokes like this
rh.exe -d:AdventureWorks

The very short story is that you need to keep different kinds of files in different folders, and that once a file has been run, it cannot change and be re-run, unless it is in one of the folders that permit just that. Enough of that, you can find what you need in the Roundhouse wiki. Let's just say it's highly configurable, and it supports SQL Server, MySQL, PostgreSQL, SQLLite and Oracle.

You can install roundhouse from Chocolatey by going
cinst roundhouse

Baseline

Again, most developers don't work with systems that incorporated Roundhouse from the start. So what do we do when a database already exists, complete with functions, stored procedures, views and actual data?

Enter PowerUp. I won't say it's complete, but it gets you a long way. You basically run it like this:

powerup.exe -s:. -d:AdventureWorks -o:"C:\code\ProjectX\AdventureWorks"

and voila! - it scripts views, functions and stored procedures, into the correct folders that roundhouse wants.

That's right, it does not get you 100% up and running. You need to get the baseline from somewhere. The author of Powerup suggests doing a backup-restore, which means running roundhouse like this:

rh -s:. -d:AdventureWorks /restore /rfp:c:\backups\Adventureworks.bak

If you don't have a backup available (yikes!) or for some other reason don't like that approach, you can script whatever you need from MSSMS and put it in your up-folder. I usually make one big script file for baseline, that includes all tables. Protip: Remember to script your indexes.

Creating the database from script may be beneficial if the database has been poorly maintained, as create scripts will fail if stuff it's depending on is missing. Things like views, stored procs and functions don't complain when you delete the table, only when you execute them.

If you don't restore a backup, you'll also need to script Synonyms and Schemas. They should go in the runBeforeUp folder. Below is a screen capture from a roundhouse folder, these are the default folder names (should be self-explanatory) - but they can be configured.

Pitfalls

Logins - not part of your backup.
Jobs - not part of your backup.
Linked servers - not part of your backup.

All of them can easily be scripted, but you want to be careful with logins. If you do a restore from backup, and the database has a custom login user mapping, it will relate to the CID of the server login. You then either have to delete the user in the database and map from the server login, or script the server login with it's CID.

Versioning

When roundhouse runs, it creates three tables in your database, to keep track of which files have been run and what content they had. For the one-time-only-scripts, in the up-folder, the content is hashed, and at the next run re-evaluated. If the hash doesn't match, roundhouse will raise an error and give up. So if your schema changes, you have to add a new file in the up folder and script the change there. It's a good idea to prefix the files 0001, 0002 etc from the start, since roundhouse runs them in ascending order.

Check out the wiki for more details on versioning, it's also highly configurable and even omittable if you don't require it.

Now your database and its changes are stored and versioned together with your code, and can be deployed with every test you run on the build server. At a cost of nothing but your time. That's worth trying out!