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

• Microsoft SQL Server Plug-in 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 13c 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 inventory and usage details for inventory summaries of your Microsoft SQL Server database.

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

• Failover to a specified node within a SQL Server Cluster.

• Create an index on a SQL Server table or view.

1.2 What's New in This Release

This release of the Microsoft SQL Server plug-in for Oracle Enterprise Manager Cloud Control 13c 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 2016

• Business Intelligence Publisher Reports

• Query Performance Historical Playback

• Added Support for Microsoft SQL Server Full Recovery Model

1.2.1 Added Support for Microsoft SQL Server 2016

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

This release supports JDBC version 4.0. The sqljdbc_auth.dll files are prepackaged with the plug-in. In addition to the prepackaged Microsoft JDBC driver, the plug-in supports the use of the JSQL Connect JDBC driver (not prepackaged).

1.2.2 Business Intelligence Publisher Reports

In this release, three new BI Publisher Reports are added, for your Microsoft SQL Server environment. The three reports are focused on Availability Groups, Database Mirroring, and Database Performance.

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

1.2.3 Query Performance Historical Playback

This release of the Microsoft SQL Server Plug-in for OEM adds a new page for viewing the Historical Playback of the monitored Microsoft SQL Server query performance.

This new UI page allows a user to see up to 2 weeks of performance diagnostics of the worst performing queries based on CPU time.

This page includes controls to select the exact date range for viewing the query performance, graphical comparison between queries, and a drop-down to select relevant performance history metrics.

This drop-down enables the user to dive deeper into data behind the query performance by offering a visual comparison of Microsoft SQL Server monitored key performance indicators to the query CPU history. These key performance indicators are specifically around the Microsoft SQL Server process and connections, memory, and SQL execution types.

Figure 1-1 Query Performance Page

1.2.4 Added Support for Microsoft SQL Server Full Recovery Model

This new feature allows users to create Full, Differential, and Transaction Log backups of their Microsoft SQL Server Database. Also added is a new job to allow for restoring backups made using the Microsoft SQL Server Full Recovery Model.

See Creating, Editing, and Using Jobs, for more details.

1.3 Supported Versions

This plug-in supports the following versions of products:

• Enterprise Manager Cloud Control (Oracle Management Server and Oracle Management Agent):

  • Only Enterprise Manager Cloud Control 13c Release 1 (13.1.0.1.0) or higher

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

  • 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 Microsoft SQL Server Plug-in 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 (Oracle Management Server and Oracle Management Agent) must be installed:

   • Enterprise Manager Cloud Control 13c

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

3. 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.

4. Windows Management Instrumentation Service is up and running.

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

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

7. 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.

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

9. 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.

10. 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.

11. 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.

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.

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.

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.