SCOM 2012 SP1 error “Failed to run a process or script” during SQL 2008 Discovery

I’ve been searching for a solution to this problem for a few days already. I had a test SCOM 2012 server to monitor SQL, SharePoint, Clusters  and then after I finished my testing I installed a new SCOM 2012 SP1 server in order to monitor my production farm.

I uninstalled the old agents and redeployed new ones from the SCOM 2012 SP1 console. Everything looks good except for one ‘Database Engine’ which is not discovered for one of the clusters SQL instances. Every other SQL role is discovered and monitored correctly on all the instances except for the Database Services on one of the instances. The SQL version used is SQL 2008 R2 and the cluster is running on Server 2008 R2. I run three SQL instances on this cluster.

SCOM will display a warning:

The process started at 11:50:36 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 “DiscoverSQL2008DBEngineDiscovery.vbs” {32FBB1E4-C6D1-0517-2F47-3DDA67D46A3B} {B4F2B3BC-D834-17F2-CE0A-2F87B502F7BE} SQL2K8-DB2.domain.com SQL2K8-DB2.domain.com SQL2K8-DB2.domain.com “Exclude:”
Working Directory: C:\Program Files\System Center Operations Manager\Agent\Health Service State\Monitoring Host Temporary Files 11\14968\

One or more workflows were affected by this. 

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

Instance name: SQL2K8-DB2.domain.com

Instance ID: {B4F2B3BC-D834-17F2-CE0A-2F87B502F7BE}

Management group: SP_prod

Warning in SCOM alersts

I tried to run the command manually but I received an error that the script does not exist in this location. I found a blog which is not really giving you everything but it gets you closer to what you should do.

Fist in order to correctly register SQL  browse to “C:\Program Files (x86)\Microsoft SQL Server\100\Shared” with a command prompt.

Then run mofcomp sqlmgmproviderxpsp2up.mof

Perform this step on all of the SQL cluster nodes.

Credits go to Carl.T here http://qa.social.technet.microsoft.com/Forums/en-US/operationsmanagermgmtpacks/thread/01eff618-1087-4b6a-9d3f-9f1402ddf3f4 for this very nice tip.

As soon as I ran this command one of the SQL instances were displayed.

In order to run the command from the SCOM error you will need to:

1) search the installation folder of the agent on the SQL nodes for a file called:

DiscoverSQL2008DBEngineDiscovery.vbs

2) if you don’t find the script, which was my case, you need to:

2.1) Go to your SCOM server and export the Microsoft.SQLServer.2008.Discovery monitoring pack to your local drive as XML. In this file we will find the script which is ran by SCOM in order to discover SQL 2008.

The monitoring pack most probably is sealed and the GUI will not allow you to export so you will need to run in powershell:

Make sure you add the Operations manager snapin to your session before running this command.
PS C:\Windows\system32> get-scommanagementpack -name “Microsoft.SQLServer.2008.Discovery” | export-managementpack -path “f:\backup”
If the name of the pack will change in the future, please use:
PS C:\Windows\system32> get-scommanagementpack | export-managementpack -path “f:\backup”
This will export all your management packs and you can then choose the one for SQL 2008 Discovery.
Open the exported XML and search for the name of the script DiscoverSQL2008DBEngineDiscovery.vbs and copy the long script body to a new text document and save it with .vbs extension.
Copy the resulted script  to c:\temp on the SQL node.
Script in XML
Try to run in an elevated command prompt the same command you found in the SCOM warning, in my case it looked like:
“C:\Windows\system32\cscript.exe” /nologo “c:\temp\DiscoverSQL2008DBEngineDiscovery.vbs” {32FBB1E4-C6D1-0517-2F47-3DDA67D46A3B} {B4F2B3BC-D834-17F2-CE0A-2F87B502F7BE} SQL2K8-DB2.domain.com SQL2K8-DB2.domain.com SQL2K8-DB2.domain.com “Exclude:” Working Directory: C:\Program Files\System Center Operations Manager\Agent\Health Service State\Monitoring Host Temporary Files 11\14968\
Kevin Holman explains this here: http://blogs.technet.com/b/kevinholman/archive/2010/03/09/basic-troubleshooting-of-discovery-scripts.aspx  but he did not specify that you need to give the full path to the .vbs script. He also explains that  SQL2K8-DB2.domain.com  needs to be the name (FQDN) of your virtual cluster SQL server. I did not understand it, it is the cluster FQDN or the SQL instance FQDN or the Cluster node FQDN but I looked for the failed command in SCOM  and used the same Cluster node name and I ran the command on the same node also.
He also explains that the GUIDs used are dummy ones during the discovery.
Now after performing those two steps, registering again SQL on each node  and running the discovery script, two of the missing SQL 2008 instances are already displayed and monitored, probably the third one will also be discovered, I will update this post if it does not.
Advertisements