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.
- If your server is involved replication,Logshipping then, you need to re-configure the replication ,log shipping otherwise it wont work
- If you have mirroring configured for 2005 and later version you need to turn off mirroring and need to re-establish the mirroring
- If the server is cluster then need to follow the different steps
- 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.
- 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
Categories: SQL General
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
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 .
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
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.