Click on the banner to learn about and purchase my database training on Azure

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

Views: 510 views
Reading Time: 4 minutes

Speak guys!

A subject that I don't usually see a lot in everyday life is the concern of DBA's and developers about the use of resources marked as "deprecated" by Microsoft, that is, they are scheduled to be removed in some future version of SQL Server according to the Microsoft roadmap.

Although we can see that some features have been deprecated since version 2000 and are still present in version 2017, it is very dangerous to maintain systems, routines and especially, create new developments using resources that can be removed, “break” your system and get you by surprise during some version update.

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 triggered when a feature that has been announced as “deprecated” is identified, that is, it will be disabled in a future version of SQL Server, but it will not be in the next version.
  • Deprecation Final Support: This event is triggered when a feature that has been announced as “deprecated” is identified and will be YES, deactivated 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!