• /
  • ログイン

Microsoft SQL Server monitoring integration

Our Microsoft SQL Server integration collects and sends inventory and metrics from your MS SQL Server environment to our platform, where you can see the health of your MS SQL Server environment. We collect both database and instance-level metrics so that you can pinpoint the source of any problems.

Read on to install the integration, and to see what data we collect.

Compatibility and requirements

Our integration requires Microsoft SQL Server 2008 R2 SP3 or higher, using mixed authentication mode.

Before installing the integration, make sure that you meet the following requirements:

Quick start

Instrument your MS SQL Server environment quickly and send your telemetry data with guided install. Our guided install creates a customized CLI command for your environment that downloads and installs the New Relic CLI and the infrastructure agent.

A screenshot of the guided install CLI.

Ready to get started? Click one of these button to try it out.

Guided install

Our guided install uses the infrastructure agent to set up the Microsoft SQL Server integration. Not only that, it discovers other applications and log sources running in your environment and then recommends which ones you should instrument.

The guided install works with most setups. But if it doesn't suit your needs, you can find other methods below to get started monitoring your MS SQL Server environment.

Install and activate

To install the Microsoft SQL Server integration:

  1. Download the latest .MSI installer image from:

  2. In an admin account, run the install script using an absolute path.

  3. Rename C:\Program Files\New Relic\newrelic-infra\integrations.d\mssql-config.yml.sample to mssql-config.yml, and edit according to your instance.

  4. Restart the infrastructure agent.

Additional notes:

Configuration

Enabling your MS SQL Server

In the Microsoft SQL Server to be monitored, execute the following script to create a new user and grant CONNECT, VIEW SERVER STATE, and read access permissions to that user. Note that SQL Server must be using mixed mode authentication.

See the Microsoft documentation for details on creating logins and users in Microsoft SQL Server.

  1. Use the following statements to create a new login and to grant CONNECT and VIEW SERVER STATE permissions to the login.

    USE master;
        CREATE LOGIN newrelic WITH PASSWORD = 'tmp_password'; --insert new password here
        GRANT CONNECT SQL TO newrelic;
        GRANT VIEW SERVER STATE TO newrelic;
        GRANT VIEW ANY DEFINITION TO newrelic;
  2. Use the following statements to grant read access privileges to the user.

    DECLARE @name SYSNAME
    DECLARE db_cursor CURSOR
    READ_ONLY FORWARD_ONLY
    FOR
    SELECT NAME
    FROM master.sys.databases
    WHERE NAME NOT IN ('master','msdb','tempdb','model','rdsadmin','distribution')
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
    BEGIN
    EXECUTE('USE "' + @name + '"; CREATE USER newrelic FOR LOGIN newrelic;' );
    FETCH next FROM db_cursor INTO @name
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
  3. Run the following command to verify that the user was successfully created.

    sqlcmd -U user_name -S host_name

Configure the integration

An integration's YAML-format configuration is where you can place required login credentials and configure how data is collected. Which options you change depend on your setup and preference.

The configuration file has common settings applicable to all integrations like interval, timeout, inventory_source. To read all about these common settings refer to our Configuration Format document.

重要

If you are still using our Legacy configuration/definition files, please refer to this document for help.

Instance settings

The SQL Server integration collects both Metrics(M) and Inventory(I) information. In the table, use the Applies to column for the settings available to each collection:

Setting

Description

Default

Applies to

HOSTNAME

Hostname or IP where MS SQL server is running.

127.0.0.1

M/I

PORT

Port on which MS SQL server is listening.
Note: Port is only required when INSTANCE is not specified.

1433

M/I

INSTANCE

The MS SQL Server Instance to connect to.
Note: Only required when PORT is not specified. Do not use both.

N/A

M/I

USERNAME

Username for accessing the MS SQL server.

N/A

M/I

PASSWORD

Password for the given user.

N/A

M/I

EXTRA_CONNECTION_URL_ARGS

Specify extra connection parameters as attr1=val1&attr2=val2.

N/A

M/I

ENABLE_SSL

Use SSL to connect to the MS SQL Server.

false

M/I

TRUST_SERVER_CERTIFICATE

if set to true, server certificate is NOT verified for SSL.

false

M/I

CERTIFICATE_LOCATION

Location of the SSL Certificate.

N/A

M/I

TIMEOUT

Timeout for queries, in seconds. Set 0 for no timeout.

30

M/I

ENABLE_BUFFER_METRICS

Enable collection of buffer pool metrics. These can be resource intensive for large systems.

true

M

ENABLE_DATABASE_RESERVE_METRICS

Enable collection of database partition reserve space. These can be resource intensive for large systems.

true

M

CUSTOM_METRICS_QUERY

A SQL query to collect custom metrics. See example below.

N/A

M

CUSTOM_METRICS_CONFIG

YAML configuration with one or more SQL queries to collect custom metrics. See example below.

false

M

METRICS

Set to true to enable metrics-only collection.

false

INVENTORY

Set to true to enable inventory-only collection.

false

The values for these settings can be defined in several ways:

  • Add the value directly to the configuration file. This is the most common way.
  • Replace the values from environment variables using the {{}} notation. This requires infrastructure agent 1.14.0+. For more on this, see more on infrastructure agent passthrough environment variables.
  • Use secrets management to protect sensible information, such as passwords, so that it's not exposed in plain text on the configuration file. For more information, see secrets management.

Labels and custom attributes

You can also decorate your metrics with labels. Labels allow you to add key/value pair attributes to your metrics so that you can query, filter, or group your metrics.

Even though our default sample configuration file includes examples of labels, they're optional. You can remove, modify, or add new ones.

labels:
env: production
role: load_balancer

Example configurations

Find and use data

To find your integration data go to one.newrelic.com > Infrastructure > Third-party services and select one of the Microsoft SQL Server integration links.

Microsoft SQL Server data is attached to the following event types:

  • MssqlDatabaseSample
  • MssqlInstanceSample
  • MssqlWaitSample
  • MssqlCustomQuerySample (if you running custom queries)

For more on how to find and use your data, see Understand integration data.

Metric data

The Microsoft SQL Server integration collects the following metric data attributes. Some metric name are prefixed with a category indicator and a period, such as asserts. or flush..

Database metrics

These attributes can be found by querying the MssqlDatabaseSample event.

Metric

Description

bufferpool.sizePerDatabaseInBytes

The size of the buffer pool per database.

io.stallInMilliseconds

Wait time of stall since last restart, in milliseconds.

log.transactionGrowth

Total number of times the transaction log for the database has been expanded since the last restart.

pageFileAvailable

Available page file size, in bytes.

pageFileTotal

Total page file size, in bytes.

Instance metrics

The Microsoft SQL Server integration collects the following instance metrics. These attributes can be found by querying the MssqlInstanceSample event.

Metric

Description

access.pageSplitsPerSecond

The number of page splits per second.

activeConnections

The number of active connections.

buffer.checkpointPagesPerSecond

The number of pages flushed to disk per second by a checkpoint or other operation that require all dirty pages to be flushed.

bufferpool.batchRequestsPerSecond

The number of batch requests per second on the buffer pool.

bufferpool.pageLifeExptancyInMilliseconds

The life expectancy of a page in the buffer pool, in milliseconds.

bufferpool.sizeInBytes

The size of the buffer pool, in bytes.

instance.backgroundProcessesCount

The number of background processes on the instance.

instance.blockedProcessesCount

The number of blocked processes on the instance.

instance.diskInBytes

The amount of disk space on the instance, in bytes.

instance.dormantProcessesCount

The number of dormant processes on the instance.

instance.forcedParameterizationsPerSecond

The number of forced parameterizations per second on the instance.

instance.preconnectProcessesCount

The number of preconnect processes on the instance.

instance.runnableProcessesCount

The number of runnable processes on the instance.

instance.runnableTasks

The number of runnable tasks on the instance.

instance.runningProcessesCount

The number of running processes on the instance.

instance.sleepingProcessesCount

The number of sleeping processes on the instance.

instance.suspendedProcessesCount

The number of suspended processes on the instance.

instance.transactionsPerSecond

The number of transactions per second on the instance.

memoryAvailable

The available physical memory, in bytes.

memoryTotal

The total physical memory, in bytes.

memoryUtilization

The percentage of memory utilization.

stats.connections

The number of user connections.

stats.deadlocksPerSecond

The number of lock requests per second that resulted in a deadlock since the last restart.

stats.killConnectionErrorsPerSecond

The number of kill connection errors per second since the last restart.

stats.lockWaitsPerSecond

The number of times per second that MS SQL Server is unable to retain a lock right away for a resource.

stats.sqlCompilations

The number of MS SQL compilations per second.

stats.sqlRecompilationsPerSecond

The number of MS SQL re-compilations per second.

stats.userErrorsPerSecond

The number of user errors per second since the last restart.

system.bufferPoolHitPercent

The percentage of buffer pools hits on the instance.

system.waitTimeInMillisecondsPerSecond

The number of milliseconds per second spent waiting across the instance.

Wait metrics

These attributes can be found by querying the MssqlWaitSample event.

Metric

Description

system.waitTimeCount

Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time_ms.

system.waitTimeInMillisecondsPerSecond

The number of waits on this wait type, in milliseconds. This counter is incremented at the start of each wait.

Inventory data

The Microsoft SQL Server integration captures the configuration parameters and current settings of the Microsoft SQL Server environment. It collects the results of the sp_configure stored procedure, as well as current running configuration settings from the sys.configurations table.

The data is available on the Inventory page, under the config/mssql source. For more about inventory data, see Understand integration data.

Check the source code

This integration is open source software. That means you can browse its source code and send improvements, or create your own fork and build it.

その他のヘルプ

さらに支援が必要な場合は、これらのサポートと学習リソースを確認してください:

問題を作成するこのページを編集する
Copyright © 2020 New Relic Inc.