Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet ----Hot issues October

Question

===============

Questions

===============

Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.

===============

Guidance

===============

Always specify MultiSubnetFailover=True when connecting to an availability group listener or Failover Cluster Instance. During a multi-subnet failover, the client will attempt connections in parallel. During a subnet failover, will aggressively retry the TCP connection.

There is another importance parameter RegisterAllProvidersIP. The effect of this property value depends on the client connection string, as follows:
• Connection strings that set MultiSubnetFailover to true
Always On availability groups sets the RegisterAllProvidersIP property to 1 in order to reduce re-connection time after a failover for clients whose client connection strings specify MultiSubnetFailover = True, as recommended. Note that to take advantage of the listener multi-subnet feature, your clients might require a data provider that supports the MultiSubnetFailover keyword.

• Connection strings that do not set MultiSubnetFailover to true
When RegisterAllProvidersIP = 1, any clients whose connection strings do not use MultiSubnetFailover = True, will experience high latency connections. This occurs because these clients attempt connections to all IPs sequentially. In contrast, if RegisterAllProvidersIP is changed to 0, the active IP address is registered in the Client Access Point in the WSFC cluster, reducing latency for legacy clients. Therefore, if you have legacy clients that need to connect to an availability group listener and cannot use the MultiSubnetFailover property, we recommend that you change RegisterAllProvidersIP to 0.
Important
When you create an availability group listener through the WSFC cluster (Failover Cluster Manager GUI), RegisterAllProvidersIP will be 0 (false) by default.

===============

Reference

===============

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15#RegisterAllProvidersIP

Answer 1

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Answer 2

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Issue :
Cannot open any Tabular model project in Visual Studio with the error message : Method not found: 'Microsoft.AnalysisServices.Tabular.Model Microsoft.AnalysisServices.Database.get_Model()'.

Symptoms :
Recently we received serval customer reports of an issue that after updating the Visual Studio SQL BI tools, they suddenly cannot open any SSAS tabular models. They would receive error message like :

Those BI extension which have this issue will be listed as :

Visual Studio 2019 (All edition);
SSAS : 16.8.4
SSIS : 3.12

Solutions :
The cause would be some conflicts related to SSIS VS project. The Microsoft Analysis Services Projects team has fixed this by the Feb.10 2021 release of SSAS project. Quoted as :

New in theFebruary 10th, 2021 Release of Microsoft Analysis Services Projects (VSIX Update 2.9.16):
• Fixed an error that occurred while trying to open Tabular models in AS projects while SSIS is installed.
We could download the new version and fix this in this link :
https://marketplace.visualstudio.com/items?itemName=ProBITools.MicrosoftAnalysisServicesModelingProjects

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Answer 3

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Issue :
Extract numbers or letters in a string

We often encounter the problem of extracting specific content in a string, such as extracting numbers in a string, or extracting letters in a string.
Here is a simple way to extract numbers or letters in a string. If you want to extract other specific content in the string, you only need to make simple modifications based on this method.

Solution:
First, let’s look at how to extract numbers from a string. Introduce through the following example.

Step1: create sample data:

-Test date  
CREATE TABLE #test(string varchar(25))  
INSERT INTO #test VALUES('HUEWAFDHI734267')  
                       ,('34724632DHeufbUDS')   
                       ,('dhd36d4DH743dre')  
SELECT * FROM #test  

Step2: create a custom function.

CREATE FUNCTION dbo.Extractnum(@str VARCHAR(MAX))  
RETURNS INT  
AS  
BEGIN  
   WHILE PATINDEX('%[^0-9]%',@str)>0  
   BEGIN  
   SET @str=STUFF(@str,PATINDEX('%[^0-9]%',@str),1,'')  
   END  
  RETURN CAST(@str AS INT)  
END  

Step3: call this function

SELECT dbo.Extractnum(string) AS Num   
FROM #test  

As you can see from the screenshot, no matter where the number is in a string, it can be extracted through a custom function.
Next, let us look at the method of extracting letters in a string. In fact, the method of extracting letters in a string is like the method of extracting numbers. In other words, the method of extracting specific content in a string is similar.
We still use the test data when extracting numbers:

Step1: create a custom function.

CREATE FUNCTION dbo.Extractlet(@str VARCHAR(MAX))  
RETURNS VARCHAR(MAX)  
AS  
BEGIN  
   WHILE PATINDEX('%[^A-Z]%',@str)>0  
   BEGIN  
   SET @str=STUFF(@str,PATINDEX('%[^A-Z]%',@str),1,'')  
   END  
  RETURN CAST(@str AS VARCHAR(MAX))  
END  

Step2: call this function

SELECT dbo.Extractlet(string) AS Letter  
FROM #test   

As you can see from the screenshot, when extracting the letters in the string, the custom function can extract all the letters whether they are uppercase or lowercase letters.

For more details, please refer to:
PATINDEX (Transact-SQL)
STUFF (Transact-SQL)