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.
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.
- Is there a default way to refer to the local server in a 4 part identifier other than omitting the server definition?
- If not, is there a way to add a linked server pointing to the local server?
- If neither of the above is possible, is there another way to manage this in SQL Server?