Migrating from (mostly) 2K8 to 2016

  • JaybeeSQL

    Hall of Fame

    Points: 3257

    HI all,

    About to start this project and would be grateful for any good comprehensive articles rated highly by the good folks here at SSC 🙂

    TIA

     

  • Jeff Moden

    SSC Guru

    Points: 995144

    In place migration or migration to a new server?

    Any partitioned tables?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98458

    You say mostly 2008 servers, any older than 2008?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • JaybeeSQL

    Hall of Fame

    Points: 3257

    I haven't seen the estate yet, presumably a mixture of in-place and new build.  Nothing older than 2K8.  Unlikely (but not impossible) they have partitioned tables.

    • This reply was modified 6 months, 3 weeks ago by  JaybeeSQL.
  • Jeff Moden

    SSC Guru

    Points: 995144

    Since this is a huge subject and you don't yet know the lay of the land, see the following.  You should read it (especially about the migration assistant) even before the "estate" is known.

    https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?view=sql-server-2017

    There's also been a shedload of articles on the subject of migration because SQL Server 2008 is going out of support.  Google is your friend here.

    The only extra help I can offer after having gone through this about 3 years ago is to say make sure you can recover as if a nuke hit your data center. If the system being migrated has any special features going on (especially when it comes to DR and data transmission but includes things like partitioned tables), you might want do some serious testing first.  In other words, consider NOT doing in-place upgrades.  If you have to do in-place upgrades, make real sure that you can "go back" easily, quickly, and properly.

    And, yeah... plan on this being done during an outage.  Even renaming a system has some nasty catches in the throat.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Grant Fritchey

    SSC Guru

    Points: 395653

    Also, the biggest pain point in upgrades from pre-2014 to post-2014 is the new cardinality estimation engine. Most queries are unaffected. Some queries benefit. A few, suffer greatly. Plan to test accordingly. Since you're doing the right thing and skipping 2014 entirely (and honestly, if I were moving everything, I'd move it all to 2017, not 2016), you get Query Store. Be sure to use it for the upgrade process, even if you don't use it after you upgrade. The basics are simple, move the database, but don't change the compatibility level. Run your load for some period of time (hours, days, weeks, you decide) with Query Store enabled. Then, change the compatibility level. If any queries suffer from regression, use Query Store to force the old plan until you figure out what else you need to do to fix the queries.

    ----------------------------------------------------
    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 6 posts - 1 through 6 (of 6 total)

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