Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Will changing the computer name on which SQL Server 2008 is installed on affect SQL Server in anyway? Or will changing the computer name be transparent to SQL Server?

For example:

I plan to install Windows 2008 Server and naming it "BobStage". Next I will install SQL Server 2008 R2 and restore a few DB's. Finally I plan to rename the windows machine to "BobLive".

Will me renaming the computer's name affect SQL Server's instance ID/name and possibly break something?

share|improve this question
    
Why don't you simply name it "BobLive" and not connect it to the network until you are ready? –  Ramhound Jul 9 '12 at 17:36
    
The only thing it will break is static connection strings. If you've installed to the default instance, SQL will notice the new machine name on startup, as @ahmed has said in the first paragraph of his answer. –  user3463 Jul 9 '12 at 18:04
    
Hi there, Ramhound yes ideally that would be the way go. Unfortunately in this company I am working for its not possible due to several reasons (competency of staff, time, DBA + me based overseas and require remote access etc...). –  user986363 Jul 9 '12 at 18:54
add comment

1 Answer

up vote 1 down vote accepted

When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use the following steps to update system metadata that is stored in sys.servers and reported by the system function @@SERVERNAME. Update system metadata to reflect computer name changes for remote connections and applications that use @@SERVERNAME, or that query the server name from sys.servers.

The following steps cannot be used to rename an instance of SQL Server. They can be used only to rename the part of the instance name that corresponds to the computer name. For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

Before you begin the renaming process, review the following information:

  • When an instance of SQL Server is part of a SQL Server failover cluster, the computer renaming process differs from a computer that hosts a stand-alone instance.

  • SQL Server does not support renaming computers that are involved in replication, except when you use log shipping with replication. The secondary computer in log shipping can be renamed if the primary computer is permanently lost.

  • When you rename a computer that is configured to use Reporting Services, Reporting Services might not be available after the computer name change.

  • When you rename a computer that is configured to use database mirroring, you must turn off database mirroring before the renaming operation. Then, re-establish database mirroring with the new computer name. Metadata for database mirroring will not be updated automatically to reflect the new computer name.

  • Users who connect to SQL Server through a Windows group that uses a hard-coded reference to the computer name might not be able to connect to SQL Server. This can occur after the rename if the Windows group specifies the old computer name. To ensure that such Windows groups have SQL Server connectivity following the renaming operation, update the Windows group to specify the new computer name.

  • You can connect to SQL Server by using the new computer name after you have restarted SQL Server. To ensure that @@SERVERNAME returns the updated name of the local server instance, you should manually run the following procedure that applies to your scenario. The procedure you use depends on whether you are updating a computer that hosts a default or named instance of SQL Server.

For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
Restart the instance of SQL Server.

For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
Restart the instance of SQL Server.

After a computer has been renamed, any connections that used the old computer name must connect by using the new name.

To verify that the renaming operation has completed successfully

SELECT @@SERVERNAME AS 'Server Name'

Note: One issue you might have is with Database Maintenance sometimes when renaming a Server and/or moving it out of a domain the SA Account can be affected, therefore you may need to delete and then recreate the Database Maintenance bear this in mind for any other SQL Server Agent Jobs that are owned by SA as well and make sure the new name resolves in DNS.

share|improve this answer
    
Ahmed, thank you for the detailed answer. –  user986363 Jul 9 '12 at 18:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.