Post reply

SQL Server 2008 R2 migration to Sql Server 2014

  • March 19, 2017 at 11:48 pm

    #323013

    Hi Experts,
    I got a requirement for sql server migration from 2008 R2 to 2014.
    I am browsing the net to find out what are the most issues will face after migration and one of them i found is cardinality estimator which is redesigned in 2014.Is this issue fixed in the latest service pack, if not then how to resolve this.
    What are the other issues normally faced while migration to sql server 2014.
    What checklist to follow during migratiion

    Thanks

  • March 20, 2017 at 2:36 am

    #1933945

    Hi 

    you may find this link helpful.

    https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/

  • March 20, 2017 at 2:50 am

    #1933947

    SQL006 - Sunday, March 19, 2017 11:48 PM

    I am browsing the net to find out what are the most issues will face after migration and one of them i found is cardinality estimator which is redesigned in 2014.Is this issue fixed in the latest service pack, if not then how to resolve

    It's not a bug.

    The Cardinality estimator was redesigned in SQL 2014, with the intention that it was more stable in terms of estimations, simpler to understand and less prone to a variety of problems that occur in the real world (ascending date columns, non-independent filters).
    Under the new CE, the majority of queries are either slightly faster or perform the same. A small number typically regress however.
    You need to to test the workload before upgrading and after upgrading and changing the compatibility level to 2014, identify the queries with a problem and either change the queries or force them to use the old CE.

    At this point in time, I would recommend upgrading to SQL Server 2016, not 2014. You need to do the same amount of testing, but you get the Query Store which makes identifying query performance problems much easier, and you get some query hints that make fixing them far easier (plus a pile of other new features)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 20, 2017 at 7:24 am

    #1933984

    GilaMonster - Monday, March 20, 2017 2:50 AM

    It's not a bug.

    The Cardinality estimator was redesigned in SQL 2014, with the intention that it was more stable in terms of estimations, simpler to understand and less prone to a variety of problems that occur in the real world (ascending date columns, non-independent filters).
    Under the new CE, the majority of queries are either slightly faster or perform the same. A small number typically regress however.
    You need to to test the workload before upgrading and after upgrading and changing the compatibility level to 2014, identify the queries with a problem and either change the queries or force them to use the old CE.

    At this point in time, I would recommend upgrading to SQL Server 2016, not 2014. You need to do the same amount of testing, but you get the Query Store which makes identifying query performance problems much easier, and you get some query hints that make fixing them far easier (plus a pile of other new features)

    Thanks Gail for the reply.
    The client doesn't have the infrastructure to upgrade to sql server 2016 .

  • March 20, 2017 at 10:43 am

    #1934042

    Since you can't go to 2016 and take advantage of Query Store, upgrading to 2014, you need to, as much as possible, simulate having Query Store available. With that in mind, do this:

    1. Backup the 2008 database.
    2. Restore it to a 2014 server
    3. Leave the compatibility level at the lower level
    4. Star capturing query performance metrics using extended events (no reason to not use extended events any more)
    5. Run the capture for a period of time that will represent an adequate level of behavior of the client's system (you have to tell me how long that is).
    6. Aggregate the mean & median metrics on the queries (getting the standard deviation would help too).
    7. Change the compatibility level
    8. Capture query metrics. 
    9. Any that change in a negative fashion (here's where standard deviation becomes real handy) you'll have to edit the queries to add a hint to use the old estimator (if that's the problem).

    "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 5 posts - 1 through 4 (of 4 total)

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