Renaming Host, again

Question

Post reply

Renaming Host, again

ramam

Old Hand

Points: 302
More actions
August 20, 2010 at 2:45 pm

#387626

Hey folks,
I need to change the computer name of a SS2k8 machine (say from oldN to newN). I'm walking through the process in a copy of the system running in a VM. I've read dozens of posts and articles here and elsewhere on the topic and I'm still not warm and fuzzy.
Here's what I've done:
1 rename the computer
2 restart the computer
3 launch SSMS
4 tried sp_dropserver, sp_addserver but launching SSMS seemed to change that already
5 renamed the local groups created by the installer changing oldN to newN
6 in SS renamed Security/Logins/ [oldN\administrator] to be [newN\administrator] (no SID change so ok)
I've been pouring through master.sys.* and looking for references to oldN. There are several remaining - such as:
1 sys.sysusers - still has a reference to oldN\administrator
2 sys.syslogins and sys.principles show the new hostname
3 sys.sysservers - still things it's oldN
4 sys.database_principals shows the oldN
Queso?

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

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

Do the 4th step manually. Don't go on assumption. Drop the existing SQL Server name and create it.

Check the server name using

SELECT @@SERVERNAME

SELECT SERVERPROPERTY('SERVERNAME')

In addition to this check the below things

*) Make sure all your DSN entries and Alias are changed properly to new server name.

*) Make sure your originating server is pointing to new name in msdb database on sysjobs table

Regards..Vidhya Sagar
SQL-Articles

ramam Old Hand Points: 302 More actions · Answer 2

vidhya sagar (8/22/2010)
Do the 4th step manually. Don't go on assumption. Drop the existing SQL Server name and create it.

Okay. I went back and dropped and added the local server.

Check the server name using
SELECT @@SERVERNAME
SELECT SERVERPROPERTY('SERVERNAME')

@@SERVERNAME is oldH

SERVERPROPERTY('SERVERNAME') is newH

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 3

Have you restarted SQL Server Services after dropping and recreating the server name in SQL? This is a mandatory step for the change to become effective. If you do this then @@servername will reflect the new name

Regards..Vidhya Sagar
SQL-Articles

ramam Old Hand Points: 302 More actions · Answer 4

You are right - I did not restart the server after the sp_drop and sp-add. That updated @@SERVERNAME. However

sys.sysusers

and

sys.database_principals

both still have references to the old hostname as part of a user account name (the windows administrator account as oldH\administrator rather than newH\administrator)

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 5

Nothing is a clean as it should be, even when following the prescribed procedure. SQL Server is quite complicated.

Cleanest is still to uninstall and re-install SQL Server. Quite controversial yet the easiest way to be sure EVERYTHING is correct.

ramam Old Hand Points: 302 More actions · Answer 6

I would be okay with un/re install if that's needed though I don't want to 🙂

My only concern with those entries that are still wrong is what they impact of them being wrong is. I am not clear as to the purpose of those entries in those tables thus I am here asking around - does it matter?