How is SQL Change Automation helping you? Share to win DevOps books.

Does SQL Change Automation support Linked databases

We have 3 databases that use synonyms to reference each other, and have a large number of views and stored procedures that reference objects in the other databases. when I try to run a script, I am getting an error that says "Synonym 'ess.tblTeeth' refers to an invalid object" this makes sense because there is no Shadow database that the synonym refers too. 
Does SQL Change Automation support linked databases? and if so how do I get this to work

Answers

  • Andrea_AngellaAndrea_Angella Cambridge, UK Posts: 66 Silver 3
    We definitely work better when the database is self-contained and it's what we recommend. However, we understand sometimes cross-databases dependencies can't be avoided.

    We did some research on this problem and this article might help https://www.red-gate.com/hub/product-learning/sql-change-automation/deploying-cross-database-dependencies
    Andrea Angella
    Senior Software Engineer | Red Gate
    Website: www.productivecsharp.com
    Twitter: @angella_andrea
  • nandertonnanderton Posts: 2 New member
    Thanks for pointing out this posting it was helpful, but too simplistic to really demonstrate the issues. It also did not refer to the issues in your tooling (Visual Studio Extension).
    The problem I have is there are a number of cross database links. Views in one database that reference the other. Stored Procedures in the other database that use the view and multiple of these situations.  We are also already using synonyms, this is because our database changes name as it goes from one environment to another (we run it multiple onsite locations and on premise). The problem is that it fails validation, building the shadow DB is not possible.
    It needs to support cross-linked databases without work arounds. Another alternative would be to support partial databases in this case it would have to be OK to have unresolved references. Alternatively generating the base script should be able to identify all of the dependencies and bring them into the script so they can all be created.
Sign In or Register to comment.