question

Willsonyuan-msft avatar image
2 Votes"
Willsonyuan-msft asked ·

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

===============
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://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sqlclient-support-for-high-availability-disaster-recovery#connecting-with-multisubnetfailover
https://docs.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-generalsql-server-transact-sqlsql-server-reporting-servicessql-server-integration-servicessql-server-analysis-services
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Received report as the following: "This thread is confusing. It is tagged with multiple different technology areas which do not relate to the original title of the thread. There does not appear to be a question, it looks like the author is trying to treat the Q&A site like a personal blog. None of the answers relate to the original topic"

I think that @Willsonyuan-msft is trying to make something like "discussion thread" in the old MSDN forum to list out "Hot issues" on the topic of SQL server and related one. It's typical that all the posts afterward are likely to be isolated items.

IMO this would be more appropriate to have "article" type than question, but since I don't have option to change it, I'll just leave it "as is" and close the report.

@Willsonyuan-msft please also consider to open a series on Wiki and post it there instead.


0 Votes 0 ·
Willsonyuan-msft avatar image
0 Votes"
Willsonyuan-msft answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Willsonyuan-msft avatar image
0 Votes"
Willsonyuan-msft answered ·

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.png (7.0 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Willsonyuan-msft avatar image
0 Votes"
Willsonyuan-msft answered ·

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)



image.png (3.4 KiB)
image.png (2.3 KiB)
image.png (2.4 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.