Identifying Deprecated Feature Usage (Part 1)
- Posted in:
- Database Administration
- Development
- SQL Server
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.
Comments