1

My apologies as this is the last SQL 2005 instance we have and it's pretty urgent or I wouldn't go through all this trouble asking.

Questions

  1. What is the risk to leave things as they currently are, what are the ramifications, and what have we not accounted for?
  2. What is the risk if we were to run the steps for sp_dropserver and sp_addserver? Could it impact applications currently successfully connecting or is this something very specific inside of the engine that shouldn't impact currently successful connections?

Points

  1. This is for SQL Server 2005.
  2. We could be 30 days or 365 days before we are able to migrate this off of SQL Server 2005.
  3. Due to recent issues, there's a bit of concern about SQL coming back up correctly if the service was stopped and restarted or the server cycled.
  4. We are still stepping through things, but we are currently functional except for the following issues we've found:
    • Things that use a linked server aren't working.
    • Scripts that use @@ServerName present the wrong name.
  5. When running SELECT @@ServerName it obviously returns SQLSERVER-05X\INSTC instead of SQLSERVER-05C\INSTC
    • SELECT @@SERVERNAME
      • SQLSERVER-05X\INSTC
    • SELECT serverproperty('ServerName')
      • SQLSERVER-05C\INSTC
  6. We have 3 Logins that are based on the wrong servername (one of our guys found that this could mess up updates and patches possibly)
    • SQLSERVER-05X\SQLServer2005MSSQLUser$VS-SQL-05X$INSTC
    • SQLSERVER-05X\SQLServer2005SQLAgentUser$VS-SQL-05X$INSTC
    • SQLSERVER-05X\SQLServer2005MSFTEUser$VS-SQL05X$INSTC
  7. When trying to add an EndPoint for a Login, SQLSERVER-05X\INSTC is listed as a server currently. You can search and add the correct server (SQLSERVER-05C\INSTC) , but the following error is thrown:

    Attempt to retrieve data for object failed for Server 'SQLSERVER-05C\INSTC'.  (Microsoft.SqlServer.Smo)
    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
    
    ADDITIONAL INFORMATION:
    Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name. (Microsoft.SqlServer.Smo)
    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&LinkId=20476
    

Steps Performed So Far

  1. SQLSERVER-05C\INSTC had issues and SQL crashed.
  2. SQLSERVER-05X was stood up as a replacement.
  3. SQLSERVER-05X had SQL installed to named instance INSTC creating SQLSERVER-05X\INSTC.
  4. SQLSERVER-05C was taken off the network and SQLSERVER-05X was renamed to SQLSERVER-05C which created SQLSERVER-05C\INSTC.
  5. The Windows team install SQL and they made some of the changes, but missed executing sp_dropserver and sp_addserver.

Info Found during Research

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

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.

From https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15&viewFallbackFrom=sql-server-previousversions

A Server by Any Other Name

By DatabaseJournal.com Staff by MAK [Muthusamy Anantha Kumar] It is imperative to change the server name when infrastructure policy changes, naming convention standard changes or making a standby server the production server (when production server fails). When a server name is changed, SQL server should go through a process to acknowledge and assimilate the change in server name. The process is a little different in different versions. I will walk you through the process for SQL Server 6.5, 7.0 and 2000.

From https://www.databasejournal.com/features/mssql/article.php/2194711/A-Server-by-Any-Other-Name.htm

sp_dropserver and sp_addserver not working

This is from books online: Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer. In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

From https://stackoverflow.com/questions/5956807/sp-dropserver-and-sp-addserver-not-working

sp_setnetname (Transact-SQL)

Sets the network names in sys.servers to their actual network computer names for remote instances of SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing SQL Server identifiers that are not valid.

From https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-setnetname-transact-sql?view=sql-server-ver15

Pointing a SQL Server Linked Server to Different Data Sources

Problem
Most of us use SQL Server Linked Servers in our environment to fetch data from remote databases. The linked server names may be different in our QA and production environment, but we want to use the same code without having to make changes to the linked server name on the different servers.  In the below example we will cover how to create a linked server with a constant name that points to different servers depending on what servers we are working with.

From https://www.mssqltips.com/sqlservertip/4578/pointing-a-sql-server-linked-server-to-different-data-sources/

Changing Properties of a Linked Server in SQL Server

Is there any way to change the properties (Product name, data source, provider string, etc...) of an existing linked server? When I go to the properties screen, all the options are grayed out.

From https://stackoverflow.com/questions/11866079/changing-properties-of-a-linked-server-in-sql-server/35271967

Is it safe or supported to remove the local security groups that the SQL Server setup creates (SQLServer2005MSSQLUser$$MSSQLSERVER)?

When the SQL Server setup is ran, it creates some local groups of the following format: SQLServer2005MSSQLUser$$MSSQLSERVER. These groups are part of the product and should not be deleted:

Deleting them renders the product unsupported and part of the functionality is lost. Plus, it poses a security risk. Specifically, after deleting these groups, it is no longer possible to install updates and Service Packs to the SQL Server product:

  • SQL Server 2005 Service Pack 2 is not successfully updated when you try to install or to uninstall a security update (https://support.microsoft.com/kb/957008)
  • Error message when you try to install a SQL Server 2005 service pack or a SQL Server 2005 hotfix package: "Error 29528. The setup has encountered an unexpected error while Setting Internal Properties" (https://support.microsoft.com/kb/925976) So, to summarize, it is neither safe nor supported to delete these groups.

From https://docs.microsoft.com/en-us/archive/blogs/john_daskalakis/is-it-safe-or-supported-to-remove-the-local-security-groups-that-the-sql-server-setup-creates-sqlserver2005mssqlusercomputer-namemssqlserver

SQl Server 2008 r2 change machine name and update local groups (added 2020-01-09 09:39:16)

We want to change the machine name of our server . We can change the name and do the required items but we have noticed that the Group Member names that are set up by SQL 2008 on install add the server name, ServerName, so when you do a name change the groups do not change to refelct the new server name.

From https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4e24d283-d5d6-465a-b420-07412a7ea126/sql-server-2008-r2-change-machine-name-and-update-local-groups?forum=sqlsecurity

Windows Service Account Issue When Restoring Master to Another Server (Added 2020-01-09 09:42:53)

I noticed when I restore a master database to a server other then the one which created the backup of master, SQL Server contains the following three local security groups that were defined on the source server. The problem is these groups are "local" and do not apply to the server where master was restored.

From https://www.sqlservercentral.com/forums/topic/windows-service-account-issue-when-restoring-master-to-another-server

Follow up to: SQL Server 2008 R2: Problems after computer name change (Added 2020-01-09 09:47:45)

The Original question can be found here:
SQL Server 2008 R2: Problems after computer name change (SQL Server 2008 R2: Problems after computer name change) I need to know if there is a way to modify the script found in the original question to allow for all instances on a computer. I have a situation where I have used an image to duplicate computers and the instances are keeping the original name of the computer.

From Follow up to: SQL Server 2008 R2: Problems after computer name change

SQL Server 2008 R2: Problems after computer name change (Added 2020-01-09 09:57:58)

I'm having a confusing problem after changing the computer name of a remote server hosting a local SQL Server instance.

From SQL Server 2008 R2: Problems after computer name change

Credit to @VolkerBachmann for mentioning the awesome dbatools (Repair-DbaInstanceName) and mssqltips article it's based on

#sqlhelp we used the https://t.co/OYwtc8MsSP command to rename an sqlserver without problems but that was not a highly used instance with AG, Replication or so.
In the documentation they refer to this article: https://t.co/zT5yVi5bP3

— Volker Bachmann (@VolkerBachmann) January 9, 2020

Steps to change the server name for a SQL Server machine

Problem
In this tip we look at the steps within SQL Server you need to follow if you change the physical server name for a standalone SQL Server.
Solution
As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server.

From https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

Repair-DbaInstanceName

Synopsis
Renames @@SERVERNAME to match with the Windows name.
Description
When a SQL Server's host OS is renamed, the SQL Server should be as well. This helps with Availability Groups and Kerberos.
This command renames @@SERVERNAME to match with the Windows name. The new name is automatically determined. It does not matter if you use an alias to connect to the SQL instance.
If the automatically determined new name matches the old name, the command will not run. https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/

From https://docs.dbatools.io/#Repair-DbaInstanceName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.