Contents
Introduction | Objectives | Objective 1: Create a new database |
Objective 2: Create a Full Upgrade | Deploying Manual Schema Changes | Objective 3: Create a partial upgrade | Using Configurations | CruiseControl Integration | NetTiers Integration
Introduction
Maintaining your database structure within Visual Studio allows you to rebuild the database from scratch and keep a record of the modification history in SourceSafe or TFS.
On one particular project, I used the standard SQL Server Project provided in Visual Studio 2005 creating my own directories to separate schema objects such as tables and stored procedures. A custom application would run the scripts in the project in a defined order to allow you to create and upgrade the database.
Recently I decided to migrate the solution to Visual Studio 2010.
When I attempted to convert the SQL Server Project I came across all kinds of issues. Such as the compilation not working because some commands such as DROP and EXEC were not supported.
I also tried to reverse engineer the database project from SQL Server into Visual Studio 2010. Being a bit of a control freak, this did not satisfy me because I didn’t know what the reverse engineering had created. I could see there were issues (e.g. permissions were not scripted how I would like), so I decided to hand-craft the project from the scripts that already existed in the old SQL Server project.
Below is a record of the objectives, along with everything that needed to be done to achieve the objectives.
Objectives
There were 3 objectives:
- Create a new database with test data
- Create a full upgrade: restore the previous version of the database and run schema and data upgrades
- Create a partial upgrade: run schema and data changes on a database that has already been generated via the full upgrade (e.g. stored procedure changes) (for high load iterative development or potentially iterative deployment)
Posted by Ruth D 
