Database Deployment in DevOps

One of the many obstacles to database development and DevOps is how to deploy databases consistently and how to automate the process. Database deployments have historically consisted of last minute treasure hunts to find or build scripts of the changes that have been made. Inevitably that leads to missed changes, no records of what was changed, and no record of when changes were made.

How can a database be deployed the same way every time and still incorporate possible exceptions for the target environment? What about auditing and knowing exactly what ran during a deployment? Can a database be reliably versioned? A deployment tool of some kind must be used to alleviate these concerns.

Deployment tools for databases fall into two category types. The first type compares the schema of two databases, a source and target, to generate a change script. The change script is then run against the target to make it conform to the schema of the source. The second category operates based on change scripts that are pre-defined. Instead of creating change scripts programmatically, SQL scripts are written. Those scripts are then run by the deployment tool.

Which is better? That is a decision that should be made by each shop. Both make sense and are much better than the treasure hunt method. The database difference tool could introduce changes that were not necessarily meant for deployment if there were objects created or changed for testing in the source database. It is best to read through the generated scripts to ensure that the schema compare interpreted everything correctly. The second type of tool is just running scripts that are fed to it. If there are scripts missing, poorly written or tested scripts then unplanned changes or errors could occur. This risk can be diminished with a well-run development shop and source control.

RoundhousE Introduction

In this series I’m going to introduce and walk through one deployment solution, RoundhousE. I’ll discus how RoundhousE fulfills the questions posed earlier, how to configure the tool and then walk through a demo.

RoundhousE is a database deployment tool developed by Rob Reynolds (B) who is also responsible for the software management platform, Chocolatey.  RoundhousE is (currently) a command line based deployment engine. It automates database deployment, database versioning and change logging for SQL Server, Oracle, Access, MySQL, SQLite, PostgreSQL and Azure as of RoundhousE version 0.8.7. RoundhousE runs in the Windows environment currently and there is a possibility of running in .NET core in a future release. RoundhousE can be incorporated into a larger deployment framework via PowerShell to handle the database portion of a larger application migration or just run on its own.

RoundhousE falls in the second category of deployment tools, running predefined scripts. Those scripts could be written by developers or created with another tool.

Versioning is stored and tracked in a table residing in the target database. When RoundhousE deploys a database a new record to that table with a version number. Database versions can be based upon a version in a TXT file, a version of a DLL, a tag in an XML file or passing in a version in the command line.  More information on how each versioning option works can be found here: https://github.com/chucknorris/roundhouse/wiki/Versioning

Change logging is done in the form of a log file written for each deployment. RoundhousE also writes a copy of every script that was run during the deployment to the logging directory for future reference.

Documentation for RoundhousE can be found in the wiki for the GitHub project here: https://github.com/chucknorris/roundhouse/wiki  There are some updates that need to be done but in whole is a good source.

To get ready for the walk through of RoundhousE the first thing is to get the code. The different options available for obtaining it are found in the wiki here: https://github.com/chucknorris/roundhouse/wiki/Getroundhouse  At the time of this article the official release is 0.8.6. This article is written based on 0.8.7 though which would need to be obtained from source and compiled. There are directions for doing that under the #Source section. The only thing missing from .6 that will be in this article is the dryrun switch. The other prerequisite to running RoundhousE is .NET 3.5.

While getting the code and prerequisites installed it would be a good idea to browse through the issues list. Familiarize yourself with the current issues that have been reported. At the time of writing this there were no issues detrimental to the core functionality of RoundhousE.

https://github.com/chucknorris/roundhouse/issues

https://github.com/chucknorris/roundhouse