Change of Computer Name

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    One of our client had to change the data server name with sql server 2005 enterprise edition installed.

    I checked the server name using

    SELECT @@SERVERNAME,SERVERPROPERTY('ServerName')

    I got two different server names @@SERVERNAME gave me the old server name where as SERVERPROPERTY gave me the new/current server name.

    I used sp_dropserver 'old server' which said that the server name does not exist. I cross checked this in sys.servers table and didn't found an entry for old server.

    However, the application is working fine and also am able to connect to sql server using the new server name but am still getting 2 different server names through @@SERVERNAME and SERVERPROPERTY even i restarted the several times; also reporting service is not working.

    when am configuring the reporting services thru am able to connect to the reporting services thru new server name but when configuring "Windows service identity" the dialog box is showing old server name and the text box is disabeled.

    DO I have aby alternate way to get things started apart from re installation.

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88260

    You still need to add the new server in using sp_addserver 'new server name', 'local';

    You should also take a look at fixing the Windows groups that SQL Server creates. You will need to drop the SQL Logins, modify the group names in Users & Groups on the server, then re-add the groups and reset the appropriate permissions.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Jeffrey Williams (5/26/2009)


    You still need to add the new server in using sp_addserver 'new server name', 'local';

    You should also take a look at fixing the Windows groups that SQL Server creates. You will need to drop the SQL Logins, modify the group names in Users & Groups on the server, then re-add the groups and reset the appropriate permissions.

    Sry I forgot to mention that the new server name is already taken up by the sql server as it was reflected in the sys.servers table...also am not worried about the logins am worried about reporting services....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88260

    Not sure about reporting services - but, you can try changing the windows identity in SQL Server Configuration Manager.

    When the system was renamed, you probably have the old IUSR accounts setup in IIS. Try changing those to the new systems IUSR accounts.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Jeffrey Williams (5/26/2009)


    Not sure about reporting services - but, you can try changing the windows identity in SQL Server Configuration Manager.

    When the system was renamed, you probably have the old IUSR accounts setup in IIS. Try changing those to the new systems IUSR accounts.

    Well....u can't configure report server services from sql server configuration manager; u should use report server configuration manager....which in my case is not reflecting the new server name....

    Also the IUSR account name has not changed, thus there isn't any need to reconfigure it........

    However, thanks for ur efforts...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Well...I have a solution....I hv to re install reporting services....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • tom.dewit-1088419

    Grasshopper

    Points: 20

    I also have a problem with My SQL server name.

    In this case, the SQL server host name was renamed, without renaming the SQL instance. When retrieving information from remote hosts, I got the old SQL name back in some reports.

    A Select @@ServerName still gave me the old name, so I used the following commands:

    sp_dropserver 'oldname'

    sp_addserver 'newname’

    After restarting the SQL services, a Select @@Servername now gives me NULL as output. Anyone knows why he isn't picking up the new name ? A new sp_addserver tells me that the entry already exists.

    Can this have something to to with srvid ? sp_helpserver gives me id=0 for my server and select * from master..sysservers gives me srvid=1 ?

    Thanks for your help

    Grtz,

    Tom

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    tom.dewit (5/28/2009)


    I also have a problem with My SQL server name.

    In this case, the SQL server host name was renamed, without renaming the SQL instance. When retrieving information from remote hosts, I got the old SQL name back in some reports.

    A Select @@ServerName still gave me the old name, so I used the following commands:

    sp_dropserver 'oldname'

    sp_addserver 'newname’

    After restarting the SQL services, a Select @@Servername now gives me NULL as output. Anyone knows why he isn't picking up the new name ? A new sp_addserver tells me that the entry already exists.

    Can this have something to to with srvid ? sp_helpserver gives me id=0 for my server and select * from master..sysservers gives me srvid=1 ?

    Thanks for your help

    Grtz,

    Tom

    it will not work only by restarting sql services...u should restart the system...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Richard M.

    SSCertifiable

    Points: 7288

    Taken from BOL:

    The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in

    each server.

    And, not sure if the 'local' is needed or not, but just in case,

    sp_addserver 'newname’,'local'

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Richard M (5/28/2009)


    Taken from BOL:

    The local definition takes effect only after the server is shut down and restarted. Only one local server can be defined in

    each server.

    And, not sure if the 'local' is needed or not, but just in case,

    sp_addserver 'newname’,'local'

    By "server" u refer to sql server or the machine that host sql server....

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • Richard M.

    SSCertifiable

    Points: 7288

    ... in this case server refers to the machine that hosts the SQL Server instance....

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Richard M (5/28/2009)


    ... in this case server refers to the machine that hosts the SQL Server instance....

    bingo...u just proved my point...

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • tom.dewit-1088419

    Grasshopper

    Points: 20

    Thanks guys,

    I added local to the sp_addserver command and will restart the server this evening to see if it works.

    Grtz,

    Tom

  • M Murphy

    SSC Enthusiast

    Points: 176

    Don't forget to check over any DTS/SSIS packages you might have. It is very easy to hardcode a server name in those and not even realize it.

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

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