As a DBA who has long looked after large, varied and ever-changing SQL Server estates, I believe in the value of having access to a detailed, self-updating inventory.
Over time I have built up a really functional inventory which makes it easier for me to performance manage my instances, make quicker decisions and understand the makeup of the environments I am paid to manage a lot better.
I ‘ve worked for a few different companies and I’ve worked with a hell of a lot of other companies. It’s fair to say I’ve seen a lot of different methods of inventorying SQL Server estates.
- Spreadsheets. Why bother? Who’s going to remember to update them. How are they populated and secured?
- Third Party Software. There are indeed some great products out there and a lot of people maximise them well, but I hate buying off the shelf if I’m honest so I’m not going to go over them here.
- SCOM / SCCM. I’ve seen people relying on Microsoft SCOM and SCCM to keep tabs on SQL installs for them. I mean, I’m all for using SCOM as a mechanism for alerting to system level failures, but don’t rely on it for inventorying.
- SSIS. I’ve used SSIS myself as a way to iterate through instances and collect data. It’s good, but it’s heavyweight and difficult to manage.
- Pen and Paper. Yes, really. Just don’t go there.
I wouldn’t kill you if you were using one of the above methods. It’s not my place to tell you how to manage your environments. What I would do is what someone did to me when I was using SSIS. A gentle conversation about efficiency, a smaller footprint, keeping things simple and PowerShell.
Now I’m not going to do a PowerShell tutorial as such. I’m going to show you how to use PowerShell to interact with the SQL Server Server Management Objects (SMO).
The SMO is essentially a hierarchical collection of namespaces which contain a series of classes, properties, methods we can use to manage SQL Server.
What we’re going to do is interact with the SMO to return information about our instances and databases in order to build a complete inventory (or a catalogue, if you prefer that term) of our SQL Server estate. We’re going to create a structured database in which to house the information we collect.
Our database design will simply follow the particular parts of the hierarchical SMO we’re going to interrogate.
This is a multi-part article which will be released in stages. In Part One, we’ll be looking at Level 1 of the SMO, i.e. Obtaining Instance Level Information.
The subsequent parts will look at completing the inventory as we look to obtain information about databases, security and SQL Agent activity.
So, here’s what you’ll need:
- Access to SQL Server
- Access to Powershell ISE
- An instance of SQL Server to house your new inventory database
When you’re ready, let’s move on to Part One.