Runtime: SqlClient / MultiSubnetFailover / Linux / 1.1.0

[jamesgibbs100]

I would really appreciate some advice if possible.

I'm running a .NET Core App 1.1.0 in a container against a SqlServer 12 database with MultiSubnetFailover.

My connection string looks like this...

Data Source=..*.com;Initial Catalog=DATABASENAME;User ID=USER_ID;Password="USER_PASSWORD";Connect Timeout=60;TrustServerCertificate=True;Application Name=".NET Core";MultiSubnetFailover=True

There are just two subnets, so when I resolved the dns name I get 2 ip addresses back.
As long as the primary is listed first, I can connect.
If the secondary is listed first it will timeout and fail

Does this mean that MSF is not available in Linux ??
Have I got an incorrect ConnectionString ?
Is the IP resolution running in serial and not parallel ?

I've tried all manner of things - for example - setting the connection time out to 200 (based on some similar queries). I've also read that I should not specify the Initial Catalog (on linux) when using MSF but this is an onerous solution to me.

Would appreciate some advice on this one as I'm running out of options and want to go into production....

Thanks in advance
James

[jamesgibbs100]

Quick update. A simple client running on linux. (.NET Core 1.1.0 / System.Data.SqlClient 4.3) fails when DNS resolves the the secondary first.

_Failure (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server))_

It works fine on Windows with either the primary or secondary presented first from DNS.

Is anyone able to confirm (or not) that MSF works on Linux as well as Windows ?

Thanks
James

[karelz]

@saurabh500 can you help please?

[saurabh500]

@corivera, can you help with this issue with MSF in Managed SNI?

[jamesgibbs100]

@corivera @saurabh500 Sorry to nudge on this but time is short - is there any workaround/advice you can provide me ?

[saurabh500]

@jamesgibbs100 Right now we don't know what is going on.
@corivera is looking into this issue.
What Linux flavor are you using?

[corivera]

I was able to repro the issue locally. Looking into it.

[jamesgibbs100]

@saurabh500 @corivera : Thank you gentlemen. I'm online and will be for a few hours, so if you need me to provide more info / test or clarify - please reach out.

[jamesgibbs100]

FYI : I can produce this in two environment -

Firstly, a Rancher powered container environment running on a base docker image FROM microsoft/dotnet:1.1.0-runtime

Secondly, on a VirtualBox run image as below :

No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 14.04.5 LTS
Release: 14.04
Codename: trusty

Linux new-linux-box 3.13.0-96-generic dotnet/corefx#143-Ubuntu SMP Mon Aug 29 20:15:20 UTC 2016 x86_64 x86_64

[jamesgibbs100]

FYI : Recently, the connection was encrypted - hence the TrustServerCertificate=True. It is possible its in this area - although this is nothing more than a hunch. I had not noticed any issue prior to that but then again I cannot say for certain if I just wasnt getting the primary IP address all the time - maybe a redherring - just sharing

[corivera]

@jamesgibbs100 TrustServerCertificate is only relevant when Encrypt=true. Interestingly, when I increase my timeout from 15 seconds to 60 I start seeing successes, although it takes most of that time to get the query results back. Can you try increasing your timeout to maybe 120 to see if that returns some success?

[jamesgibbs100]

I will try again - but having read the documentation on how the time out is sliced when MSF is involved I have previously tried 200. I will try again though

[jamesgibbs100]

No joy I'm afraid

Primary returned first from DNS and the world is good :

Resolve (132ms) XXXXXX.com to 10.XXX.49.53,10.XXX.48.53
0 1969 Success
1 5 Success
2 0 Success

Secondary returned first from DNS

Resolve (8ms) XXXXXX.com to 10.XXX.48.53,10.XXX.49.53
0 200049 Failure [XXXXXX.com] (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server))
1 7 Failure [XXXXXX.com] (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server))
2 0 Failure [XXXXXX.com] (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Ve

[First number is index, second the time taken in ms - you can see the 200 timeout occuring]

[jamesgibbs100]

FYI : The DNS resolution is nothing more than

Task

I assume the order stays consistent when the call enters the internals of SqlConnection.Open()

[corivera]

Do you notice any difference when including the port number in the data source? e.g. try using Data Source=tcp:SERVER,PORT

[jamesgibbs100]

@corivera : I've not tried.

I will try 1433 just now - bear with me

[jamesgibbs100]

Yes.

When primary reported first from DNS

Resolve (7ms) XXXXXX.com,1433 to 10.XXX.49.53,10.XXX.48.53
0 17049 Success
1 11 Success
2 0 Success

[Longer time on initial success]

When secondary frist reported from DNS

Resolve (7ms) XXXXXX.com,1433 to 10.XXX.48.53,10.XXX.49.53
0 977 Success
1 11 Success
2 0 Success

This is the first success I've had given a secondary DNS ipAddress.

ConnsectionString = "Data Source=XXXXXX.com,1433;Initial Catalog=DATABASE;User ID=USER_ID;Password=\"PASSWORD\";Connect Timeout=200;TrustServerCertificate=True;Application Name=\".NET Core\";MultiSubnetFailover=True;";

[jamesgibbs100]

Running multiple times with SERVER,PORT results in expected/hoped for performance - smiling

Oddly, now DNS always returns the same ipAddress order, where as before it would alternate ??!??

[corivera]

We have a bug where connections eat up a lot of time when no port is specified, since we try to do some queries to get the port in case the server is using dynamic ports. That might be what's causing this issue.

[saurabh500]

The issue is being tracked by dotnet/corefx#15089

[jamesgibbs100]

hey - bugs happen :-)

You are obviously onto it

Is my expectation right for the below :

a) Include the port (however I do this) and the problem goes away
b) "It" will be resolved quickly - (I cannot imagine the Sql team wanting this hanging around ?)

Basically, I didnt want to put the work around in place that I had been planning - I am comfortable if including the port number sidesteps the problem and that I know there is resolution insight

[jamesgibbs100]

I also want to thank you both for a great turn around on this.

[saurabh500]

Thanks for the information and quickly confirming our suggestions @jamesgibbs100 :)

[karelz]

I concur with kudos on fast turn around of suggestions on confirmations!

[jamesgibbs100]

Largely, I think I spoke to soon as deploying with the port number fix into our UAT environment FAILED.

In order to help understand what was going on I captured two TCPDUMP traces as I performed a simple Open on the SqlConnection object.

One container running on HOST01 fails, whilst another running on HOST02 succeeds.

The only difference i can see is the order in which DNS returns the primary and secondary - as discussed above where HOST01 returns the secondary first (this is the failing one) and HOST02 returns the primary. Of course the order shouldn't matter with MSN enabled which it is.

From looking at the traces - it doesn't appear that MSN is working at all on Linux - I do not see a parallel invocation of the open socket that I was expecting. Rather it appears to just take the first IP address and try to connect to that.

I'm happy to share the traces - only perhaps over something less public ?)

I'm also happy to perform any other kind of testing that would help us get to a resolution.

The setup is the same as above only now with port specified (1433).

Framework : .NET App 1.1.0
Library : System.Data.SqlClient - 4.3.0
Container Base : Linux Ubuntu 14.04.5

Will email dumps to @saurabh500

[corivera]

Looking into it

[corivera]

Looks like the Non-Windows version of SqlClient is opening connections serially for MSF, since some of the Linux networking APIs were missing at the time the change was made.
https://github.com/dotnet/corefx/blob/master/src/System.Data.SqlClient/src/System/Data/SqlClient/SNI/SNITcpHandle.cs#L191

Tracking the issue here https://github.com/dotnet/corefx/issues/15331

[jamesgibbs100]

Hi @corivera

Thank you for progressing this - it is consistent with what i was observing.

So, firstly, are those API's now in place ? and secondly, if so, how can I obtain some kind of patch ?

_[Even without them wouldn't the for loop be capable of being run in parallel to resolve the first successful open and ignore others?]_

As I mentioned at the start of this thread, I am trying to get into production and without this form of database HA - it's going to present me with an issue

What are your thoughts ?

Thanks again
James

[corivera]

@saurabh500 what are our patch options for delivering a fix? Replacing the app's SqlClient dll with a locally built version of System.Data.SqlClient.dll?

[karelz]

@corivera does it depend on .NET Standard 2.0 APIs?

[jamesgibbs100]

@saurabh500, @corivera @karelz

Gents, as we approach the weekend I was wondering if you had come to any conclusions ?

To @karelz point : I don't think so (probably wrong), the SqlClient references 4.1 lib so i doubt that 2.0 is an issue.

I'm hoping that you have a larger plan here to ensure that the Linux client doesn't become be a second class citizen ??

Either way - it would be great to understand your thinking at this point.

Please let me know what I can do to help....

Thanks again
James

[saurabh500]

@jamesgibbs100 We are still investigating the correct fix for this issue.
The fix should be delivered in the next release of .Net Core and we will take through a bar check for the servicing of the v 1.1 version.
In case the release dates doesn't meet your deadlines, would if be feasible for you to build and package a SqlClient binary for linux with the patch till the fix is available in the official feeds?

[jamesgibbs100]

Hi @saurabh500

Thanks for the reply and update

At pinch I could take a local build as long as I knew that there is a permanent solution in the pipeline.

Cheers
James

[jamesgibbs100]

Hi @saurabh500

How are your investigations coming along - any update at this time ?

Thanks
James

[saurabh500]

@jamesgibbs100 There is a PR that @corivera is working on
https://github.com/dotnet/corefx/pull/15494

[jamesgibbs100]

@saurabh500 , @corivera - thank you both muchly

James

[corivera]

https://github.com/dotnet/corefx/pull/15494 was checked in on Feb 10

[jongoodnow]

Hello, Is it possible to get this patch for netcore 1.1.1? I tried including "System.Data.SqlClient": "4.4.0-preview1-25220-02" in my project.json (daily build from April 20), but I'm still seeing this problem persist (not sure if that build would include this patch).

[karelz]

@jongoodnow I think you mean the future release from 1.1.x servicing branch, as 1.1.1 was already shipped. It is already too late to get it even into the subsequent release - 1.1.2.

Would it be acceptable for you to wait for 2.0 release?

[jongoodnow]

Thanks for the quick reply. 2.0 is coming soon, right? Maybe I'll try to use the preview version.

[karelz]

Yes, it is coming soon: https://github.com/dotnet/core/blob/master/roadmap.md#ship-dates (I can't share more date details publicly yet, but working on permission to do so, which will take couple of weeks still :()

[karelz]

UPDATE: I mixed it up with another issue, please disregard:
Once we have the fix, it would be awesome if you can help us validate it (from our daily builds - we have steps how to do that). It assumes that you can somewhat reproduce the problem (even if it is in the form "if I run XYZ, it typically fails in 3 days")

[karelz]

Here's how you can use daily builds: https://github.com/dotnet/corefx/blob/master/Documentation/project-docs/dogfooding.md

They are pretty stable these days. If you hit issues, just let us know.

[jongoodnow]

Thanks. I will give this a shot. I'd like to ask for my own understanding - why can't I just include the latest DLL for System.Data.SqlClient and leave everything else the on the older version? That's what I was attempting to do before.

[karelz]

Because it depends on new APIs in other DLLs.

[jongoodnow]

Ah, makes sense. Thanks!

[karelz]

And because if it doesn't, it is NIGHTMARE to juggle the dependencies in the 100-something DLLs we have. We make mistakes, which cause PAIN. Even without mistakes, it causes PAIN also to our customers, because one library pulls in more, you run untested combination, etc. The complexity & cost is just not worth the value it brings.

[jongoodnow]

That is fair. Thanks for taking the time to respond!

[ezeasharma]

Hi - I upgraded to .netcore 2.0 and still seeing this issue with SqlServer 12 database with MultiSubnetFailover.

[karelz]

@ezeasharma it would be best to file a new issue, describing symptoms and troubleshooting attempted, etc.
This issue is closed, and likely not actively monitored.