Atatus monitors self-hosted SQL Server instances, capturing performance metrics and offering query-level insights such as execution plans, wait events, and database load.

Note: SQL Server versions 2012, 2014, 2016, 2017, 2019, and 2022 are fully supported.

1. Grant the Agent Access

Create a read-only login for the Atatus Agent and assign the necessary permissions.

SQLSERVER 2014+

-- Create login and user
CREATE LOGIN atatus WITH PASSWORD = '<PASSWORD>';
CREATE USER atatus FOR LOGIN atatus;

-- Grant global monitoring permissions
GRANT CONNECT ANY DATABASE TO atatus;
GRANT VIEW SERVER STATE TO atatus;
GRANT VIEW ANY DEFINITION TO atatus;

USE msdb;
CREATE USER atatus FOR LOGIN atatus;
GRANT SELECT TO atatus;

SQLSERVER 2012

-- Create login and user
CREATE LOGIN atatus WITH PASSWORD = '<PASSWORD>';
CREATE USER atatus FOR LOGIN atatus;

-- Grant global monitoring permissions
GRANT VIEW SERVER STATE to atatus;
GRANT VIEW ANY DEFINITION to atatus;

USE msdb;
CREATE USER atatus FOR LOGIN atatus;
GRANT SELECT to atatus;

Create the atatus user in each additional application database:

USE [database_name];
CREATE USER atatus FOR LOGIN atatus;

2. Configuration

Step 1: Configure the SQL Server Integration

Edit the conf.d/sqlserver.d/sqlserver.yml configuration file in your Atatus Agent installation directory:

metrics:
  - hosts:
      - "<MSSQL_SERVER_HOST>"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    dbm: true

Step 2: Restart the Atatus Infra Agent

sudo service atatus-infra-agent restart

Enable Schema Collection for SQL Server 2017+ (Optional)

Atatus Agent can collect schema information from SQL Server 2017 and above.

Requirements

  • Agent v3.4.0 or higher
  • collect_database_info: true
  • collect_settings: true
  • Use auto_discovery: true to auto-detect all databases

Configuration Examples

  • Collect schemas from all logical databases
metrics:
  - hosts:
      - "<MSSQL_SERVER_HOST>"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    dbm: true
    dbm_mssqlserver_options:
      collect_settings:
        enabled: true
      collect_database_info:
        enabled: true
        auto_discovery: true
        # include:
        #   - db_name1
        #   - db_name2
        # exclude:
        #   - db_name1
        #   - db_name2

Collect Metrics Using Custom Queries (Optional)

You can collect custom metrics from SQL Server using the custom_queries option in your configuration.

Configuration Example

metrics:
  - hosts:
      - "<MSSQL_SERVER_HOST>"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    dbm: true
    dbm_mssqlserver_options:
      additional_metrics_options:
# The following is an example of a custom query configuration.        
        custom_queries:
          - query: SELECT age, salary, hours_worked, name FROM hr.employees;
            columns:
              - name: custom.employee_age
                type: gauge
              - name: custom.employee_salary
                type: gauge
              - name: custom.employee_hours
                type: count
              - name: name
                type: tag
            tags:
              - 'table:employees'
        max_custom_queries: 20