none
Avoiding changing Connection Strings after SQL Upgrade/Migration

    Question

  • I'm a DBA with a question. I am upgrading from sql2008R2 to SQL 2014. I'd like to do a side-by-side upgrade. When I am finished, I'd like to change the name of the upgraded server to the old server's name so that all connections from applications still work. Is this possible? I don't need lot's of detail just is it possible.
    Wednesday, February 1, 2017 7:50 PM

All replies

  • Hi yes it is possible, one step after the host rename is to run sp_dropserver and sp_addserver as per https://msdn.microsoft.com/en-us/library/ms143799(v=sql.120).aspx
    Wednesday, February 1, 2017 8:28 PM
  • It's possible.

    After renaming computer on the network, you'll also need to rename it in SQL Server metadata:
    Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

    Make sure none of the applications/user are connecting via IP address, or the connection won't work or won't connect to the correct host.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Wednesday, February 1, 2017 8:31 PM
    • Proposed as answer by KevinNicholas Thursday, February 2, 2017 1:59 AM
    Wednesday, February 1, 2017 8:31 PM
  • Unfortunately this is a confusing topic - as are the answers. So far your responders assume that you intend to rename the server machine and that you have or will install both instances as the default instance on separate machines. But this does not match your description of "side by side". The short answer is that you CANNOT rename a named instance. You can change the machine name of a computer with a default instance - which is similar to the concept of "renaming the default instance". But it really isn't - the default instance has no actual name beyond that of the machine it is running on.
    Wednesday, February 1, 2017 8:44 PM
  • I would not bother changing the server's machine name, I would rather add a CNAME record in your DNS, that will redirect clients from the old server's name to the new server.

    Named instances are the devil here, because you can't redirect the connections with just a CNAME, but you need to keep the same instance name AND port number on the new machine, unless you distribute a SQL client alias with a group policy.

    Wednesday, February 1, 2017 9:12 PM
  • I understood from the question that a separate server was being built with SQL2014, since Greg was asking about renaming a server, not an instance.

    If the actual scenario is that he intends to install a separate named instance on the same server as the existing SQL2008 instance and is asking whether the INSTANCE can be renamed, then you are correct, Scott, that is not possible.

    My apologies for misunderstanding the question or not clarifying important details before being too quick with an answer.


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, February 2, 2017 1:45 AM
  • No need for apologies, the question was ambiguous with no instance mentioned.

    'I'd like to change the name of the upgraded server to the old server's name...Is this possible? I don't need lot's of detail just is it possible'.

    Phil your answer was correct for the question.

    Thursday, February 2, 2017 1:59 AM
  • I apologize for my misunderstanding about referring to a default instance of SQL Server. I thought any installation could be called an instance of SQL Server (i.e., similar to instantiated object). A specific instance would be either a default instance or a named instance. I guess a default instance is an incorrect statement so I offer mea culpa. Your precision is both impressive and daunting.

    I am in fact speaking of 2 independent default installations which are, obviously, on 2 different servers.

    Thanks for all the responses, they are most helpful. I am confident I can work out the details, i.e., dealing with replication, mirorring, etc. I was most interested in the concept of retaining the original server name on the upgraded system since I am trying to avoid the need to change any connection strings. None should be using IP addresses.

    Thanks to all.


    Doubting DBA

    Sunday, February 5, 2017 9:54 PM
  • Hi GregRoszc,

    Do you install default instance in both machines? If so, you can change the new machine name and perform the steps in this article, this way,  you don’t need to change connection strings in applications. If not, you can use SQL alias as other’s post.

    You can review this similar thread to get difference between default instance and named instance.

    Thanks,
    Lydia Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, February 13, 2017 10:05 AM
    Moderator
  • Thank you. Essentially I did what you suggested and all went smoothly.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:14 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:16 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:17 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:17 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:17 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:17 PM
  • Thanks. I guess I need to reply to all answers. Sorry for my delay. Everyone's help aided me in a successful upgrade.

    Greg


    Doubting DBA

    Friday, April 21, 2017 10:18 PM