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.