SQL database Publishing in Visual Studio 2008

The Database Publishing Wizard (db Publishing Wizard) is a popular Visual Studio plug-in that supports the deployment of a local database to a remote host. The Visual Web Developer team announces that this wizard will be integrated into Visual Studio 2008.

Currently, the Beta 2 version of Visual Studio 2008 does not include this wizard, but it is expected that the VS 2008 RTM, released in February 2008, will install version 1.2 of this wizard together. The current version of this database publishing plug-in is 1.1, from the SQL Server Hosting Toolkit. This toolkit provides the following scenarios for users and providers of the main machine service:

For the client of the host service

Database Publishing Wizard simplifies uploading databases to hosts, and can generate T-SQL based on objects or data in your database.

For host service providers

Database Publishing Services is a group of ASP.net Web services that you can easily deploy to facilitate your customers to deploy SQL Server databases in your environment.

Scott Guthrie has written a detailed guide to the deployment database for the first release of the visual Studio Database Publishing Plug-in, and has written an overview of the current version 1.1.

Although this publishing wizard is a good start in the deployment of SQL Server databases, it lacks many features to become a fully fledged build. Jean-paul Boodhoo wrote a series of articles on blogs about "building with Nant automation." The 6th part discusses settings and customization details for building SQL Server databases.

Peter Hancock also wrote an article about his experience in integrating database deployments into continuous integration:

This article was inspired by the reading of the evolutionary Database design written by Martin Fowler and Pramod Sadalage. The concept is good, but I can not find any specific instructions on how to implement it on the Internet. So I decided to start from scratch.

[...]

It is not difficult to run successfully, and the rewards of effort are immense. In my previous company, the script was run by a DBA, and it took a week to get everyone together to make sure that the script worked, roll back the failed script, update the script that relied on the failed script, and so forth. Copying errors, permission errors, script errors, inconsistencies, and so on are common. By testing the deployment as frequently as unit tests, we can reduce the tendency to err. Finally, it is also useful to remind that the modification of the database becomes more convenient, which does not mean that the programmer can change the data table arbitrarily. The design of the database still needs to be considered.

You can also use the "database project" in VS and the corresponding MSBuild task in the build to integrate the database deployment. An overview of database building and deployment is available on MSDN for VS 2005 and VS 2008.