How to Change the SQL Server Instance Name after Renaming the Windows Host

  • The title of article is misleading. I though someone figured out a way to rename the actual instance, which right now it is not possible (in a nice supported way.... If you hammer out long enough I bet you could do it).

    This article is about how to rename the SQL server name, not the instance. Good one though.

    I got one question now: if you rename the virtual instance name on a cluster, do you still need to drop server as described here?

    I got to do that today, so I'll find out later and post here

  • That's fine for the SQL Engine but what do you do about all the SQL Agent Task jobs and Maintenance Plans that are still pointing to the old name by default?

  • what about in a replication environment when the subscriber has changed? is there a way to keep replication working when computer name changes?

  • Good article, thanks. Have done this a number of times over the years. One additional step we always take is to reboot the server afterwards. Not sure if it's still needed with the current versions of OS & SQL, but this used to be done to set the registry straight.

  • The metadata that describes the location of the databases and is used by jobs and maintenance plans is still intact. That is to say that your jobs and maintenance plans will continue running as they did.

  • Hi all,

    I have followed this procedure a while back(as in months) for one of our development SQL2008 servers.

    Ever since i renamed the server I've been getting 4 DCOM events every night around 2AM, about 30-60 seconds apart

    OS: Windows Server 2008R2 Enterprise SP1

    SQL: Microsoft SQL Server 2008 Standard Edition (10.0.5512.0)

    _____________

    Event Source: DistributedCOM

    Event ID: 10009

    "DCOM was unable to communicate with the computer [LOCAL-COMPUTERNAME] using any of the configured protocols."

    _____________

    I can't for the life of me figure out where these are coming from, but it doesnt seem to mess anything up for me.

    Has anyone else here had any experience with these weird DCOM events? Or any thoughts about where they are coming from?

    And of course, how to solve this issue.. if it is in fact an issue..

    Thanks in advance!

    Regards,

    Jay

  • The data connections still point to the old server name and I haven't found an easy way to overcome this for maintenance plans.

  • MiguelSQL (5/16/2013)


    The title of article is misleading. I though someone figured out a way to rename the actual instance, which right now it is not possible (in a nice supported way.... If you hammer out long enough I bet you could do it).

    This article is about how to rename the SQL server name, not the instance. Good one though.

    I got one question now: if you rename the virtual instance name on a cluster, do you still need to drop server as described here?

    I got to do that today, so I'll find out later and post here

    The SQL Server is the instance. The server moniker is misleading. All installations of SQL Server are instances. You need to match this to the host name to connect to the instances.

  • jpowell-918558 (5/16/2013)


    what about in a replication environment when the subscriber has changed? is there a way to keep replication working when computer name changes?

    AFAIK you need to reset replication. The connections to replicated servers are done using the naming, and this isn't automatically changed when changing the server name.

  • I have a script that will rename the server using the difference between the select @@Servername and the select SERVERPROPERTY('ServerName')

    http://www.brinnenterprises.com/rename-server.aspx

    Please let me know if this is useful, I ran this against 2 servers today and it seems to have renamed the servers without error.

  • You are misleading people. You are not changing the instance name. You are only changing the server name as known to SQL Server. Please be very careful about the words you choose.

  • Don't forget to look in security/logins and change Any accounts with the old server name such as OLDSERVER\Administrator

  • A note of caution:

    If you are still running SQL 2000 (hopefully not many places have this luck) after you remame the SQL server instance you also will need to run this script. Otherwise you will not be able to edit your SQL jobs.

    ========================================================================

    Use msdb

    GO

    UPDATE sysjobs

    set originating_server = 'ENTER HERE THE OLD SQL SERVER INSTANCE NAME'

    where originating_server = 'ENTER HERE THE NEW SQL SERVER INSTANCE NAME'

  • The SQL Server is NOT the instance. The SQL Server is the host upon 1-30 instances can reside. That should be made very clear.

    Having done this several times, I can say without a shadow of a doubt that all that needs to be done ni the renaming process is:

    Rename the physical/virtual server

    Drop and recreate the server in SQL Server (sp_dropserver, sp_addserver)

    Check the server name in sys.servers

    If necessary, set DATA_ACCESS to ON

    This thread is getting unnecessarily complicated.....

  • BlackHawk-17 (5/16/2013)


    The data connections still point to the old server name and I haven't found an easy way to overcome this for maintenance plans.

    Check the server name in sys.servers. Maintenance plans and jobs WILL run normally if this has been set correctly.

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply