Upgrading your SQL Server environment is an inevitable task that many organizations are currently facing, especially with the end of support for SQL Server 2005 approaching in the first half of next year. During my recent sessions with customers, I have received numerous questions regarding upgrades. One particular attendee reached out to me after a session, expressing that he had encountered some trouble. Intrigued, I asked for more details to understand the issue and offer assistance. Here’s what he shared:
“Hi Pinal, it was great to be part of your session the other day. However, after the session, my management had a request that I was unsure about and I need your guidance. As a DBA, I recently upgraded one of our production environments from a previous version of SQL Server. Before the upgrade, I used the SQL Server 2014 Upgrade Advisor to identify deprecated and discontinued features in SQL Server 2014. Our database and development teams have performed regression testing on the identified changes and resolved any issues found. As the SQL Server Administrator, I now need to verify that all deprecated and discontinued features have been removed. How can I do this? I have rerun the upgrade advisor, but my management insists on ensuring that we are utilizing the latest and greatest capabilities after the upgrade, with no legacy features remaining.”
This question sparked my interest, and I promptly responded to help him. This blog post is inspired by our interaction and aims to provide a solution to this problem.
Thinking Simple
There are multiple options available, but we need a systematic approach to solve this problem. Let’s start by running some simple queries against DMVs (Dynamic Management Views) to determine if any deprecated features are still in use. Here’s an example query:
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;
I advised my DBA friend to execute the above query to identify any lingering deprecated features. Additionally, I always recommend having a baseline trace to rerun on an upgraded test environment. This helps identify any features that still require attention. Although not a simple task, it is similar to what we obtain from Perfmon counters, and the results should align. Here are the steps:
- Open Performance Monitor (Perfmon).
- Under the SQL Server counters, add the “Deprecated Features / Usage” for all counters by selecting all and clicking “Add”.
Conclusion
As I mentioned, this is a simple solution to verify the absence of deprecated features. Following our initial interaction, my DBA friend and I exchanged a few more emails, which I plan to cover in a separate blog post. However, I highly recommend utilizing these simple techniques to identify and address any deprecated features. I would also like to hear from my blog readers if you have encountered these features in your environments prior to an upgrade and how you validated their removal. Please share your experiences with me.