Install & configure NRDOT for MSSQL monitoring with Azure SQL Database on Linux

Prerequisites

• A New Relic account with a valid license key.
• Network connectivity to the New Relic OTLP endpoint for your region.
• Azure SQL Database requirements:
  • An Azure SQL logical server and database.
  • The server admin login and password (or another account that can create logins and manage server roles in master).
  • Network access from the collector host to the server on port 1433, through a server firewall rule, a virtual network rule, or a private endpoint. If the server uses a private endpoint, the server name must resolve to the private IP address from the collector host.
• Collector host requirements: a host (for example, an Azure VM) with the sqlcmd utility installed.

Install NRDOT Collector

Install the NRDOT Collector on a host that can reach your Azure SQL server.

For Debian/Ubuntu on AMD64:

$
NRDOT_VERSION=$(curl -s https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest | grep '"tag_name":' | awk -F'"' '{print $4}') && curl -L "https://github.com/newrelic/nrdot-collector-releases/releases/download/${NRDOT_VERSION}/nrdot-collector_${NRDOT_VERSION}_linux_amd64.deb" --output nrdot-collector.deb && sudo dpkg -i nrdot-collector.deb

For other distributions and architectures, refer to the Linux RDS installation step and the nrdot-collector-releases page.

Create monitoring user and grant permissions

Azure SQL Database doesn't allow GRANT VIEW SERVER STATE and similar instance-level grants. Instead, you add the login to built-in server roles in master, then create a user in each database you want to monitor.

1. Create a file named nr-grant-permission-master.sql and replace <YOUR_PASSWORD> with your desired password:

   CREATE LOGIN [newrelic] WITH PASSWORD = '<YOUR_PASSWORD>';
   GO
   ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [newrelic];
   ALTER SERVER ROLE [##MS_DefinitionReader##] ADD MEMBER [newrelic];
   ALTER SERVER ROLE [##MS_DatabaseConnector##] ADD MEMBER [newrelic];
   GO

   카피

2. Run it against the master database as the server admin. Replace <YOUR-SERVER> with your server name (for example, myserver.database.windows.net):

   bash

   카피

   $
   sqlcmd -S <YOUR-SERVER> -d master -U <YOUR_ADMIN_USER> -P '<YOUR_ADMIN_PASSWORD>' -C -i nr-grant-permission-master.sql

3. Create a file named nr-grant-permission-db.sql:

   CREATE USER [newrelic] FOR LOGIN [newrelic];
   GO
   GRANT VIEW DATABASE STATE TO [newrelic];
   GO

   카피

4. Run it against each database you want to monitor. Replace <YOUR_DATABASE> with the database name:

   bash

   카피

   $
   sqlcmd -S <YOUR-SERVER> -d <YOUR_DATABASE> -U <YOUR_ADMIN_USER> -P '<YOUR_ADMIN_PASSWORD>' -C -i nr-grant-permission-db.sql

   The following table shows why each role is needed:

   Role or permission	Used for
   ##MS_ServerStateReader##	Server-level dynamic management views, such as sys.dm_exec_query_stats and sys.dm_exec_procedure_stats
   ##MS_DefinitionReader##	Resolving object and schema names for top procedures and index statistics
   ##MS_DatabaseConnector##	Connecting to databases to collect index physical statistics
   VIEW DATABASE STATE	Database-scoped dynamic management views, such as sys.dm_db_resource_stats

5. (Optional) Verify the server roles from the master database:

   bash

   카피

   $
   sqlcmd -S <YOUR-SERVER> -d master -U <YOUR_ADMIN_USER> -P '<YOUR_ADMIN_PASSWORD>' -C -Q "SELECT r.name AS [Role], l.name AS [Login] FROM sys.server_role_members rm JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id JOIN sys.sql_logins l ON rm.member_principal_id = l.principal_id WHERE l.name = 'newrelic';"

   The output lists all three roles.

6. (Optional) Verify the database permissions by connecting as the monitoring user:

   bash

   카피

   $
   sqlcmd -S <YOUR-SERVER> -d <YOUR_DATABASE> -U newrelic -P '<YOUR_PASSWORD>' -C -Q "SELECT TOP 1 * FROM sys.dm_db_resource_stats; SELECT TOP 1 * FROM sys.dm_exec_query_stats;"

   A permission error means a grant is missing.

Configure NRDOT Collector

1. Create the configuration file:

   bash

   카피

   $
   sudo nano /etc/nrdot-collector/mssql-config.yaml

2. Add the following configuration. Replace <YOUR_PASSWORD>, <YOUR-SERVER>, <YOUR_DATABASE>, <YOUR_LICENSE_KEY>, and the OTLP endpoint with your values:

   receivers:
     nrsqlserver:
       collection_interval: 15s
       # datasource replaces username, password, server, and port.
       datasource: "sqlserver://newrelic:<YOUR_PASSWORD>@<YOUR-SERVER>:1433?database=<YOUR_DATABASE>"
   
       metrics:
         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
   
       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:
         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:
         max_rows_per_query: 100
         collect_full_query_text: true
         allowed_comment_keys:
           - nr_service_guid
   
       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: https://otlp.nr-data.net:4318
       headers:
         api-key: <YOUR_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]

   카피

Update NRDOT Collector configuration

1. Edit the service configuration file:

   bash

   카피

   $
   sudo nano /etc/nrdot-collector/nrdot-collector.conf

2. Point the configuration path to your new file:

   bash

   카피

   $
   OTELCOL_CONFIG="/etc/nrdot-collector/mssql-config.yaml"

3. (Optional) Validate the configuration:

   bash

   카피

   $
   sudo /usr/bin/nrdot-collector validate --config=/etc/nrdot-collector/mssql-config.yaml

Restart NRDOT Collector

$
sudo systemctl restart nrdot-collector

Check the collector log for permission or connection errors:

$
sudo journalctl -u nrdot-collector -n 50 --no-pager

Find and use your data

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