Automating Database Deployment: Part 4 Making Changes Using RoundhousE to Migrate the Database

With the Automating Database Deployment: Part 3 Running RoundhousE post we walked through running a RoundhousE deployment for the first time to create the database and several database objects.  Script naming importance was talked about to help maintain the correct execution order. The tables were empty so a second deployment was run with new scripts added to populate the tables. That run demonstrated how RoundhousE will skip scripts that do not need to be run again.

EVERYTIME Scripts

Next up is using the EVERYTIME switch in a script file to force RoundhousE to run a script every time a deployment is run. To illustrate this let’s assume that there is a requirement that all indexes for every table in the database have to be rebuilt after a deployment. A utility script is built to rebuild all the indexes. In the name of the script the switch EVERYTIME is put in the name somewhere. In this case I put it at the end of the script name. Copy the 500.Utility.RebuildIndexes.EVERYTIME.sql script into the “9 Indexes” directory. RoundhousE usually runs scripts in that directory only if they are new or have changed. When RoundhousE finds the word EVERYTIME in the script name it will know to run that script every time no matter what directory it is in.

We are also going to alter the Customers table to add an IsActive column. Find the CustomersTableEdit.txt file and open it then copy the entire script. Navigate to the “3 RunBeforeUp” directory and open the 100.Cust.Customers.sql script. Delete everything in this script and paste in the contents of the CustomersTableEdit. The changes include a new line in the documentation header, an additional column in the initial create statement, as well as a conditional alter table statement at the end. The new column is in both the initial create and the alter so that deployments to new environments get the column created with the table and don’t have to go back and alter them.

-DryRun Switch

In version 0.8.7 of RoundhousE there is an option to make the deployment do a “dry run”. That means RoundhousE will log all the scripts that would run but not actually run them. This is a great way of double checking the deployment to ensure it is successful. To do a dry run on this pass of RoundhousE add in “-dryrun” to the end of your PowerShell command. The PS script is listed at the end of this article with the dryrun switch, please make sure the servername and directory paths are all correct for you if you do not have the PS script saved from the last article.

Run the PS script to kick off RoundhousE and you will notice in the output under the “3 RunBeforeUp” entry that the 100.Cust.Customers script is highlighted and has “DryRun:” in front of it.

The same with the index rebuild script. If you check the Cust.Customers table there is no IsActive column in the table so in fact the script was not run. Navigate to the Output directory and drill in to the latest log directory and the log file was created, but no scripts were copied here. There is an issue I found when testing RoundhousE, there is still a version record being written when the -dryrun switch is being used. (Issue 275)

Remove the “-dryrun” from the PowerShell command and run it again. The “IsActive” column will be created in the Cust.Customers table and the rebuild index script will run. If you want to prove that the EVERYTIME switch works, run the PS command again and you will see an entry in the log that it is running the rebuild index script.

Environment Scripts

RoundhousE can be setup to run different scripts based on the environment as well. It works similar to the EVERYTIME switch. For the environment script RoundhousE looks for the pattern of “.ENV.” in the script name which denotes a script that will only run in a particular environment. That file name will also need to include the name of the environment that the script runs in. For example, there are different permission scripts that run for DEV, QA & PROD environments. The script names could look like this:

DEV.Permissions.env.sql

QA.Permissions.env.sql

PROD.Permissions.env.sql

The last piece to this is when the RoundhousE command is executed a “-env <Environment Name>” switch needs to be included. In our example DEV,QA or PROD would be put after the -env. RoundhousE uses the <Environment Name> in the switch to match with files that have .ENV. in the file name.

RoundhousE Modes

RoundhousE has 3 different modes that it can run in  to accommodate different deployment work flows. These different modes are Normal, DropCreate and RestoreRun.

The Normal RoundhousE mode is the default when deploying. It is the mode that we have been running in for this walkthrough. RoundhousE will create the database and RoundhousE objects as necessary and run the scripts as usual.

The DropCreate mode of RoundhousE will drop the target database. Then a second run is initiated in the normal mode of operation to create the database and RoundhousE objects and run the scripts.

The RestoreRun mode will restore a backup of the database prior to applying the latest scripts. This allows for situations where a baseline database is used. This is useful for QA and Development environments.

You can read more about these modes and possible uses for them here: https://github.com/chucknorris/roundhouse/wiki/RoundhousEModes

Wrap Up

We’ve walked through a basic example of how RoundhousE can help answer those questions of how to make database deployments work. Using RoundhousE will allow you to deploy a set of scripts to different environments the same way every time. Audit information is created to make it easy to research exactly what was run in each environment for each deployment. A centralized repository for log files would make it easy to research deployments across multiple environments. Last, RoundhousE can apply versioning to a database and can do so using a method that is sure to work in your shop.

RoundhousE has more optional behavior that is controlled by command line switches, those switches are all documented in the Configuration Options section of the wiki.  https://github.com/chucknorris/roundhouse/wiki/ConfigurationOptions We used some of these options for renaming directories, redirecting the output and doing a dry run.

Most of the information I found on RoundhousE was from the wiki on GitHub.  There are some questions out on Stack Overflow that have good information in them. https://stackoverflow.com/questions/tagged/roundhouse  If your shop is running Octopus Deploy here is an article on using RoundhousE with Octopus. http://www.bentillman.net/deploying-db-migrations-with-octopus-deploy-and-roundhouse/

$DB = "RoundhousEDB"
$Server = "localhost"
$Files = "D:\source\RoundhousE\Sandbox"
$CreateDB = $Files + "\CustomCreateDB.sql"
$AlterDB = $Files + "\1 AlterDatabase"
$RunAfterCreate = $Files + "\2 RunAfterCreateDatabase"
$RunBeforeUp = $Files + "\3 RunBeforeUp"
$Up = $Files + "\4 Up"
$RunAfterUp = $Files + "\5 RunFirstAfterUp"
$Functions = $Files + "\6 Functions"
$Views = $Files + "\7 Views"
$Sprocs = $Files + "\8 StoredProcs"
$Indexes = $Files + "\9 Indexes"
$RunAfterAny = $Files + "\10 RunAfterOtherAnyTimeScripts"
$Perms = $Files + "\11 Permissions"
$Version = $Files + "\Version\RoundhouseDBVersion.txt"
$Out = $Files + "\Output"
 
&D:\RoundhousE\rh\roundhouse\build_output\RoundhousE\rh -database $DB -servername $Server -files $Files -createdatabasescript $CreateDB 
-alterdatabasefolder $AlterDB -runaftercreatedatabasefolder $RunAfterCreate -runbeforeupfolder $RunBeforeUp -upfolder $Up -runfirstfolder $RunAfterUp 
-functionsfolder $Functions -viewsfolder $Views -sprocsfolder $Sprocs -indexesfolder $Indexes -runAfterOtherAnyTimeScriptsfolder $RunAfterAny 
-permissionsfolder $Perms -versionfile $Version -output $Out -dryrun