Showing posts with label deployment process of Asp.net applications. Show all posts
Showing posts with label deployment process of Asp.net applications. Show all posts

Monday, 5 March 2012

Improve deployment process of Asp.net applications by generating database change scripts using Visual Studio 2010-Part 2

This is the 2nd part of an article, and if you haven't gone through the first part yet, you may want to read it first. Check out the following link:

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

In Visual Studio 2010, it is possible to completely automate the deployment process of an Asp.net web application.  A deployment package in Visual Studio 2010 can include dependencies on SQL Server databases, and, it is possible to generate the full database schema and also add custom TSQL scripts to run on the target SQL Server database using the deployment package. 

But, this post is not going to cover the automation of deployment (Including Database Deployment) using Visual Studio. Rather, this post would demonstrate a step-by-step process to help improve the deployment process of a database driven (SQL Server) Asp.net web application by generating a TSQL change script between two different databases utilizing a Visual Studio Database project. 

Here we go.

Visual Studio 2010 comes with a number of SQL Server database project template and it is possible to  compare two different databases (Source and Destination databases), find out the differences between the two and generate a TSQL script that contains all the change scripts to upgrade the source database to the destination one. Once you have that TSQL script, you can run this to a target database any time to conform it to the latest version. So, gone are the days when you had to manually write scripts for each and every database change and try to troubleshoot a BUG at a newly deployed site that was usually caused by some mistake done while deploying the database.

Follow these simple steps to carry out the overall task:

Open Visual Studio 2010 and add an SQL Server 2005 Database project (Or, SQL Server 2008 Database project) by clicking File->New->Project, and expanding the Database node and selecting SQL Server.


Figure : Create SQL Server Database Project
Once the project is added in the solution, right click on the "Schema Comparisons" folder in the solution explorer and click on "Add->Schema Comparison" to add a Schema Comparison item in the project.



Figure : Add a Schema Comparison in Database project
Visual Studio will prompt you to specify the source and destination database using the following window. Check the radio button "Database" and specify the source and destination databases by clicking on the "New Connection" button.

Note: 

The Source database should be the latest database where all the database changes have been done.
The Destination database should be the one which we want to be upgraded.


Figure : Source and Destination database for schema comparison
Once the source and destination database have been specified, click on the "OK" button to let the Schema Comparison tool start comparing the two databases. It would take some time to compare all objects between the two databases and would provide a schema comparison report as follows:


Figure : Schema comparison report in Visual Studio 2010
Note that, the report includes the list of objects in the source database (To the left of "Update Actions" column) and the list of objects in the destination database (To the right of "Update actions") column, and, it lets you specify your desired action for each particular object in the "Update Actions" column.

Click on the "Create" action (If there is anything such on the report) and you will see something like the following:


Figure : Create action on schema comparison report
A "Create" action indicates that, a database object is present in the source database, but, absent in the destination database. You can of course can specify "Skip" for a particular database object by clicking on the corresponding "Update Action":


Figure : Skip action on schema comparison report
Similarly, you can click on the "Update" action (If there is anything such in your report) to see what needs to be updated in the destination database and also you can specify skip if you want:


Figure : Update action on schema comparison report
The schema comparison report includes the comparison report of each and every database object between the source and destination databases (Tables/Stored Procedures/Views/Constraints/Keys/Triggers/Functions etc). So, you can have a look at each of the object in the report (Where there is a difference) and specify what to do.

Finally, you can generate the TSQL script based upon the actions you specify in the schema comparison report by clicking on the "Refresh Update Script". 


Figure : Refresh Update Script
The TSQL script will be generated and it would be shown in the bottom window:


Figure : Schema comparison script
Once generated, you can execute the TSQL script to the target database by clicking on the "Write Update" action:


Figure : Write Updates on target database
Alternatively, you can export the TSQL script to an sql file, execute it later, and, possibly maintain it in the source control system by clicking on the "Export to File" action:


Figure : Export generated TSQL to a file
You can run this generated TSQL script to upgrade the target database within a quick time, and this will help you ensure a smooth deployment of your Asp.net applications because you no longer have to maintain database change scripts manually which is error prone and tedious.

Visual Studio 2010 rocks!

 

 

 

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

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

Introduction 

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

Most Asp.net application deployment is usually carried out in following process
Figure : Deployment process of Asp.net 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

Option1: 

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).

Option2

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..