# 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](#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

```sql
-- 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

```sql
-- 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](/docs/reference/collector-commands/#execute_query-off-until-you-add-it) read rows
if you enable the `execute_query` feature. 

:::caution[On SQL Server, the grants are what keep the collector read-only]
T-SQL has no read-only transaction mode so if [Data Access](/docs/reference/collector-commands/#execute_query-off-until-you-add-it)
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.

```sql
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.

```sql
-- 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:

```sql
-- 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:

```sql
-- 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](#query-store).

## Collector configuration

### Self-managed SQL Server

:::caution[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](/docs/connect/self-managed/#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.

```toml
[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:

```ini
# 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.

```sh
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.

```toml
[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

:::caution[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`.
:::

```toml
[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.

:::caution[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.
:::