Document ID: TEC1328245
Last Modified Date: 01/14/2017
Show Technical Document Details
Summary:
How to use the sqlserver probe to monitor Microsoft SQL Server "named instances."
Background:
Some customers would like to monitor specific named instances of Microsoft SQL Server instead of just the default instance which yields QOS data and alarms for ALL instances. This Article explains how this can be accomplished.
Environment:
- UIM 8.31 or higher
- Microsoft SQL Server
- sqlserver v5.10 or higher
Instructions:
When setting up a connection for the sqlserver probe, for the 'server name' simply specify it in the format of '<ServerName>\<InstanceName>'
If you specifically configure the ServerName\InstanceName1, the probe will monitor just InstanceName1.
Note that if you do NOT include/specify any instance name, it will monitor ALL the instances for that server name and you may end up with a lot of QOS and alarms due to monitoring ALL instances. It also depends on how many profiles have been enabled.
- server_name is the IP address or hostname of the server.
- instance_name is the name of the instance to which you want to connect on the server.
What is a named instance?
A named instance is a copy of the sqlservr.exe executable that runs as an operating system service.
Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine independently of other instances.
So for example, here are a few <ServerName>\<InstanceName> examples:
<ServerName>\MSSQLSERVER (which is the default instance)
<ServerName>\INSTANCEONE
To determine your existing instance names:
To find the instance name of an SQL Server instance, please follow the steps below:
- Open SQL Server Configuration Manager (SSCM) from Start – > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools
- In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), and you will find entries in the list, e.g., SQL Server (<InstanceName>),
- The InstanceName is what you need to find. If you install multiple instances on the server, you will see more than one SQL Server (InstanceName) listed.
Wherever you see “SQL Server” in the “Service Type” column it means it is an instance of SQL Server. For the default instance you would see the Name as 'SQL Server (MSSQLServer).' You can only have one default instance.
An example of a default SQL Server instance (MSSQLSERVER) is displayed above.
Below is an example of a default instance as well as a named instance, which in this case is INSTANCEONE.
You can also check the name of an instance using a query:
select @@SERVICENAME
The result of the query above returns 'MSSQLSERVER' if the current instance is the default instance.
This query also returns the instance name if the current instance you have selected in the MS SQL Server Studio tool is a named instance.You would need to run select @@servicename on each database to check for the proper name. Each instance is isolated from the other.
Note also that the user must have access to the named instance to be able to monitor it.
Additional Information:
Please also refer to the release notes at the following url:
https://docops.ca.com/ca-unified-infrastructure-management-probes/en/alphabetical-probe-articles/sqlserver-sql-server-monitoring/sqlserver-sql-server-monitoring-release-notes
The release notes include 'Known Issues and Workarounds,' including a related item regarding mapping alarms to database instance names.