asked
Find discontinued features before migrating to SQL Server 2016
I want to upgrade an SQL Server 2008 R2 to SQL Server 2016. I would like to find the queries running in production (on SQL Server 2008 R2) that use discontinued features of SQL Server 2016. Is there a way to find the queries that use these discontinued features? The Database Migration Assistant only looks at the database schema (tables, stored procedures, triggers), it does not capture queries that are issued from the application.
In addition to DMA, it is always the best thing while doing any upgrade to trace (Profiler/Externded events etc.) all the workload in the existing server and replay it on the new server. You can filter the trace to the specified application, but I would not recommend that. I know this could be difficult but the benefits like finding actual errors/warnings, performance comparison etc., there will more gains than pains. Nothing is better than if you can present the clear picture the Management/Stake holders through a real time run. But this does not mean that it would be bullet proof, there could be some unseen situations like a Monthly/Quarterly process or a sleeping application is started. But you would have covered all the major scenarios in advance. Having said that, the good news is that the list of discontinued features since 2012 is not that long as compared to the deprecated features :)
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?
I should have elaborated that you need to take the backup (with COPY_ONLY option so your backup chain does not break) of the database before the trace and restore it on the new server. Otherwise, the exercise would mean near to nothing.
From the time the backup is taken, up to the start of the trace, the production database receives new entries, and therefore the autoincrement columns get incremented. For example, at the time of backup, an autoincrement column is at value 1.000. When the trace starts, the autoincrement column is at value 1.010. But when replaying the trace on the backup, the replay will start with the autoincrement column at value 1.000, not at 1.010. If this record has a child, the child will contain a parent column value of 1.010, but the parent in the replay will be actually 1.000. Therefore, the child will fail. To ensure, that the trace starts right after the backup, the production database has to be stopped, which is not possible.
I guess that data change won't be of great magnitude and one could live with some of the constraint errors. You can always filter constraints errors easily. But if you are not happy with that, then you can use full backup + log backup combo and do the point in time restoration.
18 People are following this question.
