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:
The 24HOP presentation was awesome. Thank you.
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?
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.
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. :)
I unfortunately missed the presentation at 24HOP and am happy to see it was posted here. Thank you!
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
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.
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.
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).
Post a Comment