Database Monitoring for self-hosted SQL Server

-- 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;

-- 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;

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

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

sudo service atatus-infra-agent restart

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

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