2017-07-21

Identifying Deprecated Feature Usage (Part 2)

Dave Mason - SQL Server Deprecation

In my previous post, I took a stab at monitoring deprecation events for SQL Server. It didn't go so well. A deprecation event occurred more than 5,000 times in a very short period of time, and I got one email for every occurrence. Not good. Here's what I kept seeing over and over:

USER_ID will be removed from a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use DATABASE_PRINCIPAL_ID instead.

It turns out the system stored proc msdb.dbo.sp_send_dbmail has a USER_ID() reference. I suspect an unrelated alert/email happened once, which executed sp_send_dbmail, which generated a DEPRECATION_FINAL_SUPPORT event, which ultimately led to another execution of sp_send_dbmail, which generated yet another DEPRECATION_FINAL_SUPPORT event, and round and round we go.

After figuring out the mess I'd gotten myself into, I tweaked my activation procedure to *not* send email if the EVENTDATA() included DatabaseName = 'msdb'. It was a simple fix. I recreated my QUEUE, SERVICE, EVENT NOTIFICATION, and activation PROCEDURE and waited. It wasn't long before a few dozen emails rolled in. I quickly dropped all of the objects to stop the flow of email. After my first experience, I didn't want my Inbox bloated with database mail spam. Here are three more examples of what I found:


EventType DEPRECATION_FINAL_SUPPORT
PostTime 2017-07-20T14:25:46.297
SPID 55
TextData The TEXT, NTEXT, and IMAGE data types will be removed in a future version of SQL Server. Avoid using them in new development work, and plan to modify applications that currently use them. Use the varchar(max), nvarchar(max), and varbinary(max) data types instead.
DatabaseID 11
TransactionID 38019973
NTUserName NETWORK SERVICE
NTDomainName NT AUTHORITY
HostName JTatum
ClientProcessID 2544
ApplicationName Report Server
LoginName NT AUTHORITY\NETWORK SERVICE
StartTime 2017-07-20T14:25:46.293
ObjectID 4
ServerName JTatum\DBA
ObjectName Data types: text ntext or image
DatabaseName ReportServer
LoginSid AQEAAAAAAAUUAAAA
RequestID 0
XactSequence 0
EventSequence 95018
IntegerData2
IsSystem
Offset
SqlHandle AgAAAHyWUTF3tNzcbAqiFG/6EU6iqJQdAAAAAAAAAAAAAAAAAAAAAAAAAAA=
SessionLoginName BostonGarden\JTatum$
EventType DEPRECATION_FINAL_SUPPORT
PostTime 2017-07-20T14:25:43.683
SPID 37
TextData Extended stored procedure API will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.
DatabaseID 1
TransactionID 38019507
NTUserName
NTDomainName
HostName
ClientProcessID
ApplicationName Microsoft SQL Server Service Broker Activation
LoginName sa
StartTime 2017-07-20T14:25:43.680
ObjectID 20
ServerName JBrown\DBA
ObjectName XP_API
DatabaseName master
LoginSid AQ==
RequestID 0
XactSequence 0
EventSequence 95011
IntegerData2
IsSystem 1
Offset
SqlHandle BAD/f99QRNgBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=
SessionLoginName
EventType DEPRECATION_FINAL_SUPPORT
PostTime 2017-07-20T14:25:43.563
SPID 58
TextData The use of more than two-part column names will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.
DatabaseID 8
TransactionID 38019115
NTUserName NETWORK SERVICE
NTDomainName NT AUTHORITY
HostName GHayward
ClientProcessID 2728
ApplicationName .Net SqlClient Data Provider
LoginName NT AUTHORITY\NETWORK SERVICE
StartTime 2017-07-20T14:25:43.550
ObjectID 3
ServerName GHayward\DBA
ObjectName More than two-part column name
DatabaseName Tfs_Configuration
LoginSid AQEAAAAAAAUUAAAA
RequestID 0
XactSequence 0
EventSequence 95006
IntegerData2
IsSystem
Offset
SqlHandle AwAIAGYmYkt8z74AqaMAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=
SessionLoginName BostonGarden\GHayward$

It's odd that for all three, the EventType is DEPRECATION_FINAL_SUPPORT (the feature will be removed from the *next* major version of SQL Server), yet the TextData field says the offending feature "will be removed in a *future* version of SQL Server", indicating a DEPRECATION_ANNOUNCEMENT event. Perhaps that's a bug? I ran my tests on SQL Server 2014 with SP2. I'm quite certain none of those features is gone for SQL Server 2016.

When I started out, I had envisioned seeing mostly (if not all) DEPRECATION_ANNOUNCEMENT events and a small percentage of DEPRECATION_FINAL_SUPPORT events. Along with other testing strategies, this could be used to help determine if software is compatible with the "next version up" of SQL Server. Any occurrences of DEPRECATION_FINAL_SUPPORT events would tell me parts of the software would need to be rewritten to eradicate use of what's been deprecated. But since I haven't encountered *any* DEPRECATION_ANNOUNCEMENT events yet, I have reconsidered and no longer deem this a sound strategy.

But I still think there's some value here. I'm going to log these events for a while and take my findings back to the dev team. Knowing the realities of the SDLC, no fixes will be made until absolutely necessary. Nonetheless, the dev team should appreciate knowing what they're up against ahead of time.

I will be changing my monitoring strategy. With some hindsight, I now see that sending an email once per event is overkill. In fact, I don't want any email at all. The software is in production. It works. I can simply log to a table and query the info back at any time. Near-synchronous alerts simply don't make sense here. My CREATE EVENT NOTIFICATION statement was also ON SERVER. That is more than is needed. I should have narrowed it down to one or more specific user databases and left the system DBs out of it.

I'll have one more post after this with some T-SQL scripts for creating all the objects. More to come...


SHARE