Search code examples
sql-serversql-server-data-toolscircular-reference

SSDT Circular reference: Complex project

I have a fairly complex setup with eight databases on a server each referencing each other (about every database referencing each other), giving way to quite a complex web. The design is far from ideal, but unfortunately this is something we have to work with.

We need to create a SSDT solution to facilitate CI/CD

The whole project needs to be deployed from scratch on a new instance and I am trying to get my head around this, as I have limited SSDT knowledge for a project this scale.

The approaches I consider are as follows: 1) Split objects into shared objects, and reference the shared objects. This seems to be a nightmare to implement, as we would require different layers because of the complex web of references. (shared object referencing other shared objects). Also how do we deploy such a project on a blank server? 2) Create stubs for each object in a project being referenced by other objects, and make a database reference to these. This seems to be the easiest option, although it seems that if the object the stub is based on gets changed, the stubs also needs to be maintained otherwise the project will break. Is this the right assumption? 3) Only create stubs for projects required to compile (eg. tables referenced by views in other databases), and ignore warning references. I am leaning towards this route as the stubs will be much smaller and project easier to maintain, but I hate to ignore referencewarnings..

If we deploy using the stubs option, do we need to deploy the stubs first and then delete them after successful deployment?

Another (more straightforward question). What is the best way to deploy logins, users and object permissions ?

Thanks for replying.

Solution

  • The question is too broad but these are few suggestions:

    1. You can't do anything with circular reference. There are some ways to workaround it but all of them are "hacky" and most probably will introduce more problems than to solve your problem. So try to move objects in so manner that there is only one way dependency;
    2. Use synonyms for ALL cross database objects, so there supposed to be no straight reference outside database;
    3. I agree with Peter Schott that it is better to ignore logins and users for now as handling them in SSDT is a bit of pain and you need to have good expertise on SSDT to make it working properly.