Any overhead using SQL Linked Servers between databases on the same server?

Question

We're looking to iron out issues in our different dev/test/prod environments.

Currently we have to remember to change the name of linked servers in stored procedures when we migrate from UAT into Production. For example, in Production, a sproc in SMOLDB calls across a linked server to LS_AUTH.AuthenticationDB.dbo.SomeSproc because AuthenticationDB is on a different server. But in Dev and UAT SMOLDB and Authentication are on the same server.

To get around this risk, we're thinking we'll set up consistent references to linked servers in all our environments. So even in UAT we'd create a LS_AUTH linked server, and SMOLDB will still call LS_AUTH.AuthenticationDB.dbo.SomeSproc

Do calls to linked servers go out onto the network before they are resolved? Not sure if we want to impact network traffic in those cases when the two databases are on the same server and don't need to go through a Linked Server.

The idea of ServerVariables I guess is an option...

Answer 1

We use linked servers to do exactly that, on both the production and test servers. We never noticed any performance overhead. The traffic certainly does not go over the network.

Answer 2

This may not work for you, but we had similar issues here. In our production environment, we have 2 servers, each with 1 DB. We used linked servers to go back and forth between the 2.

Now, in our testing/dev environments, we went with 2 SQL server instances; MYSERVER\LIVEDB and MYSERVER\LOCALDB. This allowed us to set up linked servers exactly like how we have in production.

As far as performance goes; we didn't notice anything out of the ordinary.

Answer 3

In SQL server you can also use synonyms to get around that problem. Use synonyms in queries and stored procedures, and just prepare synonyms for production server.

Answer 4

Thanks everyone, this is really helpful. The synonym is a new one on me too. Cheers.