[receiver/sqlserverreceiver] add named instance support

[JonathanWamsley]

Component(s)

receiver/sqlserver

Is your feature request related to a problem? Please describe.

The current SQL Server receiver collects does not support SQL Server named instances as shown in this issue.

A named instance is an installation of the SQL Server Database Engine that is identified by a unique name. By default, a SQL Server installation is identified by the computer name or the computer name followed by an instance name, which is known as the default instance. However, you can also install one or more named instances on a single computer, and each named instance will have a unique name.

When connecting to a named instance of SQL Server, you need to specify the instance name along with the computer name or IP address in the connection string.

Named instances are often used in scenarios where multiple applications or services need to access different instances of SQL Server on the same computer or network.

Describe the solution you'd like

My proposal is to provide assistance for collecting a named instance for the SQL Server receiver. This can be achieved by utilizing the winperf package that leverages the Performance Data Helper (PDH). In order to collect a named instance using PDH, a computer name and instance name are required in the PDH formatted path. The computer name and instance name will be optional config fields provided from the user when collecting from a named instance. The computer name and instance name can also be emitted as resource attributes.

An example of a config would look like:

sqlserver:
  collection_interval: 10s
  computer_name: MyServer
  instance_name: MyInstance
  resource_attributes:
    sqlserver.computer.name:
      enabled: true
    sqlserver.instance.name:
      enabled: true

If the server name is "MyServer" and the named SQL Server instance is named "MyInstance", the PDH path for a named instance would look like this:
\\MyServer\MSSQL$MyInstance:Access Methods\Pages compressed/sec

Describe alternatives you've considered

No response

Additional context

This image of a Performance monitor for a named instance TEST_NAME has an updated object path of MSSQL$TEST_NAME:

[github-actions]

Pinging code owners:

• receiver/sqlserver: @djaglowski @StefanKurek

See Adding Labels via Comments if you do not have permissions to add labels yourself.

[JonathanWamsley]

resolved with #20265