VSDBCMD deployment for additions to third party databases

Question

We have some custom objects (stored procedures etc) in an SQL Server 2005 database belonging to an ERP system. The custom objects are in different schemas to the ERP objects. We're using Database Edition .dbproj projects and vsdbcmd deployment for all our custom application databases and would like to similarly manage our custom objects in the ERP database. It's not clear how this can be done without either:

1. Importing all ERP objects (~4000 tables) into the .dbproj and manually keeping them in sync with ERP development. Visual Studio fell over the only time I tried importing these, so I've no idea whether it can actually handle a project of this size.
2. Somehow excluding the ERP schemas (there are two) from the diff process to ensure they don't get dropped by vsdbcmd. I haven't found any documentation which suggests this is possible.

I'm aware of the IgnoreDefaultSchema setting, but there are two schemas I need to ignore and I'm not comfortable with the 'default schema' approach - deployment by different users could be disasterous.

Has anyone managed to successfully use .dbproj & vsdbcmd for custom additions to a third party database? If not, how do you manage SQL source control & deployment?

Answer 1

Here's what I would try:

1. Create a partial project with only the vendor objects you are dependent on in it (I suspect this would be primarily tables).

2. Create a project to hold your custom objects and add your partial project (using a partial project will prevent someone from accidentally changing one of the vendor objects). Then add your custom objects.

note: make sure you've set your project to not drop objects that aren't in the project!

This link may be helpful as well: http://blogs.msdn.com/b/dukek/archive/2009/12/31/the-pros-and-cons-of-partial-projects-and-database-project-references.aspx