SQL Server – Knowing the Use of Deprecated or Discontinued Features

Upgrading your SQL Server environment is inevitable and I know most many are doing this given SQL Server 2005 support will end first half next year. Whenever I get in front of customers, I have questions always coming my way around upgrades. In a recent session, I had one of the attendees send me a mail stating I have got him into trouble. I was taken aback and wrote back to know what was going wrong and how I can help him get out of trouble. This is what he wrote back:

“ Hi Pinal,

It was great to be part of your session the other day, but after the session my management had an ask which I was not sure and want your guidance.

As a DBA, I have just upgraded from a previous version of SQL Server on one of our production environment. Prior to upgrading I utilized the SQL Server 2014 Upgrade Advisor to identify deprecated and discontinued features in SQL Server 2014. Our database and development teams have regression tested the changes identified in the Upgrade Advisor for deprecated and discontinued features and remediated the items found.

As the SQL Server Administrator I need to verify all of the deprecated and discontinued features have been removed. How can I do this? I have rerun the upgrade advisor and assume that is good enough. My Management has come back strongly with an approach to make sure we are using the latest and greatest capabilities because of this upgrade and there are no legacy around.”

This was an interesting question and I wrote back to help him. This blog has been inspired by this interaction.

Thinking Simple

There are multiple options and we need a systematic way to solve this problem. We will start by doing some simple queries to DMVs to understand if we are using any deprecated features.

SELECT OBJECT_NAME,
counter_name,
instance_name AS 'Deprecated Feature',
cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%:Deprecated%'
AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC
GO

I told my DBA friend to run the above query to find out if anything is still around. I always say to have a baseline trace to rerun on an upgraded test environment to know if there are still features we need to work on. This is always not simple but this is same as what we get from Perfmon counters. These two must match. If you are not aware, here are the steps:

Open up Performance Monitor (Perfmon) and under the SQL Server counters add the Deprecated Features / Usage for all counters by selecting all and Clicking ADD.

Conclusion

As I said this was a simple solution. We also exchanged few other emails after a week of interaction. I will try to write for a different blog. Having said that, I would highly recommend using these simple techniques to know if we are using Deprecated features. I would also like to learn from my blog readers if you have ever used these features in your environments prior to upgrade and as a validation process? Do let me know what you have been doing.

Reference: Pinal Dave (https://blog.sqlauthority.com)