upgrade plan common sense check please

  • snomadj

    SSCarpal Tunnel

    Points: 4277

    Be grateful for a yay or nay on my plan:

    4 nodes. 2012.  1 x AG.  FCI.  1 local sync. 2 remote async.

    • Upgrade to 2016 on all, failing over as required, doing remotes, then local secondary, then primary.
    • Reboot all.  Run checks DBCCs etc.
    • Then upgrade to SP1 via the same method above.

    Would you do it in this order or upgrade to 2016 && the SP1 in a oner?  Downtime not a problem.

    Thank you.

  • anthony.green

    SSC Guru

    Points: 112483

    I would slipstream the install of SP1 into the RTM media and do it in one go, saves failing over multiple times.

    https://www.sqlservergeeks.com/slipstreaming-sql-server/ this is for 2008 but again concepts the same.


    You have tested everything against the new cardinality estimator I hope and you have signed off the full end to end testing?  You leaving the databases in the 110 compatibility mode or moving them up to 130?  Run through all the data base migration assistant etc to ensure your code base can be ported from 2012 to 2016 with no changes etc?


    The below will be a great walkthrough also, it's for SQL2019 but the concepts are still the same.



  • Grant Fritchey

    SSC Guru

    Points: 396617

    I agree with what Anthony posted above and I would add one wrinkle.

    After the upgrade, leave your databases at the old compatibility mode for a period of time. Run Query Store to gather metrics on your databases. Then switch the compatibility mode. If you have any major regressions, you can use plan forcing in query store to deal with them. Whether or not you continue using Query Store after that to gather query metrics is a different decision. You can leave it enabled, but not have it gather metrics. This allows you to do plan forcing without adding overhead to your system.

    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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