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.

Bergschrund

Just to clarify, I did not delete my own answer, someone else did, apparently thinking it was off-topic. Given the nature of the question, I did not think that my response was inappropriate, and I think on the contrary that deleting the answer was inappropriate. – Sorbian

@tdykstra: I didn't delete it, but IMHO it was off-topic based on the OP specifying MSDeploy as the deployment mechanism. I happen to believe it's the right answer, just perhaps not the right answer for this question. If the OP would like, he should ask a question that includes the use of VSDBCMD as a possibility. – Thitherto

I was looking for an MSDeploy specific solution. I don't recall what tdykstra's answer was, but the link provided did lead me to the custom WPP targets solution which I mentioned in the question. – Bergschrund

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

Cairn

Thanks. This looks like the best solution for Ms Deploy 2.0. I'm looking forward to checking out code first data migration that has been mentioned as supported in 3.0. – Bergschrund

@seglo - I haven't heard about that, but it sounds promising. Any links? – Cairn

This is where I've heard it mentioned that code first migrations are supported for a Windows Azure web publish. Sayed is either working on or is associated with the Web Deploy team at MS. He participates on a lot of the MSDEPLOY discussion here on SO. Visual Studio 2010 Web Publish Updates – Bergschrund