- It’s time to true-up with Microsoft, what are your current license counts?
- Operations needs to schedule maintenance on ServerY, what applications will be impacted?
- A developer wants to change the password for a certain login, what linked servers will be affected?
- Are there any databases that haven’t been backed up in a week or more?
- How has DatabaseZ grown over the past year?
- Which logins have sysadmin rights?
- 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.
- 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.
- 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.
- 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?
I also collect system.dm_db_partition_stats and sys.dm_db_index_physical_stats to help to estimate future needs.
Good point, alzdba. That’s a much more fine-grained approach than I’m currently using.
Great Colleen !
I collect almost the same information except for proxies and linked servers, but I do have a daily overview of the windows event log errors from system,security and application logs.
I never used SSIS, but started from the MAk & Yan Pan book, added some extras from Laerte Jr and then anything Brad McGehee and others were advising to monitor, that I could find with SMO.
I don’t master SSRS yet, so I made some PHP pages to show the inventory tables and another Powershell script to collect important information and mail that every morning to our helpdesk colleagues.
For real time monitoring I have an icinga setup on debian, which emails, sms and tweets us when a disk has < 15% free space, when Sqlserver or SQLagent service stops and other urgent problems occur.
Good article. Have you “Hallengren’d” a solution so we can try it out, and benefit from your knowledge and experience with powershell and SQL Server?
This is what we called a RUNBOOK! To keep up with all SERVER/instances info. All info related to Logins, users, roles, DBs and Files(Filegroups) and sizes plus ProductLevel, Edition, ComputerNamePhysicalNetBIOS, EngineEdition, IsClustered, IsIntegratedSecurityOnly,
LicenseType, SqlCharSetName, SqlSortOrderName. The best approach I find is using POSH. Don’t even need to use Linked Servers and even faster.
Also, is very important to keep at least 3 generations of all of the jobs/steps and schedules for every server/instance. I found that sometimes some other DBAs by mistake, overwrote the msdb DB and some jobs were all gone.
I’d do the same with users by DB/Instance. Some DBAs refreshed non prod databases with prod databases and forgot to back up the users and simply overwrote them with the ones coming from PROD. So I also collect all of the users with their corresponding roles or granted access and keep at least the last three generations all in a table within a DBA database in a server designated for this purpose.
I also have tracked file growth thru POSH and also put together an assembly in C# to track DISK/MOUNTPOINT consumption. This is very handy for trend forecasting.
In addition to some of the items that mentioned in the article we also have monthly reports with number of servers vs. instances, free databases space vs. used space, number of the databases by department, by usage (prod/test/dev/qa/archive), by DB systems (SQL Server/Oracle). The inventory itself has the following additional information: application support contact, vendor contact, vendor’s documentation for the database setup, database migration documentation, department-owner, usage type (Prod/test/dev/qa/archive), application connection settings, availability requirements, user setup info, application version, date in production, date decommissioned, reference to the change ticket for the database creation, ports for the instances.
SGolovko – I like your addition of the application availability requirements. I don’t keep application-level information like that in my repository. That info is maintained separately, but availability requirements would definitely be something to consider adding there. Thanks!
Jorge – Good point about keeping a history of job definitions. In fact, that makes me think about also collecting job schedule information. That’s a good way to see, at a glance, when maintenance jobs are scheduled.
Jeff – I wouldn’t say I’ve gone so far as to “Hallengren” a solution, but I’d be happy to post what I’ve got once I clean it up a bit.
With regards to sqlagent jobs, we script every database at least once a week. Same for all sqlagent jobs for an instance.
These scripts are being saved in the same safe zone as our db backups, so we have a history of 2 weeks by default and hold a long term copy every 6 months for 2 years.
If you script those things, don’t forget Service Broker objects, as they don’t appear in a regular db script.
Double check if your sqlagent alerts are being scripted.
You may also want to take a look at the MAP Toolkit. There have been some enhancements for SQL 2012.
I’ll join Jeff in waiting for the post of your solution. Always looking to add to the toolbelt.
Pingback: Building a SQL Server Inventory - Part 1 | Cleveland DBA
I read your pages on inventory management and I absolutely agree with you on the importance. I’m new to the distributed platform being a mainframe DBA mostly these past 25 years. I’ve been asked to focus on SQL server this year and the first thing I wanted to do was to look at our inventory of servers, databases and applications. Much to my surprise, we have some old Excel spreadsheets that I was pointed towards.
I very much would like to download your scripts. When I tried to cut and paste them, there were no carriage controls for some reason. I tried to go to your toolbox but there was no link. I saw a reference in one of your notes to another inquiry that your have a zip file with the scripts. If I am correct, could you let me know how I could get a link to it from you? I would greatly appreciate the help.
Thanks so much,
Hi Paul – There actually are links to download then scripts on the Toolbox page, but the new WP Theme I’m using doesn’t make them stand out. I’ve edited the page to highlight the links to download any of the tools on the page. Let me know if the links don’t work for you.
Thanks very much for the reply. I’ve got the scripts. You’ve saved a newbe countless hours of work and given me a great learning opportunity. This is a big jump start to my introduction to the technology. Thanks for sharing!