A while back, I took on a fairly big project; build an database inventory manager that did the following:
- Dynamically gathered information on SQL Instances/Databases
- Dynamically gathered information on the OS underneath
- Compiled and organized this data in a single repository
- Provided a client GUI front-end for ease of use.
- Was built on free and already available tools.
When I initially finished Inventory Manger 1.0 I wrote a 5-part series that took the user through the steps of how I built what I did and how everything worked. This was a good way for me to iron out details and also provide some documentation along the way. As the months have progressed I added updates to the code and altered the posts as necessary.
Then everything went silent as I moved on to other things, but I was constantly going back and adding new features and options. During this interim, I was not able to get the code updated on GitHub, and thus it fell behind. Also, it had morphed into its own beast, moving out of “project” and more into a standard “software” mode. To signify its new direction, its name is now Simple Database Inventory Manager™.
As such, I have added the new 2.1 version to its own GitHub project (previously it existed in the SQL code section there with other unrelated snippets) and will update it accordingly. You can just download the new files and overwrite the old ones to get the new version. Spiffy, right?
I will keep the old 1.x version in the original repository so it doesn’t break links for the walk-through articles.
All that said, what fabulous prizes are in the new version? Glad you asked!
Now you can point to your Central Management Server and the Simple Database Inventory Manager™ will pull a list of servers and instances from there. It will then go through all of them recursively, and pull whatever data you want back.
In DB_DataPull.ps1, you can switch this on or off with the -UseCMS switch. If on, then you will need to specify the CMS Server with the -CMSServer ‘SOMESERVER’ parameter.
If you use the -UseCMS option, this will delete all data from the repository tables and repopulate them based on what you have in the CMS. This is backwardly compatible with the old system in that if you don’t use the option (off by default) then it will continue to use the Server and Instance List you provided manually.
Fresh, New Buttons
‘Services’ has been folded into the Inventory heading and Other Info is no more, replaced by the Reporting section (below).
Composite buttons have been created to give better side-by-side information from the GUI. Server\Instance and Instance\DB buttons have been added to do this. I think their names are pretty self-explanatory. Click on them. See what happens.
Kind of. A bunch of stock reports were added to the DB_DataPull_FrontEnd.ps1 under the Reporting heading. These rely on Views in the Reporting Schema that build customizable information you want returned.
I’m going to leave this schema (Reporting) pretty much alone for now, so users can create their own views and then use those as datasets if you want for SSRS. You can use SDIM™ as the basis for reports that refresh as often as you want to run DB_DataPull.ps1
Here’s a quick list of the out-of-the-box reports given in the GUI:
Servers Grouped by OS and Service Pack – A count of all Server OS names and versions.
Instances Grouped By SQL Version – A count of all Instances at a specific server version (ignores Service Pack)
Instances Grouped by SQL Version, Edition – A count of all instances by SQL Version and Edition (ignores Service Pack)
Instances Grouped By SQL Version, Edition and SP – A count of Instances based on SQL Server Version, Edition and Service Pack
Bug Fixes/Minor Enhancements
- Fixed an incorrectly spelled column.
- Fixed incorrect calculation of Server number.
- Added extended properties to all tables.
- Added header at the top of all Views and Procedures
And All the Rest
This (Simple Database Inventory Manager™) is of course provided free of charge, use-at-your-own-risk. There is no warranty either expressed or implied. If SDIM™ burns down your data center, uninstalls all your favorite toolbars and ruins your best pair of dress socks, I’m not at fault. Remember to back up your databases!
And if you’ve skipped over everything just to get to the link, here it is: SDIMS v2.1