Linked server connections to Multi-subnet failover cluster

The solution you linked to overcomes the problem with connecting to multi-subnet failover groups by using the MultiSubnetFailover=True connection string setting.

The way that setting deals with the problem is attempting many connections in parallel, rather than one after the other (the default behavior). This increases the chances that a connection will be made successfully before reaching the connection timeout limit.

If you don't want to use that option (you mentioned not wanting the overhead of configuring ODBC connections on all your cluster servers), another solution would be to increase the (linked) server-level connection timeout setting to a value that's high enough to deal with the serial connection attempts.

The default connection timeout on linked servers is 10 seconds (represented by "0" on the settings screen). You can increase the timeout to 20 seconds by doing the following:

EXEC master.dbo.sp_serveroption 
    @server=N'YourLinkedServerName', 
    @optname=N'connect timeout', 
    @optvalue=N'20'

If you need to change the connect timeout to something higher because seconds 20 seconds is too low:

EXEC master.dbo.sp_serveroption 
    @server=N'repl_distributor', 
    @optname=N'connect timeout', 
    @optvalue=N'60'

---

The docs on the default value for "connect timeout" are a little confusing.

sp_serveroption says

Time-out valuein seconds for connecting to a linked server.

If 0, use the sp_configure default.

sp_configure doesn't support an option called "connect timeout" but has one called "remote login timeout" (see Server Configuration Options). The description from here sounds like the thing we're looking for:

The remote login timeout option specifies the number of seconds to wait before returning from a failed attempt to log in to a remote server.
...
The default value for this option is 10 seconds.

In practice, I just created a linked server locally with the default settings, turned off the target instance, and tried to query it. Timed out at 10 seconds =)

Adding to what jadarnel27 already said, the newest version of OLEDB drivers supports MultiSubnetFailover=True

https://blogs.msdn.microsoft.com/sqlnativeclient/2018/03/30/released-microsoft-ole-db-driver-for-sql-server/