Running into an issue with linked servers in LocalDB build
Joshua_Higginbotham
Posts: 13 Bronze 3
Hoping someone can point me in the right direction on this. We are attempting to setup CI using SQL Change Automation with a database that has linked server calls. The Build step that generates the SQL Source Control Project is failing due to the linked server not existing on the localdb for our build agent. Is there a way to filter out this validation for this step or do we need to find a way to generate a linked server for the localdb?
Tagged:
Answers
Great question. I have two Product Learning articles outlined and set up to write on managing dependencies like this in builds, so good to know that it's a needed article! Here's the short version:
This happens only for specific objects in SQL Server that reference the linked server, specifically views and some functions. This is because 'deferred name resolution' doesn't kick in for those objects when they are created. That's a SQL Server thing, and there's no SQL Server option to override it.
The build validation step ensures that your database objects can be built from Source Control, so this issue can be a thorny one. The culprit is usually distributed views that reference a table across the linked server.
Option: filter
The simplest way to solve this is to simply filter the object out of your project. If you have a very small number of objects impacted and no plans to change their code / no desire to have them in the build, then that can work.
Option: synonym (and maybe a linked server in the dev env)
If you'd like to build the object, you could create the linked server, but just doing that is often not desirable because often the link is to a production database, and you typically would prefer to not make a build dependency on production if you don't have to (and often firewalls/ trust between environments wouldn't allow it anyway).
My favorite option to make this work is to create a synonym for the remote object. The synonym provides a layer of abstraction and additional control -- it gives you the ability to make the remote object reference to a local database instead of the linked server during the build itself, for example -- or you could still use a linked server but use one in the development environment instead.
Happy to give more details on either the filtering option or the synonym option, but will wait to see if either of those sound like they'll work for you.
Kendra
Thank you
Cathryn