Add Microsoft SQL Server

You can add Microsoft SQL Server entities using the Add Entity UI or using the cloud agent command line interface (omcli) with the appropriate JSON files.

Step 1: Prepare Microsoft SQL Server for monitoring.

Prerequisites

To enable monitoring for a Microsoft SQL Server Database, you can create a special database user as follows.

Create a user  (for example, moncs) and map the new user to the master and msdb databases. Then, give this user the following minimum privileges.

Note:

Beginning with Oracle Management Cloud 1.31, sqladmin-related privileges are no longer required.
CREATE LOGIN moncs
WITH PASSWORD = 'moncs';
GO
CREATE USER moncs FOR LOGIN moncs;
GO

Then, map the user moncs:

  1. From the Security menu, select Logins moncs.

  2. Right-click on moncs and select Properties.

  3. Select User Mapping.

  4. Map to all system and user databases:

USE master;
GRANT VIEW ANY DATABASE TO moncs;
GRANT VIEW ANY definition to moncs;
GRANT VIEW server state to moncs;
GRANT SELECT ON [sys].[sysaltfiles] TO [moncs];
GRANT execute on sp_helplogins to moncs;
GRANT execute on sp_readErrorLog to moncs;

GRANT EXECUTE ON dbo.xp_regread TO moncs;
USE msdb;
GRANT SELECT on dbo.sysjobsteps  TO moncs;
GRANT SELECT on dbo.sysjobs  TO moncs;
GRANT SELECT on dbo.sysjobhistory TO moncs;

For connecting to SQL server database with SSL encryption, do the following:

  1. Ensure the SQL server installation has the required updates for TLS 1.2 support as described in the following document.

    https://support.microsoft.com/en-in/help/3135244/tls-1-2-support-for-microsoft-sql-server

  2. Create a server certificate for the SQL server host.

    Set up the certificate as mentioned in the section “Install a certificate on a server with Microsoft Management Console (MMC)” in the following document: https://support.microsoft.com/en-in/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

  3. Install the server certificate for the SQL server instance.

    Set up the SQL server instance to use the server certificate created above, as mentioned in the section “To install a certificate for a single SQL Server instance” in the following document: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-2016

  4. Export to a file, the root certification authority’s certificate that has signed the SQL server host certificate, and copy this file to the cloud agent host.

    Export the certificate as described in section “Enable encryption for a specific client” in the following document: https://support.microsoft.com/en-in/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi

  5. Create a trust store on the cloud agent host, and import the root certification authority’s certificate exported above.
    keytool -import -file .\ca_cert.cer –alias mytrust -keystore .\trustStore.jks -storetype jks
  6. Form the connection URL pointing to the trust store.
    jdbc:sqlserver://xxx.xxx.com:1433;encrypt=true;trustServerCertificate=false;trustStore=C:\trustStore.jks;trustStorePassword=xxxx;

Step 2: Decide how you want to add the Microsoft SQL Server.

You can add Microsoft SQL Server entities using one of two ways:

  • Add them from UI
  • Use the agent's omcli add_entity command with the appropriate JSON files

Adding Entities from the UI

  1. From the Management Cloud main menu, select Administration, Discovery, and then Add Entity. The Add Entity page displays.
  2. Select the Microsoft SQL Server Entity Type.
  3. Enter the following UI properties.
    Microsoft SQL Server Database UI Fields
    • Entity Name: Name displayed in the Oracle Management Cloud console.
    • JDBC URL: The connection URL for the MS SQL Server database. The URL follows the formats:
      • Connect to default instance.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>
      • Connect to named instance.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>\<Instance Name> (or)
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>;instanceName=<instance-name>
      • Connect to instance by specifying port.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>:<SQL Server Database Port>
      • Connecting with SSL encryption.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>\<Instance Name>;encrypt=true;trustServerCertificate=false;trustStore=<Path to trust store file>;trustStorePassword=<trust store password>

        See the prerequisites section for details on setting up the certificates and trust store.

    • Cloud Agent: Agent monitoring the host on which the database is installed.

    Monitoring Credentials

    • Username: MS SQL Server database user name to be used for monitoring.
    • Password: MS SQL Server database monitoring user's password.

See Add Entities from the Console for detailed instructions on using the Add Entity UI.

Using omcli and the Appropriate JSON Files

  1. Download and extract the required JSON file(s) from the master JSON zip file. See the table below for the specific JSON files you'll need.
  2. Edit the file(s) and specify the requisite properties shown below.
    Microsoft SQL Server Database JSON Files and Properties

    Definition File: omc_sqlserver_db_sample.json

    • name: Your Microsoft SQL Server database name.
    • displayName: Name displayed in the Oracle Infrastructure Monitoring User Interface.
    • timezoneRegion: Time zone of your entity. It is recommended that you use the long values IANA-maintained TZ database time zones. For example: America/New_York
    • url: Under “value”, provide the connection URL for the MS SQL Server database. The URL follows the formats:
      • Connect to default instance.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>
      • Connect to named instance.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>\\<Instance Name> (or)
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>;instanceName=<instance-name>
        
      • Connect to instance by specifying port.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>:<SQL Server Database Port>
      • Connecting with SSL encryption.
        jdbc:sqlserver://<Fully-qualified SQL Server Database Host Name>\\<Instance Name>;encrypt=true;trustServerCertificate=false;trustStore=<Path to trust store file>;trustStorePassword=<trust store password>

        See the prerequisites section for details on setting up the certificates and trust store.

    Credential File: omc_sqlserver_creds.json

    • DBUserName: Under “value”, within the square brackets, provide the MS SQL Server database user name to be used for monitoring. You must have defined this user in the Prerequisite Tasks step.
    • DBPassword: Under “value”, within the square brackets, provide the MS SQL Server database monitoring user's password.

    Do not remove the square brackets.

  3. Add the entity using omcli.
    omcli add_entity agent DEFINITION_FILE [-credential_file CREDENTIAL_FILE [-encryption_method_gpg]] 
  4. Verify the status of the newly added entity.
    omcli status_entity agent DEFINITION_FILE

See step 4. Adding Entities to Your Service of Add Entities Using JSON Files for more information.

Step 3: (Optional but recommended) Set up alerts.

To enable lights-out monitoring, you can set up alert rules to generate alerts and send notifications if your entities have performance issues.

See Set Up Alert Rules and Set Up Alert Thresholds and Notifications.

Troubleshooting

If you run into any issues regarding discovery or monitoring of Microsoft SQL Server, see the following: