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.
Then, map the user
For connecting to SQL server database with SSL encryption, do the following:
|
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
- From the Management Cloud main menu, select Administration, Discovery, and then Add Entity. The Add Entity page displays.
- Select the Microsoft SQL Server Entity Type.
- 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.
- Connect to default instance.
- 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
- 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.
- 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.
- Connect to default instance.
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.
- Add the entity using
omcli
.omcli add_entity agent DEFINITION_FILE [-credential_file CREDENTIAL_FILE [-encryption_method_gpg]]
- 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: