Install & configure NRDOT for MSSQL monitoring with Ansible

You can install and configure the NRDOT Collector for SQL Server monitoring using the newrelic.newrelic_install Ansible role. The role installs the collector, creates or authorizes the monitoring identity, and configures the collector in a single play.

Prerequisites

A New Relic account with a valid license key.
A New Relic account ID.
Microsoft SQL Server 2017 or later.
Ansible Core 2.13 or 2.14, Python 3.10, and the ansible.windows and ansible.utils collections.
Self-hosted SQL Server:
- SQL Server Authentication: A Linux or Windows collector host, and a login in the sysadmin server role (or equivalent) with its password.
- Windows Authentication or gMSA: A Windows collector host, an administrator PowerShell session, and a Windows account or Group Managed Service Account (gMSA) with the required permissions.
SQL Server on AWS RDS:
- A Linux or Windows collector host with network connectivity to the database endpoint (inbound access granted on the SQL Server port within the security group), alongside the database master username and password.
- Windows Domain Authentication or gMSA: A Windows collector host and an instance joined to an AWS Managed Microsoft Active Directory domain.
Linux collector host: The curl utility, systemd, and the sqlcmd utility installed and available on PATH (or located under /opt/mssql-tools18/bin or /opt/mssql-tools/bin).
Windows collector host: The sqlcmd.exe utility installed and available on PATH.
Network connectivity to the New Relic OTLP endpoint corresponding to the target region.

Install the role

bash

$ansible-galaxy install newrelic.newrelic_install

Make sure the required collections are also installed:

bash

$ansible-galaxy collection install ansible.windows ansible.utils

Configure the playbook

This role monitors one or more SQL Server instances from a single collector. Instead of a single host/port/credential set, it reads two files that must already exist on the target host: an instances file (YAML) listing every instance to monitor, and a secrets file (KEY=VALUE) with an admin login and password for each, indexed to match.

Create the instances file on the target host. Add one entry per SQL Server instance you want this collector to monitor, or just one entry if you're only running a single instance:
```
instances:
  - host: localhost
    port: 1433
    login_name: newrelic
```
Create the secrets file on the target host with an admin login and password for each instance, indexed to match the instances file's order. The admin login must be a member of the sysadmin role (or equivalent):
bash
```
$NR_CLI_MSSQL_ADMIN_USER_1=<YOUR_ADMIN_USERNAME>
$NR_CLI_MSSQL_ADMIN_PASSWORD_1=<YOUR_ADMIN_PASSWORD>
```

Then create a file named playbook.yml and copy the following content into it. Set targets to mssql-otel and replace the placeholder values with your own:

- name: Install New Relic NRDOT for SQL Server
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host>
    NR_CLI_MSSQL_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>

Conseil

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable

Description

Default

NR_CLI_MSSQL_CONFIG_PRESET

NRDOT configuration:

1 for Basic
2 for Advanced

1

NR_CLI_MSSQL_INSTANCES_FILE

Required. Path to the instances YAML file, already present on the target host.

None

NR_CLI_MSSQL_SECRETS_FILE

Required. Path to the secrets file (KEY=VALUE, one admin login/password pair per instance), already present on the target host.

None

Conseil

To monitor more than one SQL Server instance from this collector, add more entries to the instances file and matching numbered credentials (_2, _3, ...) to the secrets file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install.

This target is only supported on a Windows collector host. It uses Windows authentication (integrated security=true) instead of SQL logins, and monitors one or more SQL Server instances from a single collector using an instances file (YAML, host/port per instance, no secrets file: one Windows identity monitors every instance).

Create the instances file on the target host, with just one entry if you're only running a single instance. Only host and port are needed (no login_name):

instances:
  - host: sql01.contoso.com
    port: 1433
  - host: sql02.contoso.com
    port: 1433

For a named instance, use host plus its static TCP port rather than host\INSTANCE.

Then create a file named playbook.yml and copy the following content into it. Set targets to mssql-otel-winauth and replace the placeholder values with your own:

- name: Install New Relic NRDOT for SQL Server (Windows Auth)
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-winauth
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_AUTH_MODE: <1 for Windows Domain Auth, 2 for gMSA, default 1>
    NR_CLI_MSSQL_WINAUTH_LOCATION: <1 for same host as SQL Server, 2 for a different host, default 1; Windows Domain Auth only>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host>
    NR_CLI_MSSQL_WIN_ACCOUNT: <Windows domain account, DOMAIN\username; required only for Windows Domain Auth on a different host>
    NR_CLI_MSSQL_WIN_PASSWORD: <password for that account; required only for Windows Domain Auth on a different host>
    NR_CLI_MSSQL_GMSA_ACCOUNT: <gMSA account, DOMAIN\gMSAName$; gMSA only>

Conseil

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable	Description	Default
`NR_CLI_MSSQL_CONFIG_PRESET`	NRDOT configuration: `1` for Basic `2` for Advanced	`1`
`NR_CLI_MSSQL_AUTH_MODE`	SQL Server authentication: `1` for Windows Domain Auth `2` for gMSA	`1`
`NR_CLI_MSSQL_WINAUTH_LOCATION`	Windows Domain Auth only: `1` if the collector runs on the same host as SQL Server (the installing user's identity is used and the service stays `LocalSystem`, so no domain account is needed) `2` if it runs on a different host	`1`
`NR_CLI_MSSQL_INSTANCES_FILE`	Required. Path to the instances YAML file (`host`/`port` per instance), already present on the target host.	None
`NR_CLI_MSSQL_WIN_ACCOUNT` / `NR_CLI_MSSQL_WIN_PASSWORD`	Required only when `NR_CLI_MSSQL_AUTH_MODE` is `1` and `NR_CLI_MSSQL_WINAUTH_LOCATION` is `2`. Windows domain account (`DOMAIN\username`) and password to grant permissions to and run the collector service as. Not needed for the same-host flow or for gMSA.	None
`NR_CLI_MSSQL_GMSA_ACCOUNT`	Required if `NR_CLI_MSSQL_AUTH_MODE` is `2`. gMSA account (`DOMAIN\gMSAName$`) to grant permissions to.	None

Conseil

To monitor more than one SQL Server instance from this collector, add more entries to the instances file. One auth mode and one Windows identity are used for every instance in the file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install. The same-host flow (NR_CLI_MSSQL_WINAUTH_LOCATION 1) only works for instances on the target host itself. Use the different-host flow or gMSA for remote or clustered instances.

This role monitors one or more RDS SQL Server endpoints from a single collector, using the same instances file / secrets file pattern as self-hosted (both files must already exist on the target host).

Create the instances file on the target host, using the RDS endpoint as host, with just one entry if you're only monitoring a single RDS instance:
```
instances:
  - host: mydb1.abcdefg12345.us-east-1.rds.amazonaws.com
    port: 1433
    login_name: newrelic
```
Create the secrets file on the target host with the RDS master username and password for each instance, indexed to match the instances file's order:
bash
```
$NR_CLI_MSSQL_ADMIN_USER_1=admin
$NR_CLI_MSSQL_ADMIN_PASSWORD_1=YourMasterPassword1
```

Then create a file named playbook.yml and copy the following content into it. Set targets to mssql-otel-rds and replace the placeholder values with your own:

- name: Install New Relic NRDOT for SQL Server on RDS
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-rds
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host, using RDS endpoints as host>
    NR_CLI_MSSQL_SECRETS_FILE: <path to the secrets file (KEY=VALUE per instance) on the target host>

Conseil

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable

Description

Default

NR_CLI_MSSQL_CONFIG_PRESET

NRDOT configuration:

1 for Basic
2 for Advanced

1

NR_CLI_MSSQL_INSTANCES_FILE

Required. Path to the instances YAML file, using RDS endpoints as host, already present on the target host.

None

NR_CLI_MSSQL_SECRETS_FILE

Required. Path to the secrets file (KEY=VALUE, one RDS master username/password pair per instance), already present on the target host.

None

Conseil

To monitor more than one RDS SQL Server endpoint from this collector, add more entries to the instances file and matching numbered credentials to the secrets file. An instance that fails its checks is skipped with a logged reason rather than aborting the whole install.

This target is only supported on a Windows collector host, and requires your RDS instances to already be joined to an AWS Managed Microsoft AD domain. It monitors one or more RDS SQL Server endpoints from a single collector using an instances file (YAML, host/port per instance, using RDS endpoints as host). Unlike the self-hosted target above, there's no "same host" option here: a domain account (or gMSA) is always required.

Create the instances file on the target host, using each RDS endpoint as host, with just one entry if you're only monitoring a single RDS instance:

instances:
  - host: mydb1.xxxxxxxxxx.us-east-1.rds.amazonaws.com
    port: 1433
  - host: mydb2.xxxxxxxxxx.us-east-1.rds.amazonaws.com
    port: 1433

Then create a file named playbook.yml and copy the following content into it. Set targets to mssql-otel-rds-winauth and replace the placeholder values with your own:

- name: Install New Relic NRDOT for SQL Server on RDS (Windows Auth)
  hosts: all
  roles:
    - role: newrelic.newrelic_install
  vars:
    targets:
      - mssql-otel-rds-winauth
  environment:
    NEW_RELIC_API_KEY: <API key>
    NEW_RELIC_ACCOUNT_ID: <Account ID>
    NEW_RELIC_REGION: <Region>
    NR_CLI_MSSQL_CONFIG_PRESET: <1 Basic, 2 Advanced, default 1>
    NR_CLI_MSSQL_AUTH_MODE: <1 for Windows Domain Auth, 2 for gMSA, default 1>
    NR_CLI_MSSQL_INSTANCES_FILE: <path to the instances YAML file on the target host, using RDS endpoints as host>
    NR_CLI_MSSQL_WIN_ACCOUNT: <Windows domain account, DOMAIN\username; Windows Domain Auth only>
    NR_CLI_MSSQL_WIN_PASSWORD: <password for that account; Windows Domain Auth only>
    NR_CLI_MSSQL_GMSA_ACCOUNT: <gMSA account, DOMAIN\gMSAName$; gMSA only>

Conseil

To enable debug logging, add verbosity: "debug" under vars in the playbook.

Variable	Description	Default
`NR_CLI_MSSQL_CONFIG_PRESET`	NRDOT configuration: `1` for Basic `2` for Advanced	`1`
`NR_CLI_MSSQL_AUTH_MODE`	SQL Server authentication: `1` for Windows Domain Auth `2` for gMSA	`1`
`NR_CLI_MSSQL_INSTANCES_FILE`	Required. Path to the instances YAML file, using RDS endpoints as `host`, already present on the target host.	None
`NR_CLI_MSSQL_WIN_ACCOUNT` / `NR_CLI_MSSQL_WIN_PASSWORD`	Required if `NR_CLI_MSSQL_AUTH_MODE` is `1`. Windows domain account (`DOMAIN\username`) and password to grant permissions to and run the collector service as.	None
`NR_CLI_MSSQL_GMSA_ACCOUNT`	Required if `NR_CLI_MSSQL_AUTH_MODE` is `2`. gMSA account (`DOMAIN\gMSAName$`) to grant permissions to.	None

Conseil

To monitor more than one RDS SQL Server endpoint from this collector, add more entries to the instances file. One auth mode and one Windows identity are used for every endpoint in the file. An endpoint that fails its checks is skipped with a logged reason rather than aborting the whole install.

Run the playbook

bash

$ansible-playbook -i <inventory_file> <playbook_file>.yml

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.
After setting up SQL Server monitoring with NRDOT, you can:
- Create custom dashboards to visualize your database metrics
- Set up alerts for critical database performance thresholds
- Explore your data using New Relic query capabilities

Set up APM-database correlation

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

Troubleshooting

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

Metrics reference

Learn about the available metrics collected by the NRDOT Collector.

Install & configure NRDOT for MSSQL monitoring with Ansible

Prerequisites

Install the role

Configure the playbook

Self-hosted - SQL Server Authentication

Conseil

Conseil

Self-hosted - Windows Authentication or gMSA

Conseil

Conseil

AWS RDS - SQL Server Authentication

Conseil

Conseil

AWS RDS - Windows Domain Authentication or gMSA

Conseil

Conseil

Run the playbook

Find and use your data

Set up APM-database correlation

Troubleshooting

Metrics reference

Install & configure NRDOT for MSSQL monitoring with Ansible

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

Install the role

Configure the playbook

Self-hosted - Windows Authentication or gMSA

AWS RDS - SQL Server Authentication

AWS RDS - Windows Domain Authentication or gMSA

Run the playbook

Find and use your data

Related documentation

Set up APM-database correlation

Troubleshooting

Metrics reference

Prerequisites