October 14, 2020 at 8:19 am
Hi,
I'm experimenting a bit with SSDT database projects, and I've hit an issue that I have some solutions to, but they all feel a bit messy.
I'm trying to get to a point where I can have several databases feeding off the same template, so repeated functionality can be maintained in only one place rather than in every database. At the moment I'm using the tSQLt framework as my test case, so I have DBProject1, DBProject2, tSQLTFramework, DBProject1UnitTest, and DBProject2UnitTest.
References are:
Now, I have a unit test for DBProject2 that wants to fake a table in DBProject1. For this, I need to call the DBProject1UnitTest version of the tSQLt.FakeTable stored proc from DBProject2UnitTest. Just referencing DBProject1UnitTest doesn't work, because the stored proc actually exists in tSQLtFramework, and I can't add another reference to the tSQLtFramework project to DBProject2UnitTest.
The possible solutions I can see are:
Thanks in advance for any thoughts anyone has.
October 14, 2020 at 1:01 pm
Option 2 is probably the way to go. Cross database dependencies are very difficult to deal with in SSDT. I always found I had to do a certain amount of work in either pre- or post-deployment scripts. Same thing goes for dealing with security between various environments. Again, lots of pre/post-deployment work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2020 at 1:50 pm
Option 2 is probably the way to go. Cross database dependencies are very difficult to deal with in SSDT. I always found I had to do a certain amount of work in either pre- or post-deployment scripts. Same thing goes for dealing with security between various environments. Again, lots of pre/post-deployment work.
They're not always difficult. If the references are always in one direction (for example, to a central Utilities database), there's no trouble at all. As soon as the references are two-way, 'very difficult' is a perfect assessment.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 14, 2020 at 4:05 pm
Option 2 is probably the way to go. Cross database dependencies are very difficult to deal with in SSDT. I always found I had to do a certain amount of work in either pre- or post-deployment scripts. Same thing goes for dealing with security between various environments. Again, lots of pre/post-deployment work.
Cheers Grant, thought this was the case but wanted to check if anyone had a cleaner solution.
Like Phil, I've never found massive issues with the basic cross-database dependencies, but having to break databases out into separate projects to accommodate circular references is a pain. I've never really got the hang of security in database projects, but it's something I'm looking into more and more. I feel like there's a lot of power in the tool but you need to be at guru level to really make use of it all.
October 14, 2020 at 4:15 pm
Database security is one of those things which in my experience is difficult to handle within SSDT.
It can be handled in environment-aware post-deployment scripts, or completely outside SSDT. Outside SSDT is the more usual, I believe, because of the need to make security changes without requiring code changes / branching / check-in etc.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply