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

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 PowerShell, administrator access, and either sqlcmd or SQL Server Management Studio (SSMS).

Install NRDOT Collector

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

Run PowerShell as Administrator:

[Net.ServicePointManager]::SecurityProtocol = 'tls12, tls'
$NRDOT_VERSION = (Invoke-RestMethod -Uri "https://api.github.com/repos/newrelic/nrdot-collector-releases/releases/latest").tag_name
$WebClient = New-Object System.Net.WebClient
$WebClient.Headers.Add("User-Agent", "Mozilla/5.0")
$WebClient.DownloadFile("https://github.com/newrelic/nrdot-collector-releases/releases/download/$NRDOT_VERSION/nrdot-collector_${NRDOT_VERSION}_windows_x64.msi", "$env:TEMP\nrdot-collector.msi")
Start-Process msiexec.exe -ArgumentList "/i `"$env:TEMP\nrdot-collector.msi`" /qn /norestart /L*V `"$env:TEMP\nrdot_install.log`"" -Wait
Get-Service nrdot-collector -ErrorAction SilentlyContinue

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

   Copy

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

   Copy

   $
   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

   Copy

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

   bash

   Copy

   $
   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

   Copy

   $
   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

   Copy

   $
   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 in PowerShell as Administrator:

   New-Item -Path "C:\Program Files\nrdot-collector\mssql-config.yaml" -ItemType File

   Copy

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]

   Copy

Update NRDOT Collector configuration

1. Point the Windows service to your new configuration file. Run in PowerShell as Administrator:

   Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\nrdot-collector" -Name "ImagePath" -Value '"C:\Program Files\nrdot-collector\nrdot-collector.exe" --config "C:\Program Files\nrdot-collector\mssql-config.yaml"'

   Copy

2. (Optional) Validate the configuration:

   & "C:\Program Files\nrdot-collector\nrdot-collector.exe" validate --config="C:\Program Files\nrdot-collector\mssql-config.yaml"

   Copy

Restart NRDOT Collector

net stop nrdot-collector
net start nrdot-collector

Check the Windows Application event log for permission or connection errors:

Get-EventLog -LogName Application -Source nrdot-collector -Newest 20

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.