Thursday, 28 March 2013

Rename SQL Server HOST Name on Stand-Alone Server

Rename SQL Server HOST Name on Stand-Alone Server

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