1 Microsoft SQL Server Plug-in Overview and Prerequisites

This chapter describes the system monitoring plug-in for Microsoft SQL Server and provides a list of available features. Review the summary of prerequisites required before configuring Microsoft SQL Server for monitoring by Oracle Enterprise Manager Cloud Control.

The following topics are provided:

• Microsoft SQL Server Plug-in Overview and Feature Summary

• What's New in This Release

• Supported Versions

• Prerequisites

• Downloading the Plug-in

• Deploying the Plug-in

• Upgrading the Plug-in

• Undeploying the Plug-in

1.1 Microsoft SQL Server Plug-in Overview and Feature Summary

The system monitoring plug-in for Microsoft SQL Server extends Oracle Enterprise Manager Cloud Control 12c to add support for managing Microsoft SQL Server instances. By deploying the plug-in within your Cloud Control environment, you gain the following management features:

• Monitor SQL Server instances.

• Supports both SQL Authentication and Windows Integrated Authentication.

• Gather configuration data and track configuration changes for SQL Server instances.

• Raise alerts and violations based on thresholds set on monitored metrics and configuration data.

• Provide rich out-of-box reports through Enterprise Manager's BI Publisher reports feature based on the gathered data.

• Support monitoring by a local or remote Windows Agent. Local Windows Agent is an agent running on the same host as the Microsoft SQL Server. Remote Windows Agent is an agent running on a host that is different from the host where SQL Server is running.

• Out-of-the-box monitoring templates for Microsoft SQL Server Cluster monitoring and Microsoft SQL Server AlwaysOn (HADR) monitoring.

• Oracle Enterprise Manager Jobs are made easy-to-access by being accessible from the plug-in's UI. These jobs allow for the following management of Microsoft SQL Server:

  • Backup, restore, schedule, and naming of Microsoft SQL Server database backups.

  • Start, stop, pause, and resume of SQL Server Instances.

  • Killing of sessions that are high in CPU or memory usage.

• Provide chargeback functionality for resource usage metering, consumption reports, and charge plans to define the resources to charge for and their associated rates.

• Provide compliance management to evaluate the compliance of targets and systems.

• Provide inventory and usage details for inventory summaries of your Microsoft SQL Server database.

1.2 What's New in This Release

This release of the Microsoft SQL Server plug-in for Oracle Enterprise Manager Cloud Control 12c includes many new features for better visibility into your Microsoft SQL Server environment. Highlighted below are some of the new features included in this release:

• Added Support for Microsoft SQL Server 2014

• Updated User Interface

• Business Intelligence Publisher Reports

• Chargeback Functionality

• SQL Server Compliance Standard

1.2.1 Added Support for Microsoft SQL Server 2014

Microsoft SQL Server plug-in for Oracle Enterprise Manager Cloud Control 12c supports a wide range of Microsoft SQL Server versions (Figure 1-1). This plug-in release adds support for the latest version of Microsoft SQL Server - SQL Server 2014. The plug-in supports not only standalone but also the cluster configuration of this version.

This release provides support for JDBC 4.0. The sqljdbc_auth.dll files are now prepackaged with the plug-in.

Figure 1-1 Microsoft SQL Server Database Instances

1.2.2 Updated User Interface

The user interface has been updated to add an index page and a new link to the home page.

The home page (Figure 1-2) has been enhanced with the addition of a link in the Summary section to the all new indexes page. This enables quicker navigation throughout the plug-in.

Figure 1-2 Microsoft SQL Server Plug-in Target Home Page

The all-new indexes page (Figure 1-3) has been added to the plug-in to give greater insight into the monitored SQL Server indexes. This includes an indexes by usage graph and table, index fragmentation table, recommended missing indexes, and a job to create indexes.

Figure 1-3 Microsoft SQL Server Plug-in Indexes Page

1.2.3 Business Intelligence Publisher Reports

In this release Information Publisher Reports included in the previous release of the MS SQL Server Plug-in have been migrated to BI Publisher Reports. BI Publisher Reports eliminate complexity and simplify report development and maintenance.

See Using Reports and Monitoring Templates for more information on using these reports.

1.2.4 Chargeback Functionality

New to this version is Chargeback functionality (Figure 1-4). Chargeback allows users to gather data on resource use, allocate charges for the use of these resources, and present the results in an intelligible format. Using Chargeback assists consumers in monitoring and controlling their IT costs.

Figure 1-4 Microsoft SQL Server Plug-in Chargeback Funtionality

For more information on how to implement Chargeback services, see the Oracle Enterprise Manager Cloud Administration Guide:

http://docs.oracle.com/cd/E24628_01/doc.121/e28814/chargeback_cloud_admin.htm#EMCLO917

1.2.5 SQL Server Compliance Standard

Another new feature in this release is the Microsoft SQL Server Compliance Standard (Figure 1-5). Associating a Compliance Standard with targets allows an administrator to track and monitor the adherence of managed targets. This will assist in revealing the least compliant targets, so measures can be taken to correct them.

Figure 1-5 SQL Server Configuration Compliance Standard

For more information on how to implement Compliance Standards to targets, see the Oracle Enterprise Manager Cloud Control Oracle Database Compliance Standards Guide:

http://docs.oracle.com/cd/E24628_01/doc.121/e36074/db_comp_intro.htm#EMDBC108

1.3 Supported Versions

This plug-in supports the following versions of products:

• Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0) or higher (Oracle Management Server and Oracle Management Agent).

• Standard, Enterprise, and Workgroup editions of Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2012, and Microsoft SQL Server 2014 as detailed below:

  • Microsoft SQL Server 2005 (32-bit or 64-bit).

  • Microsoft SQL Server 2008 (32-bit or 64-bit).

  • Microsoft SQL Server 2008 R2 (32-bit or 64-bit) including Failover Cluster support.

  • Microsoft SQL Server 2012 (32-bit or 64-bit) including Failover Cluster and AlwaysOn Availability Groups support.

  • Microsoft SQL Server 2014 (32-bit or 64-bit) including Failover Cluster and AlwaysOn Availability Groups support.

Note:

Monitoring of Microsoft SQL Server Clusters are only supported with a remote monitoring configuration. The Oracle Management Agent used in monitoring cannot be installed to one of the cluster nodes.

1.4 Prerequisites

The following prerequisites must be met before you can deploy the plug-in. Patches are available from My Oracle Support (https://support.oracle.com):

1. Enterprise Manager Cloud Control 12c (Oracle Management Server and Oracle Management Agent) must be installed. Choose from:

   • Recommended: Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5).

   • Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0) or higher and install Patch 20870437 to upgrade to version 12.1.0.4.4.

     To correct a deployment issue, install system patch 20645335: On Windows Server OMS side plug-in deployment was stuck at OUI step.

2. The following patches must be installed:

   • Configuration Management Patch 18140699.

   • Inventory and Usage Patch 20692416.

   • Chargeback Patch:

     • For Enterprise Manager Cloud Control 12c Release 5 (12.1.0.5.0), which contains Chargeback plug-in version 12.1.0.7.1, apply patch 21420915: Enterprise Manager for OMS Plug-ins 12.1.0.5.1.

     • For Enterprise Manager Cloud Control 12c Release 4 (12.1.0.4.0), which contains Chargeback plug-in version 12.1.0.6.2, apply patch 21415432: Enterprise Manager for OMS Plug-ins 12.1.0.4.14.

3. The plug-in is only supported when running the Oracle Management Agent on 32-bit or 64-bit Windows.

4. Access privileges required for non-admin System user to perform Remote Monitoring of SQL Server instance.

   For more information, see Configuring Remote Connections to Monitor Targets.

5. Windows Management Instrumentation Service is up and running.

6. Enable TCP/IP for the SQL Server instance. For more information, see Enabling and Finding TCP/IP Port Information.

7. Enable SQL or Mixed Authentication on the SQL Server instance. For more information, Enabling SQL Authentication or Mixed Authentication.

8. Create a suitable DB user with a sysadmin fixed server role. To monitor the SQL Server instance using non-sysadmin user, create a user with non-sysadmin role and provide the following access to it:

   1. Execute this command to give access to the user:

      GRANT VIEW SERVER STATE TO "login name"
      

   2. Provide database access to the user.

   3. Provide SQLAgentOperatorRole fixed database role in msdb to the user.

9. Preferred credentials are set and validated on all Agents where you want to deploy the plug-in.

10. The OS privileges for the user (set in the Preferred Credentials for the Agent) must meet the requirements documented in the "Setting Credentials for the Job System to Work with Enterprise Manager" section of the Oracle Database Installation Guide for Microsoft Windows available at:

    http://docs.oracle.com/cd/E11882_01/install.112/e24186/postcfg.htm#BABFAEIG
    

    Note:

    If you do not assign the correct privileges for users, the deployment will fail.

11. As part of JDBC URL, either IP Address or host name can be provided. Ensure that the host name can be resolved consistently on the network. Standard TCP tools such as nslookup and traceroute can be used to verify the host name. Validate using the following commands on Management Agent where plug-in is deployed:

    • nslookup <hostname>

      This returns the IP address and fully qualified host name.

    • nslookup <IP>

      This returns the IP address and fully qualified host name.

12. To enable the use of the Backup, Delete Backup, and Restore jobs, the following SQL commands must be processed on the monitored SQL Server database:

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE 
    EXEC sp_configure 'xp_cmdshell', 1
    RECONFIGURE
    

1.5 Downloading the Plug-in

You can download plug-ins in online or offline mode. Online mode refers to an environment where you have Internet connectivity, and can download the plug-in directly through Enterprise Manager from My Oracle Support. Offline mode refers to an environment where you do not have Internet connectivity, or where the plug-in is not available from My Oracle Support.

See the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud Control Administrator's Guide for details on downloading the plug-in in either mode:

http://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#CJGBEAHJ

1.6 Deploying the Plug-in

You can deploy the plug-in to an Oracle Management Service instance using the Enterprise Manager Cloud Control console, or using the EM Command Line Interface (EMCLI). While the console enables you to deploy one plug-in at a time, the command line interface mode enables you to deploy multiple plug-ins at a time, thus saving plug-in deployment time and downtime, if applicable.

See the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud Control Administrator's Guide for instructions on deploying the plug-in:

http://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#CJGCDHFG

1.7 Upgrading the Plug-in

The Self Update feature allows you to expand Enterprise Manager's capabilities by updating Enterprise Manager components whenever new or updated features become available. Updated plug-ins are made available via the Enterprise Manager Store, an external site that is periodically checked by Enterprise Manager Cloud Control to obtain information about updates ready for download. See the Updating Cloud Control chapter in the Oracle Enterprise Manager Cloud Control Administrator's Guide for steps to update the plug-in:

http://docs.oracle.com/cd/E24628_01/doc.121/e24473/self_update.htm

1.8 Undeploying the Plug-in

See the Managing Plug-ins chapter in the Oracle Enterprise Manager Cloud Control Administrator's Guide for steps to undeploy the plug-in:

http://docs.oracle.com/cd/E24628_01/doc.121/e24473/plugin_mngr.htm#CJGEFADI