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.

The following topics are provided:

• Microsoft SQL Server Plug-in Overview and Feature Summary

• Supported Versions

• Microsoft SQL Server Plug-in Prerequisites

• Setting Up the JDBC Driver

• Mirroring Monitoring

• Downloading the Plug-in

• Deploying the Plug-in

• Upgrading the Plug-in

• Undeploying the Plug-in

Microsoft SQL Server Plug-in Overview and Feature Summary

The system monitoring plug-in for Microsoft SQL Server extends Oracle Enterprise Manager to add support for managing Microsoft SQL Server instances. By deploying the plug-in within your Enterprise Manager 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.

• 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 section describes how to obtain information on the Microsoft SQL Server versions supported by Oracle Enterprise Manager in a release.

1. Sign in to My Oracle Support and click the Certifications tab.
2. In the Certification Search region, select one of the following from the Product list:
   • Enterprise Manager Base Platform - OMS, to view the certification for OMS.
   • Enterprise Manager Base Platform - Agent, to view the certification for Management Agent.
3. From the Release list, select the release you are interested in (e.g., 24.1.0.0.0).
4. Click Search.
5. In the Certification Results region, expand the 'Databases' list.
6. Locate 'Microsoft SQL Server (Managed Target)' to view supported versions.

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

1. Enterprise Manager 24ai (Oracle Management Server and Oracle Management Agent) must be installed.

2. Microsoft JDBC driver for SQL Server and the Microsoft JDBC authentication DLL must be installed on the Management Agent host. For information, see Setting Up the JDBC Driver.

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

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

Setting Up the JDBC Driver

The Microsoft JDBC driver is available from Microsoft, and consists of the following files that the Agent must be able to access, where <DRIVER_VERSION> is the Microsoft JDBC driver version in use:

• mssql-jdbc-<DRIVER_VERSION>.jre8.jar
• mssql-jdbc-<DRIVER_VERSION>.jre11.jar
• mssql-jdbc_auth-<DRIVER_VERSION>.x64.dll — for a 64-bit Windows Agent
• mssql-jdbc_auth-<DRIVER_VERSION>.x86.dll — for a 32-bit Windows Agent

As of plug-in version 13.5.3.0.0, these files are no longer bundled with the plug-in. They must be downloaded from Microsoft or copied from a preexisting deployment and placed in the Agent plug-in directory after the plug-in has been deployed or upgraded. Microsoft SQL Server targets cannot collect data until the files are in place.

To set up the Agent directory for the Microsoft JDBC driver:

1. Obtain the JDBC driver JARs and the authentication DLL using one of the following sources:

   • Fresh install: Download the latest general availability (GA) release of the Microsoft JDBC Driver for SQL Server from Microsoft's official download page:

     https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server

     When unzipped, the package contains the JAR files at the top level and the authentication DLLs under auth/x64/ and auth/x86/.

   • Upgrade from a plug-in version prior to 13.5.3.0.0: These files were bundled inside the prior plug-in’s OPAR. Each plug-in version installs into its own versioned directory on the Agent, so the files do not carry over to the new plug-in directory automatically.

     Recommended: Before upgrading, copy the JARs and DLL from the prior plug-in directory (<AGENT_HOME>/plugins/oracle.em.smss.agent.plugin_<OLD_VERSION>/scripts/emx/microsoft_sqlserver_database/) to a location outside <AGENT_HOME>/plugins/, then use those files in step 2 given below.

     If the upgrade is already complete and the files were not backed up, the prior plug-in directory often remains on the Agent for rollback and can be used as the source. Otherwise, download fresh from Microsoft as described above.

2. Copy the JDBC driver JARs into the following directory:

   <AGENT_HOME>/plugins/oracle.em.smss.agent.plugin_<VERSION>/scripts/emx/microsoft_sqlserver_database/

3. Create an x64 or x86 subdirectory, matching the Agent architecture, under the directory above if it does not already exist. Copy the authentication DLL into that subdirectory:

   <AGENT_HOME>/plugins/oracle.em.smss.agent.plugin_<VERSION>/scripts/emx/microsoft_sqlserver_database/x64/mssql-jdbc_auth-<DRIVER_VERSION>.x64.dll

   Use x86/ and the .x86.dll file for a 32-bit Agent. The subdirectory name is lowercase, and the DLL filename must match the version of the JDBC JARs copied in step 2 above.

4. Restart the Agent so it loads the driver:

   <AGENT_HOME>/bin/emctl stop agent
   <AGENT_HOME>/bin/emctl start agent

   The next scheduled collection returns each target to UP. For immediate confirmation, open the SQL Server target, go to Target Setup, then Monitoring Configuration, and click Test Connection.

Note:

The plug-in’s own impersonate_X86.dll, impersonate_X64.dll, and impersonate_IA64.dll files are still shipped with the plug-in and must not be removed.

Mirroring Monitoring

If you are not using Mirroring, the Mirroring Monitoring metrics will be disabled by default. If at a later date you enable Mirroring, you will need to run the following command on the Agent that hosts the plugin to enable the metrics:

emctl reload agent dynamicproperties <Target_name>:microsoft_sqlserver_database

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 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 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 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 to obtain information about updates ready for download. See the Updating Enterprise Manager chapter in the Oracle Enterprise Manager 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 Administrator's Guide for steps to undeploy the plug-in.