Home > SQL General > Rename SQL Server HOST Name on Stand-Alone Server

Rename SQL Server HOST Name on Stand-Alone Server

November 13, 2010

When we change the SQL Server host name, we don’t need to reinstall the SQL Server to take the new name to affect.

once you change the SQL Server HOST name on Stand-Alone machine Replication,Mirroring,Wont work so,before rename the SQL Server Host Name need to considered the following.

  1.   If your server is involved replication,Logshipping then, you need to re-configure the replication ,log shipping otherwise it wont work
  2.  If you have mirroring configured for 2005 and later version you need to turn off mirroring and need to re-establish the mirroring
  3.  If the server is cluster then need to follow the different steps
  4.  If you have reporting service configured on the server after the rename the service wont be available for more information please Click Microsoft Guide

Steps to be followed.

  1. SELECT  @@SERVERNAME  – Give you the Existing server Name present in the metadata. it will be the old server name.

Remove the Old Server Name from the Metadata.

2.  Sp_dropserver ‘Old_Server_Name’

Add the renamed server name

3. Sp_Addserver ‘New_server_Name’ , ‘Local’

if you don’t Specify the ‘Local’ then You will Get @@servername as NULL.

4. Restart the SQL Services.

5. SELECT @@SERVERNAME    – Will give you the modified Server Name in the Step No.3

 Note : This Solution is Only for the Stand-Alone Machine

About these ads
Categories: SQL General
  1. ARUNRAJA
    November 16, 2010 at 1:23 am

    Even in above SQL server 2005 renaming we need to include

    USE master
    EXEC sp_configure ‘show advanced option’, ’1′
    sp_configure ‘allow updates’, ’1′
    go
    RECONFIGURE with override

    • November 16, 2010 at 2:19 pm

      Hi Arun,

      This is only require when you are going to update the System tables directly. MS is restricting to update the System tables directly on their later version.

      So we can use the System Producers to update the Table .

  2. ARUNRAJA
    November 16, 2010 at 1:21 am

    For SQL 2000 we need to follow the below steps

    1.USE master
    EXEC sp_configure ‘show advanced option’, ‘1’
    sp_configure ‘allow updates’, ‘1’
    go
    RECONFIGURE with override

    2. select * from sysservers
    delete from sysservers where srvid0
    update sysservers set srvid=0

    3.update sysservers set srvname= ‘New_server_Name’ ,datasource=’New_server_Name’

    4.sp_configure ‘allow updates’, ‘0’
    go
    RECONFIGURE with override
    go
    select @@servername

    5.sp_addserver ‘local’, ‘New_server_Name’

    6. Recycle SQL services

    • November 16, 2010 at 2:57 pm

      Hi Arun,

      till Step no 4 everything looks fine. after updating the server name , we dont need to add the server agian it will create a new entry so the step no 5 is not needed.

      the method which i gave will work for all the version. let me attach the screen shot for SQL 2000,2005.

      the Method i gave is fully tested on SQL Server 2008.

      Thank for your comments.

  1. No trackbacks yet.
Comments are closed.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: