Upgrade Advisor or Distributed Replay tools

  • Dear MVPs
    I am planning on migrating data from 2005 to 2014 hence i am learning about using UPGRADE ADVISOR or DISTRIBUTED REPLAY
    WHich one is a better tool to use and why?

    have a lovely week
    KY

  • It's two different tools. One examines your structures & code to validate for upgrade. The other is used for testing how your queries behave. You can use both as part of an upgrade process.

     I wouldn't suggest upgrading to 2014 now. I'd suggest upgrading to 2016. The principal reason is two-fold. Starting with 2014, there is a new cardinality estimation engine. It's the one big change that can cause some of your queries to run slow. Because of this, you want to go to 2016 because it introduces the Query Store. This is a great part of the upgrade process now in 2016. You can move a database into 2016, leave it at the old compatibility level (which means the old cardinality estimator), turn on query store, capture metrics for a while (days or weeks, up to you), then change the compability level. If something goes wrong, you can use Plan Forcing in the Query Store to use the old execution plan. It's a fantastic way to upgrade and complements the other tools as well.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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