SQL Database Publishing in Visual Studio 2008 and Today

Posted by Hartmut Wilms on Oct 16, 2007

Sections

Development,

Operations & Infrastructure

Topics

.NET ,

Deployment / Datacenter

Tags

MSBuild ,

Continuous Integration ,

Visual Studio ,

Database

The Database Publishing Wizard is a popular add-in for Visual Studio, which supports deploying a local database to a remote host. The Visual Web Developer team announces that the wizard will be integrated in Visual Studio 2008.

The current beta 2 of Visual Studio 2008 does not contain the wizard, but the 1.2 release will be available pre-installed in VS 2008 RTM, which is expected to be published in February 2008. Release 1.1 of the database publishing add-in is available today as part of the SQL Server Hosting Toolkit. The toolkit provides the following solutions for hosting customers and hosters:

• For Hosting Customers
  • The Database Publishing Wizard makes it easy to upload databases to hosters, or generate T-SQL from the objects and data in your database.
• For Hosters
  • The Database Publishing Services are a set of ASP.NET web services you can easily deploy to make it simple for your customers to deploy SQL Server databases into your environment.

Scott Guthrie has published a walkthrough of deploying a database with the initial release of the Visual Studio Database Publishing add-in, as well as an overview of the current 1.1 release.

Although the publishing wizard is a good start for deploying SQL Server databases, it lacks features for a full-fledged build solution. Jean-Paul Boodhoo provides a series of articles about "Automating Your Builds with NAnt" on his blog. Part 6 covers the details of setting up and customizing a build for a SQL Server database.

Peter Hancock has written an article about his way of deploying a database as part of a continuous integration build:

The following article came about after reading reading Evolutionary Database Design by Martin Fowler and Pramod Sadalage. The concepts were great, but I couldn't find anything on the web that really showed how to go about implementing it. So I decided to have a go at it myself from the ground up.
[...]
It's not that difficult to get it running. The advantages of spending the effort are tremendous though. In previous companies I've worked at, the running of scripts has been done by the DBA, and based on a weeks work getting everyone together to rollback failed ones, update the ones that depended on the failed ones, and generally just make sure it works. It's prone to transcription errors, permission errors, script errors and inconsistencies. By testing the deployment each time, as well as unit testing, we can reduce this. One last thing worthy of note though is that just because it's easier to change, doesn't mean that developers can hack tables. Thought still needs to be put into the design of the database.

The Database Project and the corresponding MSBuild tasks are another alternative for integrating database deployment in your build. MSDN provides an "Overview of Database Build and Deployment" for VS 2005 and VS 2008.

• Older >