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.
Here’s #4, which talks about the script to poll database details from each instance.

3 thoughts on “Brand new SQL Server Inventory system

  1. Pingback: New DBA Inventory – Database Details | SQL Blog

  2. Hi Jfay

    Have a nice day,
    Many thanks for your posts for DBA inventory,
    My name is Sherif Mazar, the environment I am working in is utilizing both SQL Server and oracle databases, and I need to collect similar information for both SQL & Oracle on a daily basis and link the collected data to another set of tables related to our business.

    This data will be presented and shared with colleagues through in-house ASP.net application, something like Solarwinds but with a different set of functionalities.
    My concern is, I don’t know PowerShell at all, but when I’ve seen your posts I realized that this is the right way to get things done.

    I’ve followed the first post until creating the InventoryServerDetailsSchema then couldn’t find PowerShell scripts *.psm to download and proceed and when it comes to run PowerShell scripts via SQL Agent job do I need to create job step of type Operating system (CmdExec) or choose PowerShell type.

    It would be highly appreciated if you please send me all PowerShell scripts and files used in your posts along with ordered steps to follow in order to utilize your great posts.

    Your help is greatly appreciated.

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.