Accurately inventory and report on Microsoft SQL Server Instances with SCCM 2012
It is ironic that Configuration Manager provides such basic inventory capabilities for products such as Microsoft SQL Server and Microsoft Exchange.
This solution addresses the limitations of attempting SQL Server inventory with the native inventory information available.
Specifically this solution provides the following information for SQL Server versions, tested as far back as SQL 2005, and provides the following information for all SQL Instances detected:-
An example of the inventory data is shown below:-
An example of the provided report is shown below:-
A high level overview of the solution is listed below:-
1) The Configuration Item (script) is edited and the registry key path containing the SQL information is added / modified as required (Please see the note above).
2) The Baseline is assigned to a group of devices
3) The baseline executes and creates registry keys for each instance detected in a single registry location and stores all relevant instance inventory information strings.
4) The single registry location (and all child keys and values) is collected as part of the registry collection process and made available for reporting.
The solution zip file contains 2 elements:-
- The Baseline Cab – See the compliance baseline import section below
- The script file – The raw PowerShell code should any alterations be required
Step 1 – Configuration Baseline Import
1. Under Assets and Compliance. Right click Configuration Items and Select Import. Select Add and browse for and select the SQL Inventory.cab file contained within the zip file above.
2. Once the import is completed. Right click the SQL Inventory Configuration Item and edit the SQL Inventory script.
3. Modify the ROOT_PATH variable.
Step 2 – Deploy the Configuration Baseline
1. Right click the SQL Discovery Configuration Baseline and deploy this to a collection of your choosing.
Note: You should always trial anything new in the lab and ensure you check the contents of any files carefully.
Step 3 – Baseline Evaluation and Hardware Inventory
1. During the next baseline evaluation the script will execute and search for and store all instance information in the nominated collection registry key location.
The Registry collection evaluation will run and store all registry information which is collected by hardware inventory and available for reporting.
Step 4- Importing the SQL Reports
Copyright 2016 Esebenza Limited. This program is distributed under the terms of the GNU General Public License.
Esebenza provides all tutorials, code and articles as is with no warranty or recourse to support. Should you experience any issues during deployment Esebenza can not be held liable.