How to connect to SQL Server Always On Listeners

The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring.

Information Services has implemented the SQL Server Always On Availability groups in most of the SQL server environment. In the SQL Server Always On, we define a listener for all user connections including application id for the client applications. A virtual network name always points to the primary replica which is in our data center (DFT) and a secondary replica in the CST. The client application does not need to know the underlying configuration of Always On replica.

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. When a disaster occurred the entire availability database group fails over to CST. The client’s user id automatically repoints to the new primary replica in CST.

Connect to the primary replica in SQL Server Management Studio

Enter the listener DNS name in the Server name field.

 

Connect to the primary replica in ODBC connection

In the ODBC client, select SQL Server Native Client 11 or above.

Check the box for Multi-subnet failover.

 

Connect to the primary replica in ADO.NET provider Connection

We can specify MultiSubnetFailover in the ADO.Net provider connection string as per following connection string.

For example:

Server=tcp:SQListenerName,Port;Database=TestDB;IntegratedSecurity=SSPI; MultiSubnetFailover=True