SQL Baseline has joined the ChuckNorris Framework

I am very pleased to say that Rob and Dru have added my SQL Baseline tool to the Chuck Norris Framework. As part of SQL Baseline’s inauguration it has been renamed as PoweruP to fit alongside the likes of RoundhousE, DropkicK and WarmuP. The project has been moved over and can be found here.

I created PoweruP to help me configure RoundhousE to manage a number of existing databases. This is not an easy task and can be a barrier which stops people trying out RoundhousE as is shown by this conversation

@dantup @monkeyonahill @davidfowl @jabbr and intro @keith_bloom's sql-baseline. github.com/chucknorris/sq… (FYI, name change pending) :D

— Rob Reynolds (@ferventcoder) July 19, 2012

This is a shame because once RoundhousE is setup it greatly increases development speed, it is simple to maintain and brings database development inline with application coding. What can stop people using it, is the need to extract all the stored procedures, views, functions, etc, from the database. With one command PoweruP will scaffold a new RoundhousE project from an existing database. Plus It will create the scripts and put them in the default RoundhousE folder structure. For a more detailed explanation see this post.

I am very pleased for PoweruP to be part of the Chuck Norris framework. I hope it will help more development teams to get started using RoundhousE because it is the best tool I have found for managing changes to the database schema.

Roundhouse with a legacy database

When putting an existing database schema into source control, often the biggest hurdle is how to start. In this post I will demonstrate how Roundhouse can be added to a normal Visual Studio solution. It can then be used to manage the database in the same place as you manage the code. This then brings the benefits modern development practices like Test Driven Development and Continuous Integration to database development.

I have created a fictitious scenario to help explain how to do this. I have been asked to create a simple interface for the Adventure Works database, apparently the old Visual Basic version is showing it’s age.

I've put all the code examples in a repository on github.

Structure

As I am starting a new project I have time to plan a good structure. I know that I will have source code, external tools and database backups for Roundhouse (more on these later). So I decide upon this tree:



With the folder structure complete, I can create the solution and add a project for the database schema. Here I am using a class library and have named it AdventureWorks.Database. I have also added two folders, db and deployment for the Roundhouse files.



Now to populate the db folder with the entities from the AdventureWorks and put them in to the relevant folders. My previous client had many legacy database with thousands of entities so I created sql-baseline to automate the process. Running sql-baseline requires three parameters, the server name, database name and place to put the scripts:

Sql-baseline has extracted the sprocs, views and functions and put them in the correct folders. The steps to add the files to Visual Studio are:

• Click the “Show all file” icon in Solution Explorer
• Expand the db folder
• Select the sprocs, views and functions folder
• Right click the selected folders and select “Include in project”

With the structure of the project setup, it is time to concentrate on working with Roundhouse.

The workflow

To support this process I have created a dos batch file, called LOCAL.DBDeployment.bat, to run Roundhouse. This is in the deployment folder.

The batch file executes the Roundhouse command line runner which is in the Tools folder. It sets the run mode to restore.

In a team environment this file would be omitted from source control allowing each developer to modify the variables. When executed it restores the database using the backup in the DatabaseBackups folder at the top of the repository tree.

To keep development as quick as possible I add an External Tool to Visual Studio with the following settings

The full command text is $(ProjectDir)\deployment\LOCAL.DBDeployment.bat. Running the migrations is now a menu click. I can also map a keyboard shortcut to make it quicker.

Following theses steps and conventions Roundhouse is integrated into the development process. I have found that being able to run the process from Visual Studio keeps the focus on development and keeps context switching to a minimum.

Roundhouse modes

I have configured Roundhouse to run in what I called “Restore mode”. Roundhouse has three options for running the migration scripts:

• Drop and Create - Roundhouse will drop the database and recreate it from the scripts
• Restore - Roundhouse will restore the database from a backup and then run the scripts
• Normal - Roundhouse will run the scripts against the database

Drop and Create is typically used when developing a new database where the schema is developing rapidly but is not a good choice for an existing schema. In this situation we need confidence that the deployment to production will work and to achieve that we must develop using the current schema. This is why I use a backup of the production schema and have Roundhouse restore this before running the migration. In later posts I will explain how restore mode is used at every step in the deployment pipeline which rigorously tests the changes. Normal mode is used when the changes are finally deployed to the production database. When this is complete a new backup is taken and this is used as the new baseline during development.

Conclusion

There are many benefits of adding database development to source control.

For the development process it keeps all of the work together. When I relied on SQL scripts I would work on a feature in Visual Studio by adding tests and refactoring the code. This would be checked in and the code for the feature would evolve. A side task would be to maintain the migration scripts, deploying them and testing they work. With the database in source control each commit includes changes to the whole system; the source code and the database schema. Anyone viewing the commit will see the complete set of changes. For the person doing the work the development workflow is simpler. By including the database you can bring the benefits of test driven development to the database changes and they become part of the “Red, Green, Refactor” TDD cycle.

For the deployment process it constantly tests the database migration scripts. Previously this would be a manual task whereby each developer would test a single database script before the deployment. To test the script before deployment would be done on a shared server with a recent copy of the production schema. This method was prone to errors as another developer could have made a change to the production schema which the script was not tested against. By storing the changes in source control they are integrated during development so every developer is aware of them instantly. This brings the benefits of Continuous Integration to database development.

In my next post I will explain how Roundhouse can be added to the Continuous Integration process.

sql-baseline: a bootstraper for RoundhousE

Roundhouse is a great tool for putting the database in to source control. When Roundhouse is used on a project, changes to the schema become an after thought; every check-in updates the schema on the build server; every check-out updates the schema on all the developers PCs. When a deployment is run, the schema changes are applied automatically.

But the barrier to entry for controlling an existing database is high. Roundhouse needs all the stored procedures, views, etc as files in a folder structure. Each script must be able to create or alter the entity. For teams supporting databases with thousands of procedures and views this work will stop evaluating Roundhouse. This is a shame as they are missing out on a superb tool. So I have created something to help get you started.

What does it do?

My current client falls in to the category of having thousands of procedures and views which had to be scripted. So I created a tool to extract them from SQL Server.

https://github.com/chucknorris/powerup/

Sql-baseline is run with three options; server name, database name and location for the files:

.\sqlbaseline.exe -s:"(local)" -d:AdventureWorks -o:C:\Db\Adventure

It will generate a script for all of the stored procedures and views and put them in the default Roundhouse folders:

\db
  \sprocs
  \views

Each script is created with the Create if not Exists / Alter template which will check for the entity first and if it is missing create it. The files are then ready be run by Roundhouse.
I have a short list of updates which are coming soon

1. Export functions and indexes
2. Create all of the default Roundhouse folders
3. New option to specify which entities to create
4. Accept a set of custom folders

Conclusion

When I decided to trial Roundhouse my first hurdle was extracting the procedures and other entities in to the correct format. Using sql-baseline has enabled me to put three of my clients databases in to source control. I hope it helps others to start using Roundhouse and controlling changes to the database.