August 20, 2010 at 2:45 pm
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?
August 22, 2010 at 8:15 pm
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
August 23, 2010 at 12:27 am
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 @@SERVERNAMESELECT SERVERPROPERTY('SERVERNAME')
@@SERVERNAME is oldH
SERVERPROPERTY('SERVERNAME') is newH
August 23, 2010 at 2:42 am
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
August 23, 2010 at 11:20 am
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)
August 23, 2010 at 1:10 pm
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.
August 23, 2010 at 2:15 pm
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?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply