October 17, 2019 at 9:24 pm
Since Twitter's length constraints make answering this problematic, it was suggested I bring this question here.
I'm just starting to dive into Policy Based Management (only a decade or so late) to track a variety of configuration settings across our environment (~60 SQL Servers, mostly 2016 with a couple 2012s and one 2008 R2 thrown in just to be spiky...but with plans to upgrade everything to 2019 next calendar year). I got a hot tip via #sqlhelp on Twitter that PBM hasn't gotten much love from MS, and other monitoring tools might be preferable.
Right now, we're using Idera's SQLdm for realtime monitoring and alerting, but we haven't found a good way to use it to validate settings. But we're also planning to evaluate alternative solutions in 2020, and if I can roll into that process a requirement to do PBM-ish validation, we'd gladly go that route.
The kinds of things I want to monitor and/or enforce are:
And undoubtedly some others; this is just what I've built in PBM so far and what I have left to migrate in from a PowerShell script I wrote a while back.
Any thoughts or input much appreciated!
______
Twitter: @Control_Group
October 17, 2019 at 10:01 pm
Disclosure: I work for Redgate Software and am biased, so be aware. Also, I will not talk up or down anyone else's products. If others have experience or thoughts on products, please feel free to post.
Redgate makes SQL Monitor, which is a way to watch servers and get alerts. The alerting might be our best feature, though there are lots of things to look at. We do have a live version (in demo mode) at monitor.red-gate.com.
One of the things I might do for monitoring these items is to use a custom metric, which you can configure in SQL Monitor (https://monitor.red-gate.com/Configuration/Custom-Metrics). We allow you to mark anything that can be shown as a numeric and track this over time. This is useful for handling tech and business issues and we have a number of metrics written by various people at https://sqlmonitormetrics.red-gate.com/.
While this won't prevent changes, this can be used to track things that you're asking for. You could create custom metrics for each of these, for example, a count of accounts named sa that are enabled. Or, what I'd also want, a count of sysadmin users. If this changes, you can get an alert, similar to PBM.
What I'd likely do, however, is tackle this in the same manner as sp_blitz. That outputs a number of items that you should look at. I used to have this as a custom metric that tracked the count of items. This is because we couldn't necessarily fix, or didn't want to fix everything, but we did want stability. We tracked the count in a custom metric and if it changed, an alert was raised to the DBA team. What I'd do is set up queries to track your items on each instance (or db) and then use a single count with the latest date to track if all your issues are inline with requirements. By having this on each server, you can customize an instance to be different from the others if you have a need.
Then use a custom metric to track this. You could do this all in SQL Agent, and just use dbmail to notify you if anything changes, but if you need a monitoring tool, you'll want to integrate things where you can. You might even end up making a few different queries/metrics that track things that are important in areas, like security issues, config issues, etc.
If this doesn't make sense, let me know.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply