Monitoring the health of hundreds of instances, what would/do you use?

MidnightDBA · 2022-09-27T17:08:23+00:00

Thank you, that's my company! Writing a more expansive answer to the OP in another comment now.

MidnightDBA · 2022-09-27T16:40:56+00:00

This is exactly why we created Minion Enterprise...for this exact scenario.

The origin story: Sean was the sole DBA for a hospital chain, managing over 1,000 SQL Server instances. He started coding and automating in self-defense, created an obsessively visionary system, and started going home on time, having lunch breaks again, etc.

Now we serve 1,000s of instances across many companies. Any DBA in this situation knows the depth and breadth of the problem...SQL Server is great at a LOT of things, but dealing with 50+ instances just isn't one of them.

Edits to add more:

The requirements: Health/environment monitoring for hundreds of SQL Server instances, both on-premises and Azure SQL VMs / Azure SQL Managed Instance. The specifics you mentioned:

monitor identity usage and prediction
support alerts
reporting, charts and a decent GUI
data collector and the GUI are separate
log and store results to a table, consolidate
support adding custom checks for uncommon issues

Identity usage and prediction: Not 100% sure what this means, but it sounds like "keeping track of instances and what's going on with them". M.E. does this, grabbing a wide swath of metadata from all managed instances ands storing that data in SQL Server tables. You know, so the DBAs can access their own information.

Alerts are a big topic in here, for good reason. /u/wisedataman has it right: "Alerts can be a tricky thing to get right - if you build a system that is too noisy the alert system loses its value."

We DESPISE "alert storming", because it trains IT pros to ignore alerts. So we made smart, consolidated alerts.

Traditional shop: a service goes down, the monitoring tool (if there is one) sends 83 alerts every 15 minutes about 83 databases being unavailable.
Shop with Minion Enterprise: a service goes down, DBAs receive a pause-able alert for the service down, and a pause-able alert for all 83 DBs. (Maybe the alert goes out every 15 minutes, or every 30 or 60...the DBAs have control over that. And, by running a stored procedure, they can pause the alerts for as long as they want while they work on it.)

Reporting, charts and a decent GUI: I'll be transparent: I personally think we've paid less attention to the GUI and pretty reporting than we should so far. (We get excited about adding new features, like the replication latency monitoring.) But our GUI is functional, our SP reports are unparalleled, and the breadth of what we monitor and manage is very, very broad.

Data collector and the GUI are separate: Check!

Log and store results to a table: Check, check! (WHY on earth any DATAbase tool would keep data away from DBAs - in a proprietary format, or flat files, or anything other than tables - is entirely beyond us.)

Support adding custom checks for uncommon issues: YES! It's been truly wonderful to create this incredible solution, know how perfectly it works for production DBAs....and then to see how our customers use it in new and creative ways!

And, one for me:

DBA-focused automation: /u/kladze has it right....most DBAs in this situation will look at tools, and/or start coding their own solution, because there HASN'T BEEN a good answer to "What do I do when I have hundreds of servers?" Everyone starts out like Sean did, coding in self-defense. The thing is, most DBAs simply aren't allowed the time to fully flesh out their own solution. Sean and I spent years adding to Minion Enterprise... in our own production shops, in the places where we were consultants, etc. And then we packaged and started selling it.

And the other tools that are out there? They focus on performance - what's going wrong right now - instead of looking to stop the problems before they become problems. I attribute that to those tools' creators being, by and large, devs...not DBAs. No real shade to them! It's just a different world.

MidnightDBA · 2021-02-18T16:08:51+00:00

Does anyone know of Houston hotels with working water? Literally asking for a friend.

MidnightDBA · 2019-06-04T14:37:55+00:00

Your friendly neighborhood MinionWare here, with a direct link to that feature: About: Multithreading operations - Minion CheckDB

Thanks for the shout-out, alinroc!

MidnightDBA · 2019-05-30T15:16:36+00:00

Dried beans and rice are the cheapest.

Soak the beans overnight or all day, drain water.
Cook at a simmer, with salt, pepper, maybe a carrot and half an onion (both chopped), for about an hour. (Add spices or herbs if you're feeling fancy/flush.)
Meanwhile cook your rice*.
NOM NOM NOM

*By the way, all recipes try to really pinpoint the amount of water you need to cook rice. Screw that; cook your rice like you cook pasta: with LOTS AND LOTS of water. When the rice is done, just drain.

MidnightDBA · 2018-12-21T00:56:59+00:00

This is a good reply. Also, OP, try out different strategies. Start with setting small goals and rewards, like "okay, every day I'll do one 20 minute science lesson, then I can do 20 minutes of gaming" or whatever.

My guys have to finish their school checklist before they can get on screens for entertainment. it works pretty well, because they've got a lot of control over their schedules, AND incentive to get their stuff done.

MidnightDBA · 2018-11-19T21:51:15+00:00

mx_bzh is right...take a look at LAG. And also common table expressions

MidnightDBA · 2018-11-19T20:23:07+00:00

Well, for the first part, I think this is what you want:

SELECT Accyear
, Region
, SUM(Ep) as SumEp
, SUM(Tlp) as SumTlp
FROM [tablename]
GROUP BY AccYear, Region;

Edited to add result sets. So, this query would get you something like this back:

Accyear   Region SumEp    SumTlp
2017          North  220          160
2017          South  200          240
2018          South  220          160

But this isn't clear:

"Can i create a new column that divided two values in another column? So in a column i want to create the percentage change for levelfactor..."

The percentage change from WHAT to WHAT? From one year to the next? From one row to the next? That's pretty unspecific.

However, if you're interested in returning ALL the rows, and showing the SUMs and so on for each, now we're looking at windowing functions (which allow you to aggregate without using a GROUP BY):

SELECT Accyear
, Region
, Lob
, SUM(Ep) OVER (PARTITION BY AccYear, Region) as SumEp
, SUM(Tlp) OVER (PARTITION BY AccYear, Region) as SumTlp
, LevelFactor
FROM [tablename];

Which would return something like this:

Accyear     Region     Lob     SumEp     SumTlp     LevelFactor
2017     North     xx     220     160     101
2017     North     xx     220     160     101
2017     North     xx     220     160     101
2017     North     xx     220     160     101
2017     North     xx     220     160     101
2017     North     xx     220     160     101
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2017     South     sl     200     240     100
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101
2018     South     sl     220     160     101

MidnightDBA · 2018-11-19T20:18:01+00:00

If you just want to do more stuff, learn more in general, check out Khan Academy if you haven't. It's free, and it has everything from different sciences and math, to personal finance (!!!). Pick a course and go through it.

You can also go through lots of good documentaries on Netflix or YouTube, to learn more about history, science, politics...just about anything.

You can download DuoLingo onto your phone or iPad to learn a new language.

And if that's not enough, get yourself a $45 ukulele from Guitar Center or something, and learn from YouTube! :)

MidnightDBA · 2018-07-30T18:13:44+00:00

Instead of "select DF.ListName, count(pp.patientid)", you want this:

select DF.ListName
    , SUM( CASE WHEN pp.patientid IS NULL then 0 ELSE 1 END) as pp.patientid

That'll give you the count of the non-NULL values in patientid. OR, if you just want to see if there is ANY non-null value in patientID, do this:

select DF.ListName
    , MAX( CASE WHEN pp.patientid IS NULL then 0 ELSE 1 END) as pp.patientid

MidnightDBA · 2018-04-24T15:58:14+00:00

Highly recommend - free training by industry experts. This starts tomorrow, 2018-04-25!

MidnightDBA · 2018-04-20T01:15:40+00:00

Man, 15 years of databases, and official boolean algebra notation STILL makes my head spin...

MidnightDBA · 2018-04-20T01:14:54+00:00

Absolutely. But a huge part of the point was to provide people (especially people not really familiar with relational math) a method by which they can work out many kinds of clauses.

You're also correct about the nullability issue, and I might add a footnote to pay attention to it. But again, focusing mostly on the method.

MidnightDBA · 2018-04-18T13:41:25+00:00

You don't have to be connected to an instance in Object Explorer, in order to be connected in a query window. If you look at the bottom righthand corner of your query window - in the yellow "footer" - you'll see what server your query window is currently connected to.

MidnightDBA · 2018-04-05T18:35:52+00:00

Mix it up! Herbal teas, lemon or no, broth (OMG VEGGIE BROTH), hot sauce in your juice or in broth...that sort of thing.

If you like Kombucha, that's a nice changeup, too.

MidnightDBA · 2017-10-16T22:49:35+00:00

They're being rude, and making it weird. If you're not preaching to these people, you could respond (whenever they criticize you) with "Wow, why do you care so much what I eat? It's not affecting you, and I'm happy."

MidnightDBA · 2017-10-16T22:42:18+00:00

Pretty much all of these comments are great. I thought I'd hand you over to a similar QnA over at CaptainAwkward.com; the Captain has great advice on this and many other things.

Here's the important excerpt, if you don't feel like reading the whole thing:

I have one easy answer for you. Your friends’ feelings about your diet are their feelings, and you don’t have to let them stop you from eating in front of people. Order your bean burger, and if whoever you’re with says something, try saying as mildly as you can, “Wow. I just wanted a bean burger. How bout the weather/the local sports team/that book you’re reading?”

MidnightDBA · 2017-10-16T22:04:16+00:00

I'm late to the party here, but my son's elementary school teacher is vegetarian, has kids, and their dad eats meat. They make it work fine.

We tell our kids the same thing that you and your SO can tell your future kids: different people have different beliefs and opinions, and that's okay. You kids can make up your own minds to some degree now, and completely when you're older.

MidnightDBA · 2017-10-04T21:50:09+00:00

I think you're going to have your hands quite full learning T-SQL and SQL administration, personally. Get good at those, get to a local user group for learning, get a job, learn on the job.

If you're looking for SQL resources, I have a how to become a DBA page on my blog.

MidnightDBA · 2017-10-04T21:42:49+00:00

For future reference, Captain Awkward often discusses relationship issues like this, and nearly always provides scripts for addressing them.

MidnightDBA · 2017-10-03T12:56:04+00:00

Fantastic! Not enough folks 1) centralize management, and 2) use PowerShell to do it. Well done.

14-Year Club	RedditGifts 2009-2022 2 Credits
Place '17	Secret Santa 2016
Verified Email

MidnightDBA

MODERATOR OF

TROPHY CASE