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?

Basutoland

Did you find a solution for this? I have the same issue right now. Would be much appreciated. – Purveyance

you can do something like this dastagirhusain.com/2020/08/… – Stockjobber

Answer 1

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  

Alduino

Answer 2

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

Knife

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Uxorious