Wednesday, February 08, 2012

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.

6 comments:

Mario Pareja said...

Great stuff, Keith, hopefully this will lower the barrier to entry for other folks.

Unknown said...

It's an interesting project. Would like to see it create the onetime scripts as well. The hard part would be to get the scripts in the right order. Almost every application I've ever seen that reverse engineers existing databases, including expensive commercial apps, has trouble getting tables with foreign keys created in the right order.

Keith Bloom said...
This comment has been removed by the author.
Keith Bloom said...

Hi Bryan,

You make a good point and this is how I started but when I started using RoundhousE I realized I didn't need the existing tables as one time scripts. In fact having them caused a problem as RoundhousE tried apply them to my existing database when I first ran it. Then I read up on the RoundhousE modes and decided upon Restore Run for the entire deployment pipeline. This post is an example of how I setup the workflow.

Gary Miner said...

for the record if found your code here. https://github.com/keithbloom/powerup

Unknown said...

I recommend you to review the dbMaestro TeamWork Database Change Management solution.

I'm bios of course, as I work there :), but...

Some of the features that you can find are:
1. Real Database Change Management solution – one that has integrated change policy enforcement in the database engine
2. Managing schema structure, database code and lookup content
3. Automatically generating scripts for upgrading or to create from scratch
4. No need to manually maintain scripts
5. No need to know the database dependencies
6. Ability to correlate a change to a ticket or user-story or whatever work items terminology being used
7. Easy rollback
8. 3-way analysis that alerts on merges

And many more…