3

This is regarding MS SQL Server. I have two DB servers for my application named Torch

TorchProd and TorchTest are the db servers. There is also a data mining server DataMineProd and DataMineTest I have linked servers to these shown below...

TorchProd has a link to DataMineProd

TorchTest has a link to DataMineTest

I have a proc called TorchCRUD. Inside that proc is this sql...

select * from DataMineTest.mydatabase.dbo.mytable

If I push this proc up to production it will fail because it can't find DataMineTest. It could find DataMineProd How do I create an alias such that I can move this proc freely from Test to Prod?

3
  • Hm. I don't know any way to alias a linked server. In similar instances, I've used dynamic SQL int my code along with @@servername and db_name() to keep the same code in both databases but still be able to access the respective,different servers. Commented Mar 7 at 20:33
  • 2
    All you need is to name linked server with the same name (for example) DataMine on both prod and test servers. But in prod it should reference DataMineProd and in test it should point to DataMineTest
    – sepupic
    Commented Mar 8 at 9:13

2 Answers 2

4

Here is how we've handled a very similar situation.

On both Torch SQL instances, create a linked server pointing to the matching DataMine server. But don't include the Test/Prod designation in the name of the linked server. Something like this on TorchTest:

EXEC master.dbo.sp_addlinkedserver @server = N'DataMine', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'DataMineTest'

And this on TorchProd:

EXEC master.dbo.sp_addlinkedserver @server = N'DataMine', @srvproduct=N'sql_server', @provider=N'SQLNCLI', @datasrc=N'DataMineProd'

Notice that the name of the linked server is "DataMine" in both cases, but the one on TorchProd points to DataMineProd, and the one on TorchTest points to DataMineTest.

Then, your code can refer to the "DataMine" linked server.

select * from DataMine.mydatabase.dbo.mytable

Push the code to Test, and when it executes, it will call across the link to the DataMineTest SQL server. Then push it to Prod, and it will call across the link to the DataMineProd instance.

Note: This is in no way an endorsement of using Linked Servers. Most of the cases in which we use them would be better off handled in a different way. See https://www.brentozar.com/archive/2021/07/why-are-linked-server-queries-so-bad/. But when we have to use them, naming them in this way helps allow our code to move from Dev, to Test, to Prod mostly seamlessly.

1

I decided to just create a synonym, which I didn't want to do since the test db server gets refreshed with the prod db about twice a year.

create SYNONYM myTable FOR DataMineTest.mydatabase.dbo.mytable
2
  • Yes a Synonym would be the closest thing to an Alias. Not sure I understand why you wouldn't want to do that.
    – J.D.
    Commented Mar 7 at 22:25
  • I use synonyms for this exact scenario and i just have a post-refresh script that recreates synonyms with correct base_object_path
    – Bob Klimes
    Commented Mar 8 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.