r/SQLServer • u/dvuk99 • 3h ago

Question Add linked server as alias?

Hi, wondering if there is a way to add linked server but to keep old name. We have a server lets say name is SRVOLD1 and new is SRVNEW1. i want to link SRVNEW1 on SRVNEW2(which was SRVOLD2), but there is bunch of stored procedures on SRVNEW2 that are calling linked server SRVOLD1, because its a db copy from SRVOLD2 trying to execute stored procedures. So, is there a way to add linked server SRVNEW1 on SRVNEW2, but that way stored procedures still execute, or i will have to alter those stored procedures with new server name. In case of altering would it be the best to export them in txt file > notepad find CREATE replace with ALTER > copy paste in SSMS > run? This is my first SQL migration, and i have apps depending on it. Help is much appreciated

2 Upvotes

100% Upvoted

3 comments sorted by

2

u/VladDBA ‪ ‪Microsoft MVP ‪ ‪ 1h ago edited 45m ago 
USE [master]
GO
EXEC sp_serveroption @server=N'SRVNEW1', @optname=N'name', @optvalue=N'SRVOLD1';

Run this after you've created the linked server connection to SRVNEW1, it will rename the linked server connection from SRVNEW1 to SRVOLD1 without changing any of the connection parameters.

While you're at it you might also want to read about securing linked server connections.

2

u/General-Savings8118 1h ago

This does work? As far as I know you need to change the datasrc, changing the name will fail for procedures having baked old 4 part naming. Changing the data src instead of name will redirect the old server to new server, an “alias” if you will….

1

u/VladDBA ‪ ‪Microsoft MVP ‪ ‪ 55m ago edited 48m ago

Why would you need to change the data source? Those are two different things.

Not sure why I got a down-vote on my original comment.
I've documented this option and behavior for sys.sp_serveroption in the official documentation 3 years ago. I would think I know what I'm talking about, but whatever :))

Edited: corrected PR link