3

I have been searching for directions and steps to rename a named server instance of SQL Server 2012. I have a rare opportunity to rename both the computer name and instance of the SQL Server. I stepped into a situation where all of the naming conventions need to be revamped. I'm assuming because I have no linked jobs or connections that I should be worried about, this will be okay. I have read that this isn't possible and others who say to use the sp_dropserver, sp_addserver but that doesn't work. Syntax would be helpful. My names are as such:

{machinename}\{instancename}
{COLO-VSR=SQLDW}\{SQLBISERVER}

I want to change it to {COLO-VSR-SQLDW}{SQLDW}

Rob
  • 45,296
  • 24
  • 122
  • 150
Joe Resler
  • 101
  • 1
  • 2
  • 13

1 Answers1

0

The "safest" way to do this is to (this does assume that downtime is possible for you):

  1. Take a backup of each database (or, if you're going to install exactly the same version of SQL Server, including Service Pack level, detaching the databases is probably safe
  2. Take the old instance offline
  3. Rename the machine, including rebooting as required
  4. Install the new instance of SQL Server
  5. Restore the databases to the new instance of SQL Server
  6. Validate 7. Uninstall the old instance of SQL Server

SQL Server has never seemed to handle renaming of the server it's installed on particularly well, hence the suggestion of creating a new SQL Server instance after renaming the machine. This may be better with newer, e.g. SQL Server 2016, versions but I suspect that "rename machine" is a scenario that doesn't get a lot of engineering priority or resource.

Rob
  • 45,296
  • 24
  • 122
  • 150