Partial project functinality in SSDT

Question

Before I begin I want to say how impressed I am with the improvements that have been made with SSDT. From top to bottom the process to generate projects and create the necessary relations from very complex databases has been simple and straight forward. What took days with previous versions took hours with SSDT. Nice!

We are currently using the partial project feature of the SQL Server 2008 Database Project. In our environment we have a set of shared tables, functions and stored procedures that are used by all of our databases. We also have other partial projects that contain application specific objects. The idea is that we can create customer specific databases by combining the partial projects as needed and adding any additional customer specific objects to the parent database. The beauty of this is that all of the common objects can be maintained in one place yet the main client specific (parent) databases can be fully deployed as a unit.

I am evaluating SSDT and am trying to mimic this functionality with the new tool. As a test I created the shared database (SharedDB) and the client specific database (CsDB) and added both projects to the same solution.  I then created a new database reference in CsDB for SharedDB and chose the “Same database” as the Database Location.

At the object level this reference seems to work perfectly as all of the stored procedures in CsDB could access all of the tables in SharedDB as if they were in the same database. This is exactly what was expected because after deployment they are the same database.

The first challenge I encountered was when I tried to do a schema compare between the CsDB project and a fully deployed CsDB. The schema compare only seems to recognize and compare the objects that are physically located within the CsDB project and does not take into account the objects from SharedDB. This makes comparing databases difficult because even though the project is in sync with the deployed DB there are “Adds” for all of the objects in SharedDB. It also makes updating difficult because I would have to uncheck all of the SharedDB objects every time I update or they get imported and duplicated in the CsDB project.

I also have the same scenario when I try to deploy. The deploy creates script for CsDB but nothing is created for the SharedDB. Basically, it seems to not treat the referenced and parent project as a single unit.

I would expect this functionality if I referenced SharedDB as “Different database…” but with “Same database” I would expect that the compare and deploy behave in the same way that the object level code does and treat the projects as a single unit. Is there a property or setting that I am missing to enable the “Partial Project” functionality in SSDT?

Thank you, Mark

Answer 1

We can share connection manager you can find details



on : Connection manager

Hop this will help !









Answer 2

I don't think that schema compare has anything to do with your project references, even though you are raising valid concerns here. Your project is stored in local db model which does not have any other referenced objects - so basically schema compare does the same job as if this was database and it does not care/look at the whole picture of your project.

Answer 3

 

Hi Vishal, my question is about how I can achieve partial project functionality using SSDT. In previous versions of the tool you can have multiple partial projects that combine to create a single database the can be compared and deployed as a single unit. I don’t see how connection managers would help in this case. If I am missing something here please elaborate so I can get an understanding of what your proposed solution is.

Hi Goranco, and thank you for the response. I agree that the Schema Compare is not taking into account the whole project but that is the basis of my question. I would expect that references of type “Different Database…” are not taken into account because they are by definition an external database. I would however expect references of type “Same Database” to be treated as “Partial Projects” as they were in previous versions of the product. This means that the local db model should contain objects defined in the current project along with any objects that were included as “Same Database” from other partial projects. If this were the case then both Schema Compare and Publish would treat all of the partial projects as a single unit.

Answer 4

Hi Mark,

First of all, thanks for the positive feedback.  It's great to hear that you're finding SSDT to be a productive tool.

In order to deploy SharedDB and CsDB together, navigate to the Advanced publish properties in the Publish dialog and enable the "Include Composite Objects" option.  

This will enable projects with composite references to be deployed as a unit.

-Janet Yeilding