Enable direct connection to SQL Server instance

[crobert-1]

Component(s)

receiver/sqlserver

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

The existing implementation of the SQL Server receiver only collects information from Windows Performance counters. This was by design with the introduction of the receiver, as this solution provided good value for the amount of effort required (source). The main limitation was noted in the proposal as there would be metrics not covered by this solution, but it was acceptable at the time (source).

At this time there are two main drawbacks with the receiver's functionality:

1. As noted, there are useful metrics that can not be gathered by this solution. I've received customer requests for some of these recently, as described in [receiver/sqlserver] Request to add support for more metrics #29865.
2. SQL Server can be installed in Windows, Linux, and Docker environments, but the receiver can only be run in Windows.

Describe the solution you'd like

I want the sqlserver receiver to be able to run in all environments supported by SQL server itself, as well as scraping more metrics that can help users understand the status of the SQL server instances.

My proposed solution would be to add the sqlquery receiver as an internal dependency of the sqlserver receiver. The sqlserver receiver could expose metrics to the user as it does now, but internally rely on the sqlquery receiver to send the queries that would give metrics we want. This would require the addition of more configuration options for connection to the DB, as well as defining the metrics and queries we want.

Adding a receiver as a dependency of another may not be the best design, so a possible answer to this may also involve moving some sqlquery receiver logic into a central package as suggested here. This would allow re-using the same logic in multiple places without duplicating code.

Describe alternatives you've considered

1. Require users to manually provide all relevant queries, and use the sqlquery receiver directly. This is a poor UX experience and very liable to misconfigurations. It also doesn't provide metric naming semantics, or re-usability for different users.
2. Directly rely on the Telegraf SQL Server Plugin Input. This would change the receiver's configuration, as more options would be required to interface with Telegraf and to directly connect to a running SQL Server instance.There has been some discussion historically around directly using Telegraf plugins. This has been suggested in core, and here's an article of a working (at the time) implementation.This would allow us to leverage existing connection logic, as well as existing queries. Potential drawbacks may be relying on the Telegraf plugin itself, or potentially performance concerns around converting metrics received from telegraf into Otel's format. This would also be a bit more complex to implement.
3. Rely on Telegraf's OpenTelemetry output plugin to send data to the collector, then use the influxdb receiver to ingest it. This works but is definitely a more complicated running environment for users.

Additional context

No response

[github-actions]

Pinging code owners:

• receiver/sqlserver: @djaglowski @StefanKurek

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

[github-actions]

Pinging code owners for receiver/sqlquery: @dmitryax @pmcollins. See Adding Labels via Comments if you do not have permissions to add labels yourself.

[djaglowski]

This makes sense to me. I would prefer that we not depend directly on another receiver though, and instead take the approach where we move common code to a shared package.

[github-actions]

This issue has been inactive for 60 days. It will be closed in 60 days if there is no activity. To ping code owners by adding a component label, see Adding Labels via Comments, or if you are unsure of which component this issue relates to, please ping @open-telemetry/collector-contrib-triagers. If this issue is still relevant, please ping the code owners or leave a comment explaining why it is still relevant. Otherwise, please close it.

Pinging code owners:

• receiver/sqlquery: @dmitryax @crobert-1
• receiver/sqlserver: @djaglowski @StefanKurek

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