I have a SQL Server Instance which has a remote Linked Server (192.168.0.2) set up. In my database I have found that there are 50 local database objects (views, functions & stored procedures) querying tables within another database on the remote linked server.
My 2 different environments queries as follows:
LIVE:
select * from [192.168.0.2].DB1.dbo.MyTable
PRE-PROD:
select * from [192.168.0.2].DB2.dbo.MyTable
There is a potential risk involved when I duplicate my database to create/refresh my pre-prod environment. The risk is that on the prod environment, my database will query the remote database called DB1; on the pre-prod environment, my database will query the remote database called DB2. So for my pre-prod environment to safely use the test remote database instead of the live remote database, I am forced to have to remember to update all 50 local database objects to use DB2 instead of DB1.
I have re-factored it a bit by altering the 50 local objects to rather query views, which then queries the linked server's appropriate database.
Now I am able to query as follows within my 50 local objects:
LIVE:
select * from uvw_MyTable
PRE-PROD:
select * from uvw_MyTable
I still sit with 5 different views that I will need to manually update with every pre-prod refresh, to explicitly query DB2 instead of DB1 on the Linked Server. Is it at all possible to re-factor this scenario even more or even to make it more dynamic and get rid of the hard-coding of the different database names all together, if so kindly assist?
You need another level of abstraction.
Create local synonyms for the tables on the linked server.
CREATE SYNONYM [dbo].[MyTableSynonym] FOR [192.168.0.2].DB1.dbo.MyTable
Rewrite the queries:
SELECT * FROM [dbo].[MyTableSynonym]
Create script to recreate synonyms (DROP & CREATE) to point to test database objects when refreshing the pre-production environment.