How to correctly setup the connection string to availability group for legacy app

Question

Imagine you have the following scenario:

• A .net framework 2.0 legacy client app.
• An SQL Server 2016 Always On Availability Group.

How the connection string should be?

I can think on two options:

• Specify the availability group listener name as the Data Source.
• Specify the primary replica as the Data Source and secondary replica as the Failover Partner

Which one should I use to get high availability and automatic failover? Other options?

Answer 1

documentation: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver16 provides a detailed explanation on this.

Example connection string as recommended when we have multiple subnet connections to set MultiSubnetFailover=True.

Server=tcp:AGListener,1433;Database=AdventureWorks;Integrated Security=SSPI; MultiSubnetFailover=True

Answer 2

By the book is always to use the AG listener. I guess at the moment your connection string looks something like this:

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;
Initial Catalog=myDataBase;Integrated Security=True;

But if you successfully created an AG group, then change the connection string to use the AG listener, it would look something like this:

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI; 
MultiSubnetFailover=True