Linux RDS MSSQL monitoring with NRDOT

Preview

We're still working on this feature, but we'd love for you to try it out!

This feature is currently provided as part of a preview pursuant to our pre-release policies.

Set up Microsoft SQL Server monitoring using the NRDOT collector on Linux environments to monitor AWS RDS for SQL Server instances.

Prerequisites

Ensure your environment meets our compatibility and prerequisites.

Set up the NRDOT Collector

Install the NRDOT Collector on your Linux EC2 instance or local Linux machine that can connect to your RDS SQL Server instance.

Importante

Install the NRDOT Collector on an EC2 instance or local machine that has network connectivity to your RDS SQL Server instance. Ensure your security group allows inbound connections on port 1433 (or your custom port).

Debian / Ubuntu

For AMD64 (x86_64) Architecture:

bash

$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 ARM64 Architecture:

bash

$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_arm64.deb" --output nrdot-collector.deb && sudo dpkg -i nrdot-collector.deb

RHEL / CentOS

For AMD64 (x86_64) Architecture:

bash

$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_x86_64.rpm" --output nrdot-collector.rpm && sudo rpm -ivh nrdot-collector.rpm

For ARM64 Architecture:

bash

$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_arm64.rpm" --output nrdot-collector.rpm && sudo rpm -ivh nrdot-collector.rpm

Configure database user

Connect to your RDS SQL Server instance and create the newrelic monitoring user with appropriate permissions.

Create a file named nr-grant-permission-rds.sql on your Linux instance with the following content:
```
USE [master];
GO
CREATE LOGIN [newrelic] WITH PASSWORD = '<PASSWORD>';
GO
-- Instance-level permissions
GRANT VIEW SERVER STATE TO [newrelic];
GRANT VIEW ANY DEFINITION TO [newrelic];
GRANT VIEW ANY DATABASE TO [newrelic];
GO
-- Grant read access privileges to all user databases
DECLARE @name SYSNAME;
DECLARE db_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
SELECT [name]
FROM [master].[sys].[databases]
WHERE [name] NOT IN ('master', 'msdb', 'model', 'rdsadmin', 'distribution')
AND [state] = 0; -- Only online databases
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC('USE [' + @name + '];
            IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''newrelic'')
                CREATE USER [newrelic] FOR LOGIN [newrelic];');
  END TRY
  BEGIN CATCH
    PRINT 'Error on ' + @name + ': ' + ERROR_MESSAGE();
  END CATCH
  FETCH NEXT FROM db_cursor INTO @name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
```
Execute the script using your RDS master user credentials. Replace <YOUR-RDS-ENDPOINT> with your actual RDS endpoint and <Your_Master_Password> with your master password:
bash
```
$sqlcmd -S <YOUR-RDS-ENDPOINT> -U <YOUR_MASTER_USER> -P '<Your_Master_Password>' -C -i nr-grant-permission-rds.sql
```

(Optional) Verify the user was created successfully:

bash

$sqlcmd -S <YOUR-RDS-ENDPOINT> -U <YOUR_MASTER_USER> -P '<Your_Master_Password>' -C -Q "SELECT sp.name AS [User], p.permission_name AS [Permission_Granted] FROM sys.server_permissions p JOIN sys.server_principals sp ON p.grantee_principal_id = sp.principal_id WHERE sp.name = 'newrelic';"

Dica

For RDS: Use your RDS master username and password to execute these commands. The script automatically excludes the rdsadmin database which is reserved by AWS.

Configure NRDOT Collector

Select your configuration type based on your monitoring needs:

This configuration focuses on essential database monitoring with core metrics.

Create the configuration file. The filename must be mssql-config.yaml:
bash
```
$sudo nano /etc/nrdot-collector/mssql-config.yaml
```
Add the configuration content below to the mssql-config.yaml file you created in the previous step.

receivers:
  nrsqlserver:
    collection_interval: 15s
    # Connection details - Use your RDS endpoint
    username: <YOUR_USERNAME>
    password: <YOUR_NEWRELIC_PASSWORD>
    server: <YOUR-RDS-ENDPOINT>
    port: 1433

    # Enable comprehensive database metrics
    metrics:
      sqlserver.database.count:
        enabled: true
      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

    # Enable top query and query sample log collection
    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        enabled: true

    # Top query collection configuration
    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 configuration
    query_sample_collection:
      max_rows_per_query: 100

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}

  batch:

exporters:
  otlphttp:
    endpoint: <YOUR_OTLP_ENDPOINT>
    headers:
      api-key: <YOUR_LICENSE_KEY>
    tls:
      insecure: false
    compression: gzip

service:
  telemetry:
    metrics:
      level: none

  pipelines:
    metrics:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

    logs:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

This configuration provides comprehensive database monitoring with all available metrics.

Create the configuration file. The filename must be mssql-config.yaml:
bash
```
$sudo nano /etc/nrdot-collector/mssql-config.yaml
```
Add the configuration content below to the mssql-config.yaml file you created in the previous step.

Importante

If you're an existing NRDOT Collector user, ensure to merge the configuration with your existing mssql-config.yaml file. This includes adding the new receivers, processors, and exporters for MSSQL Database monitoring while retaining your current configurations.

receivers:
  nrsqlserver:
    collection_interval: 15s
    username: <YOUR_USERNAME>
    password: <YOUR_PASSWORD>
    server: <YOUR-RDS-ENDPOINT>
    port: 1433

    metrics:
      # Database
      sqlserver.database.count:
        enabled: true
      sqlserver.database.file.size:
        enabled: true
      sqlserver.database.io:
        enabled: true
      sqlserver.database.latency:
        enabled: true
      sqlserver.database.operations:
        enabled: true
      sqlserver.database.page_file.size:
        enabled: true
      sqlserver.database.transactions.active:
        enabled: true
      sqlserver.database.backup_or_restore.rate:
        enabled: true
      sqlserver.database.execution.errors:
        enabled: true
      sqlserver.database.full_scan.rate:
        enabled: true
      sqlserver.database.tempdb.space:
        enabled: true
      sqlserver.database.tempdb.version_store.size:
        enabled: true
      # Security / Principals
      sqlserver.database.principals.count:
        enabled: true
      sqlserver.database.principals.old:
        enabled: true
      sqlserver.database.principals.orphaned_users:
        enabled: true
      sqlserver.database.principals.recently_created:
        enabled: true
      sqlserver.database.role.members.count:
        enabled: true
      sqlserver.database.role.memberships.count:
        enabled: true
      sqlserver.database.role.permission.risk_level:
        enabled: true
      sqlserver.database.role.roles.count:
        enabled: true
      sqlserver.database.security.role_membership.count:
        enabled: true
      sqlserver.server.security.principal.count:
        enabled: true
      sqlserver.server.security.role_membership.count:
        enabled: true
      # Locks
      sqlserver.lock.by_mode.count:
        enabled: true
      sqlserver.lock.by_resource.count:
        enabled: true
      sqlserver.lock.timeout.rate:
        enabled: true
      sqlserver.lock.wait.count:
        enabled: true
      sqlserver.lock.wait.rate:
        enabled: true
      sqlserver.deadlock.rate:
        enabled: true
      # Transactions
      sqlserver.transaction.delay:
        enabled: true
      sqlserver.transaction.mirror_write.rate:
        enabled: true
      sqlserver.transaction.longest_running_time:
        enabled: true
      sqlserver.transaction.version_cleanup.rate:
        enabled: true
      sqlserver.transaction.version_generation.rate:
        enabled: true
      # Memory
      sqlserver.memory.area:
        enabled: true
      sqlserver.memory.cache.object.count:
        enabled: true
      sqlserver.memory.grants.pending.count:
        enabled: true
      sqlserver.memory.page.count:
        enabled: true
      sqlserver.memory.target:
        enabled: true
      sqlserver.memory.usage:
        enabled: true
      # OS
      sqlserver.os.disk.size:
        enabled: true
      sqlserver.os.memory.usage:
        enabled: true
      sqlserver.os.memory.utilization:
        enabled: true
      sqlserver.os.scheduler.runnable_tasks.count:
        enabled: true
      sqlserver.os.wait.duration:
        enabled: true
      sqlserver.os.wait.tasks.count:
        enabled: true
      # Pages / Buffer
      sqlserver.page.buffer_cache.hit_ratio:
        enabled: true
      sqlserver.page.buffer_cache.free_list.stalls.rate:
        enabled: true
      sqlserver.page.life_expectancy:
        enabled: true
      sqlserver.page.lookup.rate:
        enabled: true
      # Batch / Compilation
      sqlserver.batch.request.rate:
        enabled: true
      sqlserver.batch.sql_compilation.rate:
        enabled: true
      sqlserver.batch.sql_recompilation.rate:
        enabled: true
      sqlserver.batch.compilation.utilization:
        enabled: true
      sqlserver.batch.page_split.utilization:
        enabled: true
      sqlserver.recompilation.ratio:
        enabled: true
      sqlserver.parameterization.rate:
        enabled: true
      sqlserver.plan.execution.rate:
        enabled: true
      sqlserver.attention.rate:
        enabled: true
      # Processes / Connections
      sqlserver.process.count:
        enabled: true
      sqlserver.processes.blocked:
        enabled: true
      sqlserver.user.connection.count:
        enabled: true
      sqlserver.login.rate:
        enabled: true
      sqlserver.logout.rate:
        enabled: true
      # Thread Pool
      sqlserver.thread_pool.tasks.count:
        enabled: true
      sqlserver.thread_pool.workers.count:
        enabled: true
      sqlserver.thread_pool.workers.max:
        enabled: true
      sqlserver.thread_pool.workers.utilization:
        enabled: true
      # TempDB
      sqlserver.tempdb.allocation.wait_time.total:
        enabled: true
      sqlserver.tempdb.contention.waiters.count:
        enabled: true
      sqlserver.tempdb.data_files.count:
        enabled: true
      sqlserver.tempdb.file.size:
        enabled: true
      sqlserver.tempdb.space.usage:
        enabled: true
      # Latch
      sqlserver.latch.superlatch.count:
        enabled: true
      sqlserver.latch.superlatch.transition.rate:
        enabled: true
      sqlserver.latch.wait.rate:
        enabled: true
      sqlserver.latch.wait_time.avg:
        enabled: true
      sqlserver.latch.wait_time.total:
        enabled: true
      # Index
      sqlserver.index.search.rate:
        enabled: true
      # Resource Pool
      sqlserver.resource_pool.disk.operations:
        enabled: true
      sqlserver.resource_pool.disk.throttled.read.rate:
        enabled: true
      sqlserver.resource_pool.disk.throttled.write.rate:
        enabled: true
      # Replica / Always-On
      sqlserver.replica.data.rate:
        enabled: true
      # Server Properties
      sqlserver.computer.uptime:
        enabled: true
      sqlserver.cpu.count:
        enabled: true
      # Tables
      sqlserver.table.count:
        enabled: true
      # Errors
      sqlserver.kill_connection.error.rate:
        enabled: true

    events:
      db.server.query_sample:
        enabled: true
      db.server.top_query:
        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

processors:
  memory_limiter:
    check_interval: ${env:NR_MEM_LIMITER_CHECK_INTERVAL:-1s}
    limit_mib:       ${env:NR_MEM_LIMITER_LIMIT_MIB:-500}
    spike_limit_mib: ${env:NR_MEM_LIMITER_SPIKE_MIB:-50}

  batch:


exporters:
  otlphttp:
    endpoint: https://otlp.nr-data.net:4318
    headers:
      api-key: <YOUR_LICENSE_KEY>
    tls:
      insecure: false
    compression: gzip

service:
  telemetry:
    metrics:
      level: none

  pipelines:
    metrics:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

    logs:
      receivers: [nrsqlserver]
      processors: [memory_limiter, batch]
      exporters: [otlphttp]

Configuration parameters

The following table describes the key configuration parameters for the nrsqlserver receiver:

Parameter	Description
`<YOUR_USERNAME>`	Enter your database username for authentication
`<YOUR_NEWRELIC_PASSWORD>`	The password you set for the newrelic user
`<YOUR-RDS-ENDPOINT>`	Your RDS SQL Server endpoint (e.g., `mydb-instance.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com`)
`port`	Enter your SQL Server port number. The default value is 1433.
`<YOUR_OTLP_ENDPOINT>`	Enter the New Relic OTLP endpoint. For more information, refer to New Relic OTLP endpoints documentation.
`<YOUR_LICENSE_KEY>`	Enter your New Relic license key.

Dica

Note: The max_concurrent_queries option controls how many metric queries the receiver executes in parallel against SQL Server during each scrape cycle.

receivers:
  nrsqlserver:
    max_concurrent_queries: 4  # default is 4 if omitted or set to 0

Dica

Note: If you enable all available metrics, increase the limit_mib value (e.g., 1000) to prevent the memory limiter from dropping data.

Update the collector configuration file

Run the following command to edit the service configuration:
bash
```
$sudo nano /etc/nrdot-collector/nrdot-collector.conf
```
Update the config path to point to your new mssql-config.yaml file:
bash
```
$OTELCOL_CONFIG="/etc/nrdot-collector/mssql-config.yaml"
```

Validate NRDOT Collector configuration

Validate the NRDOT Collector configuration to ensure it's correctly formatted and will work properly.

bash

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

Restart NRDOT collector

After updating your configuration, restart the NRDOT Collector service:

bash

$sudo systemctl restart nrdot-collector

Dica

Always restart the NRDOT collector service after making configuration changes to ensure the new settings take effect.

Next steps

After setting up Linux RDS monitoring:

Preview

Prerequisites

Set up the NRDOT Collector

Importante

Configure database user

Dica

Configure NRDOT Collector

Standard configuration

Full-feature configuration

Importante

Configuration parameters

Dica

Dica

Update the collector configuration file

Validate NRDOT Collector configuration

Restart NRDOT collector

Dica

Next steps

Linux RDS MSSQL monitoring with NRDOT

Preview

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

Set up the NRDOT Collector

Importante

Configure database user

Dica

Configure NRDOT Collector

Full-feature configuration

Configuration parameters

Dica

Dica

Update the collector configuration file

Validate NRDOT Collector configuration

Restart NRDOT collector

Dica

Next steps

Prerequisites