SQL Server - How to identify deprecated features being used in instance (Extended Events - XE)

Views: 249
Reading Time: 4 minutes

Hey guys!

One issue I don't often see much in everyday life is DBA's and developers' concern about the use of features marked "deprecated" by Microsoft, that is, they are set to be removed in some future version of SQL Server according to Microsoft roadmap.

While we may note that some features are from the 2000 release marked deprecated and are still present in the 2017 release, it is very dangerous to keep systems, routines and especially create new developments using removable features, break your system and get you. surprise during some version upgrade.

The idea of ​​this post is to demonstrate how you can identify features that are marked for deactivation and take appropriate steps to change your code so that good development practices are used while always being aligned with the latest product features.

To meet this demand, I will use the Extended Events (XE) feature and the “Deprecation Announcement” and “Deprecation Final Support” events:

  • Deprecation Announcement: This event is fired when a feature that has been announced as "deprecated" is identified, meaning it will be disabled in a future version of SQL Server, but will not be in the next version.
  • Deprecation Final Support: This event is fired when a feature that has been announced as "deprecated" is identified and will be YES, already disabled in the next version of SQL Server. These are the most worrying cases in the short term, as these features will stop working as soon as you upgrade SQL Server.

If you have been following my blog for some time, you should remember other posts I have made about Extended Events:
SQL Server - How to Create Error and Exception Tracking in Your Database Using Extended Events (XE)
SQL Server - How to generate a deadlock history for failure analysis in routines

To identify which features you use in your environment, let's create a new session using XE

I chose not to use any template and chose the 2 events already mentioned, as print

In this screen you can choose some columns you would like to collect information

In this configuration step, you must define where the result file will be written to, whether it will be written as a physical file to disk and setting the maximum size it will reach or using ring_buffer to always have the latest information (recommended use for burning). to be continued).

Following is a summary of the settings I used to create this Extended Event.

Below is the source code of the generated script:

Following are examples used to test the feature:

And now, I will use this script to collect the recorded data:

Viewing the results:

If you would like to know all features that are marked deprecated, access this article here and if you are migrating to new version of SQL Server be sure to use this tip together with o Data Migration Assistant (DMA).

That's it folks!

I hope you enjoyed this post and see you next time.
Strong hug!