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

Willsonyuan-MSFT 111 Reputation points
2020-10-28T07:52:23.33+00:00

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

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,485 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,240 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Willsonyuan-MSFT 111 Reputation points
    2020-10-29T02:00:01.787+00:00

    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.

    0 comments No comments

  2. Willsonyuan-MSFT 111 Reputation points
    2021-03-23T08:20:49.467+00:00

    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 :
    80507-0.png

    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.

    0 comments No comments

  3. Willsonyuan-MSFT 111 Reputation points
    2021-04-26T03:19:53.387+00:00

    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  
    

    90996-image.png

    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  
    

    90998-image.png

    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   
    

    91065-image.png

    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)

    0 comments No comments