Prepare SQL Server

Set up a monitoring login and Query Store on your SQL Server before installing the collector. The collector reads SQL Server in three places:

Where it runs	[component.provider] type	Login	Host metrics from
Your own server or VM	self_hosted	SQL login and password	node_exporter on the host
AWS RDS for SQL Server	aws_rds	SQL login and password	CloudWatch
Azure SQL Database	azure_sql	Microsoft Entra ID, or a SQL login and password	Azure Monitor

Self-managed and AWS RDS for SQL Server need collector 0.15.0 or later. Azure SQL Database needs 0.15.1 or later. An earlier collector exits at startup with unsupported combination (engine=sqlserver, provider=self_hosted, auth=password).

Supported versions

The collector supports SQL Server 2022 and SQL Server 2025, and Azure SQL Database.

An older SQL Server, such as 2019, is refused at discovery. The collector logs unsupported server version 15.0.4500.2: SQL Server 2022 (16) or 2025 (17) and the server does not appear in DBGorilla.

On AWS RDS, the Express, Web, Standard and Enterprise editions are all supported.

Database grants

Self-managed SQL Server and AWS RDS for SQL Server use the grants below. Azure SQL Database grants access through server roles instead, under Azure SQL Database.

Create a dedicated login for the collector. It needs server-level grants in master and a user in each database you want monitored.

Required

-- in master
CREATE LOGIN dbg_collector WITH PASSWORD = '<strong password>';
GRANT VIEW SERVER STATE TO dbg_collector;      -- sessions, waits and performance counters
GRANT VIEW ANY DEFINITION TO dbg_collector;    -- server logins and roles

-- in each database you want monitored
CREATE USER dbg_collector FOR LOGIN dbg_collector;
GRANT VIEW DEFINITION, VIEW DATABASE STATE, SHOWPLAN TO dbg_collector;
GRANT SELECT ON sys.sql_expression_dependencies TO dbg_collector;

Replace <strong password> with a password of at least eight characters, using at least three of uppercase letters, lowercase letters, digits, and symbols. SQL Server refuses a weaker password with Password validation failed, and every statement after it fails too.

CREATE LOGIN and the two server grants run once. Run the database block in every database you want to monitor, including databases you add later.

A database the login is not mapped into is skipped, and the collector will produce warning logs with details.

On AWS RDS, the master user may be refused GRANT VIEW ANY DEFINITION. Skip that line. Without it, server logins and roles are left out of the schema, and the per-database VIEW DEFINITION grant still covers everything else.

SHOWPLAN is required if you wish to allow DBGorilla to ask the database for an estimated plan for a query without executing it. SQL Server also checks that the login could run the query, so Explain needs SELECT on the tables it reads as well, granted below.

sys.sql_expression_dependencies is readable only by db_owner by default. The grant on it lets the collector order views, functions and procedures that reference each other in order to produce an accurate topology of the schema. Without it, objects that reference each other may be copied out of order.

Required for Explain and statistics

-- in each database you want monitored
GRANT SELECT TO dbg_collector;                    -- every schema in the database
-- or, one schema at a time:
GRANT SELECT ON SCHEMA::sales TO dbg_collector;

The configuration files below turn on Explain and statistics, and SQL Server refuses both without SELECT.

SELECT lets the collector access statistics the planner uses to execute your queries. This permission is required in order for DBGorilla to conduct accurate experiments on schema-only clones of your database. It also lets Data Access read rows if you enable the execute_query feature.

On SQL Server, the grants are what keep the collector read-only

T-SQL has no read-only transaction mode so if Data Access is enabled the T-SQL that users are allowed to run is based on the grants given to the collector. With the grants above, a DELETE fails with The DELETE permission was denied.

What each grant gives

Grant	What DBGorilla gets	Without it
VIEW SERVER STATE	Server metrics, waits and session activity	The collector connects and captures the schema, but every metrics and activity scrape fails with The user does not have permission to perform this action.
VIEW ANY DEFINITION	Server logins and roles	Those are left out. Everything else is captured.
CREATE USER	Access to that database	The database is skipped, and the collector logs the collector login cannot open this database, so it is not monitored.
VIEW DEFINITION	Views, procedures, functions, and database users and roles	Without it and without VIEW ANY DEFINITION, those objects are left out of the schema.
VIEW DATABASE STATE	Per-database state, including Query Store	Nothing is lost while the login holds VIEW SERVER STATE, which includes it.
SHOWPLAN	Query plans	DBGorilla cannot fetch a plan, and reports SHOWPLAN permission denied in database 'app'.
SELECT ON sys.sql_expression_dependencies	Dependencies between views, procedures and tables	Without it and without SELECT, DBGorilla shows a warning under the database that names this grant.
SELECT	Query plans, and the read-only queries DBGorilla runs	SQL Server compiles a plan only for a login that may run the statement, so plans fail with The SELECT permission was denied on the object.

Query Store

Query Store access is required in order for DBGorilla to be able to understand the real T-SQL workload running on your database. It records query patterns and per-query statistics.

You can run the following command to ensure that the Query Store is enabled.

ALTER DATABASE [app] SET QUERY_STORE = ON
  (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);

Without Query Store, the database’s schema and metrics are still collected, but DBGorilla will have no workload data that can be used to provide slow query analysis and query improvement recommendations.

-- run in each database
SELECT actual_state_desc, query_capture_mode_desc
FROM sys.database_query_store_options;

What you see	What DBGorilla gets
READ_WRITE, ALL	The complete workload.
READ_WRITE, AUTO	Part of the workload. AUTO is the default, and it skips infrequent and cheap ad hoc statements.
READ_ONLY	Only what was recorded before. Query Store switches to READ_ONLY when it reaches its maximum size, and then records nothing new. Raise MAX_STORAGE_SIZE_MB and set it back to READ_WRITE.
OFF	No workload for that database.

A new database on SQL Server 2022 or 2025 already has Query Store on, but captures AUTO, which skips a query that runs once. ALL records it.

Turning Query Store on needs no database restart. You do not need to restart the collector after turning Query Store on. The collector picks up the change within three minutes.

Azure SQL Database

Each Azure SQL database is its own isolated SQL instance, so the collector monitors one database per component. Two databases on the same logical server are two [[component]] blocks.

The collector signs in to Azure as a managed identity, a workload identity or a service principal. Give that identity the Reader and Monitoring Reader roles on the logical server, which apply to every database on it. The collector reads the database’s configuration and its Azure Monitor metrics with them.

Then create the identity’s login once per logical server, connected to master as the server’s Microsoft Entra admin:

-- in master
CREATE LOGIN [dbg-collector-mi] FROM EXTERNAL PROVIDER;   -- or: CREATE LOGIN dbg_collector WITH PASSWORD = 'replace-me';
ALTER SERVER ROLE ##MS_DatabaseConnector## ADD MEMBER [dbg-collector-mi];
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [dbg-collector-mi];
ALTER SERVER ROLE ##MS_DefinitionReader##  ADD MEMBER [dbg-collector-mi];

Explain, statistics and schema ordering need grants that no server role gives. Add them in each database you want to monitor:

-- in each database you want monitored
CREATE USER [dbg-collector-mi] FROM LOGIN [dbg-collector-mi];
GRANT SELECT, SHOWPLAN TO [dbg-collector-mi];
GRANT SELECT ON sys.sql_expression_dependencies TO [dbg-collector-mi];

Query Store is on by default in Azure SQL Database, capturing AUTO. Set QUERY_CAPTURE_MODE = ALL in each database for the complete workload, as in Query Store.

Collector configuration

Self-managed SQL Server

DBGorilla can only capture host metrics on Linux hosts

DBGorilla currently only supports self-managed databases running on Linux. Windows support will be supported at a later date.

Run node_exporter on the database host for host metrics, as described in node_exporter. The collector finds it on port 9100 and adds CPU, memory, disk and network metrics within a minute.

The collector runs as a Docker container that can reach the server. Copy the agent id, tenant id and secret from the app when you add a collector.

[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = "${DBG_SERVER_SECRET}"

[[component]]
name   = "staging-sqlserver"
engine = "sqlserver"

[component.provider]
type = "self_hosted"

[component.auth]
method   = "password"
user     = "dbg_collector"
password = "${SQLSERVER_PASSWORD}"

[component.connect]
host      = "sqlserver.internal"
port      = 1433
databases = []                           # empty: every user database the login can open
ca_cert   = "/etc/dbg/sqlserver-ca.pem"  # the CA that signed the server certificate

[topology]
interval = "60s"

[commands]
enabled = true
allowed = ["explain", "collect_statistics"]   # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands

The connection is encrypted, and the server’s certificate is verified. ca_cert is the CA that signed your server’s certificate. Without it, a server signed by a private CA is refused with the server's certificate was rejected during the TLS handshake: UnknownIssuer. On a test server with a self-signed certificate, use trust_server_certificate = true in place of ca_cert. Setting both is refused.

Store the secrets in a file with mode 0600, and keep the file out of version control:

secrets.env

DBG_SERVER_SECRET=your-client-secret
SQLSERVER_PASSWORD=your-database-password

Then run the collector with the config and the CA file mounted. Pin a release tag in place of latest for anything you keep running.

docker run --rm \
  -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
  -v "$PWD/sqlserver-ca.pem:/etc/dbg/sqlserver-ca.pem:ro" \
  --env-file secrets.env \
  dbgorillapublic.azurecr.io/dbg-collector:latest \
  --config-file /etc/dbg-collector/collector.toml

Within a minute, the collector logs discovered scopes with your databases listed, and the server appears in DBGorilla under its name. Queries follow once Query Store has recorded some.

AWS RDS for SQL Server

RDS for SQL Server signs in with a SQL login and password. The collector reads host metrics from CloudWatch, so run it where its AWS identity can describe the RDS instance and read its CloudWatch metrics.

[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = "${DBG_SERVER_SECRET}"

[[component]]
name   = "prod-rds-sqlserver"
engine = "sqlserver"

[component.provider]
type        = "aws_rds"
region      = "us-east-1"
instance_id = "prod-sqlserver"

[component.auth]
method   = "password"
user     = "dbg_collector"
password = "${SQLSERVER_PASSWORD}"

[component.connect]
host      = "prod-sqlserver.abc.us-east-1.rds.amazonaws.com"
port      = 1433
databases = ["app"]

[topology]
interval = "60s"

[commands]
enabled = true
allowed = ["explain", "collect_statistics"]   # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands

The collector image already trusts the AWS RDS certificate authorities, so RDS needs no ca_cert. The rdsadmin database is always skipped by default.

Azure SQL Database

Ensure that resource_id matches the database

The resource_id and database under [component.connect] must refer to the same database. You can print the id with az sql db show --query id.

[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = "${DBG_SERVER_SECRET}"

[[component]]
name   = "orders-db"
engine = "sqlserver"

[component.provider]
type        = "azure_sql"
resource_id = "/subscriptions/<subscription>/resourceGroups/<group>/providers/Microsoft.Sql/servers/<server>/databases/orders"
# client_id = "<user-assigned managed identity client id>"

[component.auth]
method = "entra"
user   = "dbg-collector-mi"   # the login created in master

[component.connect]
host     = "<server>.database.windows.net"
database = "orders"

[topology]
interval = "60s"

[commands]
enabled = true
allowed = ["explain", "collect_statistics"]   # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands

Leave client_id unset to use AZURE_CLIENT_ID from the environment, or the system-assigned managed identity. For a service principal, set AZURE_CLIENT_ID, AZURE_TENANT_ID and AZURE_CLIENT_SECRET in the collector’s environment. On AKS with workload identity, the federated token is picked up from AZURE_FEDERATED_TOKEN_FILE.

To sign in with a SQL login instead, set method = "password" with user and password as in the other two files.

Connection keys

engine = "sqlserver" supports the following keys in [component.connect].

Key	Type	Default	Description
host	string	Required	Server host.
port	integer	1433	Server port.
databases	list	[]	Databases to monitor. Empty monitors every user database the login can open.
exclude_databases	list	[]	Databases to skip, applied after databases.
database	string	none	Azure SQL Database only: the one database this component is. Cannot be combined with databases or exclude_databases.
encrypt	bool	true	Encrypt the connection. Set false only for a local test server.
trust_server_certificate	bool	false	Encrypt without verifying the server certificate. For a self-signed test server only.
ca_cert	path	system trust store	CA bundle the server certificate is verified against. Cannot be combined with trust_server_certificate.

Database names match case-insensitively.

Ensure custom certificates are mounted if required

Certificate paths are paths inside the container. Mount the files in and point at them by absolute path.