Roundhouse your database
As you started to use UppercuT for your builds, it is definitely worth to try worth to try RoundhousE for versioning, deployment, migration of your database.
RoundhousE - setup and usage
Let’s begin, just to to project web site and download latest package. Package contains documentation (just some draft version, not yet completed), samples and framework itself.
Initially I spent sometime to make samples run. Documentation said that it has been developed and tested with SQL 2008 Server and there is no information that it would run on SQL 2008 Express Edition. After several fail/repeat sessions I was happy to see that it actually could work with Express edition as well!
Before you start
Before you start, you have to change something in you SQL Express configuration. Go to Start -> All programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager. There you have to configure your SQL instance Network Configuration, to be able with “Named Pipes” protocol. So, go to Protocols for <%INSTANCE_NAME%> and enable Named Pipes
UppercuT configuration
Now you have to change something into UppercuT config. First you have to modify LOCAL.settings (file that is used for generation of deployment scripts). You have to supply server.database (in case of local Express it would be .\SQLEXPRESS), database.name the name of your db.
After small changes is required for UppercuT.config. First, folder.database I just used “db”, short and clear.
Check out this diff, for instance.
DB folder layout and content
Now, you have to create folder that would contain all sql scripts. I places it in the same level as docs, lib etc (but it could be placed to src folder as well). So, please folder with name “db”.
Documentation currently lacks information about folder layout, but from samples it is pretty clear:
- functions - sql for functions definitions
- permissions - setup permissions for database
- sprocs - stored procedures definition
- up - update scripts (create, drop, alter tables and records here)
- views - views definition
If your application doesn’t currently use stored procedures or view.. it is OK to just leave those folders empty.
Deployment options
You have 3 options of deployment: by Nant, by MSBuild or by RH executable. It does not actually matters what to choose, all options are equal by its functionality. I choose executable, because it lightweight and only one file (please not that it would be included to deployment package).
To go with RH you have to copy it from RoundhousE packages into yours deployment folder. So, you will have deployment/rh/rh.exe.
Correction of deployment scripts
Deployment scripts are ones that will be used on production to update database. They are created based on templates and settings you set. You have to modify yours DBDeployment.bat file. Since I use rh.exe I created template for it. It simply runs rh.exe that does the rest of work.
Check out this diff, for instance.
Running RoundhousE
As soon as build application, go to code_drop folder you can run LOCAL.BEDeployment.bat and RounhousE will setup database with version it.
That’s basically it! As soon as its configured you can easily add new scripts to db folder, RoundhousE will handle changes of them.
Additional scripts I use
There are something that I added for my personal needs. If I do build on new environment, I would like to create database first. For such purpose I created initdb.bat, so build procedure on clean environment would be:
- run build.bat
- run initdb.bat
- run test.bat all
I also created script to reset database, resetdb.bat drops existing database and just creates new instance. This very useful during development, if you database became “dirty”.
As you started to use UppercuT for your builds, it is definitely worth to try worth to try RoundhousE for versioning, deployment, migration of your database.
RoundhousE - setup and usage
Let’s begin, just to to project web site and download latest package. Package contains documentation (just some draft version, not yet completed), samples and framework itself.
Initially I spent sometime to make samples run. Documentation said that it has been developed and tested with SQL 2008 Server and there is no information that it would run on SQL 2008 Express Edition. After several fail/repeat sessions I was happy to see that it actually could work with Express edition as well!
Before you start
Before you start, you have to change something in you SQL Express configuration. Go to Start -> All programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager. There you have to configure your SQL instance Network Configuration, to be able with “Named Pipes” protocol. So, go to Protocols for <%INSTANCE_NAME%> and enable Named Pipes
UppercuT configuration
Now you have to change something into UppercuT config. First you have to modify LOCAL.settings (file that is used for generation of deployment scripts). You have to supply server.database (in case of local Express it would be .\SQLEXPRESS), database.name the name of your db.
After small changes is required for UppercuT.config. First, folder.database I just used “db”, short and clear.
Check out this diff, for instance.
DB folder layout and content
Now, you have to create folder that would contain all sql scripts. I places it in the same level as docs, lib etc (but it could be placed to src folder as well). So, please folder with name “db”.
Documentation currently lacks information about folder layout, but from samples it is pretty clear:
- functions - sql for functions definitions
- permissions - setup permissions for database
- sprocs - stored procedures definition
- up - update scripts (create, drop, alter tables and records here)
- views - views definition
If your application doesn’t currently use stored procedures or view.. it is OK to just leave those folders empty.
Deployment options
You have 3 options of deployment: by Nant, by MSBuild or by RH executable. It does not actually matters what to choose, all options are equal by its functionality. I choose executable, because it lightweight and only one file (please not that it would be included to deployment package).
To go with RH you have to copy it from RoundhousE packages into yours deployment folder. So, you will have deployment/rh/rh.exe.
Correction of deployment scripts
Deployment scripts are ones that will be used on production to update database. They are created based on templates and settings you set. You have to modify yours DBDeployment.bat file. Since I use rh.exe I created template for it. It simply runs rh.exe that does the rest of work.
Check out this diff, for instance.
Running RoundhousE
As soon as build application, go to code_drop folder you can run LOCAL.BEDeployment.bat and RounhousE will setup database with version it.
That’s basically it! As soon as its configured you can easily add new scripts to db folder, RoundhousE will handle changes of them.
Additional scripts I use
There are something that I added for my personal needs. If I do build on new environment, I would like to create database first. For such purpose I created initdb.bat, so build procedure on clean environment would be:
- run build.bat
- run initdb.bat
- run test.bat all
I also created script to reset database, resetdb.bat drops existing database and just creates new instance. This very useful during development, if you database became “dirty”.