Install & configure NRDOT for MSSQL monitoring with Chef

You can install and configure the NRDOT Collector for SQL Server monitoring using the newrelic-install Chef cookbook. The cookbook installs the collector, creates or authorizes the monitoring identity, and configures the collector when you run the newrelic-install::default recipe.

Prerequisites

A New Relic account with a valid license key.
A New Relic account ID.
Microsoft SQL Server 2017 or later.
Chef 15 or later.
Self-hosted SQL Server:
- SQL Server Authentication: A Windows, Ubuntu, or RHEL/CentOS collector host, and a login in the sysadmin server role (or equivalent) with its password.
- Windows Authentication or gMSA: A Windows collector host and the Windows domain account (or gMSA) to grant permissions to.
SQL Server on AWS RDS:
- A Windows, Ubuntu, or RHEL/CentOS collector host with network connectivity to the RDS endpoint (inbound access granted on the SQL Server port within the RDS security group), alongside the RDS master username and password.
- Windows Domain Authentication or gMSA: A Windows collector host and an RDS instance already joined to an AWS Managed Microsoft Active Directory domain.
Network connectivity to the New Relic OTLP endpoint corresponding to the target region.

Download the Chef cookbook

Download the newrelic-install Chef cookbook from the Chef Supermarket to your chef-repo directory:

bash

$knife supermarket install newrelic-install

Replace the default attributes

Replace the default attributes in attributes/default.rb with your account details:

This cookbook 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 node: 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 node. 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 node 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>
```

default['newrelic_install']['NEW_RELIC_API_KEY']    = <API key>
default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = <Account ID>
default['newrelic_install']['NEW_RELIC_REGION']     = <Region>
default['newrelic_install']['targets'] = [
  'nrdot-collector-mssql'
]
default['newrelic_install']['env']['NR_CLI_MSSQL_CONFIG_PRESET']   = <1 Basic, 2 Advanced, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_INSTANCES_FILE']  = <path to the instances YAML file on the node>
default['newrelic_install']['env']['NR_CLI_MSSQL_SECRETS_FILE']    = <path to the secrets file (KEY=VALUE per instance) on the node>
# See all available targets at: https://github.com/newrelic/chef-install

Attribute (under env)

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 node.

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 node.

None

Conseil

No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via default['newrelic_install']['env'][...].

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).

This file must already exist on the node, 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.

default['newrelic_install']['NEW_RELIC_API_KEY']    = <API key>
default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = <Account ID>
default['newrelic_install']['NEW_RELIC_REGION']     = <Region>
default['newrelic_install']['targets'] = [
  'nrdot-collector-mssql-winauth'
]
default['newrelic_install']['env']['NR_CLI_MSSQL_CONFIG_PRESET']    = <1 Basic, 2 Advanced, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_AUTH_MODE']        = <1 for Windows Domain Auth, 2 for gMSA, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_WINAUTH_LOCATION'] = <1 for same host as SQL Server, 2 for a different host, default 1; Windows Domain Auth only>
default['newrelic_install']['env']['NR_CLI_MSSQL_INSTANCES_FILE']   = <path to the instances YAML file on the node>
default['newrelic_install']['env']['NR_CLI_MSSQL_WIN_ACCOUNT']      = <Windows domain account, DOMAIN\username; required only for Windows Domain Auth on a different host>
default['newrelic_install']['env']['NR_CLI_MSSQL_WIN_PASSWORD']     = <password for that account; required only for Windows Domain Auth on a different host>
default['newrelic_install']['env']['NR_CLI_MSSQL_GMSA_ACCOUNT']     = <gMSA account, DOMAIN\gMSAName$; gMSA only>
# See all available targets at: https://github.com/newrelic/chef-install

Attribute (under `env`)	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 node.	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

No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via default['newrelic_install']['env'][...].

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 node itself. Use the different-host flow or gMSA for remote or clustered instances.

This cookbook 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 node).

Create the instances file on the node, 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 node 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
```

default['newrelic_install']['NEW_RELIC_API_KEY']    = <API key>
default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = <Account ID>
default['newrelic_install']['NEW_RELIC_REGION']     = <Region>
default['newrelic_install']['targets'] = [
  'nrdot-collector-mssql-rds'
]
default['newrelic_install']['env']['NR_CLI_MSSQL_CONFIG_PRESET']  = <1 Basic, 2 Advanced, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_INSTANCES_FILE'] = <path to the instances YAML file on the node, using RDS endpoints as host>
default['newrelic_install']['env']['NR_CLI_MSSQL_SECRETS_FILE']   = <path to the secrets file (KEY=VALUE per instance) on the node>
# See all available targets at: https://github.com/newrelic/chef-install

Attribute (under env)

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 node.

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 node.

None

Conseil

No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via default['newrelic_install']['env'][...].

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.

This file must already exist on the node, 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

default['newrelic_install']['NEW_RELIC_API_KEY']    = <API key>
default['newrelic_install']['NEW_RELIC_ACCOUNT_ID'] = <Account ID>
default['newrelic_install']['NEW_RELIC_REGION']     = <Region>
default['newrelic_install']['targets'] = [
  'nrdot-collector-mssql-rds-winauth'
]
default['newrelic_install']['env']['NR_CLI_MSSQL_CONFIG_PRESET']   = <1 Basic, 2 Advanced, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_AUTH_MODE']       = <1 for Windows Domain Auth, 2 for gMSA, default 1>
default['newrelic_install']['env']['NR_CLI_MSSQL_INSTANCES_FILE']  = <path to the instances YAML file on the node, using RDS endpoints as host>
default['newrelic_install']['env']['NR_CLI_MSSQL_WIN_ACCOUNT']     = <Windows domain account, DOMAIN\username; Windows Domain Auth only>
default['newrelic_install']['env']['NR_CLI_MSSQL_WIN_PASSWORD']    = <password for that account; Windows Domain Auth only>
default['newrelic_install']['env']['NR_CLI_MSSQL_GMSA_ACCOUNT']    = <gMSA account, DOMAIN\gMSAName$; gMSA only>
# See all available targets at: https://github.com/newrelic/chef-install

Attribute (under `env`)	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 node.	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

No default attribute values are shipped for these variables. Set the ones relevant to your target explicitly via default['newrelic_install']['env'][...].

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.

Upload the Chef cookbook

Upload the newrelic-install Chef cookbook to your Chef server:

bash

$knife cookbook upload newrelic-install

Update the run list

Add the newrelic-install recipe to the run list of a node:

"run_list": [
  "recipe[newrelic-install]"
]

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 Chef

Prerequisites

Download the Chef cookbook

Replace the default attributes

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

Upload the Chef cookbook

Update the run list

Find and use your data

Set up APM-database correlation

Troubleshooting

Metrics reference

Install & configure NRDOT for MSSQL monitoring with Chef

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

Download the Chef cookbook

Replace the default attributes

Self-hosted - Windows Authentication or gMSA

AWS RDS - SQL Server Authentication

AWS RDS - Windows Domain Authentication or gMSA

Upload the Chef cookbook

Update the run list

Find and use your data

Related documentation

Set up APM-database correlation

Troubleshooting

Metrics reference

Prerequisites