Tuesday, June 23, 2015

[Presenting] Overlord, my 24 Hours of PASS presentation on Event Notifications

Growing the Community


Welcome, all!

I hate being "that guy", the one with the awesome presentation who swears he'll have it up "today or tomorrow".  So, here's my presentation for 24 Hours of PASS.  Still stunned that I was chosen, thrilled to present it, and hopeful that you'll fall in love with it like I did.  Thanks for visiting!




And everything (slides, code, my in-production repository) can be found at:

https://drive.google.com/file/d/0B3a287PS_UJITXNON2h1STlmZFE/view?usp=sharing

10 comments:

Bernard said...

The 24HOP presentation was awesome. Thank you.

bourgon said...

Much appreciated! I love this stuff, and my fellow DBAs agree - I've had two move on to greener pastures, and they both run it in their new shops.

Things I need to investigate:
* does it work if the WMI service is turned off
* will SQL Express work?

Unknown said...

Just watched your 24HOP presentation. What a great presentation and awesome tool kit! I have downloaded the zip file and can't wait to try it out in our environment (test servers first, of course). Would love to have you present to our local SQL Server User group (HRSSUG, SE Virginia) either remotely or in person.

bourgon said...

David, it would have to be remotely, alas. I did present it at SQLSat DC last fall (and hope to be there this fall with a different presentation), but I'm located in Texas. A biiiiit too far to drive. :)

David Frederick said...

I unfortunately missed the presentation at 24HOP and am happy to see it was posted here. Thank you!

bourgon said...

David, videos of all sessions will be posted next week. I have a version of it from a SQLSaturday last year (but largely the same) at: https://www.youtube.com/playlist?list=PLWk_0YUrTXkSCvOQbpCqEDFBqXnMfDNOw

Martin Guth said...
This comment has been removed by the author.
bourgon said...

To people looking at the scripts: for the full repository, use the subfolder in the Zip - it includes all the sub-scripts, which makes it a lot easier to understand what's going on. I even include some sample exclusion rules. The script 14 is basically one gigantic file with the code, but it's much easier to use the subfolder.

Cody said...

Watched the last video plus the new video ;-)

One thing I'm surprised nobody asked, how gracefully does this fail if the collection server stops processing? I take it that the messages would begin to queue up on the monitored hosts in msdb (which would also begin to grow), but I'm wondering if you have any insight on how quickly it grows and what happens when msdb fills and the queue can't take any more.

And secondarily, your collection server, can it be an AOAG? I'm curious if/how that would work (and if it might help with the above).

Cheers though I plan on trying it out.

bourgon said...

Cody - thanks for watching!

Stopping processing: awesome question. I'm working on a post for that currently; I had a problem in one of my secondary environments, and nobody had set up monitoring correctly. So I was having that exact problem - granted, it was down for ~6 months, but my MSDB grew to 30gb on my receiver (since it wasn't processing it correctly nor acknowledging messages properly), and up to 12gb on the senders. That could be.... nerve-inducing to people. (And is part of why I say this is a 300-level class - we're not solving problems that 200-level DBAs have, and when it goes wrong it's more complex than most problems)

So there are a few questions there:
* how fast does it grow
* how can you detect
* how fast can it drain
* is there a faster/better way to drain it (I did the SET NEW_BROKER on my receiver box, which IMHO was a mistake; still trying to clean things up. See Stack Overflow)
* how can you prevent it (this one appeared to be a SQL installation Gone Wrong - patching it to SP3 fixed it)

As for Always On - another good question. I would expect it to - after all, AO is using Service Broker behind the scenes. Looking online, I see...
* you have to include "LISTENER_IP = ALL" in your endpoint definition
* you need to make sure that both servers allow the proper CONNECT permissions
* you'd need to point at the listener name/IP

A couple articles I found to start with:
https://msdn.microsoft.com/en-us/library/hh710058.aspx
http://www.sqldiablo.com/2012/04/15/service-broker-alwayson-availability-groups-odd-transmission-queue-behavior/ (that one mentions bugs in SB on 2012)
https://technet.microsoft.com/en-us/subscriptions/hh710058.aspx

I've added that to my list of Things To Do, (documenting ruining and fixing it, speed of growth, dealing with stopped queues and the like), along with a script to build it out in Azure on VMs to that people can muck with it in a pre-fab lab environment, dealing better with 2005, and answering a couple other questions from 24HOP (WMI service, SQL Express).