Support SQL Server Availability Groups

[rossipedia]

At Stack Exchange we need to support SQL Server AlwaysOn Availability Groups, and bringing this support to Tedious would make it a much more viable solution for those in similar situations. There's definitely quite a few parts to getting the full feature-set implemented, so I'd like to keep this PR open as kind of a running discussion/integration during the implementation process.

The first set of changes involves the fReadOnlyIntent bit. I found that Tedious had the wrong bits set for the various TypeFlags. Fixing that allowed setting the fReadOnlyIntent bit and getting the proper ENVCHANGE token (type 20) back from SQL Server after logging in. After that it was a matter of fixing up the state machine to properly transition and reconnect to the server/port indicated in the ENVCHANGE token.

Hopefully (meaning I haven't tested this yet), this will have the added benefit of gracefully handling a manual fail-over as well.

The next step is to handle the situation where a DNS lookup for the SQL Server host name returns multiple IP addresses, which should be attempted in order until a connection is made. I'm pretty sure I'll be able to re-use the REROUTING state for that. We're going to use Microsoft's native driver as the reference implementation for how connection attempts should be performed. See SQL Server Native Client Support for High Availability, Disaster Recovery.

[chrislukkk]

We are a software company also uses SQL Server AG Listener as well as its High Availability and Disaster Recovery feature, we found this feature is not completely implemented in Tedious(specificlly the MultiSubnetFailover connection property), so we want to add it to Tedious to support that.

The Logic will be using dns.lookup to get all the ip addresses of the Listener's DNS name, and connect to multiple ip addresses almost at same time and found the first one correctly connected.

There is one problem with our implementation is the function dns.lookup with options to return multiple IP addresses for single host name only added to node in v0.12.x(see the [, options] parameter in documentation), but in package.json of Tedious the engine version is set to be

  "engines": {
    "node": ">= 0.10"
  },

We are wondering is it possible to increase the engine version to 0.12 for Tedious at this time so that our MultiSubnetFailover implementation can run on all existing consumers? Our major concern is that the increment of support runtime version will break other existing function in Tedious depends on 0.10 (If that's the case we will have to search an alternative way.)

Thanks!

[bretcope]

It's really up to @arthurschreiber to decide what versions Tedious supports at this point. 0.10.x really isn't that long ago though, so I wouldn't be surprised if a lot of people were still running it.

You probably have three options:

1. Don't provide full AG support in 0.10.
2. Use dns.resolve() instead of dns.lookup() which has a different lookup strategy, but returns multiple IP's, even in 0.10.
3. Find or build a shim for 0.10 which does DNS lookup the way you need it to.

Option number 1 is probably fine. It won't be any worse than the current state of things.

[chrislukkk]

Thanks for replying Bret.

We did choose option 2 and implemented two ways (dns.resolve and dns.lookup). The dns.lookup way looks more elegant, that's why we want to check the possibility of bump up the node runtime version of tedious.

If current maintainer think it's OK to support MultiSubnetFailover for only version >=0.12, we could also choose option 1.

[karenClendinning]

@chrislukkk, I need to change our use of mssql to use MultiSubnetFailover. We use the tedious driver mssql provides. Are you planning on adding your changes to Tedious?

[chrislukkk]

@karenClendinning We are still waiting for the response from @arthurschreiber, but yes we hope to contribute to Tedious with our changes.