Database Monitoring for Azure SQL Database

Atatus monitors Azure SQL Database by capturing query metrics, execution plans, and database events. This guide provides the steps needed to enable monitoring and configure your environment for complete observability of your Azure-hosted SQL databases.

1. Grant the Agent Access

Create a read-only login to connect to your server and grant the required Azure SQL Roles:

CREATE LOGIN atatus WITH PASSWORD = '<PASSWORD>';
CREATE USER atatus FOR LOGIN atatus;

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER atatus;
ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER atatus;

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

Grant the Agent access to each additional Azure SQL Database on this server:

-- Repeat this for each target database
USE <DATABASE_NAME>;
CREATE USER atatus FOR LOGIN atatus;

2. Configure the Atatus Infra Agent

In Azure SQL Database, each database runs in its own isolated environment and is treated as a separate host. Even if the databases are part of an elastic pool, each one is monitored individually.

Step 1: Configure the SQL Server Integration

Edit the /etc/atatus-infra-agent/conf.d/sqlserver.d/sqlserver.yml configuration file in your Atatus Agent installation directory:

metrics:
  - hosts:
      - "<SERVER_NAME>.database.windows.net"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    db_name: <DATABASE_1>
    dbm: true
    dbm_mssqlserver_options:
      collect_settings:
        enabled: true
      collect_database_info:
        enabled: true
        auto_discovery: false

  - hosts:
      - "<SERVER_NAME>.database.windows.net"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    db_name: <DATABASE_2>
    dbm: true
    dbm_mssqlserver_options:
      collect_settings:
        enabled: true
      collect_database_info:
        enabled: true
        auto_discovery: false

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
    db_name: <DB_NAME>
    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_HOST>"
    port: 1433
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    dbm: true
    db_name: <DB_NAME>
    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      

​1. Grant the Agent Access

​2. Configure the Atatus Infra Agent

​Step 1: Configure the SQL Server Integration

​Step 2: Restart the Atatus Infra Agent

​Enable Schema Collection for SQL Server 2017+ (Optional)

​Requirements

​Configuration Examples

​Collect Metrics Using Custom Queries (Optional)

​Configuration Example

1. Grant the Agent Access

2. Configure the Atatus Infra Agent

Step 1: Configure the SQL Server Integration

Step 2: Restart the Atatus Infra Agent

Enable Schema Collection for SQL Server 2017+ (Optional)

Requirements

Configuration Examples

Collect Metrics Using Custom Queries (Optional)

Configuration Example