Microsoft SQL Server Plug-in Overview and Prerequisites

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.

Supported Versions

This plug-in supports the following versions of products.

Table 1-1 Enterprise Manager (EM) (Oracle Management Server and Oracle Management Agent)

MS SQL Server Version (Standard, Enterprise, and Workgroup Edition)	Plug-in Version 13.2.1.0.0	Plug-in Version 13.3.2.0.0	Plug-in Version 13.3.3.0.0	Additional Compatibility Details
Microsoft SQL Server 2008	EM 13.2, 13.3, & 13.4	EM 13.3	EM 13.3	32-bit or 64-bit. (Up to EM 13.3.x) Microsoft SQL Server 2008 R2 (32-bit or 64-bit) including Failover Cluster support. (Up to EM 13.3.x)
Microsoft SQL Server 2012	EM 13.2, 13.3, & 13.4	EM 13.3, 13.4, & 13.5	EM 13.3, 13.4, & 13.5	32-bit or 64-bit including Failover Cluster and AlwaysOn Availability Groups support. (EM 13.2.1.0.0 and higher)
Microsoft SQL Server 2014	EM 13.2, 13.3, & 13.4	EM 13.3, 13.4, & 13.5	EM 13.3, 13.4, & 13.5	32-bit or 64-bit including Failover Cluster and AlwaysOn Availability Groups support. (EM 13.2.1.0.0 and higher)
Microsoft SQL Server 2016	EM 13.2, 13.3, & 13.4	EM 13.3, 13.4, & 13.5	EM 13.3, 13.4, & 13.5	32-bit or 64-bit including Failover Cluster and AlwaysOn Availability Groups support. (EM 13.2.1.0.0 and higher)
Microsoft SQL Server 2017	.	EM 13.3, 13.4, & 13.5	EM 13.3, 13.4, & 13.5	32-bit or 64-bit including Failover Cluster and AlwaysOn Availability Groups support. (EM 13.3.2.0.0 and higher)
Microsoft SQL Server 2019	.	EM 13.4 & 13.5	EM 13.4, & 13.5	32-bit or 64-bit including Failover Cluster and AlwaysOn Availability Groups support (EM 13.4.1.0.0 and higher)

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.

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):

Enterprise Manager Cloud Control (Oracle Management Server and Oracle Management Agent) must be installed:
- Enterprise Manager Cloud Control 13c
The plug-in is only supported when running the Oracle Management Agent on 32-bit or 64-bit Windows.
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.
Windows Management Instrumentation Service is up and running.
Enable TCP/IP for the SQL Server instance. For more information, see Enabling and Finding TCP/IP Port Information.
Enable SQL or Mixed Authentication on the SQL Server instance. For more information, Enabling SQL Authentication or Mixed Authentication.
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.
Preferred credentials are set and validated on all Agents where you want to deploy the plug-in.
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 Oracle Enterprise Manager" section of the Oracle Database Installation Guide for Microsoft Windows.

Note:

If you do not assign the correct privileges for users, the deployment will fail.
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.
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
```

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.

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.

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.

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.