A vendor app uses 4-part names to reference objects. I am pushing back on that, but the current version is what it is. We installed the app using a SQL Alias for all the good reasons you do that, but the app had tons of errors because its four part references were starting with the alias instead of the local server name. Defining the alias on the db server had no effect – the references were being made within the DBEngine.
To get around the errors, I added a second server definition (linked server) named after the alias that referred back to the same DBEngine. I recently heard about the performance costs of linked servers (e.g. no caching) and about how bad an idea it is to define a linked server that refers to the local server.
I am stuck with this version of the app for now, so I cannot do anything about the four-part references and cannot reinstall without the alias (my client is not willing to pay for that work).
Would it help to delete the default server definition and replace it with the alias name instead of the machine name.
Comments? Warnings? Risks? Will doing that avoid the performance issues associated with self-referencing linked servers?
As a secondary question, I would be interested in a clear explanation of why four part names are a bad idea – something I can pass on to the vendor.
Best Answer
A linked server even if it links to itself is still a linked server with all of the pain points that entails (as you outlined in your question). I would either stand up a new server/instance with the name that you had installed and run the database there without the linked server, or get the app to reinstall with the actual server name.
Having an Alias does make things simpler - mainly that you can move the database around without the app knowing - but if it's going to do every query through a linked server then benefit is outweighed (in my opinion) by the cost.