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

• System – model, manufacturer, description, domain, system type, number of processors, number of cores, total memory, etc
• Disk – name, label, drive letter, capacity, free space
• Memory – name, capacity, device locator, tag
• OS – OS name, type, version, language, product suite, service pack major and minor version
• Physical Nodes – logical server name and physical server name

SQL Server info

• Instance – instance name, version, service pack level, edition, collation, clustered flag, master database path and log path, root directory, service account, MAXDOP, min and max memory, xp_cmdshell enabled
• Logins – name, date created and last modified, password change date, default database, is policy checked, is expiration checked, expired status, days until expiration, bad password count, bad password time, is locked, SID, login type
• Server roles – name, member logins and SIDs
• Databases – name, ID, status, recovery model, compatibility level, collation, last full, differential, and log backup dates, creation date, mirroring enabled, owner, primary file path, backup directory
• Database users – database name, user name, date created, SID, type
• Database files – database name, file id, type, logical name, physical name, size, growth percent or mb
• Database roles – database name, role name, member users and SIDs
• Backups – database name, media set id, family sequence number, physical device name, start and finish dates, first family number, last family number
• Jobs – name, enabled status, description, creation and modified dates, last run date and status, error message if last run failed
• Proxies – ID, name, credential ID, enabled status, description, date created, user SID
• Credentials – ID, name, identity, creation and modified dates
• Linked Servers – linked server name, remote instance, provider, default database
• Linked Server Logins – linked server name, local and remote logins

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

• Daily report of all job statuses, so we can see, at a glance, what jobs failed overnight.
• Weekly report of the top 10 fastest growing databases and the bottom 10 servers ranked by weeks of disk space remaining, based on recent growth rates
• Monthly report for Operations of all backup LUNs, so we make sure what we’re backing up to disk is actually making it to tape.

On-demand reports

• SQL Server instances and databases that reside on a specified physical server.  The NOC uses the physical server names, not cluster names, when planning outages, so it was important to break this down by physical server name.  While I’m collecting database information, we have a separate database maintained by developers where they can map their application to the database(s) it touches.  I join to that database in my report to provide the applications associated with each database and the developer responsible for each app.
• A license report broken down by SQL Server version, edition, and license type.
• Various auditing reports, including a list of logins with sysadmin privileges, a list of SQL logins and their last password change date, which logins have access to which databases, etc.
• What instance(s) does a particular login reside on and what linked servers would be affected by a password change?

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.

• Automate testing of SQL database backups.   I’ve created a weekly job that randomly selects 10 databases (or whatever sample size I specify) and performs a restore of their most recent full backup to a test server.  The job then performs an integrity check on each restored database and logs everything to a couple of log tables.  I come in on Monday and check for any errors.
• Automate password changes across multiple instances, including updating linked servers.  This is an extension of the report I mentioned above.  Using a single stored procedure I can update the password for a given login everywhere it exists.  Because I’m using a procedure, this means I can also schedule the change to run off-hours.
• Automate changing service account passwords.  Using PowerShell and a comma-delimited list of service accounts, old, and new passwords, I can find what SQL Server instances are using those accounts and change the password in the SQL Server services on those servers, in addition to changing the password in Active Directory.  This is especially handy when we’re changing a bunch of passwords at once.
• Deploy Performance Monitor across multiple servers.  Ok, technically this can be done without an inventory.  I’m only using the inventory for a list of instances and that can always be pulled from a text file.  The advantage of using an inventory, though, is that it should always be up-to-date with your server information.  If you use a text file, you’ll need to remember to update it with any new (or retired) servers.

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?