Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
Tech Note 742
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
All Tech Notes, Tech Alerts and KBCD documents and software are provided "as is" without warranty of any kind. See the Terms of Use for more information. Topic#: 002517 Created: November 2010
Introduction
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.
Application Versions
Windows Server 2008 R2 Microsoft SQL Server 2008 SP1
Updating System Metadata
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.
Note: 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 VMK6432 that hosts an instance of SQL Server named Instance1 to another name, such as VMK6432New. However, the SQL Server instance part of the name (Instance1), will remain unchanged. In this example, the \\ComputerName\InstanceName will be changed from \\VMK6432\Instance1 to \\VMK6432New\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. For more information, see Replication and Log Shipping in SQL Books online. 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. Use the following steps to update system metadata. 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. [11/16/2010 4:56:01 PM]
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
Assumptions
This Tech Note assumes the following: SQL Server is installed locally as the default instance. The SQL Server database Engine has accepted the new name. SQL Server 2008 x86 is locally installed. Windows 2008 R2 is locally installed.
Renaming a SQL Server Database Engine
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 complete either of the following procedures. The procedure you use depends on whether you are updating a computer that hosts a default or named instance of SQL Server. Figure 1 (below) shows that the Host name is correct, but running SELECT @@SERVERNAME AS 'Server Name' returns the old server name. The New Server Name should return VMK6432New.
FiGure 1: MicrOsOft SQL Server ManaGement StuDiO QuerY To rename a computer that hosts a stand-alone instance of SQL Server Run the following Stored Procedures: [11/16/2010 4:56:01 PM]
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
sp_dropserver GO sp_addserver , local GO
To rename a computer that hosts a named instance of SQL Server, Run the following Stored Procedures:
sp_dropserver GO sp_addserver , local GO
Example
To rename a computer that hosts a default instance of SQL Server Run the following Stored Procedures (Figure 2 below).
sp_dropserver VMK6432New GO sp_addserver VMK6432New , local GO
FiGure 2: MicrOsOft SQL Server ManaGement StuDiO QuerY Restart the SQL Server (MSSQLSERVER) service (Figure 3 below).
[11/16/2010 4:56:01 PM]
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
FiGure 3: Server ManaGer Services As a result of the Host running SELECT @@SERVERNAME AS 'Server Name' statement the current server name is returned (Figure 4 below).
FiGure 4: MicrOsOft SQL Server ManaGement StuDiO QuerY
K. Nourbakhsh
[11/16/2010 4:56:01 PM]
Renaming a Computer that Hosts a Stand-Alone SQL Server Instance
Tech Notes are published occasionally by Wonderware Technical Support. Publisher: Invensys Systems, Inc., 26561 Rancho Parkway South, Lake Forest, CA 92630. There is also technical information on our software products at Wonderware Technical Support.
For technical support questions, send an e-mail to support@. Back to top
?2010 Invensys Systems, Inc. All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by anyinformation storage and retrieval system, without permission in writing from Invensys Systems, Inc. Terms of Use.
[11/16/2010 4:56:01 PM]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- 075 2009 renaming in batches sas
- relational algebra ii california institute of technology
- relational algebra and sql cornell university
- directquery in sql server 2016 analysis services
- guide to using sql synonyms and the rename statement oracle
- pro power bi desktop semantic scholar
- nested queries and aggregation cheriton school of computer science
- automatically renaming common variables before merging
- renaming a computer that hosts a stand alone sql server instance
- cs 450 relational algebra 1 george mason university
Related searches
- stand alone crm software
- a word that means a great amount
- azure sql managed instance hyperscale
- sql managed instance hyperscale
- azure sql managed instance vs sql db
- sql managed instance limitations
- sql managed instance authentication
- sql managed instance high availability
- spectrum stand alone internet price
- excel stand alone 2016
- sql server sql syntax
- stand alone internet plans