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