2017-07-20

Identifying Deprecated Feature Usage (Part 1)

Dave Mason - SQL Server Deprecation

I've wanted to do some Event Notification testing for SQL Server deprecation events for quite some time. The thought process here is that I could send myself an alert to identify usage of SQL Server features that will be removed from the next major version (or future version) of SQL Server. I could then forward this info to development and let them take action...or not (I kid, I kid). Today is the day I finally got around to the testing. I didn't get very far, though.

Without rehashing the basics of event notifications (this post may help if you need some context), I created an EVENT NOTIFICATION for the TRC_DEPRECATION event group. That group includes the DEPRECATION_FINAL_SUPPORT and DEPRECATION_ANNOUNCEMENT child events. I also created a QUEUE, a SERVICE, and an activation PROCEDURE (for the QUEUE). The proc is simplistic. It takes the EVENTDATA() XML data, transforms it into an HTML <table>, and emails the info to me.

After activating the QUEUE, in about a minute or so I got hit with about 5,000 emails. Yikes. I quickly dropped all the objects to stop everything and give the SMTP relay a break. Every one of the events appeared to be identical, with EVENTDATA() similar to this:

EventType DEPRECATION_FINAL_SUPPORT
PostTime 2017-07-20T11:12:11.180
SPID 31
TextData 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.
DatabaseID 4
TransactionID 37420798
NTUserName dmason
NTDomainName BostonGarden
HostName
ClientProcessID
ApplicationName Microsoft SQL Server Service Broker Activation
LoginName BostonGarden\dmason
StartTime 2017-07-20T11:12:11.177
ObjectID 16
ServerName RLewis\DBA
ObjectName USER_ID
DatabaseName msdb
LoginSid AQUAAAAAAAUVAAAA53DCrRi9KvnePLdohQQAAA==
RequestID 0
XactSequence 133143991863
EventSequence 93397
IntegerData2 5846
IsSystem 1
Offset 5540
SqlHandle AwAEAKLkNwcw5PoAQqYAAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=
SessionLoginName

Haaa! The ApplicationName is "Microsoft SQL Server Service Broker Activation". Now I don't know if the USER_ID usage is my fault (either directly or indirectly) or if it's Service Broker's fault. I'll have to do some more digging. But if it *is* Service Broker's fault, that's kind of funny. I'm using Event Notifications (ie Service Broker) to monitor use of deprecated features...and Service Broker is using deprecated features. Does that qualify as irony?


UPDATE

After some investigation and further testing, I've made some additional observations and drawn a few conclusions. More on that in Part 2.


SHARE