SQLBits presentation - database projects in visual studio 2010

antxxxx — Mon, 04 Oct 2010 13:02:00 GMT

This weekend saw sqlbits 7 in York. This was a great conference and i learned a lot and met some really great people. A huge thanks to all the organisers and sponsors of the event

You can find the slides from my presentation on database projects here.

formatting xml files in visual studio

antxxxx — Tue, 23 Feb 2010 10:02:00 GMT

This is not strictly a sql post, but it is to do with xml which is used in sql server.

xml files do not need to have any formatting in them. That means that when viewing an xml file produced by an application, all the data can be one line. Although this is valid xml and can be processed by sql server or ssis, sometimes it is useful to view the data in a human readable format.

You can use an xml editor for this, or you can open the file in visual studio and use the shortcut ctrl+k, ctrl+d which formats it all nicely with indents to make it easier to read.

Upgrading database projects to GDR release

antxxxx — Wed, 15 Jul 2009 09:42:00 GMT

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

when it should get added as

<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

Anthony Brown : visual studio

SQLBits presentation - database projects in visual studio 2010

formatting xml files in visual studio

Upgrading database projects to GDR release