ERROR


1) Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor". (.Net SqlClient Data Provider)

2) OLE DB provider "SQLNCLI11" for linked server "repl_distributor" returned message "Unable to complete login process due to delay in opening server connection".

Msg 7303, Level 16, State 1, Procedure sp_adddistributor, Line 168 [Batch Start Line 50]

Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "repl_distributor".




Cause:

OLE DB

1. OLE DB in the SQL Server Native Client does not support the MultiSubnetFailover keyword.

2. A multi-subnet environment is defined when the OS cluster used for AlwaysOn has server nodes that are in multiple, different subnets.

3. By default, the behavior of the SQL client libraries is to try all IP addresses returned by the DNS lookup – one after another (serially) until all of the IP addresses have been exhausted and either a connection is made, or a connection timeout threshold has been reached.

Solution for ODBC


> Microsoft added a new connection string parameter that can be added to change the connection behavior. This new parameter, MultiSubnetFailover, should be used and set to “TRUE.” When set to TRUE, the connection attempt behavior changes. It will no longer attempt all of the IP addresses serially, but in parallel.

-> The parameter MultiSubnetFailover cannot be used in the Linked Server as part of the provider string. We will have to create a Data Source and then use the Data Source in the Linked Server.

https://jbswiki.com/2017/09/20/creating-linked-server-to-a-multi-subnet-availability-group-listener-with-readonly-routing/

Solution for SQL Server OLEDB


1) MSOLEDBSQL


The new Microsoft OLE DB Driver for SQL Server is the 3rd generation of OLE DB Drivers for SQL Server, introduces multi-subnet failover capabilities, and keeps up with the existing feature set of SQL Server Native Client (SNAC) 11*, including the latest TLS 1.2 standards.

The new Microsoft OLE DB Driver for SQL Server is the 3rd generation of OLE DB Drivers for SQL Server, introduces multi-subnet failover capabilities,

https://docs.microsoft.com/en-us/archive/blogs/sqlnativeclient/released-microsoft-ole-db-driver-for-sql-server

2) HostRecordTTL and RegisterAllProvidersIP


If this driver is not installed or not able to use in repl_distributor linked server then we need to change HostRecordTTL and RegisterAllProvidersIP parameter in Cluster setting.

Open powershell with administrative permission and verify above 2 parameters.

-----To check available AG listener
Get-ClusterResource
---Verification of particular listener
Get-ClusterResource -Name DistributionAG | Get-ClusterParameter
Get-ClusterResource -Name TEST | Get-ClusterParameter

---DNS verification
--Check DNS entry for Listener. It will give 2 IP's i.e. all available IP’s are registered.
>nslookup TEST

--------CHANGES TO FIX THE LINK SERVER ISSUE by updating parameters---

Get-ClusterResource -Name TEST | Set-ClusterParameter HostRecordTTL 10
Get-ClusterResource -Name TEST | Set-ClusterParameter RegisterAllProvidersIP 0

Get-ClusterResource -Name DistributionAG | Set-ClusterParameter HostRecordTTL 10
Get-ClusterResource -Name DistributionAG | Set-ClusterParameter RegisterAllProvidersIP 0

Get-ClusterResource -Name DEMO | Set-ClusterParameter HostRecordTTL 10
Get-ClusterResource -Name DEMO | Set-ClusterParameter RegisterAllProvidersIP 0

After these changes, Failover the Availability group to have the above changes take effect.

---DNS verification
--Check DNS entry for Listener.It will give only active IP
>nslookup TEST