---
title: Install & configure NRDOT for MSSQL monitoring with Azure SQL Database on Windows
source: https://docs.newrelic.com/docs/opentelemetry/db360/mssql/azure-sql-windows
---

Monitor your Azure SQL Database performance with database metrics, query analysis, and system health data using the New Relic Distribution of OpenTelemetry (NRDOT) collector on Windows.

> #### 💡 TIP
>
> To run the collector on Linux, refer to [Azure SQL Database on Linux](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/azure-sql-linux).

## Prerequisites [#prerequisites]

-   A New Relic account with a valid [license key](https://docs.newrelic.com/docs/apis/intro-apis/new-relic-api-keys/#ingest-license-key).
-   Network connectivity to the [New Relic OTLP endpoint](https://docs.newrelic.com/docs/opentelemetry/best-practices/opentelemetry-otlp) 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 [#setup]

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

Run PowerShell as Administrator:

```powershell
[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 [#user]

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:

    ```sql
    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`:

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

> #### 💡 TIP
>
> To securely manage sensitive information, such as database credentials, store them in [secret management](https://docs.newrelic.com/docs/opentelemetry/db360/capabilities/db-apm/#secret-management) tools.

## Configure NRDOT Collector [#configure-collector]

1.  Create the configuration file in PowerShell as Administrator:

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

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

    ```yaml
    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]
    ```

> #### 💡 TIP
>
> -   The `datasource` value is a connection URL. If your password contains characters other than letters, digits, and `- . _ ~ ! $ * + ,`, URL-encode them.
> -   The collector connects to the single database named in `database=`. To monitor several databases on the same server, use a separate receiver for each, as described in [Multi-receiver configuration](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/multi-receiver#multi).
> -   Always-On, failover-cluster, and some latch and tempdb metrics aren't available on Azure SQL Database and return no data.
> -   The `logs` pipeline carries SQL Server query telemetry (query text, execution plans, and samples), not collector debug logs.

## Update NRDOT Collector configuration [#update]

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

    ```powershell
    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"'
    ```

2.  (Optional) Validate the configuration:

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

## Restart NRDOT Collector [#restart]

```powershell
net stop nrdot-collector
net start nrdot-collector
```

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

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

## Find and use your data [#find]

1.  Go to **[one.newrelic.com](https://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.

## Related documentation [#related-docs]

[Set up APM-database correlation](https://docs.newrelic.com/docs/opentelemetry/db360/capabilities/db-apm)

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

[Troubleshooting](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/troubleshooting)

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

[Metrics reference](https://docs.newrelic.com/docs/opentelemetry/db360/mssql/metrics-reference)

Learn about the available metrics collected by the NRDOT Collector.