SSDT database project multiple references to another project

  • chris.johnson.0120

    Old Hand

    Points: 308

    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:

    1. DBProject2

      1. DBProject1

    2. DBProject1UnitTest

      1. DBProject1 - same database
      2. tSQLtFramework - same database

    3. DBProject2UnitTest

      1. DBProject2 - same database
      2. DBProject1 - different database
      3. tSQLtFramework - same database

    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:

    1. Create a new version of tSQLtFramework for each database. This pretty much defeats the purpose, which is to be able to maintain the code in one place. The idea is to use this for things other than tSQLt, which we will want to update someday.
    2. Add a post-build command line script to tSQLtFramework, to copy the .dacpac file several times with different names. Each database can then have their own .dacpac file, and I can include several of these in one project. Feels a bit messy to me, and means editing the command line script every time I make a new db but is the solution I'm leaning towards right now.

    Thanks in advance for any thoughts anyone has.

  • Grant Fritchey

    SSC Guru

    Points: 396751

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Phil Parkin

    SSC Guru

    Points: 244780

    Grant Fritchey wrote:

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • chris.johnson.0120

    Old Hand

    Points: 308

    Grant Fritchey wrote:

    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.

  • Phil Parkin

    SSC Guru

    Points: 244780

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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