Chapter 21. Deprecation feature

Cristian Lefter

With every release of SQL Server, new features are added. Also, some features are discontinued or marked as deprecated, which means that they’ll be removed in a future version of SQL Server. Starting with SQL Server 2008, the deprecation policy is taken very seriously. How seriously? Seriously enough to mention that the first public CTP of the product came with a feature that allows you to monitor usage of deprecated functionality. You may ask yourself: why would I need to track the usage of deprecated features?

Among the possible reasons are the following two:

• You can obtain greater longevity for an application if you’re an application developer.
• You can identify possible upgrade problems for your existing applications before the next release of SQL Server hits the shops.

The deprecation feature consists of two components:

• The SQLServer:Deprecated Features object performance counter.
• The Deprecation category of trace events, including the Deprecation Announcement event class (indicates that a feature will be removed in a future version of SQL Server) and the Deprecation Final Support event class (indicates that a feature will be removed in the next major release of SQL Server).

The complete list of deprecated features can be found in Books Online and has more than nine printed pages. Table 1 displays a few of them.

Table 1. Deprecated features

Deprecated feature	Replacement
DATABASEPROPERTY	DATABASEPROPERTYEX
The 80 compatibility level and upgrade from version 80	Only compatibility levels for the last two versions of the product available
sp_dboption	ALTER DATABASE
text, ntext, image data types	varchar(max), nvarchar(max), varbinary(max) data types
sp_attach_db	CREATE DATABASE statement with the FOR ATTACH option

A simple usage example

To get a feel for how the deprecation feature works, listing 1 shows how to read the performance counter SQLServer:Deprecated Features before and after using the DATABASEPROPERTY function (which will be replaced by the DATABASEPROPERTYEX function). Note that if you run the sample on a named instance, you have to change the counter name.

Listing 1. Reading the SQLServer:Deprecated Features counters

-- Declare a variable to hold the current value of the counter
DECLARE @CurrentCount bigint;
SELECT
    @CurrentCount = cntr_value
FROM sys.dm_os_performance_counters
WHERE
    object_name='SQLServer:Deprecated Features'
    AND instance_name='DATABASEPROPERTY';

-- Increase the value of the counter by 1
-- using the deprecated feature the function DATABASEPROPERTY
SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;

-- Retrieve the difference between the current counter value
-- and the original value
-- The value returned is 1 (or greater if another session used the
-- DATABASEPROPERTY function after saving the counter value
SELECT
    (cntr_value - @CurrentCount) AS SessionUsage
FROM sys.dm_os_performance_counters
WHERE
    object_name='SQLServer:Deprecated Features'
    AND instance_name='DATABASEPROPERTY';

The example in the listing reads the performance counters from T-SQL by using the sys.dm_os_performance_counters dynamic management view. A more attractive image can be obtained using the Performance Monitor. From the SQLServer:Deprecated Features performance object, select and add the DATABASEPROPERTY counter. Then back in the SQL Server Management Studio, run again the next statement:

SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;

Figure 1 shows the SQLServer:Deprecated Features performance object in Performance Monitor.

Figure 1. Tracking deprecated features using Performance Monitor

Methods of tracking deprecated features

Probably the most useful scenario based on tracking deprecated features is to collect data and save it on a data store, and then build a report over the data store and optionally include the report in SQL Server Management Studio. The frequency of collection, the synchronous or asynchronous mode of collection, and the data store used are a matter of choice (personally I prefer using Extended Events). Some of the possible tracking methods follow:

• Performance Monitor— Can be used to display graphically the values of counters for the SQLServer:Deprecated Features performance object.
• The sys.dm_os_performance_counters Dynamic Management View— Based on the same SQLServer:Deprecated Features performance object, it allows taking snapshots for the current values of the counters.
• The performance logs and alerts— Use the same object as for Performance Monitor; the difference is that the values can be tracked over time.
• SQL Profiler and SQL Trace— This method is based on the Deprecation Announcement and the Deprecation Final Support event classes.
• Event notifications— This method consists of creating an event notification for the DEPRECATION_ANNOUNCEMENT and DEPRECATION_FINAL_SUPPORT events. A stored procedure can be used to log the events.
• Extended Events— The sqlserver.deprecation_announcement and the sqlserver.deprecation_final_support events support this method. An example is provided later on.
• SQL Trace Collector— If you need automation for collecting deprecation events, the SQL Trace Collector part of the Data Collector architecture can be helpful.
• WMI— This method consists of creating SQL Agent alerts based on WMI queries. It’s mentioned only as a possible option, not as a suggestion, because it doesn’t bring any advantages over the other methods.

The next section highlights two of the mentioned methods. First, a simple skeleton for the event notifications method is displayed in listing 2.

Listing 2. Event notification method

-- Create a queue
CREATE QUEUE DeprecationFeatures_Queue;
GO

-- Create a service
CREATE SERVICE DeprecationFeatures_Service
ON QUEUE DeprecationFeatures_Queue([http://schemas.microsoft.com/SQL/
     Notifications/PostEventNotification]);
GO

-- Create a route
CREATE ROUTE DeprecationFeatures_Route
WITH SERVICE_NAME = N'DeprecationFeatures_Service',
ADDRESS = N'LOCAL';
GO

-- Create the actual event notification
CREATE EVENT NOTIFICATION DeprecationFeatures_Notification
ON SERVER
FOR DEPRECATION_ANNOUNCEMENT, DEPRECATION_FINAL_SUPPORT
TO SERVICE 'DeprecationFeatures_Service', 'current database';
GO

For using Extended Events, listing 3 creates a session and then adds the two deprecation events. For storage, a ring buffer with maximum 1000 entries is used.

Listing 3. Extended Events method

-- Create an event session and add the two deprecation events
CREATE EVENT SESSION TrackDF
ON SERVER
ADD EVENT sqlserver.deprecation_announcement,
ADD EVENT sqlserver.deprecation_final_support;
GO
-- Add a the ring buffer target and configure it
-- to retain 1000 events
ALTER EVENT SESSION TrackDF
ON SERVER
ADD TARGET package0.ring_buffer
(
      SET occurrence_number = 1000
);
GO

-- Start the session and begin event collection
ALTER EVENT SESSION TrackDF
ON SERVER
STATE = start
GO

-- Use a deprecated feature
SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;

-- View the collected events
SELECT CAST(xet.target_data as xml)
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'TrackDF'
GO

-- Stop the event session and remove it from the server
ALTER EVENT SESSION TrackDF
ON SERVER
STATE = STOP;
GO

DROP EVENT SESSION TrackDF
ON SERVER
GO

The result of the previous SELECT query is displayed in listing 4.

Listing 4. XML result

<RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="424">
  <event name="deprecation_final_support" package="sqlserver" id="207" version="1" timestamp="2008-10-30T08:54:31.689Z">
    <data name="feature">
      <type name="unicode_string" package="package0" />
      <value>DATABASEPROPERTY</value>
      <text />
    </data>
    <data name="message">
      <type name="unicode_string" package="package0" />
      <value>DATABASEPROPERTY will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.</value>
      <text />
    </data>
  </event>
</RingBufferTarget>

Summary

SQL Server 2008 makes it easy to track the features that will be removed in future releases of the product. Pick any of the methods mentioned in this chapter and make sure that your applications will have a greater longevity.

About the author

Cristian Lefter is a SQL Server MVP and a former SQL Server developer, currently working as a consultant at Micro Training, a consulting and training company. Cristian is a writer, blogger, SQL expert, and frequent reviewer for Manning, Apress, Wiley, and other publishers, as well as for ASPToday, Simple-Talk, Microsoft E-Learning, Asentus, Content Master, GrandMasters, and more. He’s based in Bucharest, Romania.

You can reach him at his blog at http://sqlserver.ro/blogs/cristians_blog/default.aspx, or his email address at [email protected].