Automated database migration with MSDeploy

Question

I'm a bit of a Continuous Delivery junkie and I have a passion for automating build, testing, and deployment processes. One thing that I really need some help on is what are the best practices when deploying database migration scripts in an automated deployment. These are the pertinent details of my technology stack.

• ASP.NET MVC 3 Web Site
• Visual Studio 2010 Database Project
• SQL Server 2008 R2 Database
• Windows Server 2008 R2
• Remote deployment via Web Deploy 2.0 (MS Deploy)

Currently I am capable of deploying our ASP.NET MVC website with the Web Deploy 2.0 tool (wow, that was fun to setup!). MSDeploy has a 'dbFullSql' provider, but this can only be used to deploy whole databases, not migrations. MSDeploy leaves the door open for extension with the 'runCommand' provider to execute an arbitrary process on the remote server. I think I may be able to utilize this to execute my migration scripts that way. However, I feel like there should be a better way to do this.

When answering keep in mind that I would like to use MSDeploy for all interactions with the remote deployment target. Although if you know of a tool that can be used to push migrations securely to remote targets that's not MSDeploy, I'd be interested in hearing about that too!

EDIT: An answerer (who deleted their answer!) made reference to the following article: http://www.asp.net/web-forms/tutorials/deployment/web-deployment-in-the-enterprise/deploying-database-projects

This is a terrific resource I was not aware of. Most of this content I've read in one form or another and unfortunately this particular article reaffirms my own suspicions. Essentially, there is no easy way to push custom incremental updates with the current tooling (vaporware promises for better functionality in future releases in typical MS style).

In the note at the topic of the article it mentions.

... using VSDBCMD is the recommended approach to incremental database publishing.

However VSDBCMD can only generate delta scripts between a source and a target which I will simply not trust for a migration, especially on a production deployment. The only solution the article mentions to push incremental updates via Web Deploy is a to create a custom WPP targets file with a custom SQL migration script included.

You can configure Web Deploy to run the SQL deployment script that the database project generates, but in order to do this, you need to create a custom WPP targets file for your web application project. This adds a substantial amount of complexity to the deployment process. In addition, Web Deploy does not directly support incremental updates to existing databases. For more information on this approach, see Extending the Web Publishing Pipeline to package database project deployed SQL file.

Argh! It does indeed look complicated. I've read about this before and I was hoping there would be a better way. At of now I'm still leaning toward my MSDeploy 'runCommand' provider executing a SQLCMD.

Answer 1

You can actually specify a SQL script as the "source" for the dbFullSql provider (-source:dbfullsql="C:\sqlcmd.sql") and it will simply run that script. I've used this before to deploy with a custom migration script management tool that generates.

I do wish database projects were more CD-friendly, as the "auto" stuff tends to break down fast when a db refactor is necessary.

Web Deploy dbFullSql Provider reference