I'm part of a small team that want to move to using TFS for source control and build management for a SQL Server 2008 database. For our product, it is possible to have customers that are running different versions of the product/database. One issue I need to resolve is how to manage complex upgrade-scripts for situations beyond the normal capabilities of deployment.
A simple example of this:
Version 1.0 has separate tables for Admins and Clients.
Version 2.0 replaces these tables with one Users table. For this, a custom script is needed to migrate information from Admins and Clients to Users. According to the guide from Visual Studio ALM Rangers (http://vsdatabaseguide.codeplex.com), the best way to do this would be to copy the data into a temporary database in pre-deployment and then copy out of the temporary database in post-deployment.
This method would work fine for deploying Version 2.0 to an existing Version 1.0 database, but how do you manage this update script for future versions? One of the goals of using TFS here is to be able to deploy Version 3.0 to databases that are running either Version 1.0 or Version 2.0; the upgrade-script would have to remain as part of the deployment process so that going from 1.0 to 3.0, but the script should not be included when going from 2.0 to 3.0.
Can upgrade-scripts like this be managed through TFS? Does TFS have the capability to only execute certain scripts based of any kind of versioning?