Tedious: multisubnetfailover support for tedious

[pradeep250]

Hi,
I am new to node js, I recently started an application to develop web service for the MSSQL database.
I am using tedious driver to connect to database, i came to know that multisubnetfailover feature supports in tedious but not yet merged into master.
May i know by when can this change be submitted into master?
Is there any way by which I test multisubnetfailover option by installing through npm from a branch where it got developed?

Thanks in advance.

[mitant]

See #362
When I tested that PR, I still get occasional timeouts unfortunately. You can test that PR by fetching from it directly into your node_modules or by using shrinkwrap

[SaloniSonpal]

@arthurschreiber and @tvrprasad are working toward fixing the issues of #362 (#346 has been merged). Have you tried the latest PR, @pradeep250 ?

[YogeshPD]

Is there any update on this? I tried with the latest version of mssql package (v 4.3.0) that uses tedious driver and I am not able to connect to a clustered SQL Server. Connection request times out.

[catchnthrow]

I am using tedious 6.4.1, but still facing the same issue. Any updates?

[MichaelSun90]

Hi @prashant19sep, I have just checked that the feature has been merged since Mar 10, 2017, and released as tedious version 1.15.0. The feature is still in the latest version of tedious. To use it, you can set an option called "multiSubnetFailover" under config.options to enable this feature. Can you try it, and see if that works?

var config = {
options.multiSubnetFailover=true;
}

[YogeshPD]

We found that options.multiSubnetFailover=true doesn't work.. still doesn't connect to SQL cluster.. Had to work around the issue

[catchnthrow]

Hi @prashant19sep, I have just checked that the feature has been merged since Mar 10, 2017, and released as tedious version 1.15.0. The feature is still in the latest version of tedious. To use it, you can set an option called "multiSubnetFailover" under config.options to enable this feature. Can you try it, and see if that works?

var config = {
options.multiSubnetFailover=true;
}

It worked for me now. I was adding the flag directly, and not with "options."

[mrinalmonga]

The thing is that the "dns" (used by tedious js) module of node (https://nodejs.org/api/dns.html) has "lookup", "resolve4" and "resolve6" methods. There is a lot of difference between "lookup" and the "resolve4", "resolve6" methods.

The "lookup" method of "dns" does NOT necessarily do DNS lookup (as one might think, given the name of the method).

Since tedious js uses dns.lookup that is why it does not support multisubnet failover gracefully 100% times.

I ran an experiment on my machine. The results of which can be summarised as follows -

Suppose that there is a cluster "xyz.abc.com" which resolves to 2 IP addresses (1) and (2) . Also, suppose that only one of the IP addresses is active and that the other is NOT active. Then, when I ran the "dns.lookup" method couple of times it gave me (say) , then when I opened the terminal/command prompt of the Operating System and explicitly did a DNS Look Up using appropriate commands ('nslookup' command) for "xyz.abc.com", I found that afterward, the dns.lookup method started giving me instead of .

Bottom Line - As stated in the documentation of DNS for the lookup method _"do not necessarily perform any network communication"_ . This is a dead give away that why , it is wrong to use this method in tedious js code. See Github https://github.com/tediousjs/tedious/search?q=dns.lookup&unscoped_q=dns.lookup

SOLUTION -

We engineered a simple solution, which is in line with the recommendation from Microsoft Recommendation ( See diagram given on https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/sql-server-multi-subnet-clustering-sql-server?view=sql-server-2017)

(1) USE dns.resolve4 or dns.resolve6 RATHER than using dns.lookup of the "dns" module for the "host"
(2) You will get a list of all the IP addresses corresponding to the "host"
(3) Try all registered IPs, obtained by dns.resolve4 method (which actually does a DNS Lookup over the network, as opposed to dns.lookup which does not do that, and is still used by tedious js)
(4) Connect on first response .

NOTE -

(1) Quite surprisingly we found no performance penality for doing an actual DNS Lookup over the network using dns.resolve4, rather than using dns.lookup (which does NOT necessarily do an actual DNS lookup over the network)

(2) tedious js's implementation needs to be updated. We found NO reference to the resolve4 or resolve6 method in tedious js's code base (as of the time of writing this comment )
Please See the link https://github.com/tediousjs/tedious/search?q=dns.resolve&unscoped_q=dns.resolve as a PROOF

[arthurschreiber]

@mrinalmonga Thank you so much for the very detailed writeup! I'll try to update the code in tedious to not use dns.lookup, based on the research you've provided! ❤️

[arthurschreiber]

@mrinalmonga I read through the SQLServer documentation you linked, and through the documentation on dns.lookup and dns.resolve4/dns.resolve6 and their differences, and I think the implementation in tedious is correct.

If I understand correctly, when RegisterAllProvidersIP is set to 1, which it should be by default for clustered setups, all IP addresses (whether these are active or not) are registered for the cluster's DNS name. When looking up the name via dns.lookup, the first IP address out of the list of registered addresses is returned. This is what you're seeing when you call dns.lookup or nslookup. The order of the IPs can change, and this is usually cached at the system level, and that's why you see that nslookup can influence the result of dns.lookup, and vice-versa.

But in tedious, this is not what we're doing - we're calling dns.lookup with the all: true option set - this returns all the IP addresses registered to the given hostname, and then we try to connect to these in parallel (if multisubnetfailover: true is set), or in sequence.

Effectively, it does not matter that we use dns.lookup (which uses the operating system's DNS resolving functionality) instead of using dns.resolve4/6, as we still request all IP adresses to be returned and try connecting to them. I'd even argue that using dns.resolve4/6 directly would be unintuitive, because we suddenly would not honor the operating system's configuration (and overrides e.g. via /etc/hosts files and similar).

I hope this explanation makes sense. 🙇

@IanChokS @MichaelSun90 can you double check this with the SQL Server team and/or with what other client drivers are doing?

[IanChokS]

Hi @arthurschreiber, just discussed with OLEDB driver team, they elicit the same behavior as tedious in that

this returns all the IP addresses registered to the given hostname, and then we try to connect to these in parallel (if multisubnetfailover: true is set), or in sequence.

[mrinalmonga]

Hi @arthurschreiber

1. As of the time of publishing the comment, we did try using the multisubnetfailover: true but it did not work. We tried it on both Windows machine and Linux. As it didn't work we had to come up with a different solution.

2. I think that the purpose of ecosystems such as Node Js and packages provided in that ecosystem is that they act as abstractions. These packages and Node Js abstract away lower details such as Operating System , the processor being used, its architecture from the Developer using the package.

If that were not the case then why have them in the first place ? Usage of abstraction recommends that the developer using the abstraction should not need to know the inner implementation details of a method.

1. I think that using dns.resolve4/6 is better as the code is directly talking to the the source of truth for Domain Name Resolution and always gets the latest information.

[arthurschreiber]

@mrinalmonga Using dns.resolv4/dns.resolv6 would be going against how all the other built-in functionality in Node.js works, e.g. http, https and even net just use dns.lookup. You can see this here: https://github.com/nodejs/node/blob/4bec6d13f9e9068fba778d0c806a2ca1335c8180/lib/net.js#L1037

Long-term, what we could do, is allow users to provide their own lookup functionality so they could perform whatever type of lookup they see fit. This is also something that Node.js does, see the lookup option on net.connect: https://nodejs.org/api/net.html#net_socket_connect_options_connectlistener

@mrinalmonga Would such an option be satisfactory for your use case?

---

@mrinalmonga On another note, would you mind checking again if multisubnet failover works for you out-of-the box with a current version of tedious? I'm pretty sure that it works, but would like to double-check and ensure it really does. 🙇

[arthurschreiber]

@IanChokS @MichaelSun90 Do you have access to a test environment that is set up with multisubnet failover functionality?

[IanChokS]

Currently we don't, but I can try to look into it.

[Jindam]

I had the similar issue with connecting to MSSQL multisubnet failover database instances from node app, fixed it by following changes

let config = {
          userName: username,
          password: password,
          server: host,
          options: {
            port: conn.config.port,
            database: conn.config.database,
            multiSubnetFailover:true
          }
        };
var connection = new Connection(config);

[Jindam]

Hi @prashant19sep, I have just checked that the feature has been merged since Mar 10, 2017, and released as tedious version 1.15.0. The feature is still in the latest version of tedious. To use it, you can set an option called "multiSubnetFailover" under config.options to enable this feature. Can you try it, and see if that works?

var config = {
options.multiSubnetFailover=true;
}
Thanks, it worked

[IanChokS]

@mrinalmonga @Jindam @arthurschreiber, @MichaelSun90 and I are in the process of creating a test environment to test this and fix any issues with it.

---

@mrinalmonga what is your config set up? Note, in the config, it's multiSubnetFailover (with camel case) not multisubnetfailover

[IanChokS]

@arthurschreiber @mrinalmonga So after testing, I believe the multiSubnetFailover is working as it should.

When "multiSubnetFailover": false,, you can see that the client sends out a ping for each IP Address one at a time.

But when "multiSubnetFailover": true,, the IP Address are pinged in parallel.

---

@YogeshPD, to connect to the Sql Server using different IP Addresses, I had to manually specify the TCP Port number for each IP Address in the Sql Server Configuration Manager. I'm not sure if this might be the same set up as yours though

[IanChokS]

Closing for now. Feel free to re-open if this issue still persists for anyone.