Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

<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.

Verify renaming operation

• Select information from either @@SERVERNAME or sys.servers. The @@SERVERNAME function will return the new name, and the sys.servers table will show the new name. The following example shows the use of @@SERVERNAME.

SELECT @@SERVERNAME AS ‘Server Name‘; 

 

Additional considerations

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

erver: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44 There are still remote logins for the server ‘SERVER1‘.

To resolve the error, you must drop remote logins for this server.

Drop remote logins

• For a default instance, run the following procedure:

sp_dropremotelogin old_name;  
GO

For a named instance, run the following procedure:

sp_dropremotelogin old_name\instancename;  
GO

Linked Server Configurations - Linked server configurations will be affected by the computer renaming operation. Use sp_addlinkedserver or sp_setnetname to update computer name references. For more information, see the sp_addlinkedserver (Transact-SQL) or sp_setnetname (Transact-SQL).

Client Alias Names - Client aliases that use named pipes will be affected by the computer renaming operation. For example, if an alias "PROD_SRVR" was created to point to SRVR1 and uses the named pipes protocol, the pipe name will look like \\SRVR1\pipe\sql\query. After the computer is renamed, the path of the named pipe will no longer be valid and. For more informati

=======

SELECT  HOST_NAME() AS ‘host_name()‘,
@@servername AS ‘ServerName\InstanceName‘,
SERVERPROPERTY(‘servername‘) AS ‘ServerName‘,
SERVERPROPERTY(‘machinename‘) AS ‘Windows_Name‘,
SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS‘) AS ‘NetBIOS_Name‘,
SERVERPROPERTY(‘instanceName‘) AS ‘InstanceName‘,
SERVERPROPERTY(‘IsClustered‘) AS ‘IsClustered‘
If you find any mismatch, then you need to follow below steps:
 1. Execute below to drop the current server name
  　　EXEC sp_DROPSERVER ‘oldservername‘
 2. Execute below to add a new server name. Make sure local is specified.
  　　EXEC sp_ADDSERVER ‘newservername‘, ‘local‘
 3. Restart SQL Services.
 4. Verify the new name using:
　　  SELECT @@SERVERNAME
  　　SELECT * FROM sys.servers WHERE server_id = 0
I must point out that you should not perform rename if you are using:
 1. SQL Server is clustered.
 2. Using replication.
 3. Reporting Service is installed.

Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

标签：who   cluster   function   manual   ilo   turn   ted   resolve   updating