Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Relic Solution: MSSQL On-Host Integration Configuration Guidelines for a Multi-Instance Setup

mssql
infrastructure
on-host-integrations

#1

You have deployed the MSSQL On-Host Integration to monitor a single instance of MSSQL Server. What if you wanted to create an instance stack and monitor each instance with the same deployment?

Hopefully, this guide will provide you with the answers you need to complete this setup.

Installation Prerequisites

For a full list of our compatibility and requirements please refer to our documentation here.

For this guide I created a stack consisting of three instances:

  • MSSQLSERVER (Default Instance)
  • MSSQLSERVER1 (Named Instance)
  • MSSQLSERVER2 (Named Instance)

There are two ways in which instances can be picked up by the integration:

1. Through a static unique port assigned to the instance in which case the MSSQL Server Browser service does not need to be running (the default port 1433 should not be used more than once).
2. Run the MSSQL Server Browser service and allow it to assign a dynamic port to the instance.

I have chosen the second way for the integration to connect to each of my instances. As ports are dynamically assigned I do not need to include a port in the config file only the instance name under the instance: key.

The mssql-config.yml config file

integration_name: com.newrelic.mssql

instances:
  - name: mssql-server
    command: all_data
    arguments:
      hostname: WIN-9CKR3T4TP9E 
      username: will
      password: [PASSWORD]
      instance: MSSQLSERVER
    labels:
      env: production
      role: mssql
  - name: mssql-server1
    command: all_data
    arguments:
      hostname: WIN-9CKR3T4TP9E 
      username: will1
      password: [PASSWORD]
      instance: MSSQLSERVER1
    labels:
      env: production
      role: mssql
  - name: mssql-server2
    command: all_data
    arguments:
      hostname: WIN-9CKR3T4TP9E 
      username: will2
      password: [PASSWORD]
      instance: MSSQLSERVER2
    labels:
      env: production
      role: mssql

Each instance name and it’s user credentials are specified under its own block starting with -name:. An instance block can be removed or added from the config file as required.

Connectivity Configuration Requirements

  • TCP/IP must be enabled for each instance (named and default).
  • SQL Server Authentication must be enabled for each instance (named and default). The Mixed Mode (SQL Server Authentication and Windows authentication) option will need to be selected to enable this authentic type.

Creating the User and Granting the Correct Permissions

A new login must be created, mapped to a new user and CONNECT, VIEW SERVER STATE permissions granted to that user for each instance. Please refer to our documentation here for the queries that needed to be run to carry out this step. It’s important that this is done for each instance (named and default).

It is recommended that the user name and login name are the same value for each instance although it is not necessary for them to be the same between different instances.

Once you have created the login and it’s associated user check that their mapping is successful and that the correct permissions have been granted. This can be checked in the properties of the login created:

Testing Connectivity for each user created

Make sure you can connect to each instance using the user created in the previous step with both SQL Server Authentication and with TCP/IP selected.

The instance value used for the instance: key in your config should match the output of the command below:

SELECT @@SERVICENAME

How Instances are Displayed in your Account

If you have followed this guide and the installation steps correctly each instance should automatically appear in your MSSQL On-Host Integrations dashboard.

As you’ll notice the default instance only displays the hostname WIN-9CKR3T4TP9E and is not suffixed by the instance name. This is expected behavior for the default instance.