Required Housekeeping on SQL Server after renaming the hostname

SQL Server 2008 – Server Name Change (compiled and written by Rinith)

It might cause serious impact for your business applications running on backend SQL server, if the SQL server hosted server has been renamed without doing a proper housekeeping on the SQL Server itself.

So, in other words, if windows admin changes the server name, the following changes needs to be carried out on a sql server 2005-2008 (tested ok):

 

Changed server name shows 'hrms-app2' so we need to make this effective on the SQL server as well.

1. Open SQL Server Management Studio and click New Query.

2. Type Select @@ServerName to verify that the server name is correct or incorrect. In this example, I changed the Windows 2003 from HRMS-APP1 to HRMS-APP2.

follow the steps:

3. Next, type sp_dropserver 'HRMS-APP1'

syntax:

sp_dropserver <old_name>  (old name is the name you get from the select @@servername)

GO

4. You are now ready to add the correct name by typing sp_addserver 'HRMS-APP2', local

syntax:

sp_addserver <new_name>, local

GO

if you just mention sp_addserver 'newservername' wont show the right result, though the command will executed properly.

Restart sql server and the sql server agent by opening a command prompt and type: net stop mssqlserver and then net start msssqlserver to stop and start the sql server agent, type net stop or start sqlserveragent.

you might see a message with status 'disconnected' when executing sql queries after the above net start commands.

just close the sql management studio and start again, should work

note: A reboot might be required based on various environmental factors like:

even after reconnecting sql server name not displayed properly.. or selecting the statement showed servername as null.

this might have caused coz we executed the command sp_addserver 'HRMS-APP2' instead of sp_addserver 'HRMS-APP2', local

Restart the SQL server services with the above commands (net start...) and then see if the new servername is displayed or not.

check and confirm the server name (Select @@ServerName ) after restarting the server.

tech ref