GettingStarted
Welcome to RoundhousE (RH)! RH is one of the most intelligent database change management tools out there. Over the next couple of chapters you are going to learn how to effectively use RoundhousE to solve the complex business problem of managing change for your database. You will see that RH makes it really easy and as you learn the tool, you will wonder how you ever used anything else.
Whatever database you are going to use, be it SQL Server or be it MySQL, needs to be installed so you can do database change management.
You will also need to have a .NET framework/runtime installed. Currently RH supports .NET Framework 4.6.1 and .NET Core 2.1
The install agent (usually a person running rh) needs to be able to create databases on the database (which usually means SA).
If you've downloaded the application, the best way to see the power of RoundhousE is to actually run the sample. It will also help you familiarize yourself with how RoundhousE works.
The sample is run against SQL Server 2005/2008 (and it works with express as well), so you would need that installed. You can also attempt to run the sample against other database types.
-
Go to
code_drop/sample(if you have the source) or tosampleif you downloaded the release. Go into the deployment folder.
-
Run
LOCAL.DBDeployment.bat.
-
Press
Enter. Notice all of the things it does. It just created our database, versioned it, then applied all of the database scripts (marking them as part of this version). Then it saved the output of what it ran to a folder.
Notice how it ended telling us there was a folder where it put all of the changes it made. Let's head there. Below is the output folder. The structure of the output folder is databasename\server\yyyyMMdd_HHmmss_ffff.
The convention here is the common application data folder on a machine followed by RoundhousE. Then we have a folder for the particular database followed by the instance. The actual folder we drop the changes into are YYYYMMDD_HHmmss_fractions. Hence the one in the picture was run on 06/07/2011 at 21:54:37 with so many fractions of a second.
In the folder we see two things:
The migration log (roundhouse.changes.log) and the actual sql files that it ran get dropped into the itemsRan folder.
You can see that on a first time run, it's pretty much a mirror of what you have in your sql migrations folder.
Let's take a look at that change log file more in depth.
RH goes through several stages when it runs. Let's take a look at the change log file and talk about each part of the migration process.
Running RoundhousE v0.8.0.305 against (local) - TestRoundhousE.
Looking in C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE for scripts to run.
Please press enter when ready to kick...
The first thing you notice is that it tells you is the version and what server and what database it is going to run on. The database does not have to exist prior to run for SQL Server. It will create it automatically. Then it mentions where it is going to look for scripts. Next and slightly important, when it is in interactive mode (the default), it stops and gives you the opportunity to double check to be sure this is correct.
==================================================
Setup, Backup, Create/Restore/Drop
==================================================
Creating TestRoundhousE database on (local) server if it doesn't exist.
==================================================
RoundhousE Structure
==================================================
Running database type specific tasks.
Creating RoundhousE schema if it doesn't exist.
Creating [Version] table if it doesn't exist.
Creating [ScriptsRun] table if it doesn't exist.
Creating [ScriptsRunErrors] table if it doesn't exist.
Now it creates the database if it doesn't exist (you can configure a custom database creation script if you don't like the default). Then it creates its schema (schema name is configurable) and three roundhouse tables for keeping track of changes and migration errors. Version (table name is configurable) is where it keeps track of versioning. The ScriptsRun table (which has a foreign key back to Version, name is configurable) is where it notes what files have run with every migration. The ScriptsRunErrors table (name is configurable) is where you can get to the actual error that has occurred.
==================================================
Versioning
==================================================
Attempting to resolve version from C:\code\roundhouse\code_drop\sample\deployment\_BuildInfo.xml using //buildInfo/version.
Found version 0.8.0.305 from C:\code\roundhouse\code_drop\sample\deployment\_BuildInfo.xml.
Migrating TestRoundhousE from version 0 to 0.8.0.305.
Versioning TestRoundhousE database with version 0.8.0.305 based on http://roundhouse.googlecode.com/svn.
See Versioning
Migrating your database from one state to another is the most important stage in RH. It follows a particular order.
==================================================
Migration Scripts
==================================================
Looking for Update scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\up". These should be one time only scripts.
--------------------------------------------------
Running 0001_CreateTables.sql on (local) - TestRoundhousE.
Running 0001_CreateTables_NH.sql on (local) - TestRoundhousE.
Running 0002_ChangeTable.sql on (local) - TestRoundhousE.
Running 0003_TestBatchSplitter.sql on (local) - TestRoundhousE.
--------------------------------------------------
Looking for Run First After Update scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\runFirstAfterUp".
--------------------------------------------------
--------------------------------------------------
Looking for Function scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\functions".
--------------------------------------------------
Running ufn_GetDate.sql on (local) - TestRoundhousE.
--------------------------------------------------
Looking for View scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\views".
--------------------------------------------------
Running vw_Dude.sql on (local) - TestRoundhousE.
--------------------------------------------------
Looking for Stored Procedure scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\sprocs".
--------------------------------------------------
Running usp_GetDate.sql on (local) - TestRoundhousE.
Running usp_SelectTimmy.sql on (local) - TestRoundhousE.
--------------------------------------------------
Looking for Run after Other Anytime Scripts scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\runAfterOtherAnyTimeScripts".
--------------------------------------------------
Running createFiveItems.sql on (local) - TestRoundhousE.
--------------------------------------------------
Looking for Permission scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\permissions". These scripts will run every time.
--------------------------------------------------
Running 0001_AppRole.sql on (local) - TestRoundhousE.
Running 0002_AppReadOnlyRole.sql on (local) - TestRoundhousE.
Running 0003_AppPermissionsWiring.sql on (local) - TestRoundhousE.
LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will run based on this check.
Running LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql on (local) - TestRoundhousE.
TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will NOT run based on this check.
Skipped TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql - No changes were found to run.
RH then looks through the scripts folders and executes scripts. Let's break that down a bit.
Looking for Update scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\up". These should be one time only scripts.
--------------------------------------------------
Running 0001_CreateTables.sql on (local) - TestRoundhousE.
Running 0001_CreateTables_NH.sql on (local) - TestRoundhousE.
Running 0002_ChangeTable.sql on (local) - TestRoundhousE.
Running 0003_TestBatchSplitter.sql on (local) - TestRoundhousE.
The UP folder (folder name is configurable) and subfolders is where you put your schema changes and database insert scripts. This is a one time only folder. That means once you have run a script here, if RH detects the file has changed (even so much as a space) it will shut down and report errors. Of course there is a configuration that will allow you to just warn on one time script changes (check the configuration section of the documentation).
Notice the file naming convention here. 0001 comes prior to 0002. RH runs files in the order it finds them. The convention is to start all scripts with a nice long numeric sequence.
Looking for Run First After Update scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\runFirstAfterUp".
--------------------------------------------------
--------------------------------------------------
Scripts run prior to other anytime folders are found here (folder name is configurable) and subfolders. This folder exists to allow you to put sql files in when you need to run out of order, say a stored procecure prior to a function. It is not normal occurrence to have many files in here or any for that matter.
Looking for Function scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\functions".
--------------------------------------------------
Running ufn_GetDate.sql on (local) - TestRoundhousE.
--------------------------------------------------
Functions are found in a functions folder (folder name is configurable) and subfolders. If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
Looking for View scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\views".
--------------------------------------------------
Running vw_Dude.sql on (local) - TestRoundhousE.
--------------------------------------------------
Views are found in a views folder (name is configurable) and subfolders. If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
Looking for Stored Procedure scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\sprocs".
--------------------------------------------------
Running usp_GetDate.sql on (local) - TestRoundhousE.
Running usp_SelectTimmy.sql on (local) - TestRoundhousE.
--------------------------------------------------
Stored procedures are found in a sprocs folder (folder name is configurable) and subfolders. If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
Looking for Run after Other Anytime Scripts scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\runAfterOtherAnyTimeScripts".
--------------------------------------------------
Running createFiveItems.sql on (local) - TestRoundhousE.
--------------------------------------------------
This folder exists to allow you to run scripts after you have set up your anytime scripts (folder name is configurable). It's pretty open what you put in here, but remember that it is still an anytime folder.
Looking for Permission scripts in "C:\code\roundhouse\code_drop\sample\deployment\..\db\SQLServer\TestRoundhousE\permissions". These scripts will run every time.
--------------------------------------------------
Running 0001_AppRole.sql on (local) - TestRoundhousE.
Running 0002_AppReadOnlyRole.sql on (local) - TestRoundhousE.
Running 0003_AppPermissionsWiring.sql on (local) - TestRoundhousE.
LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will run based on this check.
Running LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql on (local) - TestRoundhousE.
TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will NOT run based on this check.
Skipped TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql - No changes were found to run.
Permissions scripts are found in a permissions folder (name is configurable) and subfolders. If you have any that need to run prior to others, make sure they are alphabetically first before the dependent scripts.
Permissions may contain auto-wiring of permissions, so they are run every time. This is pretty interesting. We have a set of permissions scripts here. One called LOCAL.something.ENV.sql and another called TEST.something.ENV.sql. Only one of these runs based on the environment we are in. Yes, RH is environment aware (as long as you provide it in the configuration). If you take a look at these scripts again, one is called LOCAL.something.ENV.sql and the other is called TEST.something.ENV.sql. It's the ".ENV." that tells RH that it is an environment file. Now RH starts looking for a match in the name of the file to the environment it is in.
RoundhousE v0.8.0.305 has kicked your database (TestRoundhousE)! You are now at version 0.8.0.305. All changes and backups can be found at "C:\ProgramData\RoundhousE\TestRoundhousE\(local)\20110607_215437_2015".
The last thing RH does is report success. It also tells you where you can find the changes it made in an output folder.
If RoundhousE encounters an error - it will ROLLBACK any transactions pending.