Thursday, November 17, 2011

A Script Combiner to combine sql scripts

While preparing a release for our tour operator software TourMast, building the sql release script is always a night mare especially when you have many clients at different versions of the application. In the current situation we have the following folder structure for sql server scripts.

Sql scripts folder structure


Here for each version we have a folder with the version name, inside it we have the scripts all named according to the order in which it is to be run. So for a person to run version 1.0.0 he just has to run the scripts in the 1.0.0 folder according to the order. Now suppose he has to upgrade to 1.0.8 and his current version is 1.0.0, then he has to run each script in the folders from 1.0.0 to 1.0.8.

The issue:

Here he manually has to run each script, but when a person has to run this many scripts, the probability of him missing a script or jumbling the order is very high. This will result in the database not being in the requisite state, and often such mistakes are only known when something breaks in the client site.

Our solution:

We will make an application that will automate this process and remove the human factor from the deployment of database.

 

Overview of the application

Inputs :

  • Application Name
  • Version to be upgraded to
  • Database name

Internal Process :

  • Get latest version from svn
  • Read database to ascertain current version
  • Prepare database for upgrade
  • Combine scripts to upgrade from current version to required version
  • Take a snapshot
  • Run Scripts
  • In case of any error revert to the snapshot and inform user that upgrade failed
  • In case of successful execution, update latest version info in database

 

Now what??

Now i will have to develop the same!! Will blog about the remaining in the next blog!! cheers!!

No comments:

Post a Comment