Need to rename server but name is in lots code - alias?

Question

Need to rename server but name is in lots code - alias?

smv1009

Valued Member

Points: 57
More actions
May 22, 2018 at 8:00 am

#412072

I have to rename a SQL Server soon. Unfortunately, the server name is hard-coded in many procs, views, scripts. After renaming the server, can I just create an alias that is the same as the old server name? That way, existing like below would still work?
select field1, field2 from OldServerName.db.dbo.table1
where OldServerName is now an alias to the new name?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

smv1009 - Tuesday, May 22, 2018 8:00 AM
I have to rename a SQL Server soon. Unfortunately, the server name is hard-coded in many procs, views, scripts. Is creating a server alias a solution, so that code, such as...
select field1, field2 from OldServerName.db.dbo.table1
Will still work. I'd...
1. Rename the server from OldServerName to NewServerName
2. Create a server alias called OldServerName
Will this allow the statement to still function?

Could you simply keep the OldServerName and point the linked server to the NewServer?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

smv1009 Valued Member Points: 57 More actions · Answer 2

Hi, Luis. Our new parent company is going to make us apply their server naming scheme to all of our servers. So
all servers must be renamed.

Would linked servers versus alias work? That is
1. Change the name of "OldServer" to "NewServer".
2. Create a linked server named "OldServer" that points to "NewServer" (or localhost)

Is this better or equal to using an Alias. I've never used a server alias, but I use linked servers.

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

DNS alias? That works - I've used it on occasion.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

First, protect yourself for the future because this won't be the last time you run into such a thing. STOP putting 3 and 4 part naming into your code. Instead, create synonyms to point to linked servers and other databases. Then, when this happens again, you just need to repoint the synonyms and Bob's your uncle.

To find what you currently have, please see the following article. Disclaimer.... This was recommended in an SSC article yesterday. I make no claims that it works as advertised.
https://port1433.com/2018/04/27/finding-linked-server-references-using-powershell/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)