Microsoft SQL Server: Inventory Discovery, Normalization and Analytics Support on SAM Pro

[SrinivasRamanu1]

ServiceNow supports the discovery of MS SQL Server database and component services. To understand more on MS SQL Server, you can check the website here. To understand details about different editions and component services, you can check the website from Microsoft here. 

The aim of this blog is to understand how ServiceNow discovers SQL Server DB instance and component services and how does SAM Pro determine Licensability of these SQL Server deployments. Finally, we show how customers can view all this information comfortability through smart analytics on Software asset analytics. 

Discovery using ServiceNow Discovery 

In this section , we will display the details about the solution using ServiceNow discovery. Please note that you can use other discovery sources such as Service Graph connector for SCCM, AWS (Amazon Web Services), Azure, GCP (Google Cloud Platform) etc., ACC-V etc. . However, there could be differences in details. 

High Level Solution using ServiceNow Discovery with SAM Pro

*2 entries created on the software install table as one is created by generic discovery and other by application pattern discovery. The Application pattern discovery helps determine the edition correctly and is used for reconciliation purposes. The install created by generic discovery is ignored. 

**Edition Licensability allows editions to be licensable or non-licensable. You can check the latest on Product license exception rules: samp_prod_lic_excep_rule. 

Infrastructure discovery  

Based on the running process discovered, ServiceNow Discovery creates a record on the DB instance table on ServiceNow with install status as installed. As soon as a record is created on the DB instance table- a record is created on the software installation table.

• Install records signify deployment and are important for calculating correct license compliance position. Hence, it is important to delete the software installation when the SQL Server DB is removed from the infrastructure.  

• To understand more details about the install creation and deletion process. Please check the blog here 
• Note that with the SQL Server DB the edition and version of the SQL Server database instance are also discovered 

SQL Server DB instance discovery on ServiceNow

• Likewise, the discovery of SQL Server component services like Analysis Services and Integration services along with their edition is crucial for correct license compliance calculation. 

• Please note that it is essential to discover SQL Server components such as analysis services, integration services etc. along with their Editions for correct license compliance results. Please use the latest discovery and service mapping pattern to discover the same. Edition discovery helps associate the right SQL Server license to the components. 

To understand more details about SQL Server discovery, please check here 

Installation record creation on SAM (Software Asset Management) Pro 

Based upon the SQL Server DB and component service discovered, ServiceNow SAM Pro will auto create an installation record on SAM Pro.  

However, through ServiceNow discovery 2 records will be created. This is as per design, and will not lead to wrong license compliance calculation, as one of the records will be marked as active= false. 

This happens due to one records created by generic or registry-based discovery and other through application pattern-based discovery. 

Please note that when you use other discovery sources like SG- SCCM, only 1 record will be created.

Normalization and content support 

Based on the install record created, ServiceNow SAM pro will auto normalize these records based on the large content support provided. Through the content, the installation records will be standardized and valuable information such as licensability, suite components, downgrades, end of life information will be automatically updated. 

You can check the Product type Licensable- to determine that SQL Server Standard edition is a licensed product. 

In addition, you can check here: SQL server express edition is non licensable. To check the list of non-licensable edition you can check the product exception table.

Inventory and Normalization analytics on SAM Pro

Finally, SAM Managers can view this information through smart analytics on Software asset analytics-> Discovered Inventory Read more about it here . On this page, you can filter the inventory details for a specific product such as SQL Server and analyze all required inventory information.

[dreinhardt]

Hi @SrinivasRamanu1,

Thanks for this blog article, again really helpful!

I miss a use case about standalone SQL components (e.g., Reporting Service SSRS). Could you please add a few details about this.

Thanks and Best, Dennis

[dineshthaku5524]

Thank you so much for this article, helps a lot! 🙂

[Pedro31]

That is very helpful, thak you!

[BradP-InnoSolv]

I would like to understand how the cmdb_ci 'environment' field (Production / Development / Test) is taken into consideration.   Are there out of the box scripts during reconciliation that refer to this or do we need to manually configure this when needed?  Any additional insight would be helpful.

[dreinhardt]

Hi @BradP-InnoSolv,

the feed for the „Environment“ field is external and isn’t provided by Discovery itself. Of course, you can create your own rules based on CI dats to publish this field (e.g., device name pattern identifies a CIs environment, name, DNS, ….).

To use the benefit of this field for the recon you can a) define a software install condition per software model or b) a product install condition to cover the whole product with such a rule (e.g., Visual Studio is used only for DEV)

The recon will taken this into account and calculates the balance based on this (will include or exclude devices based on the environment).

Best. Dennis 

[tiagomacul]

https://www.servicenow.com/community/itom-articles/servicenow-sql-server-discovery-troubleshooting-t...