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

c# sql-server legacy sqlclient availability-group
Jesús López picture Jesús López · Jan 6, 2018 · Viewed 7.7k times · Source

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

Knotty picture Knotty · May 8, 2018

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

Related questions

How to execute a stored procedure within C# program

I want to execute this stored procedure from a C# program. I have written the following stored procedure in a SqlServer query window and saved it as stored1: use master go create procedure dbo.test as DECLARE @command as varchar(1000), @…

Read More
How can I set an SQL Server connection string?

I'm developing a simple C# application, and I'd like to know this: When I connect my application to SQL Server on my PC, I know the connection string (server name, password, etc.), but when I connect it to another PC, …

Read More
Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More