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:
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):
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.
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:
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
- Process Info and Locks
- Cache and Buffer
- Database Backups and Jobs
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.