Troubleshooting SQL database discovery in System Center Operations Manager

Applies to: System Center 2016 Operations ManagerSystem Center 2012 R2 Operations ManagerMicrosoft System Center 2012 Operations Manager

What does this guide do?
Helps administrators diagnose and fix SQL database discovery issue in System Center 2012 Operations Manager and later versions. 

Who is it for?
Administrators who manage System Center Operations Manager.

How does it work?
By using the SQL Server 2012 database discovery in System Center 2012 Operations Manager as the example, we’ll take you through a series of troubleshooting steps that help you understand and fix discovery issues.

Estimated time of completion:
20-30 minutes.

Before you start troubleshooting, it’s important to check the following:

  1. What is the name and class of the object that needs to be discovered?

    For SQL database discovery, the SQL database class is specific to the version of SQL Server that hosts the database. If you use SQL Server 2012, the name is SQL server 2012 DB.
  2. Is the appropriate Monitoring Agent installed?

    If the agent isn't installed, follow Install Agent on Windows Using the Discovery Wizard to install the agent.
  3. Is proxy enabled on the agent?

    You must enable agent proxy for SQL database discovery.
  4. Is the latest version of SQL Server Management Pack installed?

    Most objects are version specific, for example, the Management Pack for SQL Server 2012 may not work on a SQL Server 2012 R2 instance. Additionally, the latest version of Management Pack contains fixes for known issues in earlier versions. Installing the latest version sometimes fixes the SQL database discovery issue.

Before you start troubleshooting, it’s important to check the following:

  1. What is the name and class of the object that needs to be discovered?

    For SQL database discovery, the SQL database class is specific to the version of SQL Server that hosts the database. If you use SQL Server 2012, the name is SQL server 2012 DB.
  2. Is the appropriate Monitoring Agent installed?

    If the agent isn't installed, follow Install Agent on Windows Using the Discovery Wizard to install the agent.
  3. Is proxy enabled on the agent?

    You must enable agent proxy for SQL database discovery.
  4. Is the latest version of SQL Server Management Pack installed?

    Most objects are version specific, for example, the Management Pack for SQL Server 2012 may not work on a SQL Server 2012 R2 instance. Additionally, the latest version of Management Pack contains fixes for known issues in earlier versions. Installing the latest version sometimes fixes the SQL database discovery issue.

The target of SQL Server DB discovery rule must be discovered before the discovery rule runs. In our example, the target of SQL Server 2012 DB discovery rule is SQL Server 2012 DB Engine.

How to determine the target of the SQL Server 2012 DB discovery rule

To do this, follow these steps:

  1. In the Operations Manager console, go to Authoring > Management Pack Objects > Object Discoveries.
  2. Click Scope, select View all Targets, and then click Clear all.
  3. Input SQL Server 2012 DB in Look for, check SQL Server 2012 DB, and then click OK.

    Search target
  4. Locate the target of returned discovery rule (the Target column), in our example, the target is SQL Server 2012 DB Engine.

    Target

How to determine if the target object is discovered

To do this, follow these steps:

  1. In the Operations Manager console, go to Monitoring > Discovered Inventory.
  2. Click Change Target Type… in the Tasks pane.
  3. Select View all target, input SQL Server 2012 DB Engine in Look for.
  4. Select SQL Server 2012 DB Engine, and then click OK.

    Find target
  5. In Discovered Inventory, look for the name of the SQL server or the name of the cluster that hosts the database. If you can't find any of them, you need to troubleshoot the DB Engine discovery rule.

If the Windows agent shows a gray state, follow Troubleshooting gray agent states in Operations Manager 2012 to fix the issue.

To do this, follow these steps:

  1. In the Operations Manager console, go to Authoring > Management Pack Objects > Object Discoveries.
  2. Click Scope, select View all targets, and then click Clear All if it isn't greyed out.
  3. Check the SQL Server 2012 DB checkbox in Look for, and then click OK.
  4. Right-click Discover Databases for a Database Engine, and then select Choose Overrides > Summary > For the Object Discovery.
  5. Review any overrides that may affect the discovery rule.

    Overrides summary
      

The SQL Server 2012 DB discovery uses the SQL Server Discovery Account Run As profile. To check whether the Run As account is associated with this profile, follow these steps:

  1. In the Operations Manager console, go to Administration > Run As Configuration > Profiles, and then open the properties of the SQL Server Discovery Account profile.
  2. Move to the Run As Accounts page.

    Run as account


    If a Run As account is listed in this page, it's the account that is used to run the discovery. If nothing is listed, the default Run As account on the server is used to run the discovery. To find the default Run As account on the server, follow these steps:
    1. In the Operations Manager console, go to Administration > Run As Configuration > Profiles, and then open the properties of the Default Action Account profile.
    2. Move to the Run As Accounts page, locate the server name in the Path column, and then note the corresponding account in the Account Name column.

      Default action account


      Note This account is usually the Local System Action Account, sometimes it may be a user account.

Make sure that the Run As account has the required SQL permissions. For necessary permissions, check the SQL Server Management Pack guide.

Look for the following events and errors:

  • Events that reference the discovery rule workflow. The discovery name is Discover Databases for a Database Engine, and the workflow name is Microsoft.SQLServer.2012.DatabaseDiscoveryRule.
  • Errors that are related to the Run As account. These errors may occur after the Health Service restarts.
  • Errors that are related to the DiscoverSQL2012DB.vbs script. Here are some examples:
    • Event 21405

      To fix this issue, change the port number in the SQL instance from 60005, 1433 to 60005,1433. The additional space causes the command line to pass incorrect variables. 

    • Event 7103

      To fix this issue, make sure that the Run As account has required permissions. For necessary permissions, check the SQL Server Management Pack guide.

  1. Enable verbose ETL tracing, and then override the discovery rule to force it to run more frequently on the problematic agent.

  2. Review TracingGUIDSNative.log and TracingGUIDSScript.log for events that are associated with the discovery. Look for events that reference the DiscoverSQL2012DB.vbs script or the Microsoft.SQLServer.2012.DatabaseDiscoveryRule workflow.

  3. For script-based discovery, the TracingGUIDSScript.log also contains the parameters that are used to start the script. Check whether the discovery data (in XML format) is returned.

  • If the missing objects aren't included in the discovery data, there may be a permission issue or an issue with WMI.

    For example, if the Run As account doesn't have the necessary permissions, you may get an output like the following:  

    <DataItem type="System.DiscoveryData" time="2018-01-29T14:27:53.0318929-05:00" sourceHealthServiceId="D5E3AD1A-589F-DDE5-B4AE-18D955BE5408"> 
        <DiscoveryType>0</DiscoveryType> 
        <DiscoverySourceType>0</DiscoverySourceType> 
        <DiscoverySourceObjectId>{21A3C28F-B3CB-59A1-54C4-73232A9BA7EE}</DiscoverySourceObjectId> 
        <DiscoverySourceManagedEntity>{26B3B2EF-806B-6EA4-35DD-E669C83E36C8}</DiscoverySourceManagedEntity> 
    </DataItem> 

    To check WMI issue, follow these steps:

    1. On the SQL server, open WBEMTEST.

    2. Connect to root\Microsoft\SqlServer\ComputerManagement11.

    3. Run the following query:

      select * from SQLService where SQLServiceType=1

      SQL query

    4. If you receive a WMI error or no output, make sure that you have a backup of the server, open an elevated command prompt, and then run the following command to repair the WMI namespace:

      mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof" 
  • If the missing objects are included in the discovery data, review the following event logs:
    • The Operations Manager event logs

      Look for events which report that discovery data is dropped. For example, event 5000 or 4506.

      When there's too much data in the sending queue, data may be dropped. To fix this issue, increase the size of the queue on the agent.

       

    • The management server's event log

      Look for events which report that discovery data is dropped, or reference SQL Server performance issues which prevent timely writing data to the Operations Manager database.

If no discovery data is returned, the reason may be that returned data is too large. Operations Manager discoveries have a size limit of 4MB, if the data item is larger than 4MB, it's dropped without any warning.

In this case, following these steps:

  1. Export the script from the Management Pack

    1. Export the Management Pack from Operations Manager by running the following command from an Operations Manager Shell instance:

      get-scommanagementpack -Name Microsoft.SQLServer.2012.Discovery | Export-SCOMManagementPack -path c:\temp

      Note Make sure that you specify a valid path. Don't include the Management Pack name in the path.
    2. Locate the Microsoft.SQLServer.2012.Discovery.xml file in the c:\temp folder, and then open it in Notepad or an XML editor.

    3. Search for <ScriptName>DiscoverSQL2012DB.vbs</ScriptName>, copy the content of the <ScriptBody> element, and then paste it to a new text file.

      script body

    4. Remove the start tag <ScriptBody> and the end tag </ScriptBody>.

    5. Find and replace the following characters in the file:

      Replace &lt; with <
      Replace &gt; with >
      Replace &amp; with &

    6. Save the new file as DiscoverSQL2012DB.vbs.

  2. Manually run the script
    1. Look for a line that's similar to the following in TracingGUIDSScript.log:

      "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2012DB.vbs" {21A3C28F-B3CB-59A1-54C4-73232A9BA7EE} {26B3B2EF-806B-6EA4-35DD-E669C83E36C8} 7103 MS-SQL.contoso.com MS-SQL.contoso.com  MS-SQL MSSQLSERVER "Exclude:" "1433"

      Note This is the command that's used to run the DiscoverSQL2012DB.vbs script.

    2. Copy this line, and then paste it in command prompt to run the DiscoverSQL2012DB.vbs script.

Congratulations! Your SQL database discovery issue is fixed.

For more information about System Center Operations Manager, post a question in our forum here.

For all the latest news, information and tech tips, visit System Center Operations Manager Team Blog.

Unfortunately we are unable to fix your issue by using this guide. For more help, post a question in our forum or contact Microsoft Support

For all the latest news, information and tech tips, visit System Center Operations Manager Team Blog.