Take the 2-minute tour ×
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.

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:

Dev:
  Server1: DB1, DB2
UAT: 
  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
2  
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 at 15:38

1 Answer 1

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
1  
This seems like a logical solution to the problem –  Ryan Gross Aug 21 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 at 20:54

Your Answer

 
discard

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.