I have an application that I'm developing in Visual Studio 2010 that utilizes a SQL Server database. As such, I am using a VS SQL Server 2008 Database Project.
The database project requires a target connection for which is used to build the database.
Problem:
This project is under source control for a team of developers and a build server. Some of the developers have their '08 SQL Server instance named whereas others have a default instance. The idea of just using 'local' syntax will not suffice.
I need the ability to configure the database project connection outside of source control. I see that the database project offers a SQL command variables files, is this what I'm after?
What is the best practice approach on accommodating this problem?
I'm using MSBuild as part of my build server - how might this approach fit in with MSBuild?
The Visual Studio 2010 database project properties "Deploy" tab contains a drop down labeled "Configure deployment settings for:". This drop down supplies two options "My project settings" and "My isolated development environment". This setting allows for isolation between your local development settings and the project settings which will be persisted to the .dbproj file.
In you scenario clearing the "Target connection" currently assigned to "My project Settings" followed by submission to repository will relieve the shared connection setting. You could also clear the "Target database name:" setting leaving the build process in control of specifying the value for it. Once this is done developers are free to select "My isolated development environment" and configure connection settings without them being committed to the repository, but deploying therein.
The build process would then take control by doing something like the following: msbuild.exe {projectName}.dbproj /target:Deploy /property:TargetDatabase={targetDatabaseName};TargetConnectionString="{targetConnectionString}" Optionally you may opt to remove the "TargetDatabase" property favoring the project to supply this value.