Microsoft SQL Server Licensing
One challenging product to license has been Microsoft SQL Server. When it installs, regardless of the edition, it leaves behind the same Programs entries, the same registry values. Regardless of whether you install Enterprise, Standard, Developer or Express, the same executable(s) are run. This makes querying off the inventory difficult if not impossible to determine what edition of SQL you need to purchase.
There is one place in the registry where the current edition of SQL Server is stored, and that is in the instance description. The problem is that the registry key to search for is tied to the instance name, so there really is no way to predict what the exact key will be ahead of time, so you cannot configure the inventory scanner to gather it.
To alleviate this problem, Discovery Services has the ability to gather this information.
If you configure a Discovery Services configuration to gather this information, the Data Translation Services Licensed Software rules will be able to correctly assign the edition of SQL Server that is being used.
To configure SQL Discovery
The first step is configuring an address object that will only run a discovery on devices running SQL Server.
1. In Discover Services Right click on Addresses – All Addresses and select Add Address.
2. Give it a name.
3. Then select Management Suite Query as the type of address and select All SQL Server Machines from the drop down list.
(This is a prebuilt query that installs with Discovery Services.)
1. Right click on Logins and select Add Windows Login.
Note : This is the Windows Login that will be connecting to the SQL Servers via WMI to read the registry. This user must have admin access to these devices. You may create as many login objects as needed.
1. Right click on Configuration and select Add Config.
3. 2. Give the config a name, select WMI and click next.
4. 3. Click Next
5. The next dialog is the Addresses for this config.
4 .Select the address created in the first step of this document and click next.
6. 5. Next select the logins created in step two and click Next.
7. 6. On the WMI groups dialog, delete Full Scan from the list and Add in the SQL Server Instances group. This will only gather the SQL Server instance information, not all the information a full scan would.
8. 7. Click Next and Finish the config.
Then run this configuration.
9. 1. Right Click Run now
It will scan all devices in the query and if it can connect, pull back the instance information. This information will be stored under the Computer.Managed Planet.SQL Instances class in inventory.
In 9.6 SP2 it is listed in Software > Additional Detection > SQL Instances
The Licensed Software rules will then use the Edition attribute to determine what kind of SQL installation this is. These rules are run nightly by the Software Manager Service.
It is suggested this configuration is scheduled to run periodically to keep the data current.
There are some instances where the data will not be able to be gathered from a SQL server. You can use the following document to run the same process that discover service uses as a powershell script:
If you want to test out WMI connectivity to you SQL server, you can use this article to test that connectivity:
The SQL Server Instances group looks through the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\ and finds the list of instance names on the sql server. From there it will search for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Setup. In that location it will find the version and the edition of each SQL Instance on the device and send it along to inventory.