Monday, 5 March 2012

Improve deployment process of applications by generating database change scripts using Visual Studio 2010-Part1

Improve deployment process of applications by generating database change scripts using Visual Studio 2010


Database deployment is perhaps the most important part of any application deployment, and, if the database deployment is smoothly done, there is a chance that the overall application deployment is done smoothly.

Most application deployment is usually carried out in following process
Figure : Deployment process of applications
Development Site
This is where the developers deploy their individual tasks for QA testing and fixing bugs.

Staging Site
This is where the overall application is deployed and the QA team performs production testing, once all individual tasks are passed on Development site.

Production Site
This is where the overall application is deployed once all testing on Staging site is passed.

The deployment process

Usually, source codes are maintained using a version control system (Say, SVN), and, when a deployment is required, the overall codes (From the corresponding branch) are checked out and built using a build management script (Say, NANT).  Once build is successful, the binaries and other files are deployed on target server.

The corresponding database changes are also usually maintained as individual database script files within the version control system, and the build management system executes those scripts to make necessary database changes on the target database, to make sure that the database conforms to the current version of the codebase. The database change scripts include the all database related changes such as Creation/Modification of Tables/Stored Procedures/Views/Functions/Triggers and other necessary database objects.

If everything goes right, the deployment proceeds very smoothly. However, experience shows that, even if all tasks are passed on Development site, once staging deployment is done, BUGs are found and reported from the staging site (Even if the same BUG doesn't exist in Development site). Most of the cases the reason is found to be a database deployment issue. For this reason, the staging deployment is often found to be time consuming and problematic. 

Why this happens?

Usually, in Development site, developers deploy their database and code related changes incrementally. That is, each developer deploys codes and the corresponding database change scripts individually. They also commit their corresponding database scripts within the source control. Now, when staging deployment is to be done, a copy of the production database is taken and copied at the Staging database server, and, the overall database change script is run there at once to conform it to the latest version of the codebase. That's where the main problem occurs.

Ideally, the database change scripts should be perfectly maintained within the source control so that whenever the scripts are run, the current version of the database at production is immediately be upgraded to the latest version, to conform it to the current source codes. But, unlike the application's source codes, the database scripts cannot be built and hence, it is hard to identify whether there is any missing database script or there is any script maintained wrongly in the source control, before the script is to be run at a target database.

So, often there are situations where it takes hours or even days for a developer to fix all the database script issues and ensure a complete staging deployment.

How to improve the situation?

There could be two possible ways to improve this situation


Ensure a process to make sure that, no individual database change script is run on Development database. Like the staging deployment, the overall database scripts have to be run even if a single task has to be deployed there.

Pros : This will make sure that, before staging deployment, all corresponding database scripts are correctly maintained in the source control, and hence, staging deployment will likely be much quicker and smoother.

Cons : Individual task deployment at staging site would take more time than before (Because in order to deploy each little task, the overall build process has to be run).


Use some intelligent tool to generate the overall database change scripts between the database at the development site and the production site, and, run the database change script into staging database to conform  it to the current codebase. Also, maintain the change script in source control.

Pros : 

This will not require running the overall build process to deploy a single task on development site. Developers will just need to run their individual database change scripts.

The database change scripts doesn't have to be maintained in source control, so, this will save some time at each task level.

The staging deployment will likely to be much more smooth.

Cons : No real cons :)

The next post is all about utilizing such an intelligent tool which is available right within your favorite Visual Studio 2010!

 next part check it..

Post a Comment