Printed 2017/08/16 06:03PM

The Importance of a SQL Server Inventory


Pop quiz!

  1. It’s time to true-up with Microsoft, what are your current license counts?
  2. Operations needs to schedule maintenance on ServerY, what applications will be impacted?
  3. A developer wants to change the password for a certain login, what linked servers will be affected?
  4. Are there any databases that haven’t been backed up in a week or more?
  5. How has DatabaseZ grown over the past year?
  6. Which logins have sysadmin rights?
  7. Based on your current database growth rate, when will ServerX run out of data disk space?

Building a SQL Server inventory

In a small shop with only a few servers, answering these questions might not be so difficult.  But if you’re managing tens or even hundreds of SQL Server instances, you’re going to need an organized system for tracking this kind of information.  And let’s face it, big shop or small, would you rather spend your time counting licenses or tuning performance?  Thought so.

That’s why I highly recommend that every DBA take some time to create an inventory database for their SQL Server instances, and an automated method for keeping it up to date.

What do I collect?

What I collect for my inventory has evolved over the years as my knowledge of SQL has grown and more questions have come up.  At the moment, the information I gather can be divided into 2 categories:  Server-related and SQL-related.

Server info

SQL Server info

How to collect it?

When I first started building my inventory, I used SSIS. I wasn’t well versed in SSIS, to be sure, but on the other hand, I wasn’t collecting much information. In fact, when I started out, my intention wasn’t even to build an inventory, I was merely collecting up all of the Job statuses to compile into a daily report. So even though I had very limited knowledge of SSIS, it was enough to get the job done. Over time, however, as I began gathering more and more information, my SSIS package got a bit unwieldy for me. When the time came to completely overhaul my load process, I decided to ditch SSIS and rewrite it from scratch in PowerShell.

So what’s the best way to gather all of this information? Whatever way works for you. There’s no one perfect tool. Ultimately, you’re going to be responsible for the care and feeding of this beast, so use something you’re comfortable with, or at the very least something you’re interested in learning.

What can I do with it?

The biggest use for an inventory is obviously reporting.  All of this data isn’t worth much if no one can see it.  In my environment, I’ve created a series of reports using SSRS to present the data in a meaningful fashion.  The reports are subdivided between those that are scheduled to run on a regular basis and are automatically disseminated, and those that are run on an as-needed basis.

Scheduled reports

On-demand reports

Not just a pretty face

Reports are great, but my inventory database also has practical uses.  With the help of some PowerShell and SQL scripts, I’ve been able to automate various routine, and sometimes odious, tasks.

What about you?

So how about it, do you keep a SQL Server inventory?  What data do you collect?  How do you maintain it?  And, most importantly, what cool things do you do with it?

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.