MsSQL agent

Installing the Microsoft SQL Server (MSSQL) Agent helps managing and automating various tasks, such as backups, maintenance, and monitoring, for your SQL Server instances. It helps maintain the health and performance of your SQL Server environment.

Prerequisites

  • For prerequisite checks for agent installation, see Prerequisite checks for agent installation.
  • URL/IP of the SixthSense collector (your tenant URL, which will be provided by Customer Support).
  • Access token from the SixthSense portal. For accessing the token, see Accessing your Access Token under Getting started with the Observability Portal.
  • MsSQL agent version "latest".
  • Database details (host name, port, database name, database user credentials).
  • Package token to download agent (provided by the onboarding team)
  • Database User Permissions: Grant SELECT permission for all keyspaces to db_user.

1. Downloading and extracting the agent

For Linux:

a. Download SixthSense MsSQL DB Agent using the following command.

curl --header "PRIVATE-TOKEN:y1KmKXF13nc_Yts7LVV4" --output meter-agent.zip "https://artifacts-observability.sixthsense.rakuten.com/api/v4/projects/32/packages/generic/METER-AGENT/latest/meter-agent.zip"

b. Extract the meter-agent.zip file and edit the configuration in the yaml file as given in this document below.

For Windows:

a. Open cmd and use the following curl command to download SixthSense MsSQL DB Agent.

curl --header "PRIVATE-TOKEN:{TOKEN}" --output meter-agent.zip "https://artifacts-observability.sixthsense.rakuten.com/api/v4/projects/32/packages/generic/METER-AGENT/v4.1.1/meter-agent.zip"

b. Extract meter-agent.zip file and edit the configuration in the yaml file as given in this document below.

2. Creating the SixthSense User on Master DB

Use the following command.

USE master;
CREATE LOGIN sixthsense WITH PASSWORD = 'xxxxxxxxxxx';
CREATE USER sixthsense FOR LOGIN sixthsense;

3. Granting permission for the SixthSense User on Master DB

GRANT CONNECT SQL TO sixthsense;
GRANT VIEW SERVER STATE TO sixthsense;
-- Goes through each user database and adds public permissions
DECLARE @name NVARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE('USE "' + @name + '"; CREATE USER sixthsense FOR LOGIN sixthsense;' );
FETCH next FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

4. Configuring the MsSQL agent

Create a config.yaml file in the same directory as the agent.

# sixthsense-kubernetes-agent can be configured via yaml or environment variables
#
# Note: Environment Variables are given higher priority over yaml file
#
# Following are the environment variables that can be set:
# ENV VARIABLE CORRESPONDS TO
# SS_SERVICE_NAME service_name
# SS_AUTHENTICATION authentication
# SS_BACKEND sixthsense_backend
# SS_LOGGING logging
# SS_SECURE secure
sixthsense:
service_name: <name of the service>
# Login to Sixthsense UI --> Go to Settings on Side Navbar --> Copy Access Token
authentication: <authentication token from dashboard>
# debug|error|info
logging: <level> (level supported: debug ,error ,info)
sixthsense_backend: <Name of the Collector>
# SSL connection with backend true|false
secure: <boolean, flag if ssl is required>
# MY | ES | MS | CB | OR
type: <Database Type>(Select MY for MySql)
# cron: "* * * * *"(optional)
hostname: <Hostname of the database>
port: <Port number of the database>
user: <Database User>
password: <Password for the database user>
# database: <Name of the database>(optional)
# socket: <Socket File> (Set this if your are running MySql using sock file Ex: /tmp/mysql.sock)
note
  • Use either the sql port or socket.
  • It supports instance name also with hostname

Example: hostname: Hostname\SQLExpress

5. Running the agent

For Linux:

Run MsSQL agent in the terminal using the following commnad.

./meter-agent path/to/config/ss-mssql-config.yaml

For Windows:

a. Open command prompt with run as Administrator and enter the following command:

nssm.exe install “Service_Name”

In the following NSSM service installer screen, the Application tab is selected by default.

NSSM installer

b. Enter the following details:

  • Path: meter-agent.exe full path
  • Startup directory: folder path
  • Arguments: yaml full name

c. Click the Details tab.

NSSM installer

d. Enter the following details:

  • Display name: service display name
  • Description: service description
  • Setup type: select type for setup such as automatic.

e. Click the Log on tab.

NSSM installer

f. Select Local System account radio button.

g. Select Allow service to interact with desktop check box.

h. Click Install service.

The following screen is displayed.

Cloud Monitoring dashboard

Editing the configuration in the yaml file

In yaml file, add the following configuration details:

  1. service_name: Servive name will display on UI
  2. authentication: Get access token from dashboard under setting
  3. sixthsense_backend: Backend collector URL
  4. hostname: hostname
  5. port: port
  6. user: db_user
  7. password: db_user_password
note

It supports instance name also with hostname

Example:

hostname: Hostname/SQLExpress