10 Use OEM to monitor Microsoft SQL Server

 

• Configure sshd service on Windows host
• Install the Microsoft  SQL Server OMS plugin
• Download the Windows Agent
• Install Windows Agent
• Add the Target

prerequisite

• OEM installation
• Microsoft SQL server installation: To simplify the test, I made some configuration change:
• Turn off firewall
• Edit host file so it contains the linux host name to contain the linux host. %SystemRoot%\system32\drivers\etc\hosts
• Disable local security policy
• Create the local admin account with the same login name on the linux ( oracle). 

 

Configure sshd service on Windows host

Please refer here.

 

Install the Microsoft  SQL Server OMS Plugin

Setup –> Extensibility –> Self Update

Click Plugin

Click the Microsoft SQL Server plugin , If the plugin is not downloaded ( status: Available), click the download first . Once it is download, click the Apply

Select the Management Servers

 

 

Download the Windows Agent

• From the 12c, the agent can not be  download separately from OTN. We have to download through the 12c either through the online or offline mode.
• The easier way to get the agent download is setup the online mode.

 

Setup –> Extensibility –> Self Update

Click Agent software

Select Microsoft windows and click download

Click the Apply

 

Install Windows Agent

• The Windows agent must be deploy on the Windows platform. The Agent use WMI to monitor the Windows, if the agent is on the Linux host, we would get the WMI error. “_OS_Type_Agent_DynamicProperty;Process invocation failure: java.io.IOException: Cannot run program "/u01/app/oracle/agent12cr2/core/12.1.0.2.0/bin/nmefwmi” . The nmwfwmi is the wmi monitor executable.

Setup –> Extensibility –> Self Update

 

Fill in the target host name

Fill in the host information. If the directory does not exist, it would be created

• Installation Base Directory:  C:\OEM
• Instance Directory: C:\OEM\agent_inst

Provide the host credential

Then, click deploy agent

After the agent is deploy, we can verify the agent status

C:\OEM\agent_inst\bin\emctl.bat status agent

Add the Target

Setup –> Add Target –> add Targets Manually

Select the agent on the windows

Click add manually

Fill in the information

Database Role: sysadmin

This is OEM bugs, test connection would always fail with below error: SKIP the test and add the target.

Test Connection failed: [_WinAuthDLLToLoadDynamicProp;DB Credentials are found missing. DB Credentials are required for SQL based connections, MSSQL_NumClusterNodes;Can't resolve a non-optional query descriptor property [dllFile] (dllFile), _Wbem_Remote_Determination_DynamicProperty;Can't resolve a non-optional query descriptor property [dllFile] (dllFile), _MSSQL_Instance_TestMetric_DynamicProperty;Can't resolve a non-optional query descriptor property [dllFile] (dllFile), _OS_Type_TargetHost_DynamicProperty;Can't resolve a non-optional query descriptor property [STDINWBEM_HOST] (ms_sqlserver_host), MSSQL_NumClusterNodes;Can't resolve a non-optional query descriptor property [dllFile] (dllFile)]

In the example above, I use the sql authentication. If I use windows authentication, I would get the “'C:\OEM\plugins\oracle.em.smss.agent.plugin_12.1.0.2.0\scripts\emx\microsoft_sqlserver_database..\..\..\..\dependencies\oracle.em.smss\jdbcdriver\sqljdbc_auth.dll' is found missing ” . We have to create the directory and download the sqljdbc_auth.dll from MSFT website then copy to that directory.  However, I still get the test connection fail error.

 

After the target installed. check the host agent status

emctl config agent listtargets

Target –> All Target –> Click the host pandawin

Target –> All Target –> Click the MSSQL

we can also see the instance information

 

Reference

• http://gavinsoorma.com/2012/06/installing-the-12c-cloud-control-plug-in-for-microsoft-sql-server-database/
• http://gavinsoorma.com/2012/06/deploying-the-12c-management-agent-on-windows-using-the-12c-cloud-control-self-update-feature/
• https://forums.oracle.com/forums/thread.jspa?messageID=10745368
• https://forums.oracle.com/forums/thread.jspa?messageID=10784916
• http://docs.oracle.com/cd/E17559_01/em.111/e10314/emptg.htm