Automating Database Deployment: Part 3 Running RoundhousE

Now that we have a basic understanding of RoundhousE database deployment from the first two parts of this series let’s get to the good stuff. If you haven’t read the first articles you might take some time to go back through them.  Here are links to those first two.

Automating Database Deployment: Part 1 Introduction to RoundhousE

Automating Database Deployment: Part 2 Setting Up the RoundhousE Environment

This walkthrough will setup the environment and then will go through running RoundhousE several times to initially create a database and then migrate it and deploy scripts. This walkthrough was written using Microsoft SQL Server https://www.microsoft.com/en-us/sql-server/sql-server-downloads. Another option would be to take the scripts in the walkthrough and adapt them to whatever platform you have access too. Also remember to check for .NET 3.5 on the machine that RoundhousE is running on.

Starting out at the beginning, RoundhousE needs to be downloaded if you have not already. At the time of this post the current version is 0.8.6 and 0.8.7 is unreleased but available. For this walkthrough either will be fine but it will touch on a few new things in .7. Follow the directions to get RoundhousE setup, if you want the latest code follow the Source directions at the bottom of the page.

For now, put the RoundhousE code in the same directory that was created in the previous article. If you want to put it in a separate sub-directory that will work fine.

There are a set of sql scripts that we will use. RoundhousEScripts Download that and unpack it into the root directory of the structure you created. As we go through this exercise the scripts will get moved into the correct directories.

The first script to focus on is the database creation script. The script for this example is called CustomCreateDB.sql. This script can live anywhere, though keep it out of the 11 directories that were defined previously. For this exercise let’s leave it in the root. This script should just contain the initial create statements that are customized for the environment. It may be easiest to create the database using SSMS then script out the create once it is done. The script for this example is extremely simple, but do modify the filename paths so it will run correctly for you. Do note that the CREATE is wrapped in an IF block so that it will only run if the database does not exist. This is important because the create database script will run every time RoundhousE does a deployment.

The next scripts to focus on in this case are the schema create scripts. (001.Schema.Cust.sql & 001.Schema.Sales.sql ) Put the two schema create scripts in the 1 Alter Database directory. Depending on how you decide to classify scripts they could go into the 3 RunBeforeUp directory as well. They are both Anytime directories so would work. I chose the Alter Database to put the schemas in to be sure they are always created first before any objects they might contain. Remember that scripts in the RunAfterCreateDatabase directory will only be run if RoundhousE creates the database without a custom create script.

What is that 001.Schema doing in front of the script name you ask? It is similar to a naming convention that I ran across with a customer. It works well in this situation where the sort order is critical to the order that scripts are run. In this case I’m assigning three digits to the type of object and then object name. I use Schema.ObjectName in the script name, but in the instance of schema creation and utility scripts there really isn’t a schema so I named them with the type. This is simple, there are lots of type of objects that are not listed, but here is my naming convention for this example to give some ideas.

The gaps in the number sequence allow you to manipulate the order in which scripts run. This would make sure interdependent objects run in the correct order. For example with nested stored procedures, the nested stored procedure would be in the 300.<schema>.<name> range and the parent stored procedure would be  350.<schema>.<name>. That would ensure that the dependent objects would be created in the correct order.

The next scripts are to create the Customers, Orders & Orderlines tables.  (100.Cust.Customers,  100.Sales.Orders & 110.Sales.Orderlines) Move those scripts into the ‘3 RunBeforeUp’ directory. The RunBeforeUp directory is an ‘Anytime’ directory, so RounhousE will run scripts from this directory when they are new or have changed since the last deployment. All the create and alter statements are wrapped in IF NOT EXISTS logic to avoid errors in the script. Any changes to the tables would be added to bottom of the script as another Alter statement. Optionally, if there is a change that could be put into the initial CREATE TABLE (a new column for instance) it could be put in there as well. When deploying to a new environment the table could be deployed in its entirety instead of in steps.

Take the view script, 200.Sales.vw_CustomerOrderLines, and drop it in the ‘7 Views’ directory. Notice that the view has the same type of logic to the creation process. It checks to see if it exists and creates an empty view if not. Then runs an ALTER VIEW that contains the actual code for the view. This is particularly nice when there is specific permissions on objects that would be lost if this were a DROP/CREATE script.

There is one simple stored procedure to put in place. Put the 300.Sales.usp_GetAllOrderLines script in the ‘8 StoredProcs’ directory. The logic for creating this proc is the same as the view, it creates an empty proc if necessary then all the real logic is in an ALTER statement.

Last is the versioning and output. Copy the RoundhousEDBVersion.txt to the Version directory. I used a date version that looks like YYYYMMDD.x where ‘x’ is the build number for that day. You can put anything you want in there for the version, the datatype for the version column is nvarchar(50).  Nothing needs to be done to the output directory other than checking it make sure it is there. That is where the log and audit files are written.

There is now a simple structure ready to start running RoundhousE. I prefer running RoundhousE from a Windows PowerShell prompt. The script below is what I use. Because of all the switches that are available in RoundhousE it is nice to have the variable capability that PowerShell gives. The top of the script is assigning values for the command line switches to variables. The second part of the script is the command line that will run the deployment. Copy the script to your favorite text editor and update the server name, database name and paths. If you have specific credentials that need to be used then create a complete connection string and use the –connectionstring switch. The following link has all of the available switches for RoundhousE.

https://github.com/chucknorris/roundhouse/wiki/ConfigurationOptions

$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

Now that the PowerShell script is updated open a PowerShell prompt and copy the script in and run it. Or save the script as a .PS1 file and run it in PowerShell. After starting the script, RoundhousE will begin returning the log file as it runs scripts. (see the example below) This log is being written to a file in the Output folder that was created.

After the RoundhousE migration is complete there should be a RoundhousEDB database created. There will be the two schemas, 3 tables, 1 view and 1 stored procedure created. In addition there will be a RoundhousE schema and three RoundhousE tables to track migration information. Querying the [RoundhousE].[ScriptsRun] table will show the scripts that were just run and information about the run including the hash that will be used to compare scripts in the future.

Navigate to the Output folder that you created and drill into the migrations folder.  A subdirectory structure of Migrations\<database name>\<server name>\<timestamp> will be created and the file roundhouse.changes.log will be written there. There will also be a directory named itemsRan created and will contain any scripts that were run in this deployment of the database. Because of the sub directory naming, keeping all the RoundhousE log files in a central repository for multiple servers/databases would not be difficult.

Now let’s get some data in the tables. Copy the 175.Cust.CustomersInsert.sql and 176.Sales.OrderInsert.sql scripts into the “4 Up” directory. That directory is a ‘Run Once’ directory so the inserts will only run one time.

Since there is data in the tables and there is a stored procedure pulling data out of the Orders table using order date, let’s create an index. Copy the 400.Sales.idx_OrderDate.sql script into the “9 Indexes” directory.

Open the RoundhouseDBVersion.txt file and bump the version number up to .2 for today’s date since it is the second deployment. Run the PowerShell commands again to kick off RoundhousE. Notice it has entries for the table and view scripts in the log, but those files were skipped because there were no changes. It does show that it ran the two insert scripts and the index script. Running the following queries against the database should show the data that was just inserted as well as making sure the view and the stored procedure runs.

select * from [Cust].[Customers]
select * from [Sales].[Orders]
select * from [Sales].[Orderlines]
select * from [Sales].[vw_CustomerOrderLines]
exec [Sales].[usp_GetPreviousDayOrderLines]

Navigate to the Output directory and drill into the sub-directories. You will find another directory in there for the second run and the log/audit files. Query the RoundhousE tables in the database and examine the details about the scripts that were run. There should be two records in the Version table, one inserted by each run of RoundhousE.

We have walked through the initial running of RoundhousE to create a database, tables, views and stored procedures. We ran a second deployment to insert some data into the tables and saw how RoundhousE evaluated the scripts to skip the ones that did not need to be run. The last article in the series will be updating objects in the database and demonstrating the everytime and env file tags.

p.s. Please save your PowerShell script for the next article!