Changing Your SQL Server Name

Is the name of your SQL Server correct?

Is the name of your SQL Server correct?

Why Worry About The Server Name In SQL?

When the name of the actual host machine’s server name does not match the server name stored in SQL Server a lot of things can start to go wrong.

Reporting services can break, Connections denied, maintenance plans fail. Generally chaos can happen, but not always right away.

How does these names get out of sync? Usually is is from some sort of move or rebuild of a server. I experienced it with one client that physically moved data centers, and in the process decided they wanted to change the IP address and name for every server. We are talking thousands of servers moved, and every one of them renamed. That included over 750 SQL Servers.

I came onto the project after the move had been done and it took a while to realize that a lot of maintenance plans were failing. Turns out that the detailed checklist for the data center move, including renaming the servers, had left out the critical step of resetting the server name on each SQL Server. Production and development system were affected and needless to say it made for a fun week to get everything fixed as fast as possible.

Exceptions

There are a few exceptions to remain aware of when you are looking to rename a SQL Server:

[list_check] [li]Fail-over Clusters – When as instance is part of a SQL Server fail-over cluster, the renaming process outlined here will not work.[/li] [li]Replication – renaming computers involved in replication, except for log shipping with replication, as not supported. You need to remove replication for the change to be able to be done. Be sure to script out all your settings first so you can more easily rebuild it after the server name change. If you are using log shipping with replication and you completely lose the primary, you can rename the secondary. You can get more info on this scenario in the MSDN library here: Log Shipping and Replication (SQL Server) [/li] [li]Reporting Services – After a rename, reporting services may not be available. To fix this you need to edit the RSReportServer.config file in a couple of ways. Details can be found in MSDN here: Rename a Report Server Computer.[/li][li]Database Mirroring – Mirroring needs to be turned off prior to changing the server name. Then once renamed you will need to re-establish the mirror using the new server name. Metadata for mirroring foes not update automatically.[/li][li]Windows groups – If using a hard-coded reference to the server name. This occurs if the Windows group specifies the old computer name. You would need to update the Windows group after the name change to specify the new server name.[/li] [/list_check]

Now that that is all out of the way, let’s get to actually changing our server name.

Changing the Server Name

First you are going to want to check if there are any remote logins. If you have any remote logins, that reference the old server name, you will receive an error when you try to change the server name.

This script will check your server for any remote logins so you can remove them.

Be sure to record the remote logins so you can recreate them afterwards.

To drop any remote logins you may be found, you can use the following syntax:

Once the remote logins are out, you can go ahead and change the name of the server with this script:

To add the remote logins back after you have changed the server name you can use the following syntax:

After all the changes have been made you will need to restart the SQL Server service to be able to connect with the new server name.

 

[icon_box icon=”users” title=”Who is Chris?”]

Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com.

Click here to contact Chris or to follow him on twitter.

Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp. New in 2015 WaterOx Consulting is introducing the SQLPunch series of half day training events designed to help others learn to efficiently and effectively use SQL Server.

He is the founding president of PASSDC and organizes the annual SQLSaturday for the DC area. Chris frequently speaks at and attends SQL Server events, sharing his passion for all things SQL Server.

In 2012 Chris was a finalist in the worldwide Exceptional DBA competition and in 2014 he received the Microsoft MVP award in recognition of his open sharing of his knowledge with the technical community. His blog is currently syndicated to SQLServerCentral.com and ToadWorld.com

[/icon_box]

 

Comments 5

  1. Pingback: Changing Your SQL Server Name - SQL Server - SQL Server - Toad World

  2. Somehow, my servername has gotten out of sync. However, not the hostname part, but the instance name.

    A few weeks after install we couldn’t connect to the SQL Server anymore. One of the developers figured out that logging in without the instance name dit work. So we went from “SQLSRV\SQL2017” to “SQLSRV”. If you ask the browser for a list, it still returns “SQLSRV\SQL2017”.

    So I tried the following:

    Then the last SELECT returns “SQLSRV\SQL2017”! Do you have any explanation for how this could not work? I am not sure what to do now 🙁

    1. Post
      Author

      Initial guesses without trying to recreate this in a lab are:

      1) Was the service restarted after the rename?
      2) Is an alias being used in the developer’s sql configuration?
      3) Did someone remap the server instance name via a DNS entry?

      In general, the guidance of this post is if you change your server name, not the installed SQL instance name.
      What you are trying to do gets quite tricky as things such as folder names are created based on the instance name selected during install.

  3. What if rename the hostname from Windows? Will it effect the sql server instance and reporting servcies

    1. Post
      Author

      The host being renamed is what usually leads to the need to change the SQL Server name. I have yet to encounter a case when the SQL Server instance was renamed instead of the host being renamed. The name that SQL references is internal to the instance so even if changed in the Windows OS it will not change things in the SQL instance.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.