TFS Migration - Database Project Circular Dependency

Question

I am trying to create a solution for a web application (that also contains the database as a database project) and then deploy it from TFS using web deploy for the application and DACPAC for the SQL database. Unfortunately the database is referencing another database using 3-part names:

Select * From Database1.dbo.Table1

This forces me to import the referenced database as a project in the solution for the application that references it and set it as a reference in the other project, as seen in the picture below:

The problem is that Database1 is referencing Database2, but Database2 is also referencing Database1.

However when I try to do this I get the following error:

I have searched online for a solution and found two:

1) Using composite projects to create another 3rd project that contains the references between the two databases and then make this project reference the other two.

See this link: Composite projects solution

2) Replacing all the 3-part names queries to dynamic SQL, such as this:

EXEC('Select * From Database1.dbo.Table1')

None of this solutions is good for me as I don't just have two databases referencing each other, but many databases referencing a central database that references them back, as seen in the schema below:

The first solution would require that I import all the databases into the solution of each application (as they are linked to each other via the Central Database). Also there would be the circular reference error for each pair of projects (Database, Central Database).

The second solution would work as the queries would be seen as strings and would not require me to reference the Central Database in the solution, however I do not like the idea of having so many dynamic queries. Also it would be way to much work to replace all queries with dynamic SQL in each application database.

I would like to know if there are any other solutions beside the two I have mentioned.

Cenogenesis

Answer 1

The right way to solve the circular references problem is using the composite projects.

In general the "trick" consists to isolate all the shared objects (and the ones referenced by these; eg. a shared view and all the tables/functions used in its definition) in a composite project for each database. In this way each database will be defined by a couple of database projects: one containing the objects used only inside it (base) and one containing all the objects to be shared with the other databases (shared). Then you have to link a base database project with the shared one whose object are needed in its definitions.

One picture is worth a thousand words:

The dashed lines represent the "Same database" references (composite project). The solid ones are "regular" references.

I've updated my blog post with a generic case:

SSDT: How to Solve the Circular References Issue

Howells

Answer 2

You can also create a dacpac out of the existing database and add that dacpac as the database reference. We did that using a "Schema" folder to store all of the dacpacs and updated/referenced those as needed.

http://schottsql.blogspot.com/2012/10/ssdt-external-database-references.html

Obverse

This will apparently solve the issue thanks to a correct build. If you try to release such a solution with sqlpackage.exe (providing all the dacpacs you use in the project), the circular references issue still persists and you can't deploy your solution. – Howells

Wasn't thinking about that aspect, but you're mostly right. It can be done, but involves turning off the transactions and rollback options and incrementally publishing DBs until you have a successful deploy. We had that issue for 2 DBs in a circular relationship and got through after deploying DB1 (with failures), DB2, then DB1 again. It got enough objects created to keep going. Once we had successful deploys, we just backed up the DBs as a starting point to use for the future. – Obverse

Definitely, but it worked and for the most part I don't really need to worry about the circular references once the DBs are deployed. Besides, we had a bunch of "seed" data used for local QA and basic program ops so the backed up DBs really helped us once we got them set. I only have to go through that first build once (DB2 depended on "People" table in DB1) and once that was created, we were good. Could probably have done a pre-deploy script as well or a pre-build batch file. Ours wasn't nearly as complex. :) – Obverse

Answer 3

You can create another project that can be referenced Database1 and Database2 and this project handle calling between the two projects.

and let this project communicate with web application.

Ootid