At TechEd: Event Notifications and scope creep

Started my part of the festivities today with a chalktalk mentioning SQL Server DDL triggers and Event Notifications. But explaining Event Notifications meant explaining Service Broker services, queues, and contracts. And the fact that both Event Notifications and DDL Triggers use a XML format. That you can change to a rowset format using the XML.nodes method. Which meant talking about XML.nodes. Showed a DDL trigger for CREATE ASSEMBLY that had to be written in SQLCLR.

Went on from there to setup a cross-database event notification, which (the way that I did it) entailed using the TRUSTWORTHY database property. And what TRUSTWORTHY means (I'll do a blog entry on that in future). And because cross-database service broker uses session keys (BEGIN DIALOG CONVERSATION uses encryption by default), each database must have a database master key. Explained what database master keys are.

Finally, I cross-instance event notification. Needed to explain what ENDPOINTs were and the ENDPOINT abstraction. And explain service broker ROUTEs. A good portion of the new "infrastructure type features" (I don't think they're only for developers any more) in one talk. That's some (fairly useful, I think) scope creep.

On to the WMI provider for events, and how this works with SQLAgent. Watching it use WAITFOR (a queue) and RECEIVE. Uh, oh…

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.