SSDT complex circular reference project

Question

SSDT complex circular reference project

sharky

Default port

Points: 1494
More actions
August 20, 2018 at 3:09 am

#372081

Hi All,
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 deploy the whole project 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 is 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

I would tend to lean toward establishing ALL the linkages between the databases as a single final step done by an "incremental" T-SQL script, and let the base objects get deployed the usual way, with the incremental script being the final element of deployment, even if it has to be run manually. That kind of complexity is incredibly difficult to maintain as a project.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246959 More actions · Answer 2

Have you managed to get all these DBs imported into SSDT and are you able to build the solution? If so, how did you do it (and avoid the circular reference errors)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sharky Default port Points: 1494 More actions · Answer 3

Hi Phil, Steve

I have imported everything successfully, but because of the Circular reference problem I am not able to build the solution. I have started with the biggest database (most references) and created stub databases for only the objects required to build (eg: views referencing other objects in databases), and ignoring references errors not required for build. At the moment I am working through the solution this way. Was able to build the project(database), and now working through the others using the same methodology

I was thinking of doing it this way, deploying the stubs first, then deploying each project and dropping the stubs eventually.. Will this work?

Thanks Steve for the solution, got me thinking... What I don't want is to worry about future maintenance(although I realise this will probably be necessary for a complex project like this).

How do you normally go about with logins and other server objects?

Thanks for the help

sgmunson SSC Guru Points: 110640 More actions · Answer 4

sharky - Monday, August 20, 2018 8:54 AM
Hi Phil, Steve
I have imported everything successfully, but because of the Circular reference problem I am not able to build the solution. I have started with the biggest database (most references) and created stub databases for only the objects required to build (eg: views referencing other objects in databases), and ignoring references errors not required for build. At the moment I am working through the solution this way. Was able to build the project(database), and now working through the others using the same methodology
I was thinking of doing it this way, deploying the stubs first, then deploying each project and dropping the stubs eventually.. Will this work?
Thanks Steve for the solution, got me thinking... What I don't want is to worry about future maintenance(although I realise this will probably be necessary for a complex project like this).
How do you normally go about with logins and other server objects?
Thanks for the help

For logins and other server objects, I once again use incremental scripts, in some cases post-deployment. Some of that depends on how easy it is to enforce the order of what gets deployed. If deployment order can be easily controlled and managed, it's part of the solution. If not, it's a post-deployment script. Some folks use 3 or 4 digit prefix numbers at the beginning of the filenames where it's required that submitted scripts must get numbered initially in increments of 100, with only the folks doing the deployment changing those values when they know they need to re-order things, and then always using increments of 50, 25, 10, 5, 2, and even 1 if it really get's crazy...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

Maybe you have already considered these but still worth mentioning.

http://www.sqlservercentral.com/articles/SQL+Server+Data+Tools+(SSDT)/156986/
http://blogs.solidq.com/en/sqlserver/ssdt-how-to-solve-the-circular-references-issue/

but in any case its never straightforward.