6

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?
1
  • 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] Aug 21 '14 at 15:38
11

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.

2
  • 2
    This seems like a logical solution to the problem
    – Ryan Gross
    Aug 21 '14 at 19:43
  • 1
    +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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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