I’m not a database guy. In fact, it’s one of the aspects of programming I least like. So it surprised some of my colleagues when I told them I was attending a session at Techorama aimed at DBAs.

But the description struck a chord with me. Grant Fritchey gave a session named "Solving the Database Deployment Problem".

In my short career, I’ve only seen one place where database deployment was done correctly. And it was my first employer. Every change to the SQL database had to be put in a .sql file. The file went into source control. All these scripts could be executed multiple times on our local development sandbox (our own PC, no connections to servers). Essentially, you could rebuild the database time and again and start messing about from scratch.

These scripts were also executed on the build server, staging server, and finally production servers. We used RoundhouseE for database versioning and it all went more or less automatic and it was a blast!

Other companies seem to struggle with their database (although they won’t admit the arcane and tedious wizardry that is easy-once-you-know-how-it’s-done is struggling). Scripts need to be executed manually, versioning is done inside the script file, database compares are done, etc.

That’s why I took a picture of what I believe was Grant’s best slide:

First of all, treat it as code. That means (among others) putting it in source control. Too often, databases are still managed like we did in the nineties (well not we, as I was still on my skateboard back then, oblivious of the coolness of programming).

And automate! Don’t waste developer’s or DBA’s precious time with tedious monkey jobs. Invest in an automated solution.

One question I asked him was if he had experience with NoSQL databases and automating that. NoSQL databases don’t always have an easy way of creating scripts for updates and putting that into source control.

While Grant did have experience with NoSQL databases, they had a product (can’t remember which) that did support scripts.

Although it should be feasable to have your application run some code on startup, and check/update the database if necessary. Sort of like Code First Migrations and MigrateDatabaseToLatestVersion in Entity Framework.

Not part of the Techorama session, but this slide deck Grant recently uploaded covers the session in more detail. And be sure to check out this extensive resource on the subject.

Building an Automated Database Deployment Pipeline from Grant Fritchey