Install & configure NRDOT for MSSQL monitoring with Docker

Set up SQL Server monitoring using the NRDOT Collector as a sibling container to a SQL Server instance that's already running in Docker.

Tip

Looking to automate this at scale, or run on Kubernetes instead? Refer to Ansible install, Chef install, or Helm chart install.

Prerequisites

Docker installed on the host.
A SQL Server container already running (SQL Server 2017 or later), with the sqlcmd utility available inside it. Microsoft's mssql-server images bundle it under /opt/mssql-tools18/bin or /opt/mssql-tools/bin.
A login in the sysadmin server role (or equivalent) with its password.
A New Relic license key.
Network connectivity from the collector container to New Relic OTLP endpoints.

Create the monitoring user and grant permissions

Run the grant script inside your SQL Server container with docker exec. Replace <YOUR_MSSQL_CONTAINER_NAME>, <YOUR_ADMIN_USERNAME>, <YOUR_ADMIN_PASSWORD>, <YOUR_DB_USERNAME>, and <YOUR_DB_PASSWORD> with your own values. Adjust the sqlcmd path (/opt/mssql-tools18/bin/sqlcmd or /opt/mssql-tools/bin/sqlcmd) to match your image.

bash

$docker exec -i <YOUR_MSSQL_CONTAINER_NAME> /opt/mssql-tools18/bin/sqlcmd \
>  -S localhost -U <YOUR_ADMIN_USERNAME> -P '<YOUR_ADMIN_PASSWORD>' -C \
>  -Q "
$USE [master];
$GO
$CREATE LOGIN [<YOUR_DB_USERNAME>] WITH PASSWORD = '<YOUR_DB_PASSWORD>';
$GO
$-- Instance-level permissions
$GRANT VIEW SERVER STATE TO [<YOUR_DB_USERNAME>];
$GRANT VIEW ANY DEFINITION TO [<YOUR_DB_USERNAME>];
$GRANT VIEW ANY DATABASE TO [<YOUR_DB_USERNAME>];
$GO
$-- Grant read access privileges to all user databases
$DECLARE @name SYSNAME;
$DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
$SELECT [name]
$FROM [master].[sys].[databases]
$WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution')
$AND [state] = 0;
$OPEN db_cursor;
$FETCH NEXT FROM db_cursor INTO @name;
$WHILE @@FETCH_STATUS = 0
$BEGIN
$  BEGIN TRY
$    EXEC('USE [' + @name + '];
$      IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''<YOUR_DB_USERNAME>'')
$      BEGIN
$        CREATE USER [<YOUR_DB_USERNAME>] FOR LOGIN [<YOUR_DB_USERNAME>];
$      END;
$      GRANT VIEW DATABASE STATE TO [<YOUR_DB_USERNAME>];');
$  END TRY
$  BEGIN CATCH
$    PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
$  END CATCH
$  FETCH NEXT FROM db_cursor INTO @name;
$END
$CLOSE db_cursor;
$DEALLOCATE db_cursor;
$GO
$"

Tip

This script only creates database-level users and grants for databases that are ONLINE at the time it runs, excluding master, msdb, model, rdsadmin, and distribution. Re-run it whenever you add a new database you want monitored.

(Optional) Verify the login and its server-level permissions:

bash

$docker exec -i <YOUR_MSSQL_CONTAINER_NAME> /opt/mssql-tools18/bin/sqlcmd \
>  -S localhost -U <YOUR_ADMIN_USERNAME> -P '<YOUR_ADMIN_PASSWORD>' -C \
>  -Q "SELECT sp.name AS [User], p.permission_name AS [Permission_Granted] FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE sp.name = '<YOUR_DB_USERNAME>';"

(Optional) Verify the monitoring user can authenticate and see every database:

bash

$docker exec -i <YOUR_MSSQL_CONTAINER_NAME> /opt/mssql-tools18/bin/sqlcmd \
>  -S localhost -U <YOUR_DB_USERNAME> -P '<YOUR_DB_PASSWORD>' -C \
>  -Q "SELECT name FROM sys.databases;"

Ensure Docker network connectivity

Create a shared user-defined network (if you don't already have one) and connect your SQL Server container to it, so the collector container can resolve it by name:

bash

$docker network create <YOUR_DOCKER_NETWORK> 2>/dev/null || true
$docker network connect <YOUR_DOCKER_NETWORK> <YOUR_MSSQL_CONTAINER_NAME> 2>/dev/null || true

Tip

Both commands are safe to re-run: || true swallows the "already exists" or "already connected" errors from a prior run.

Create the NRDOT Collector configuration

This configuration focuses on essential SQL Server monitoring with the nrsqlserver receiver. Run the following on the Docker host to generate mssql-config.yaml in your current directory:

bash

$cat << 'EOF' > mssql-config.yaml
$receivers:
$  nrsqlserver:
$    collection_interval: 15s
$    username: "<YOUR_DB_USERNAME>"
$    password: "<YOUR_DB_PASSWORD>"
$    server: "<YOUR_MSSQL_CONTAINER_NAME>"
$    port: 1433
$
$    # Enable comprehensive database metrics
$    metrics:
$      sqlserver.database.count:
$        enabled: true
$      sqlserver.database.io:
$        enabled: true
$      sqlserver.database.latency:
$        enabled: true
$      sqlserver.database.operations:
$        enabled: true
$      sqlserver.database.tempdb.space:
$        enabled: true
$      sqlserver.database.tempdb.version_store.size:
$        enabled: true
$      sqlserver.deadlock.rate:
$        enabled: true
$      sqlserver.os.wait.duration:
$        enabled: true
$      sqlserver.processes.blocked:
$        enabled: true
$      sqlserver.memory.grants.pending.count:
$        enabled: true
$      sqlserver.database.file.size:
$        enabled: true
$      sqlserver.memory.area:
$        enabled: true
$
$    # Enable query sample, top query, query plan, and top procedure log collection
$    events:
$      db.server.query_sample:
$        enabled: true
$      db.server.top_query:
$        enabled: true
$      db.server.query_plan:
$        enabled: true
$      db.server.top_procedure:
$        enabled: true
$
$    # Top query collection configuration
$    top_query_collection:
$      lookback_time: 60s
$      max_query_sample_count: 1000
$      top_query_count: 250
$      collection_interval: 60s
$      collect_full_query_text: true
$      allowed_comment_keys:
$        - nr_service_guid
$
$    # Query sample collection configuration
$    query_sample_collection:
$      max_rows_per_query: 100
$      collect_full_query_text: true
$      allowed_comment_keys:
$        - nr_service_guid
$
$    # Top stored procedure collection configuration
$    top_procedure_collection:
$      max_procedure_sample_count: 1000
$      top_procedure_count: 250
$      collection_interval: 60s
$
$    resource_attributes:
$      db.system.version:
$        enabled: true
$      sqlserver.db.edition:
$        enabled: true
$
$processors:
$  memory_limiter:
$    check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
$    limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-200}
$    spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}
$
$exporters:
$  otlp:
$    endpoint: "<YOUR_NEWRELIC_OTLP_ENDPOINT>:4318"
$    headers:
$      api-key: "<YOUR_NEWRELIC_LICENSE_KEY>"
$    tls:
$      insecure: false
$    compression: gzip
$
$    sending_queue:
$      enabled: true
$      sizer: bytes
$      queue_size: 100_000_000
$      num_consumers: 10
$      batch:
$        sizer: bytes
$        max_size: 1_000_000
$        min_size: 0
$        flush_timeout: 5s
$
$service:
$  telemetry:
$    metrics:
$      level: none
$
$  pipelines:
$    metrics:
$      receivers: [nrsqlserver]
$      processors: [memory_limiter]
$      exporters: [otlp]
$
$    logs:
$      receivers: [nrsqlserver]
$      processors: [memory_limiter]
$      exporters: [otlp]
$EOF

Configuration parameters

Parameter	Description
`<YOUR_MSSQL_CONTAINER_NAME>`	The name (or Docker DNS-resolvable hostname) of your running SQL Server container.
`<YOUR_DB_USERNAME>`	The monitoring login you created in Create the monitoring user and grant permissions.
`<YOUR_DB_PASSWORD>`	The password of the monitoring login you created in Create the monitoring user and grant permissions.
`<YOUR_NEWRELIC_OTLP_ENDPOINT>`	Your New Relic OTLP endpoint (host only, no scheme or port; `:4318` is appended for you). For more information, see New Relic OTLP endpoints.
`<YOUR_NEWRELIC_LICENSE_KEY>`	Your New Relic license key.

full configuration

This configuration focuses on essential database monitoring for your Docker environment. To enable comprehensive monitoring with all available metrics, refer to the configuration reference.

Start the NRDOT Collector container

Run the collector on the same Docker network as your SQL Server container:

bash

$docker run -d \
>  --name nrdot-collector \
>  --network <YOUR_DOCKER_NETWORK> \
>  -v "$PWD/mssql-config.yaml:/etc/nrdot-collector/mssql-config.yaml" \
>  newrelic/nrdot-collector:latest \
>  --config /etc/nrdot-collector/mssql-config.yaml

Confirm the collector container is running:

bash

$docker ps --filter name=nrdot-collector

If it's not running, check its logs for errors. The most common causes are YAML indentation issues in mssql-config.yaml or an unreachable SQL Server container:

bash

$docker logs nrdot-collector

Tip

After changing mssql-config.yaml, restart the container to apply your changes:

bash

$docker restart nrdot-collector

Find and use your data

Once your data is being collected, you can access comprehensive SQL Server database monitoring through the New Relic UI.

To find your SQL Server database entity in New Relic:

Go to one.newrelic.com > All capabilities > Databases.
From the Entity type dropdown, select MSSQL instance, then click Apply.
Select your SQL Server database from the list of entities.

Metrics reference

Learn about the available metrics collected by the NRDOT Collector.

Troubleshooting

Learn how to troubleshoot your MSSQL monitoring setup in New Relic.

Set up APM-database correlation

Learn how to correlate your application performance with database operations in New Relic.

Install & configure NRDOT for MSSQL monitoring with Docker

Tip

Prerequisites .css-21sua1{background:none;border:none;width:0;padding:0;}

Create the monitoring user and grant permissions

Tip

Ensure Docker network connectivity

Tip

Create the NRDOT Collector configuration

Configuration parameters

full configuration

Start the NRDOT Collector container

Tip

Find and use your data

Related documentation

Metrics reference

Troubleshooting

Set up APM-database correlation

Prerequisites