Brand new SQL Server Inventory system

I am back working as a DBA after trying a supervisory role as an IT Quality guy. We won’t get into that. Instead, I’m starting a new way for me and the DBA Team to inventory and track our SQL Servers, Instances, Databases, Disks, Data Files, SPN’s, Logins, Connections, Applications, and other great information (such as Licensing!).

I had previously written a number of Powershell scripts that polled the SQL Servers in my charge, and dumped the data to a set of Excel files. I have since started a new iteration that polls a larger set of details and stores them in a SQL Database. Inventory 2.0 if you will

This post will be the master post – like a table of contents where I link to each of the parts of the system. The goal of this system is to track the inventory, it is not designed or intended as a monitoring, performance troubleshooting, or alerting system – there are many free and paid for systems for that. We use Solarwinds where I work and I have Solarwinds DPA to do in depth analysis of those details and to fire alerts.

The requirements as they are today:

  1. Centralized system to track any Servers with SQL installed including VM’s, Physical standalone servers, and HA Clusters.
  2. A Web front end for viewing and some limited data entry, modification abilities (we’re DBA’s that use this, connecting and firing off a few SP’s to manipulate data is trivial for us).
  3. A set of Powershell scripts to collect the data.

About our environment. We have about 100 servers and approximately 180 instances. These are scattered around the globe, in Americas North and South, in the UK and in Europe, in Russia, China, South Africa, Australia, India, and China. All this for the most part is connected via a massive MPLS network. We also have a few servers in Azure and two in a Colo datacenter. The Azure DB’s are not connected to our network, we use a software VPN client to connect to them (this will change as some point). I do not have a solution yet to actively poll those systems. The two servers in our Colo are connected to our MPLS network and I can poll those regularly. They are on a different domain and there is no trust to our internal domain. To address that I do have the concept of creating and securely storing “Alternate SQL Credentials” to connect and poll the SQL Servers on those instances using a SQL Login.

On each SQL Instance that we support, there is a DBA helper database that performs all the needed maintenance, including Backups and retention management, Index and Stats, an Implicit Conversion tracker, DBCC Check DB handling and some other things. It also performs some local Inventory aggregations, such as calling out to Powershell to poll the physical disks, Cluster details, and Login and user tracking.

Coming soon will be the schema for the Inventory database, and the first couple of powershell scripts that poll the Server via WMI, and the instance via WMI.

Here’s the first post, which talks about the first set of the Inventory Schema and a PowerShell script to poll for Server Details.
Here’s #2, which talks about the physical disk details via Powershell and WMI calls for each server in the Inventory System.
Here’s #3, which talks about the script to poll each instance via SMO.

3 thoughts on “Brand new SQL Server Inventory system”

Leave a Reply

Your email address will not be published. Required fields are marked *

*