0

The shop I work in has thousands of stored procedures which support applications on MS SQL Server 2008 R2 instances. We need to upgrade these to MS SQL 2016 over the course of the next year. Is there a tool that exists which will cycle through stored procs, functions, etc. which will identify anything that may have been deprecated prior to running an actual update?

2 Answers 2

3

One of my personal fav. checklists is from Thomas LaRock SQL MVP.

The first 3 are highly relevant to your question. Also note the Perfmon metric for 'Deprecated Features' that you can use to see if any are running.

1- The SQL Server 2016 Upgrade Advisor (UA) is just that: an advisor. Much like a consultant, it doesn’t fix everything that is wrong, it merely advises you on what actions you should take when upgrading to SQL 2014. The actions the UA recommends will come in two forms: those actions to be done prior to a migration, and those actions to be completed post-migration. The UA is really good at finding what I call the “stub-your-big-toe” things that need fixing prior to a migration. But it is not foolproof, it will not identify every last detail. You will need to play the role of an actual DBA when migrating to a new version. Many of the items below will help you to do just that.

  1. REVIEWING THE “BREAKING CHANGES” SECTION IN THE BOOKS ONLINE Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, you do now. You should review them to the point that they are familiar to you. You don’t have to memorize them all, just be familiar with them so that if something odd happens you can think to yourself “…hey, is this odd behavior listed in the breaking changes section of the Books Online (BOL)”? I would like to believe that the UA will alert you to many of these breaking changes but the truth is the UA is not as dynamic as the BOL. That means the BOL may have an entry or two that doesn’t make it into the UA checklist, and that is why you should review this section.

  2. REVIEWING THE “BEHAVIORAL CHANGES” SECTION IN THE BOOKS ONLINE Similar to the breaking changes, the behavioral changes are changes that could still affect you in an adverse way. They are definitely worth reviewing, and they are also things that the UA is likely to never report back to you about because they aren’t things that will break, but merely things that could break. Also worth noting is that the BOL appears to have two entries for behavioral changes, one for SQL Server features, and one specific for the database engine. I’d advise you keep your eye on both going forward.

2

Since the question is about upgrading to SQL 2016 and not 2014, I will recommend to start with Data Migration Assistant (DMA). You can find details here. Download page is here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.