dep: Upgrade legacy SQL Server driver from denisenkom/go-mssqldb to microsoft/go-mssqldb

[AlaiaNakama]

Description

Currently, nri-flex uses github.com/denisenkom/go-mssqldb v0.12.3 as its database driver for SQL Server (database: sqlserver). This repository is deprecated, has not been updated in over 5 years, and contains legacy connection pooling bugs.

We are requesting an upgrade to the officially maintained fork: github.com/microsoft/go-mssqldb.

Running the legacy driver causes critical issues when utilizing db_async: true for SQL Server integrations. When multiple lightweight, fast queries (e.g., executing under 30ms) are fired concurrently, the legacy connection pool suffers from a silent race condition during rapid connection leasing. This causes queries to drop transiently, resulting in missing data and NULL metric values on fast-running database instances. Upgrading to the Microsoft fork will leverage upstream reliability and connection-context fixes to prevent these collisions.

Acceptance Criteria

• Remove github.com/denisenkom/go-mssqldb from go.mod.
• Add the latest release of github.com/microsoft/go-mssqldb to go.mod.
• Update the driver import statements in the Go codebase.
• Verify existing SQL Server connection strings and configurations still function correctly without breaking changes.
• Validate that concurrent queries utilizing db_async: true execute reliably under load without dropping metric payloads.

Describe Alternatives

• Configuration Workarounds: Advising customers to either disable db_async: true (which degrades collection performance) or manually segment their SQL queries into multiple API blocks to naturally "stagger" the connection pool leases.
• Directing to nri-mssql: Recommending the native MSSQL integration for custom queries. However, many enterprise DBAs specifically choose nri-flex over nri-mssql to avoid the overhead of nri-mssql's default, non-configurable query sweeps.

Dependencies

• Flex codebase/testing: Requires regression testing against MS SQL Server to ensure no connection string parameters behave differently with the updated driver.
• Documentation: Potential updates to the Flex documentation if the Microsoft driver introduces new connection string capabilities (like Entra ID/Azure AD auth) or deprecates legacy connection flag behaviors.

Additional context

We recently diagnosed a customer issue where executing a 13ms query and a 26ms query concurrently via db_async: true resulted in silent data drops (NULLs in NRDB). However, when a 5000ms query was added to the same batch, the data came through perfectly because the long-running query naturally staggered the driver's connection pool leases, preventing the race condition.

The original community driver package specifically points to the Microsoft fork for all modern updates: https://github.com/microsoft/go-mssqldb

Microsoft's fork completely rewrote the internal connection handling, context cancellation, and state checking. The modern driver proactively checks connection states on the socket level before returning them to the pool or leasing them to the application. This ensures bad or interrupted connections are immediately destroyed rather than causing cascading NULL failures.

Estimates

M = 3-5 days (1 week). The code change itself is a small dependency swap, but it requires thorough regression testing to ensure no disruptions for existing SQL Server users.

For Maintainers Only or Hero Triaging this bug

Suggested Priority (P1,P2,P3,P4,P5): P3

[rajrohanyadav]

Hi @AlaiaNakama , thanks for raising this issue. I did some investigations and found that the real culprit is a race condition while writing results and not the driver (though, we need to update the driver as well). I'm creating PRs for both. Would you be able to test out the fix on the customer environment to validate? And we can release after that. I'm able to replicate the race condition locally and validate the fix as well.

[rajrohanyadav]

Raised #661 to fix the race condition.