Automating Database Deployment: Part 2 Setting Up the RoundhousE Environment

Remember from the introduction that RoundhousE is a command line driven database deployment engine. This article of the series will discuss the deployment flow and the RoundhousE environment, looking at the default behavior and how to modify it using command line switches.

When a RoundhousE deployment is run the first step is a check to see if the target database exists. If it does not then it will create a new database. With the default behavior RoundhousE will create a database based upon the server defaults. If the database needs a different setup than the default for the server write a custom script to do so.

After the database has been created or confirmed that it exists, RoundhousE will check for a schema named RoundhousE in the target database. If the schema does not exist it will create it. RoundhousE will then check for three tables in that schema. Again, creating them if they do not exist. The RoundhousE schema and logging table names are definable. Care should be taken with this, as there is lots of dependencies on these tables and could cause issues if locations are changed or the command line is incorrect.

• ScriptsRun – Log for all the scripts run.
• ScriptsRunErrors – Log for any errors generated by a script
• Version – Database versioning

RoundhousE categorizes scripts into three main types of scripts. The three different types of scripts are based upon when the RoundhousE engine will run them. RoundhousE determines what category a script belongs to by what directory it is in, which is discussed next.

• Run One Time – One time scripts run a single time against a database. If a run once script has changed since the initial run RoundhousE will raise an alert.
• Run Anytime – Anytime scripts run when new and subsequently any time they have been updated.
• Run Every Time – Every time scripts will run with every deployment whether there have been changes or not.

The exceptions to the script types are the Environment and Everytime tagged script files. (how it works is covered later) The Environment and Everytime script is a designation that can exist in any of the main script types. When there is an environment script the RoundhousE engine follows the rules above but will only run it if the environment tag is correct, in addition to the regular rules. The Everytime script tag will force a script to run every time no matter what folder it is in.

RoundhousE operates on top of a folder structure that stores the SQL scripts. Those folders are accessed in a specific order and should contain a specific category of scripts. The folder structure has been pre-defined, but the names can be modified with tags in the command line (covered later). All the folders need to exist, RoundhousE will look in the default folder or the root of the folders if an invalid path or not path is defined. Here are the folders that RoundhousE is looking for in the order they are used and how the scripts are treated within those folders.

After RoundhousE is done with the scripts in the Permissions folder the deployment will be complete. A confirmation message will show and include the path to the log folder for that run.

Some of folder names may not make sense for your organization, they can be customized. All of the folders must be there though. Since windows likes to alphabetize and the RoundhousE folders are not in alpha order, I customized the directory names by adding numbers to the beginning so it was easy to determine the order. It could be useful to further name the directories with a designation that represents what type of script is in them. If the directory names do not make sense for your shop then decide what makes sense and implement that. To make setup of the directory structure I created a short PowerShell script to take care of that. Start setting up for RoundhousE by creating a root directory for everything in this demo and then running the PowerShell script to create the sub-directories. Keep the naming convention the same just to make working through this exercise easier.

<#  PowerShell for creating directory structure for scripts #>
<#  Create a root directory for the directory structure and then run this in that 
directory, or just alter the -Path switch. #>
 
md -Name "1 AlterDatabase" -Path ".\" 
md -Name "2 RunAfterCreateDatabase" -Path ".\" 
md -Name "3 RunBeforeUp" -Path ".\" 
md -Name "4 Up" -Path ".\" 
md -Name "5 RunFirstAfterUp" -Path ".\" 
md -Name "6 Functions" -Path ".\" 
md -Name "7 Views" -Path ".\" 
md -Name "8 StoredProcs" -Path ".\" 
md -Name "9 Indexes" -Path ".\" 
md -Name "10 RunAfterOtherAnyTimeScripts" -Path ".\" 
md -Name "11 Permissions" -Path ".\" 
md -Name "Output" -Path ".\" 
md -Name "Version" -Path ".\" 

You will note that there are two directories in that PowerShell script that are not part of the numbered sequence. The Output folder will be the destination for the log files in this example. The Version directory will contain a TXT file for versioning the database in this example. Those are not required, I put them in there to organize the logs and version information. It would definitely make sense to push the log files somewhere else if a source control system of some kind was tracking this directory structure.

A standard for writing scripts should be implemented and maintained when working with RoundhousE. The majority of the directories classify scripts as Run Anytime and should be considered when building scripts. Best practice for scripts that create objects should be checking if the object exists and altering instead of dropping and re-creating. That will ensure that any special permissions that were setup are maintained.

Here are some examples (written for SQL Server) of how to setup this practice. Notice that the table script has an alter at the end of it. This would be for any changes after the initial creation, for example a new column.

/********************************* STORED PROCEDURE *********************************************/


IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[<ProcSchema>].[<ProcName>]') 
 and OBJECTPROPERTY(id, N'IsProcedure') = 1 )	
     EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [<ProcSchema>].[<ProcName>] 
     AS PRINT ''This is an empty stored proc'''
GO

ALTER PROC [<stored proc schema>].[<stored proc name>]
AS
BEGIN
--	<PROC code goes here and gets updates>
END

/************************************** VIEW *****************************************************/

IF NOT EXISTS (SELECT TOP 1 * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '<view name>' 
 and TABLE_SCHEMA = '<view schema>')
	EXEC dbo.sp_executesql @statement = N'CREATE VIEW [<view schema>].[<view name>] 
        AS SELECT ''This is an empty View'''
GO

ALTER VIEW [<view schema>].[<view name>]
AS
--	<VIEW code here>

/**************************************** TABLE **************************************************/

IF NOT EXISTS (SELECT TOP 1 * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '<table name>' 
 and TABLE_SCHEMA = '<table schema>')
BEGIN
	CREATE TABLE [<table name>].[<table schema>] (
		<Column List>
		)
END

ALTER TABLE [<table name>].[<table schema>]
--	<TABLE code here>