Best migration approach

  • Hi Colleagues,

    I am considering the across-the-wire migration method to SQL Server 2017.

    However, my source is an SQL Server 2014 Availability group with three nodes, one asynchronous.

    Is launching a new VM on the remote site and the same cluster to migrate this first node a good idea?

    Later, demote the old node, then use the same method for the next secondary.

    Thank you for your confirming ideas,

    Best Regards,

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This will work and can be a good way to minimise downtime at cutover.

    I would ask why you are going for SQL2017 rather than SQL2019 or SQL2022. In your position I would aim for SQL2022 due to having the longest supported lifetime, plus it is a better product than SQL2017.

    The main issue the approach you are suggesting is that while you can replicate a SQL2017 secondary from a SQL2014 primary, you cannot replicate the other way round.

    I would suggest you start with a test system to try out your migration. It can be hard to ensure all components, permissions, etc installed on an old server have been documented and transferred to the new server. Build a checklist of what you expect to see after the cutover. Then do a test cutover and see what happens.

    You will find a delay while databases upgrade to SQL2017 standard but otherwise failover will be quite quick. Review your checklist and other observations and fine tune the cutover process. Repeat the test if there were major issues. Make sure your plan to get to full resilience with multiple new nodes works. Also have a post-upgrade checklist, there will be various options you may want to enable or tune in your new environment.

    Then you are ready for live and should get very few surprises when it is done.

    • This reply was modified 12 months ago by  EdVassie.
    • This reply was modified 12 months ago by  EdVassie.

    Original author: 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • In addition to EdVassie's reply, I would urge you to expel at least one of the SQL2014 nodes from the Availability Group *before* failing over to any of the SQL2017 nodes. Once the replica databases have been upgraded to SQL2017, they cannot fail back to SQL2014. Expelling a node prior to the upgrade gives you a much less painful recovery path (on the off chance that you need to revert your changes). Once you determine that the upgrade is a success and you will not be reverting, that expelled node can be repurposed or decommissioned.

Viewing 4 posts - 1 through 3 (of 3 total)

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