locked
Migrate SQL Server 2005 Cluster to 2014 RRS feed

  • Question

  • I'm planning a migration and could use some feedback on the best approach. Here's what we have currently:

    • Windows Server 2008 R2 Enterprise configured in a 2-node fail-over cluster
    • SQL Server 2005 Enterprise clustered

    This is where we need to go using the existing cluster:

    • Windows Server 2008 R2 Enterprise configured in a 2-node cluster (same as above)
    • SQL Server 2014 Standard clustered

    Restrictions/complications that I'm aware of:

    1. Upgrading from Enterprise to a Standard edition instance is not permitted and I wouldn't allow an in-place upgrade in production anyway.
    2. SQL Server 2005 is a default instance and I also want 2014 to be the default instance.
    3. I want to maintain the same virtual cluster name and IP address.
    4. We don't have any servers available to add additional nodes to the cluster.
    5. Shared SAN storage is limited so I'm hoping to reuse the same shared volumes.

    Given these restrictions, I'm thinking about the following general approach:

    1. Stop SQL Server Agent and restart the instance in single-user mode.
    2. Create multiple redundant backups of all databases.
    3. Detach all user databases.
    4. Shut down and uninstall SQL Server 2005 Enterprise on both nodes.
    5. Install SQL Server 2014 Standard cluster
    6. Attach/upgrade SQL Server user databases from 2005 to 2014.

    Any comments on this approach or additional things to consider?


    • Edited by M.Glenn Tuesday, May 31, 2016 3:23 AM
    Tuesday, May 31, 2016 3:23 AM

Answers

  • This approach looks risky to me but again subject to how much downtime/risk you company could afford.

    If this will be your final approach, then My suggestion to try backup to restore in any Non Prod SQL 2014 and check if its accessible from your application without any issue and perhaps do some regression. If this works then shouldn't be any problem.


    Pankaj Negi

    Tuesday, May 31, 2016 3:28 AM

    1. Shashank ["check about CNO in the domain whether it has been removed or not"]: Can you please expound or clarify?

    Thanks.



    CNO is cluster name object. Its identification of cluster in domain. For time being ignore the advise. There is other step to rename a cluster in which you dont have to worry about CNO and other things

    1. Let us suppose your SQL Server 2005 cluster virtual name is Virtualname1 with IP 1.1.11

    2. Before shutting it down rename the SQL Server 2005 cluster to VirtualName2 and Change IP to 1.1.1.2

    3 Create new SQL Server 2014 cluster with name VirtualName1 and IP 1.1.1.1, this is possible because you already renamed 2005 to new name VirtualName2

    4. Restore all backups, create jobs and logins and test your application.

    Please follow MS article to rename cluster. Also note its not supported if database in cluster is replicated


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, June 1, 2016 6:55 AM

All replies

  • This approach looks risky to me but again subject to how much downtime/risk you company could afford.

    If this will be your final approach, then My suggestion to try backup to restore in any Non Prod SQL 2014 and check if its accessible from your application without any issue and perhaps do some regression. If this works then shouldn't be any problem.


    Pankaj Negi

    Tuesday, May 31, 2016 3:28 AM
  • Your approach is bit risky as you are removing SQl Server 2005 before creating 2014, so technocally you are removing your *easy* rollback plan. You don't need to start SQL Server instance in Single user mode for backup and other things.

    If you are using virtual machine stop application connecting to the database and take snapshot backup of the whole machine, this will let you have complete backup of whole windows machine

    You also need to backup your users and system databases, you need to then script out logins with there permissions, you then also need to script out jobs and linked servers if you have.

    Make sure you keep all this backup on some safe location

    After you are sure you have complete backup of everything then go ahead and destroy SQL server 2005 cluster. After this is done ask team to check about CNO in the domain whether it has been removed or not. After things are fine create SQL Server 2014 cluster and restore the databases followed by login and jobs creation.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, May 31, 2016 6:34 AM
  • Pankaj & Shashank: Thanks for taking time to contribute your insight.

    • One thing in our favor is we can tolerate 4 to 6 hours of downtime during off-peak hours. I'll ask for 8.
    • This provides the extra time needed to possibly back out the SQL Server 2014 install and reinstall 2005 cluster if something goes wrong.
    • To clarify, the cluster nodes are physical not virtual. I agree virtual would have provided better recovery options.
    • I'm going to single user mode to prevent updating of any databases before the final backups.
    • Agree that test restores are a good idea. We have a test SQL Server already running 2014 that can be used for this purpose.
    • Yes, of course the system databases, must be backed up last. That should have been stated explicitly in my list.
    • Script out logins with permissions, jobs and linked servers if you have. Yes, good catch.

    Questions:

    1. Pankaj ["do some regression"]: Can you please give me an example or two of suggested regression tests?
    2. Shashank ["check about CNO in the domain whether it has been removed or not"]: Can you please expound or clarify?

    Thanks.



    • Edited by M.Glenn Tuesday, May 31, 2016 12:53 PM
    Tuesday, May 31, 2016 12:51 PM
  • Example or two of suggested regression tests?

    1. SQL 2005 backup after restore on SQL 2014 should be accessible. Also you should be able to do basis SQL query on databases after restore. Don't forget to restore the permission. This way your application users/Service account db permission will remain intact.

    2. you must have some front end application that accesses your database. Repoint you application to new SQL2014 DB instance and request your application support or testers to do some application operation where database operation is involved. This way you will have more confidence on your approach. Hope sounds logical and helpful to mitigate risk.


    Pankaj Negi

    Wednesday, June 1, 2016 6:37 AM

    1. Shashank ["check about CNO in the domain whether it has been removed or not"]: Can you please expound or clarify?

    Thanks.



    CNO is cluster name object. Its identification of cluster in domain. For time being ignore the advise. There is other step to rename a cluster in which you dont have to worry about CNO and other things

    1. Let us suppose your SQL Server 2005 cluster virtual name is Virtualname1 with IP 1.1.11

    2. Before shutting it down rename the SQL Server 2005 cluster to VirtualName2 and Change IP to 1.1.1.2

    3 Create new SQL Server 2014 cluster with name VirtualName1 and IP 1.1.1.1, this is possible because you already renamed 2005 to new name VirtualName2

    4. Restore all backups, create jobs and logins and test your application.

    Please follow MS article to rename cluster. Also note its not supported if database in cluster is replicated


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, June 1, 2016 6:55 AM