SSDT circular reference/unresolved reference workaround needed

Question

I've just started dabbling with SSDT and hit an issue already.

My solution consists of 2 databases. Both databases reference each other with the use of synonyms. Thus we have circular references as far as SSDT is concerned.

I'm aware of the design issues with this arrangement so no need to comment on that nor suggest structural changes to the DB's themselves. This is an existing system and I don't have the power to change it structurally.

I'm also aware of the fact SSDT will not allow circular references. There is a workaround here(http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/5fd12f01-54e6-4e7d-b7e2-14fa9df9a7ef). It suggests to split DB1 into 2 projects DB1 and DB1a for example where DB1a references DB1 and DB2 and make DB2 reference DB1. But I'm unsure how to configure this to work without actually creating an extra database.

I'm thinking my only option is to leave it as 2 projects but set them to ignore unresolved references.

Kelle

How many objects are interdependent between these two databases? If it is only a few, you may like to consider just deploying them imperatively by including them in a post-deployment script in one of the database projects. That will save you creating the third db project. – Wop

It's about 30-40 triggers in both databases. It's a two-way interface to keep the old system updated from the new one and vice-versa (cost more to write than the new system! But the customer is always right :) ). Anyway, I have taken the triggers out of the SSDB project for now and will add them as a post deployment as you suggest. I think I've found a way to stop errors on the synonym creation by changing the Extended TSQL Verification property. – Kelle

Actually, splitting up the database in two projects doesn't make it two databases. These are called composite projects. See also sqlblog.com/blogs/jamie_thomson/archive/2012/01/01/… – Jilt

A way to manage circularity is explained here: https://mcmap.net/q/1914437/-tfs-migration-database-project-circular-dependency – Sideline

Answer 1

As mentioned in the comments, I suggest looking at deploying your triggers imperatively as Post-Deployment scripts in either of the two existing database projects.

Note that by doing this, you won't be able to make any references to the triggers themselves in your SSDT Database Project (unless those objects also included in the Post-Deployment script). Not sure if an object type actually exists that could have a dependency on a trigger, but I thought it worth mentioning anyway :).

Wop