I have a problem with our database scripts. We have three environments: DEV, QA and UAT. We have two databases: DB1 and DB2. On DEV/QA those two databases are on different physical servers hence we have a linked server to DB2 in DB1. However on UAT it is a single server with both databases.
Our issue is that the scripts we write involve referring the DB2 using the DB2 linked server, which fails in UAT. The process we adopt is to use manual ways to deploy to UAT, which is not efficient.
Can someone suggest what can be done to better this?
You can create a linked server in your UAT environment's database referencing itself, with the same name as the linked server in your DEV/QA environment.
Suppose the server name of the UAT's database is MYUATSRVNAME (use the output of SELECT @@SERVERNAME). You can create a self-referencing linked server with the same name as the linked server name used in your DEV/QA's DB1. Suppose that name is UVWXYZ, on your UAT database execute
EXEC sp_addlinkedserver @server='UVWXYZ',@srvproduct='',@provider='SQLNCLI',@datasrc='MYUATSRVNAME';
That way you can use the same scripts referencing the same linked server name. Eg this query should then work in DB1 on both DEV/QA and UAT:
SELECT * FROM [UVWXYZ].[DB2].[your_schema].[your_table];