• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.
  • Advertisements

SQL Server Deprecated Features – Part 3

In this third installment of my mini-series on SQL Server deprecated features (see part 1 and part 2), I will look further at tools to help detect the usage of deprecated features.

SQL Server 2000

For SQL Server 2000 instances, you are pretty much stuck with using the Upgrade Advisor tool for the version of SQL Server to which you are upgrading. In addition, you need to read BOL Backwards Compatibility (links available in part 1) as a reference and manually search your code and database configurations.

SQL Server 2005

Starting with SQL Server 2005, Microsoft has added some detection capabilities into the SQL Server Profiler tool.  Two new event classes were added to report “Deprecation Announcement” feature usage and “Deprecation Final Support” feature usage.

Per the text inside Profiler when “Deprecation Announcement” is selected: Occurs when you use a feature that will be removed from future version of SQL Server, but will not be removed from the next major release of SQL Server…

Per the text inside Profiler when “Deprecation Final Support” is selected: Occurs when you use a feature that will be removed from the next major release of SQL Server…

If you setup your profiler trace to record to a table (I named mine Deprecated_Features_Trace_tb), then you can query it as follows:

SELECT * FROM [dbo].[Deprecated_Features_Trace_tb]  
WHERE EventClass IN (125,126)

EventClass 125 corresponds to “Deprecation Announcement” and EventClass 126 corresponds to “Deprecation Final Support”.

Ironically, you may find that you want to use a Column Filter on ApplicationName to exclude the Profiler tool and SQL Agent (and maybe other internal Microsoft features) which are currently using deprecated features themselves.  For example, when recording the profiler results to a table, the table is created using ntext and image datatypes which have been deprecated and are therefore reported events. Alternatively you could filter on DatabaseName and exclude the system databases.  However, you need to be absolutely sure that no one has created non-Microsoft supplied objects in those databases if you chose to filter on database name.

SQL Server 2008

In SQL Server 2008, an explicit performance counter object has been added for deprecated features with specific “instances” of deprecated features. This enables you to use the DMV sys.dm_os_performance_counters to query if the usage of any of these features has occurred within your SQL Server 2008 instance.

SELECT * FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%:Deprecated%' AND cntr_value > 0

Of course, this won’t tell you where the deprecated features are used, but at least you will know whether you need to go hunting in-depth.

Another new feature in SQL Server 2008 which can help with this task is Extended Events. You can setup an Event Session to capture both “deprecation_announcement” events and “deprecation_final_support” events.  If you are new to Extended Events, then you must read Jonathan Kehayias’ old blog and/or new blog – these links point specifically to all of his Extended Event tagged entries.  Jonathan has written a “XEvent a Day” series and much more. If you want to learn about Extended Events – he’s the man!

Final Thought for Detecting Deprecated Features

For all versions, however, no matter what tools you’ve initially used, always, always, always run the Upgrade Advisor.


3 Responses

  1. […] SQL Server Deprecated Features – Part 3 […]

  2. […] I pointed out in Part 3 of my earlier blog series, you can detect most deprecated features by using the SQL Server: […]

  3. […] I’ve only highlighted a sample of discontinued features in this set of articles; for the complete list of discontinued database engine features in SQL Server 2012, please reference BOL. And for help finding out if you are using these features (which were deprecated in earlier releases), I covered that here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: