Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In my client's setup, the database servers in lower environments are all on one server for cost saving purposes, but in higher environments, these databases are deployed on separate servers, and linked servers are used when queries run across the servers.

Example setup:

  Server1: DB1, DB2
  Server1: DB1 (with linked server for DB2)
  Server2: DB2

I am trying to maintain the databases with a single set of SQL Server Data Tools projects, using 4 part [server].[database].[schema].[object] identifiers and database reference variables to model the relationships. This works well when the databases are not on the same server, but fails when the databases are on the same server.

My questions:

  1. Is there a default way to refer to the local server in a 4 part identifier other than omitting the server definition?
  2. If not, is there a way to add a linked server pointing to the local server?
  3. If neither of the above is possible, is there another way to manage this in SQL Server?
share|improve this question
You can not add a local server as linked server but still you can use server full name for four part naming i.e. [server].[database].[schema].[table/view] – aasim.abdullah Aug 21 '14 at 15:38
up vote 9 down vote accepted

Use a synonym, and just don't sync the synonyms. So when it is on a local server:

CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName;

When it is remote:

CREATE SYNONYM dbo.FooBar FOR ServerName.DBName.dbo.TableName;

Now all your code just has to reference dbo.FooBar no matter where it is.

share|improve this answer
This seems like a logical solution to the problem – Ryan Gross Aug 21 '14 at 19:43
+1, I didn't know you could do this. I may suggest something like this at work to make our shenanigans simpler. – Cruncher Aug 21 '14 at 20:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.