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!