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?

Answer 1

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.