Thursday, March 01, 2012 2:29 AM
We were heavy users of the partial project feature in previous versions of VSTDB. That solution is a seried of "linked files" and you can still create those in SSDT. It isn't very practical, however, keeping all those links up to date as items are added and removed from the external project.
Our core requirement is to create a composite project. We have a schema of shared tables, views, functions and procedures we call our "framework" schema. We deploy that schema into the databaseas of all our major application projects. The application-specific data is put into other schemas of the same database.
We have one VS database project where the framework schema objects are maintained for all applications. Since switching to SSDT, we snapshot the framework schema project and reference the resulting dacpac file in our other projects. That works great and is really easy to administer by copying the dacpac's to a shared location where all developers can point their application databases to refer to it.
The problem is that when you deploy your application project that references the "framework.dacpac" - it doesn't deploy anything for the framework schema. This is consistent with the way dacpac's like "master" are handled. It makes sense, but we think it would be awesome if you could specifiy - when adding a database reference - whether or not you want it deployed. When the property is ON, then SSDT would conduct the change analysis and deployment just like installing a dacpac through other tools.
We think a feature like this would be a great replacement for partial projects and give DB development the "shared .dll" kind of functionality available in other tiers of development.
Does this sound useful to anyone else?
Thursday, May 03, 2012 6:13 PM
Absolutely, we are heavy users of partial projects for a similar reason. We have a core partial project that contains shared objects used by all of our clients. We also have shared application specific partial projects. We then have a separate database solution for each client that includes the core shared schema and one or more of the application specific partial projects. These composite solutions also include client specific tables, functions, stored procedures, etc. The partial project functionality is a perfect fit because development of core functionality could be done on the partial projects while client specific development, comparisons and deployment could be done from one composite solution.
The ability to compare and deploy multiple partial (referenced) databases as a single unit would be very high on our list of desired functionality.
- Edited by Mark D G Thursday, May 03, 2012 11:34 PM
Monday, May 07, 2012 3:35 PM
Agreed - this is a much-desired capability
Wednesday, June 06, 2012 12:06 PM
All, see the option in SSDT publish profiles called "Include Composite Objects". I have a similar requirement as you guys and this is working for me by having a central shared project, referencing it and specifying same server, same database when choosing the reference
The only possible limitation is that the referenced project needs to be the same version. As a result I've had to resort to having a a solution with multiple projects for each version (2005, 2008, 2012) and using the old link to file ability in here to prevent duplication.
The advantage is that new objects are automatically included.
Give it a go and see if this provides the ability you need
Wednesday, June 06, 2012 2:28 PM
Thanks Brett. Yes - that totally works. Sorry I missed that option earlier.
For our group, anyway, that is a complete solution.
Thursday, June 07, 2012 5:50 PMYep, works very nicely. Thanks from me, too!
Friday, June 08, 2012 3:15 PM
Actually, one thing I'm not finding....
When the "base" project defines a post-deploy script, that doesn't appear to execute. Am I missing another simple config step?
I've defined some test data in a separate project that references the core database, and I need the post-deploy of the initial project to run so that system data gets added before that user data.
Any suggestions very much appreciated!
Wednesday, June 13, 2012 8:52 PM
Not sure if you have already tried this but can you call your post-deployment script from your first project, in the post deployment script of the second one? Those scripts run in SQL command mode so I'm thinking you could use the run syntax - e.g.
:r .\C:\Users\YOU\Documents\Visual Studio 2010\Projects\YOURPROJECT\PostDeploymentScript1.sql
Friday, July 20, 2012 9:33 PMAfter further experience with the "Include Composite Objects" feature - we find there is still a problem. New objects from the dacpac are created in the target schema OK but if an object that has already been deployed has changed in the dacpac, the tool identifies the change - but the check box to apply the change is unselected and disabled in the schema compare. The only way we have found to deploy the changes then, is to Publish. Not a show stopper but seems odd that the tool detects the change in the schema compare but doesn't let you apply it.