Sql-server – renaming a sql server with installed named instances

sql serversql-server-2008-r2

We have a hosted server that is named, by the hosting company, as something like WinEnt99. We would like to rename it to something more meaning like OurMonitor1.

We have already installed a few instances of SQL Server 2008 R2.

Is there anything that we need to (re)configure in SQL Server / SSMS ?

I have an application that was reading the database settings from the registry, and connecting the database fine. Now, it won't connect to the database. The only thing that I can think of is that we renamed the server last week sometime.

EDIT 02 Oct 2012:-
The Server is being renamed. The named instances are not.

I've run

sp_helpserver
select @@servername

Which showed the old server name,
and

sp_dropserver 'Old Server Name\Instance_name'
go
sp_addserver 'New Server Name\Instance Name','local'
go

Then confirmed the details in

sp_helpserver
select @@servername

Best Answer

You are only allowed to change the server name or hostname of a server running SQL server. Microsoft does not support renaming the instance without a reinstall. So if you have a default instance on the server that is referenced by the hostname you can rename the "instance" in that manner. However if you are wanting to go from "hostname\instance1" to "hostname\instance2" you will have to reinstall SQL Server.

Related Solutions

SQL Server 2008 R2 – Issues After Computer Name Change

When you added the new server name using sp_addserver, did you remember to include the "local" designation. It is that tag that updates the metadata for @@SERVERNAME. More information.

sp_addserver 'servername', local

Sql-server – Renaming SQL Server Agent operator

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.

Best Answer

Related Solutions

SQL Server 2008 R2 – Issues After Computer Name Change

Sql-server – Renaming SQL Server Agent operator

Related Question