Any 2014 to 2019 migration issues?

  • Hi,

    Were planning a migration from 2014 to 2019 soon, and were wondering if anyone has experienced any particular issues we should watch out for.

    At the moment our databases are in a mix of compat 100 and 120, and will initially remain so after the upgrade, after which we'll be testing for a compat upgrade. The compat 100 databases had a lot of plan issues on compat 120 with the 2014 CE, so it was decided to leave them at 100 until a future upgrade.

    Are there any breaking changes / behaviour changes/ subtle issues etc anyone has experienced doing this kind of migration?

    Thanks for the help

  • I haven't encountered issues yet.

    In any case, best is to start your preparation using   SQL Server Data Migration Assistant


    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    I haven't encountered issues yet.

    In any case, best is to start your preparation using   SQL Server Data Migration Assistant

    As a bit of a sidebar, I won't use the DMA anymore.  The damned thing reports way too much information to MS for my liking.  Seems like MS does whatever the hell they want.  Between the automatic reboots of desktops in Windows 10, DMA, etc, etc, MS products have become incredibly invasive.  There should be a law against it that specifies a guaranteed "Opt Out" setting.

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • A day late, but testing, as always, is going to be your bestest buddy. I'm not aware of any fundamental issues in the move from 2014 to 2019. You're already over the big hurdle of the new cardinality estimation engine (assuming your databases are not running in compatibility mode). However, as with any upgrade, while there aren't huge issues, there may be tiny issues that can cause you pain. The optimizer and more has changed since 2014. Mostly, I'd argue, for the better. That's not to say you won't find an edge case.

    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

  • Hi T.C.,

    I haven't ran into any issue as well.  You'll actually see a performance boost by migrating to SQL Server 2019.  The first boost I saw were in the inserts.  Our great developers have a process to import 1B+ records into a single hash table using INSERT INTO...SELECT.  it usually takes 10+ hours but we cut it in half by 5+ hours.

    SQL Server 2019 now allows parallel inserts (mainly raw tables without PKs and Indexes) if you specify the TABLOCK hint

    INSERT INTO [ParallelInsert_Destination] WITH(TABLOCK)

    SELECT * FROM [DummySource_Table]


    • If the target is a heap table (a table without clustered indexes), a parallel insert execution plan is not created by the query optimizer
    • Table variables do not allow parallel insert execution plans
    • Database compatibility level must be 130 or above

    *** Please network with me and feel free to IM if you have any more DBA questions and concerns

    james rossi
    Senior SQL Server DBA
    Senior Software Developer
    Alpha One Data
    Brain Box SQL Mobile DBA

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

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