How to configure your Availability Group listener to ASP.NET

SQL Server’s availability group Always On feature is great to have features for your Database. Anytime one of your database nodes goes down, your secondary replica will automatically take over. After a failover, your secondary cluster node becomes the primary cluster. Now the question arises, “Do I need to configure my APP server connectionstring each time I face a failover cluster?”. The answer is NO, you don’t have to configure your app server connectionstring every time.

Default ConnectionString

By default, your App server connectionstring looks something like this –

<connectionStrings>
    <add name="ConnStringDb1" connectionString="Data
Source=localhost;Initial Catalog=YourDataBaseName;Integrated
Security=True;" providerName="System.Data.SqlClient" />
  </connectionStrings>

ConnectionString for Failover Partner

You can manually specify the failover partner in your connectionstring like this

<connectionStrings>
    <add name="ConnStringDb1" connectionString="Data
Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial
Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient"/>
  </connectionStrings>

 Here you need to specify your primary node in “Data Source” and your Failover secondary node in Failover Partner.

ConnectionString for AG (Availability Group)

With AG (Availability Group) properly set up, you can simply do this –

<connectionStrings>
    <add name="ConnStringDb1" connectionString="Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;
MultiSubnetFailover=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

 Here you need to specify your Availability group listener in “Server” field and take some vacation perhaps. Your App Server is going to manage the failover on its own.