SQL Server Track Deprecated Features with Extended-Events

Hi geeks,

hope you all enjoyed my last blog on SQL Server Statistics Only database.

In this blog we will explore one possible solution to the problem all database developer and administrator have during SQL Server upgrade from one major release to another. Every new release of SQL Server brings some new features to the world which is great but it also deprecates some old features that will be either replaced or discontinued. Now suppose your current application is using one such feature that is not supported in the version you are upgrading to. Well you can perform a test migration in your test server before implementing it to the production. But one problem to this approach is that you can’t reproduce the same production environment in your test server. And also it’s quite exhaustive to test all the features manually and compare them with the list of deprecated features that Microsoft releases with every major release , which brings us to today’s topic how to find all those deprecated features that your application is currently using that is announced to be deprecated or already deprecated. A simple efficient and short answer will be to use Extended-Events. And one more reason to use Extended-Events is that you can use it in your real production box with minimum overhead.

There are two events that are available in Extended-Events to track deprecated features.

1. deprecation_final_support (Occurs when you use a feature that will be removed from the next major release of SQL Server.)

2. deprecation_announcement (Occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release of SQL Server.)

The Event Field for the two events is of same format.

Demo:
First we will track the features that are already removed from the next major release by using deprecation_final_support event. I have used ring buffer as a target in this demo.

--Create an Extended Event Session to track Features that are deprecated and will be removed in next major release 
CREATE EVENT SESSION [find_deprecation_final_support] ON SERVER 
ADD EVENT sqlserver.deprecation_final_support 
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=3 SECONDS)
GO
--Start Event Session
ALTER EVENT SESSION [find_deprecation_final_support]
ON SERVER
STATE=START
GO
 
--Change compatibility level of AdventureWorks2012 from 110 to 90
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 90
GO
 
--database compatibility level 90 will be removed from the next version of sql server
--so extended event will capture this when we use a database with compatibility level 90
USE [AdventureWorks2012]
GO
 
--ROWCOUNT is another deprecated feature that will be removed in the next version
SET ROWCOUNT 4;
SELECT *
FROM Production.ProductInventory
WHERE Quantity < 300;
GO
 
-- Wait for Event buffering to Target
WAITFOR DELAY '00:00:05';
GO
 
--Get Event Session result from ring buffer 
DECLARE @xml_holder XML;
SELECT @xml_holder = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = N'find_deprecation_final_support'
  AND t.target_name = N'ring_buffer';
SELECT
   node.value('(data[@name="feature_id"]/value)[1]', 'int')as feature_id,
      node.value('(data[@name="feature"]/value)[1]', 'varchar(50)')as featu,
         node.value('(data[@name="message"]/value)[1]', 'varchar(200)')as message,
    node.value('(@name)[1]', 'varchar(50)') AS event_name
FROM @xml_holder.nodes('RingBufferTarget/event') AS p(node);
GO

Output:

--Stop Event Session
ALTER EVENT SESSION [find_deprecation_final_support]
ON SERVER
STATE=STOP
GO
 
-- Drop Event Session 
DROP EVENT SESSION [find_deprecation_final_support]
ON SERVER;
 
--Change database compatibility level back to 110
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110
GO

You can get the complete list of deprecated features of SQL Server 2012 in this link: http://technet.microsoft.com/en-us/library/ms143729.aspx

Now let’s track the features that are announced to be deprecated with deprecation_announcement event.

--Create an Event Session to track Features that are not yet deprecated but will be removed in a feture release 
CREATE EVENT SESSION [find_deprecation_announcement] ON SERVER 
ADD EVENT sqlserver.deprecation_announcement 
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=3 SECONDS)
GO
 
--Start event session
ALTER EVENT SESSION [find_deprecation_announcement]
ON SERVER
STATE=START
GO
 
--sp_lock is one such feature
sp_lock @@spid
 
-- Wait for Event buffering to Target
WAITFOR DELAY '00:00:05';
GO
 
--Get the output of this Event Session from Ring Buffer
DECLARE @xml_holder XML;
SELECT @xml_holder = CAST(target_data AS XML)
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets AS t 
    ON t.event_session_address = s.address
WHERE s.name = N'find_deprecation_announcement'
  AND t.target_name = N'ring_buffer';
SELECT
   node.value('(data[@name="feature_id"]/value)[1]', 'int')as feature_id,
      node.value('(data[@name="feature"]/value)[1]', 'varchar(50)')as feature,
         node.value('(data[@name="message"]/value)[1]', 'varchar(200)')as message,
   node.value('(@name)[1]', 'varchar(50)') AS event_name
FROM @xml_holder.nodes('RingBufferTarget/event') AS p(node);
GO

Output:

--Stop the event
ALTER EVENT SESSION [find_deprecation_announcement]
ON SERVER
STATE=STOP
GO
 
-- Drop  Event Session 
DROP EVENT SESSION [find_deprecation_announcement]
ON SERVER;

Regards

Debjeet Bhowmik

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook