locked
SQL MP - Issues with Discovery VBS Scripts. RRS feed

  • Question

  • I need some assistance.  We have three SQL servers and one of them (A Windows 2008 STD - SQL 2005 x64 server) is generating warnings when running the SQL discovery scripts as show below in RED.   When I look under Monitors> Microsoft SQL Server>Computers:  the Server has a Green Healthy Circle under State, but blank under SQL 2008 DB Engine and SQL Server 2005 DB Engine columns.  The other servers have a Green Healthy Circle under State and SQL Server 2005 DB Engine.  This tells me since the discovery scripts cannot run properly that SCOM will be unable to determine the DB Engine.

     

    I do not think this is a permissions issue since the other two SQL servers are using the same Run As accounts and have no issues with the scripts.  I also double checked the permissions on this SQL server several times and they are fine.

     

    Here is my Run As account setup:

    1.       A domain user account was setup for this Run As account. 

    2.       This domain Run As account was added to the Local Admin Group and sysadmin for all instances on all SQL servers.  

    3.       In SCOM, I added the Run As account to the following Run As Profiles:

    ·         SQL Server Default Action Account

    ·         SQL Server Discovery Account

    ·         SQL Server Monitoring Account

     

    I followed Kevin Holman’s Article for the Run As setup:  http://blogs.technet.com/b/kevinholman/archive/2010/09/08/configuring-run-as-accounts-and-profiles-in-r2-a-sql-management-pack-example.aspx

     

    I am not sure where to go from here, but here are some things I tried.

    ·         Deleted SQL Server Monitoring Management Pack 6.1.314.36 and re-imported from MSI

    ·         Re-Installed SCOM Agent on troubled SQL server

    ·         Ran the scripts manually, but for some reason there is no output

    ·         Checked WSH Version:  All servers have the same version:  5.7

    ·         Checked and SP3 for SQL 2005 is installed

    ·         Searched all over this forum for answers…

     

    SQL Discovery warnings: 

    Warning 1:

    The process started at 11:46:39 PM failed to create System.Discovery.Data, no errors detected in the output.  The process exited with 0

     

    Command executed:     "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2005RSDiscovery.vbs" {4CC14CC2-E918-1733-726A-32631502C25B} {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18} DB02.domain.corp DB02.domain.corp DB02

    Working Directory:          C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 3\963\

     

    One or more workflows were affected by this.

     

     

    Workflow name: Microsoft.SQLServer.2005.ReportingServicesDiscoveryRule.Server

     

    Instance name: db02.domain.corp

     

    Instance ID: {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18}

     

    Management group: DomainSCOM

     

    Warning 2:

    The process started at 11:46:39 PM failed to create System.Discovery.Data, no errors detected in the output.  The process exited with 0

     

     

    Command executed:     "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2005DBEngineDiscovery.vbs" {E71360F6-C12E-8326-4539-FBC9D78862F5} {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18} DB02.domain.corp DB02.domain.corp DB02 "Exclude:"

    Working Directory:          C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 3\959\

     

     

    One or more workflows were affected by this.

     

     

    Workflow name: Microsoft.SQLServer.2005.DBEngineDiscoveryRule.Server

     

    Instance name: DB02.domain.corp

     

    Instance ID: {5CE82F7D-D7F7-F71E-8D7C-6BAD4E197D18}

     

    Warning 3:

    The process started at 11:46:39 AM failed to create System.Discovery.Data, no errors detected in the output.  The process exited with 0

     

     

    Command executed:     "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2005ASDiscovery.vbs" {AAD43FAE-0F82-0499-51E1-B30B1344AF93} {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18} DB02.domain.corp DB02.domain.corp DB02

    Working Directory:          C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 3\2968\

     

     

    One or more workflows were affected by this.

     

     

    Workflow name: Microsoft.SQLServer.2005.AnalysisServicesDiscoveryRule.Server

    Any help with this would be much appreciated.

    Thursday, October 28, 2010 3:53 PM

Answers

  • Look on the management servers - in the event log - around the time of the failure.  Look for a 33333 and or 10801 event - see if the discovery data is being sent but rejected.

    I doubt that's happening - because it looks like the disocvery data simply is not returning output.

    Running the script manually:  find the script "DiscoverSQL2005DBEngineDiscovery.vbs" in one of the \MonitoringHostTemporary Files\ directories and copy it to a local directory on the C: drive, like "C:\temp"

    Then open a command prompt, change to the C:\temp directory, and run the following:

    "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2005DBEngineDiscovery.vbs" {E71360F6-C12E-8326-4539-FBC9D78862F5} {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18} DB02.domain.corp DB02.domain.corp DB02 "Exclude:"

    The guids you pass dont matter for a manual test run.  The server name needs to match the local server name, or cluster virtual name if clustered.

    This should dump a bunch of XML output to the screen.  If it doesnt.... then I'd say thats where you should focus.  Because if a manual run doesnt produce results - OpsMgr never will.  Check the WMI providers for SQL and make sure they are present and working.  If WMI is stopped or busted - you will get no output.

     

    Thursday, October 28, 2010 10:25 PM
  • Are the SSIS Counters available in the performance monitor on the local servers?

    Maybe you're running into this article: http://support.microsoft.com/kb/941154.

    Regards,

    Mark

    Friday, October 29, 2010 8:38 AM
  • These are probably errors in addition to the ones you are seeing.  When the agent is saying "no discovery data returned" it means the script didn't create any output.  If this is the case, you should continue to twiddle with your permissions - figure out how to get the manual run to return XML per kevin's suggestion.  Once you do, the agent can start working.  A permission issue in the database (the script needs highly elevated permissions so it can access the master database).

    The errors on the RMS are indication that a race condition is happening within the discovery set that is happening.  SQL mp returns a distributed applicaiton hierarchy, so there are some special conditions that have to happen.  First, agent proxy needs to be turned on for all agents involved.  If the referenced classes do not exist, an SP1 SCOM will not create them, and an R2 will fail to auto-create them if the relationship type is containment (key cannot be inferred).

    This just really means that the items being referenced by later discoveries that are functioning have not been discovered (probably related to your first issue, since those are higher level diagram elements.)

    Keep plugging away - SQL permissions need to be pretty high for the discoveries that paint the engine and other master database sourced elements.  Your DBA's can probably help you troubleshoot to see what is being sent and then they can help you figure out what permissions in SQL need to be assigned to the account you are using.

     


    Microsoft Corporation
    Saturday, December 18, 2010 5:12 PM

All replies

  • Are there any GPO policies concerning the accounts you chose for Run-As?  Such as - do they all have Log On Locally right? 

    Did you distribute the run as account to each healthservice?

    Look in the OpsMgr event log on the agents - look for other warnings and errors that might clue you in to a config issue.

    Thursday, October 28, 2010 6:22 PM
  • Kevin,

    We do not have any GPO polices which would impact the Run As accounts.  The Run As account has been added to the Local Admin group on this SQL server, so it should have Log On Locally rights. 

     

    I distributed the Run As account to each object/server. 

     

    This is what I see in the OpsMgr event:

     

    This looks good for the Run As account.

    ·     The Health Service successfully logged on the RunAs account domain\SQLMonitor for management group domainSCOM

     

    Then we receive these three warnings before the scripts fail:

    ·     In PerfDataSource, could not resolve counter SQLServer:SSIS Pipeline, Rows read, . Module will not be unloaded.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SQLServer.2005.SQLServer_SSIS_Pipeline_Rows_Read_15.0_minutes_2_Rule

    Instance name: MsDtsServer

    Instance ID: {6555A60C-12DE-4282-7248-DED3660AEE9F}

    Management group: domainSCOM

     

    ·      In PerfDataSource, could not resolve counter SQLServer:SSIS Pipeline, Buffers spooled, . Module will not be unloaded.

     One or more workflows were affected by this.

    Workflow name: Microsoft.SQLServer.2005.SQLServer_SSIS_Pipeline_Buffers_Spooled_15.0_minutes_2_Rule

    Instance name: MsDtsServer

    Instance ID: {6555A60C-12DE-4282-7248-DED3660AEE9F}

    Management group: domainSCOM

     

    ·     In PerfDataSource, could not resolve counter SQLServer:SSIS Pipeline, Rows written, . Module will not be unloaded.

    One or more workflows were affected by this.

    Workflow name: Microsoft.SQLServer.2005.SQLServer_SSIS_Pipeline_Rows_Written_15.0_minutes_2_Rule

    Instance name: MsDtsServer

    Instance ID: {6555A60C-12DE-4282-7248-DED3660AEE9F}

    Management group: domainSCOM

     

    These are the only warnings we are seeing.  I also see these Warnings on the other SQL servers.  Nothing else is leading me to the exact issue.

     

    • Edited by MSFT Matrix Thursday, October 28, 2010 7:06 PM Change text size.
    Thursday, October 28, 2010 7:04 PM
  • Look on the management servers - in the event log - around the time of the failure.  Look for a 33333 and or 10801 event - see if the discovery data is being sent but rejected.

    I doubt that's happening - because it looks like the disocvery data simply is not returning output.

    Running the script manually:  find the script "DiscoverSQL2005DBEngineDiscovery.vbs" in one of the \MonitoringHostTemporary Files\ directories and copy it to a local directory on the C: drive, like "C:\temp"

    Then open a command prompt, change to the C:\temp directory, and run the following:

    "C:\Windows\system32\cscript.exe" /nologo "DiscoverSQL2005DBEngineDiscovery.vbs" {E71360F6-C12E-8326-4539-FBC9D78862F5} {1CE85F7B-D7F7-F71E-8D7C-6BAD4E197D18} DB02.domain.corp DB02.domain.corp DB02 "Exclude:"

    The guids you pass dont matter for a manual test run.  The server name needs to match the local server name, or cluster virtual name if clustered.

    This should dump a bunch of XML output to the screen.  If it doesnt.... then I'd say thats where you should focus.  Because if a manual run doesnt produce results - OpsMgr never will.  Check the WMI providers for SQL and make sure they are present and working.  If WMI is stopped or busted - you will get no output.

     

    Thursday, October 28, 2010 10:25 PM
  • Are the SSIS Counters available in the performance monitor on the local servers?

    Maybe you're running into this article: http://support.microsoft.com/kb/941154.

    Regards,

    Mark

    Friday, October 29, 2010 8:38 AM
  • I appreciate everyone’s input!

     

    Kevin, you were correct about errors 333333 and 10801.

    As you can see below, our MS server is rejecting the discovery data.   Is there any reason why the discovery data is being rejected on this one particular SQL server and not the others?  I tried to re-install the agent and enable proxy as suggested on another thread and the results are the same… 

    When I run the script manually, I do not get any output.

     

    Warning:

    Data Access Layer rejected retry on SqlError:

     Request: p_RelationshipDiscovered -- (RelationshipId=98d556a9-8064-0595-1577-be903834100e), (SourceEntityId=2e6948d3-14ea-39c4-6e8f-525bf4c4ba92), (TargetEntityId=b3f597ab-31ee-7c85-e9b3-0a21e0022cd4), (RelationshipTypeId=acfe2f40-0a73-6764-21a5-bf59c41b2844), (DiscoverySourceId=2c67cb4e-a02c-39f4-ea4d-1ebdd83004ff), (HealthServiceEntityId=8855650b-81c4-28df-e753-7c5a32317417), (PerformHealthServiceCheck=True), (TimeGenerated=10/29/2010 2:26:56 PM), (RETURN_VALUE=1)

     Class: 16

     Number: 777980003

     Message: The specified relationship doesn't have a valid target.

     

    Error:

    Discovery data couldn't be inserted to the database. This could have happened because  of one of the following reasons:

                - Discovery data is stale. The discovery data is generated by an MP recently deleted.

                - Database connectivity problems or database running out of space.

                - Discovery data received is not valid.

      The following details should help to further diagnose:

      DiscoveryId: 5a84ee62-20c2-46a2-10b9-3dedaff65df6

     HealthServiceId: 8855650b-81c4-28df-e753-7c5a32317417

     Invalid relationship target specified in the discovery data item.

    RelationshipTargetBaseManagedEntityId: b3f597ab-31ee-7c85-e9b3-0a21e0022cd4

    RuleId: 5a84ee62-20c2-46a2-10b9-3dedaff65df6

    Instance:

    <?xml version="1.0" encoding="utf-16"?><RelationshipInstance TypeId="{acfe2f40-0a73-6764-21a5-bf59c41b2844}" SourceTypeId="{00000000-0000-0000-0000-000000000000}" TargetTypeId="{00000000-0000-0000-0000-000000000000}"><Settings /><SourceRole><Settings><Setting><Name>{5C324096-D928-76DB-E9E7-E629DCC261B1}</Name><Value>DB02.domain.com</Value></Setting><Setting><Name>{AF13C36E-9197-95F7-393C-84AA6638FEC9}</Name><Value>\\.\PHYSICALDRIVE1</Value></Setting></Settings></SourceRole><TargetRole><Settings><Setting><Name>{5C324096-D928-76DB-E9E7-E629DCC261B1}</Name><Value> DB02.domain.com </Value></Setting><Setting><Name>{AF13C36E-9197-95F7-393C-84AA6638FEC9}</Name><Value>Disk #1, Partition #0</Value></Setting></Settings></TargetRole></RelationshipInstance>.

     

    Friday, October 29, 2010 2:43 PM
  • Mark,

    I tried following this KB article, but this did not resolve the issues. 

    Thanks

    Monday, November 1, 2010 1:46 PM
  • These are probably errors in addition to the ones you are seeing.  When the agent is saying "no discovery data returned" it means the script didn't create any output.  If this is the case, you should continue to twiddle with your permissions - figure out how to get the manual run to return XML per kevin's suggestion.  Once you do, the agent can start working.  A permission issue in the database (the script needs highly elevated permissions so it can access the master database).

    The errors on the RMS are indication that a race condition is happening within the discovery set that is happening.  SQL mp returns a distributed applicaiton hierarchy, so there are some special conditions that have to happen.  First, agent proxy needs to be turned on for all agents involved.  If the referenced classes do not exist, an SP1 SCOM will not create them, and an R2 will fail to auto-create them if the relationship type is containment (key cannot be inferred).

    This just really means that the items being referenced by later discoveries that are functioning have not been discovered (probably related to your first issue, since those are higher level diagram elements.)

    Keep plugging away - SQL permissions need to be pretty high for the discoveries that paint the engine and other master database sourced elements.  Your DBA's can probably help you troubleshoot to see what is being sent and then they can help you figure out what permissions in SQL need to be assigned to the account you are using.

     


    Microsoft Corporation
    Saturday, December 18, 2010 5:12 PM