none
Find discontinued features before migrating to SQL Server 2016 RRS feed

  • Question

  • Hello,

    The production environment uses SQL Server 2008 R2, and we want to migrate it to SQL Server 2016.

    If I capture a trace with the SQL Profiler on the production environment, how can I find, if the trace contains discontinued features of SQL Server 2016?


    • Moved by Tom Phillips Thursday, March 22, 2018 4:26 PM Upgrade question
    Thursday, March 22, 2018 3:31 PM

All replies

  • The Data Migration Assistant will tell you.

    Please see:

    https://docs.microsoft.com/en-us/sql/database-engine/install-windows/prepare-for-upgrade-by-running-data-migration-assistant

    Thursday, March 22, 2018 4:11 PM
  • The Data Migration Assistant, checks only the schema of the database. Here is a screenshot of the Data Migration Assistant:

    The problem is, that there might be queries (issued by the application) running on that database, that have discontinued functionality. I want to find these queries. 

    I thought, that it would be possible, to take a trace of one day from the production database, and find the discontinued features. Is there a tool, that can find discontinued features in a trace file?

    Friday, March 23, 2018 7:19 AM
  • Besides compatibly level versions, there are very few actual breaking changes between SQL 2008 R2 and SQL 2016 database engine that would cause application issues.

    Parsing the trace is extremely difficult and will be very time consuming.  You are better off just installing it and testing it on SQL 2016.

    You can see discontinued functions by running this query.   However, you would run this on SQL 2008 R2 and it will only tell you things on that version which you should have already fixed.  Be aware, this returns ALL discontinued items in use on the server, and MS internally uses some items which are flagged as discontinued.

    https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

    Friday, March 23, 2018 11:05 AM
  • Replaying the production trace on the SQL 2016 database will produce a lot of errors, because the autoincrement columns will have different values, and therefore all parent-child relationships, as well as the updates will fail. How will I be able to distinguish the failed queries with discontinued features from the failed queries with constraint violations?
    Friday, March 23, 2018 11:21 AM
  • When I said test, I meant install your application and your database on SQL 2016 and try it.

    There is a 99% chance you will have no issues upgrading from SQL 2008 R2 to SQL 2016.

    Friday, March 23, 2018 11:39 AM
  • Yes, but the test might not cover 100% the functionality of the production. It would be safer to validate a production trace for discontinued features.
    Friday, March 23, 2018 1:25 PM
  • Besides compatibly level versions, there are very few actual breaking changes between SQL 2008 R2 and SQL 2016 database engine that would cause application issues.

    There are a couple. The old RAISERROR syntax was removed in SQL 2012, as was the COMPUTE clause. And if you were running in compat level 80, you can also get burned by the old outer-join operators.

    One way to find such usage is to run a trace and capture the Deprecation events. They are likely to catch too much, but you still get an idea of what you should test on SQL 2016.

    Friday, March 23, 2018 10:47 PM
  • Yes, but not all discontinued features of SQL Server 2016 are included in the deprecated features of SQL Server 2008 R2.
    Monday, March 26, 2018 8:44 AM
  • Yes, but not all discontinued features of SQL Server 2016 are included in the deprecated features of SQL Server 2008 R2.

    Care to mention any example?

    Since SQL 2012 no functionality has been dropped from SQL Server. Nor have there been any new deprecations. Thus, I would expect the trace to trap all suspects.

    Monday, March 26, 2018 9:29 PM