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.
- Microsoft Server 2012 R2 Standard
- Microsoft SQL Server 2012 Express
- Microsoft SQL Server Management Studio 2012 Express
- Infrastructure Agent 1.3.18
- MSSQL On-Host Integration 1.0.3
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
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
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
The instance value used for the
instance: key in your config should match the output of the command below:
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.