Scanning Every Server with Dynamic Reports

I am really big on using a couple of methods to scan every server in my environment with a huge array of queries. Why? because

There’s the classic purposes to doing this.

  1. Find exceptions to best practices. everything from checking if the [sa] account is disabled, configuration items like maxdop and cost threshhold, growth settings, max file settings, etc on individual database files, literally hundreds of items in the environment that can make performance and your go faster anywhere from a 1 % to Order of Magnitude difference in the way your server behaves. Fixing , say a dozen items like that can literally offset the need for more hardware, and greatly improve your customer/query experience.
  2. If every server is configured the same way, and every server has the same set of tools, you make your environment uniform. Regularly Checking if specific procs, jobs, tools are deployed(and their checksum to make sure they are the *latest*), along with making sure your best practices are in place reduces the need to check for every possible issue and allows your DBAs to concentrate their efforts on any real issues.
  3. Pulling all the servers information all in central locations for analysis. Having a single spreadsheet with all, say , 270 servers information in a grid greatly enhances your ability to review servers for multiple things.

How do I do it? I have two core methods, but multiple flavors of those methods. Via either a PowerShell script I wrote or an SSIS package I created, that reads all the servers in Central Management Server and Joins that data to a KeePass vault.

I typically output the data to two locations: dynamically creating a new table in a Database for centralized reporting, and also an Excel document created via OpenXML, so that it can be saved to disk each time it runs, and optionally email the report.

For any servers that require a non-domain login, it uses the matching vault credentials to run the queries. Using this methodology, I can scan any RDBMS system, and additionally combine data sets from SQL Server, Aurora MySQL, MongoDB, Snowflake and more for common items like names, logins, resources, and so much more.

Leave a Reply