Wednesday, July 15, 2009 10:42 AM antxxxx

Upgrading database projects to GDR release

Database projects are available in visual studio database edition (which is covered under the same licence as visual studio developer edition). They allow you to store all the objects that make up a database in a project and easily deploy them to a server. The objects are stored as sql scripts that create the object. When you deploy it compares the object definitions to the target database and creates a difference sql script that makes the target server the same as the project.

With th GDR release for visual studio 2008 version, there are many improvements, including support for sql 2008, and the seperation of build and deploy steps. Gert Drapers has an excellent article at http://blogs.msdn.com/gertd/archive/2009/06/05/declarative-database-development.aspx explaining how database projects work in the GDR release

If you have an existing database project and you install the GDR release, you need to upgrade your projects to the new version.

There are 3 methods I will discuss here on how to do this.

The first method is to first deploy your old database project to a blank database, and then import it into the GDR version. This is probably the cleanest method. However, it does mean that the location of your scripts (ie object definitions) will change, which if you have your project under source control may cause problems. It is also very time consuming, so it may not be an option if you have many projects.

The second method is to just open the database projects in the GDR version and go through the upgrade wizard. This just changes the dbproj file, and adds some extra files, but leaves all the scripts in the same location. If you have your project under source control, this is a better method for that reason. However, it is time consuming and if you have many projects, it can take a long time.

There is a bug in the upgrade wizard. The database.sqlpermissions file gets added to the dbproj file like this

<PropertiesFile Include="Properties\Database.sqlpermissions" />

when it should get added as

<Build Include="Properties\Database.sqlpermissions">

<ModelBuilderType>Permissions</ModelBuilderType>

</Build>

So you will manually need to edit the dbproj file after the upgrade.

It also does not move the contents of the permissions.sql file into the database.sqlpermissions file, so this is something you need to do manually.

The third method is to automate the 2nd method using powershell. I have recently used this to upgrade hundreds of projects in a few hours. The main script is called upgrade-projects.ps1

The script finds all dbproj file, and then callls

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe $dbprojname /upgrade

on them which does the upgrade without the need for intervention.

It also fixes the database.sqlpermissions file in the dbproj file automatically, and changes the model collation to be uk case-insensitive (as all my database projects have this collation)

It also calls another script - upgrade_permissions.ps1 which tries to upgrade the permissions.sql file into database.sqlpermissions format. This script works for basic grant statements on tables and stored procedures (eg grant select, grant delete, grant execute etc) and for most grants on xml schema collections. There may be problems with it if you try to do it with other objects.

It uses the get-tfs cmdlet to try and check out and add files to tfs. This is available from here - http://blogs.msdn.com/jmanning/archive/2006/09/28/776141.aspx. If you use another source control provider you will need to change these parts.

Finally the script calls another script called build-all.ps1 which tries to build all the projects to check for any errors.

All 3 scripts are in the file here. I hope this helps anybody that is upgrading database projects

Filed under: , ,

Comments

No Comments