Monitoring Microsoft SQL Server using Enterprise Manager Grid Control

The Enterprise Manager offers several system monitoring plug-ins for third-party products. One of them is Microsoft SQL Server Plug-in. This plug-in supports Standard, Enterprise, and Workgroup editions of Microsoft SQL Server 2000, Microsoft SQL Server 2005, and Microsoft SQL Server 2008 (both 32 and 64bits).

I have tried to prepare step by step guides to demonstrate how to install Grid Control 11 (to Linux) and how to deploy agent to windows in my previous posts:

How to install Grid Control 11g
How to deploy Grid Control agent to a windows server

Now, I’ll show how to deploy this plug-in to a windows server we already registered with our Grid Control. Microsoft SQL Server Monitoring Plug-in can be deployed to any machine which can connect to target SQL Server service, but I prefer to deploy it to the host which MS SQL Server is installed.

First, we’ll download the plug-in from Oracle website (download r12):

http://www.oracle.com/technetwork/oem/grid-control/downloads/devlic-099348.html

Then we’ll import the plug-in file into our EM Grid Control.

Login to the Grid Control, click “setup” and then click “management plug-ins”. You’ll see the list of plugins which are already installed. Click “import” button.

In “import management plug-ins” page, click choose file and select the file we downloaded (microsoft_sqlserver_database.jar).

Click “list archive”, you’ll see the information about plug-in in the list. Check the plug-in and click OK to import.

Our plug-in is ready to deploy but we need to make our windows server ready for it. We should enter the OS credentials of the target server to be able to deploy the agent.

Click “preferences”, then click “preferred credentials” and then click “set credentials icon” of the agents.

Enter the credentials for the target server, and click “apply” button.

Now let’s get back to the deployment. Click “setup” and then click “management plug-ins” to open the “management plug-ins” page which you can see and deploy the plug-ins. Then click “deploy” button of the Microsoft SQL Server. Select the target server, as I said this can be any server which can connect to SQL Server but I’ll use my windows server which Ms SQL Server is installed. Click next.

Click “finish”.

The plug-in deployment is completed, now we need to configure the plug-in.

Click “setup” and then click “agents” tab, pick the server you installed the agent (click on its name).

Select “microsoft sql server” as monitoring target and click “go”.

Enter a name for the target SQL Server. I recommend you to enter a meaningful name because you’ll see this name in the databases tab. Enter the JDBC URL. It should be in this format:

jdbc:sqlserver://host:port

Default port of MS SQL Server is 1433. Because my SQL Server was listening in default port, I entered jdbc:sqlserver://winserver2008.gokhanatil.com:1433 as JDBC URL. If SQL Server is not in same server with the agent (which we deployed our plug-in), enter the OS credentials. If you’ll not use windows integrated authentication, enter the credentials of database user (which has sysadmin role).

If you have read my previous blog posts, I created an OS user called “oracle” in this server and installed the EM Grid Control agent with using this account. To be able to use “windows integrated authentication”, I have defined the “oracle” user, as sysadmin of SQL Server. Therefore, I’ll just enter YES to “connect using windows integrated authentication” and will not enter any more information.

If you have entered the required information, click “test connection”. If you see the “test successful” message, click OK to configure the plug-in.

Configuration is completed.

In the databases tab, we can see our SQL Server. Wait for a while (30 mins-1 hour), so agent can collect some data, then click on its name to see how the MS SQL Server plug-in works.

As you may notice, some metrics and alerts are defined out-of-the-box. If you need, you can define more metrics and alerts in this page (check the “all metrics” link at the bottom of the page). You can also start/stop the SQL Server, or compare its configuration with another server. You can click “reports” tab to get some valuable reports.

Microsoft SQL Server plug-in provides the following reports:

  • System Configuration
  • Database(s) Configuration
  • Memory Statistics
  • Space Usage
  • Performance
  • Process Info and Locks
  • Cache and Buffer
  • Database Backups and Jobs
  • Cluster
  • Statistics

This is “Memory statistics” report.

This is “Cache and Buffer” report.

These reports can not be compared to the performance and monitoring reports for Oracle RDBMS but they are still useful and EM Microsoft SQL Server plug-in is worth to try.

Please share
  • 1
  •  
  •  
  • 17
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

16 Comments

  1. Arnon

    Hi, Great article.

    Please I wanna know how to I can schedule and start backups of MSSQL by Cloud Control. My objective is obtain the same experience of results reports of cloud control on Oracle DBs for our MSSQL databases on Cloud Control Pannel.

    Thank You!

  2. Gokhan Atil

    Hi Arnon, I haven’t checked the capabilities of cloud control plugin for Microsoft SQL Server. I’ll try to test it and blog about it soon.

  3. Azmat Zaheer

    Hi,

    Can you please clarify the following point:

    Server1:
    It has got the enterprise manager 11g grid control installed-Microsoft Windows 2003 R2. I have got the *.jar file downloaded from the given link and copied here as per your instructions and have successfully imported it.

    Server2:
    It has got SQL Server 2005 and Windows 2003 Server R2 on it.From the following step onwards i am lost :

    “Click “preferences”, then click “preferred credentials” and then click “set credentials icon” of the agents.”

    Confusion:
    Which server2 go into the hostname and does this means that it needs to have a running agent installed as my Server 2 currently has got no agent installed on it.

    Because when on Server1 inside from the grid i try to search the host (i.e Server2) the grid doesnt locates it.

    Please advise.

    Regards

    • Gokhan Atil

      Hi Azmat,

      In this post, I assume that you have already deployed a management agent to your target server (server2 according to your sample).

      So I import MSSQL Server agent to Grid Control (server1), then I deploy this plugin to target server (server2) and configure it to manage and monitor the SQLSERVER instance running on server2.

  4. satish

    Dear Gokhan,

    Can you please tell me how to monitor sql server 2008 by using 12c grid control. is it same way?

    Thanks & Regards,
    Satish Kumar Sadhu

    • Gokhan Atil

      Satish, I haven’t tested it yet but EM12c has plugin to monitor Ms SQL Server, so it should be very similar.

    • Gokhan Atil

      Graham,

      As far as I know, OEM plugin for monitoring Microsoft SQL Server is only certified for Microsoft SQL Server 2008.

  5. Jaya

    Hello Gokhan,

    We have 12c OEM Cloud COntrol already installed on an Linux server to monitor Oracle systems. I wanted to leaverage this and install Microsoft SQL Server Plug-in and set MSSQL Server Instances are targets to be monitored. Is this possible?

    I also want to know what all can be monitored, configured and what features do we have using this Plug-in for MSSQL Server.

     

  6. Sander Wendel

    Dear Gokhan,

     for your information, I found the prices for “Oracle System Monitoring Plug-ins for Non Oracle Databases Microsoft SQL Server” at the Oracle Store (shop.oracle.com) by simply searching for SQL Server at this site.

    Sometimes life is so easy 😉

    For a perpetual processor license the price is currently €1207 per processor.

    Kind regards,

    Sander Wendel.

     

  7. Kartar Rana

    Firstly, this is a great post. Content on using OEM with SQL Server is pretty less out there and since my company was implementing this change, this did helped me a lot.

    Secondly,  I am trying to create a report and facinig some issues. Hoping that you can help. I am trying to run a SQL based query like SELECT * FROM sys.databases and need the results back n a table form n the report. The only problem is the query never runs.  tried to setup a custom metric to run this query with no luck. Have you ever tried it?

  8. Pradeep

    Dear Gokhan,

    How to install multiple sql server instances of same server (Target) in OEM12c

    • Gokhan Atil

      Hi Pradeep,

      If I get you correct, you asked how you can add “named instances” as targets. In this case, you should add the instance name to JDBC URL:

      jdbc:sqlserver://[serverName[\instanceName][:portNumber]]

Leave Comment

Your email address will not be published. Required fields are marked *