Renamed computer and connected to sql server without problem!

Question

Post reply

Renamed computer and connected to sql server without problem!

Ach-230297

Right there with Babe

Points: 759
More actions
September 5, 2005 at 4:22 am

#91015

I have read that if I change "computer name" and then try to startup sql server it will recognize the changes automatically BUT you need to execute sp_dropserver and sp_addserver procedures too in order to set all thing right.But yesterday I tried it on a Personal edition on xp and after reboot sql server came up without any problem and I could connec it without executing sp_dropserver and sp_addserver! Was it a specific situation or no need to those SPs at all?
-PM: Is there any difference between named or default instances in this context? I have read you need those SPs fot both of them,but I doubt now!Just curious!
-Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

vadba SSChampion Points: 11133 More actions · Answer 1

It doesn't cause any problems other than a possible invalid or null value for the global variable @@servername.

What do you get when you execute

SELECT @@ServerName

Of course, you must stop and then restart SQL Server to see the effect of sp_dropserver and sp_addserver.

gbn SSCertifiable Points: 6429 More actions · Answer 2

When you connecting to a SQL Server install (simply), say "SQLServerBox" it looks for the computer name in DNS, resolves this to IP address, then connects to that.

SQL Server (2000) does not care what the computer is called and will start OK.

But, SELECT @@SERVERNAME will return the old name (from sysservers) thus the sp_dropserver etc is needed.

SELECT SERVERPROPERTY('MachineName') returns the host (computer ) name

And I forget what SERVERPROPERTY('ServerName') does, I would have to check.

Now if you renamed your XP box and the connected to "NewName" rather than "OldName", then "NewName" would be resolved to an IP address etc (see above)

Yes, the same applies for instances... because the hostname is part of the instance name, where as a default instance just uses the hostname

Er, clear?

Cheers, Shawn

Ian Yates SSCoach Points: 19738 More actions · Answer 3

I think it was SQL 7 that got very upset about changes to computer names. SQL 2000 corrected this as described previously

Ach-230297 Right there with Babe Points: 759 More actions · Answer 4

Thank all for your replies,

I have read it in a SQL Server 2000 book.I considered it to be uptodate but it seems some DBAs(authors) are stick to older versions and habits without any malicious purpose!

-Regards

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

SQL v7 had an option on the installation CD that 'you had to trust' ... it worked 98% of the time. As for SQL2000, the sp_dropserver/sp_addserver/reboot is still the 'correct' method to make sure all the midofications to the SQL Server 'engine' have been completed after a physical server rename. Trust the book. There are hundreds of things (differenct functionalities of the DBMS) scattered all over that you may not use now, but may be broken later since you did not perfrom sp_dropserver/sp_addserver/reboot sequence.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Simon-220850 SSCrazy Points: 2789 More actions · Answer 6

I have the requirement to have to do this with a SQL 7 Server Soon. The Instructions for this would seem to be

Rename the physical server by going
Reboot the server.
Run the SQL Server setup program.
Reboot the server.
Run this stored procedure: "sp_dropserver <old server name>"
Run this stored procedure: "sp_addserver <new server name>, local"

What should be run on the SQL 7 Setup disk - is it a case of going through the motions as if to install SQL 7 again ? My concern is that I will overwrite my existing installation

Cheers