For connection strings, in a High Availability setup, how does the routing work?

Question

i believe partially the answer is, it connects to a listener server which redirects to a real database.

say for example, a connection string is

data source=SQLCL01;initial catalog=UserDatabase;user id=test-user;password=##pass##;MultipleActiveResultSets=True

and SQLCL01 is a listener, which i guess will route to a real database in that group, but what happens if SQLCL01 dies? wouldnt the connection break? how does this work as HA if there is a single point of failure there?

I know some databases have 8 servers. Are they worried the single listener at SQLCL01 would go down? Or do we set up multiple listeners and can pass two in the connection string?

dont think dba's are programmers so might not know? obviously some crossover? — Luke, Nov 26 '21 at 02:06
This is not useful for furthers users in S.O. Please read the guidelines and the tutorial of Stack Overflow. This is not about programming, even with a "connectionString". Even, this is not related to any about .net — Leandro Bardelli, Nov 26 '21 at 02:07

score 0 · Answer 1 · answered Nov 26 '21 at 16:40

0

Assuming that the HA implementation is Availability Groups, the listener is an ANAME that's managed as a Windows Failover Cluster Resource. On a failover, the listener does go down momentarily while quorum it recalculated and the failover is in progress. But it will come up on the new primary node.

Your question suggests a common misunderstanding though - "high availability" doesn't mean "100% uptime". What it usually means is that the system described as such is failure tolerant and that the time to reestablish service is much lower than it would be in the absence of HA.

answered Nov 26 '21 at 16:40

Ben Thul

31,080
4
45
68

An ANAME routes to a single IP. since all DNS is cached with a TTL, if that single IP goes down, your downtime is going to be 0.5 * your TTL on average. How is that going to help you with downtime, unless you are running a very low TTL? – Luke Nov 28 '21 at 10:06
1

A couple of things. First, an ANAME needn't route to a single IP; search for "multi-subnet listener". Second, if the two nodes are on the same subnet, the new primary will assume ownership of the cluster resource and clients will be redirected accordingly. Third, even if the IP of the listener changes as a result of the failover, Windows Clustering changes the IP of the ANAME to whatever it needs to be. – Ben Thul Nov 28 '21 at 20:00

For connection strings, in a High Availability setup, how does the routing work?

1 Answers1