Deprecate TransparentNetworkIPResolution and switch MultiSubnetFailover on by default #4494

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

TransparentNetworkIPResolution (TNIR) has become the more error‑prone of two overlapping "connect faster across multiple DNS-resolved IPs" strategies in Microsoft.Data.SqlClient, and it is on by default only on .NET Framework:

• .NET Framework: TNIR is registered as a connection‑string keyword and defaults to true (see DbConnectionStringDefaults.TransparentNetworkIpResolution gated on Switch.Microsoft.Data.SqlClient.DisableTNIRByDefaultInConnectionString).
• Modern .NET (Core / .NET 5+): TNIR is not a recognized keyword. Setting it in the connection string throws ArgumentException (KeywordNotSupported) via SqlConnectionOptions.ParseInternal.

Even on .NET Framework, the code path silently disables TNIR whenever it is likely to hurt the caller — Azure SQL endpoints, any Microsoft Entra ID auth method, or AccessToken (see ShouldDisableTnir). That signal alone is worth acting on: the driver already believes TNIR is the wrong default for the majority of modern workloads, but leaves it on for everyone else.

Related history:

• TNIR defaulting to "true" for azure sql #3574 ("TNIR defaulting to true for azure sql") — resolved by auto‑disabling TNIR for Azure SQL / Entra / AccessToken.
• Docs: SqlConnection.ConnectionString TransparentNetworkIPResolution row misrepresents actual behavior #4489 — TNIR docs did not describe actual behavior. Fixed by docs: correct TransparentNetworkIPResolution behavior wording (Fixes #4489) #4493, but the docs still have to explain a keyword that is silently ignored in many cases and unavailable on modern .NET.
• SqlClient: Keyword not supported: 'TransparentNetworkIPResolution' #99 — historical confusion about KeywordNotSupported on modern .NET.

MultiSubnetFailover (MSF) covers the same "connect faster across multiple target IPs" goal, works consistently on both .NET Framework and modern .NET, applies to any listener that resolves to more than one IP (despite the name, the IPs do not have to live in different subnets), is the documented strategy for SQL Server Always On availability group listeners, and already has an opt‑in default switch: Switch.Microsoft.Data.SqlClient.EnableMultiSubnetFailoverByDefault (see LocalAppContextSwitches).

Describe the solution you'd like

In a future major version:

1. Mark TNIR obsolete on the public API surface

   • [Obsolete("...")] on:
     • SqlConnectionStringBuilder.TransparentNetworkIPResolution (both netcore/ref and netfx/ref reference assemblies).
     • The "Transparent Network IP Resolution" / "TransparentNetworkIPResolution" connection‑string keyword documentation on SqlConnection.ConnectionString.
   • Obsoletion message should point at MultiSubnetFailover as the recommended alternative and note that TNIR is .NET Framework–only.

2. Flip the .NET Framework default for TNIR to false

   • Effectively invert Switch.Microsoft.Data.SqlClient.DisableTNIRByDefaultInConnectionString so DisableTnirByDefault returns true by default.
   • Provide a compatibility switch (Switch.Microsoft.Data.SqlClient.UseLegacyTnirDefault or equivalent) so callers that still depend on TNIR‑on can opt back in for one release cycle.

3. Flip the default for MultiSubnetFailover to true

   • Effectively invert Switch.Microsoft.Data.SqlClient.EnableMultiSubnetFailoverByDefault so MultiSubnetFailover returns true by default on both .NET Framework and modern .NET.
   • Provide a compatibility switch to restore the historic false default for one release cycle.

4. Documentation and migration guide

   • Update the connection string keyword tables (SqlConnection.xml, SqlConnectionStringBuilder.xml) to describe the new defaults and the obsolete status of TNIR.
   • Publish a short migration note covering the two compatibility switches and the behavior differences observed by apps that previously relied on TNIR.

Describe alternatives you've considered

• Leave the current auto‑disable logic in place and do nothing else. Preserves back‑compat, but keeps the API surface confusing, keeps a keyword that throws on modern .NET, and forces docs to keep explaining a workflow that is silently disabled for most callers.
• Remove TNIR entirely from .NET Framework. Simpler long-term, but this is a breaking change with no runway. Marking obsolete and flipping the default is the standard staged deprecation.
• Only flip TNIR default, do not change MSF default. Fixes the "silent misconfiguration" side, but leaves callers whose listener resolves to multiple target IPs (including availability group listeners) with no default retry strategy — worse than status quo for that population.

Additional context

• Public API changes should be gated through the reference assemblies (netcore/ref, netfx/ref) and reviewed against policy/coding-best-practices.md and .github/instructions/api-design.instructions.md.
• Existing compatibility switch precedents: UseLegacyFailoverAlternationOnLoginSqlErrors, DisableTNIRByDefaultInConnectionString, EnableMultiSubnetFailoverByDefault — this proposal follows the same shape.
• Impact on LoginNoFailover / LoginWithFailover should be modest: TNIR paths already short‑circuit via disableTnir; MSF paths are the ones the code exercises for Azure SQL and multi‑target‑IP listener scenarios today.
• Related recent PR (docs correction, not a behavior change): docs: correct TransparentNetworkIPResolution behavior wording (Fixes #4489) #4493.