Skip to main content

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.

 

Labels:

Comments

Post a Comment

Most Loved Posts

SQL Data Tools - Compare Data

Compare Data between two tables SQL Server Database with the same schema architecture can differ in different environments like Dev, Staging, and Production, especially in configuration tables. Let's see how we can easily sync the data in two different tables.
1 comment
Read more

How to deal with Slow SQL Server due to Autogrowth issue

  Why you should not stick to SQL Server’s default Initial file size and autogrowth We hear a lot of these statements : My SQL Server is running slow My Production DB was fine when we started, But it is staggeringly slow now My Business end users are frustrated to wait too long Well, there are lots of reasons why your SQL Server might be slow. Setting the Autogrowth option to default is definitely one of the vital ones which we seem to ignore most of the time. Slow SQL Server and Tortoise SQL Server provides you with some default settings for autogrowth when you install it for the first time. These default cases are defined with increment by 8MB or by 10%. You need to change it to suit your own needs. For Small application, this default value might work but as soon as your system grows, you feel the impact of it more often. What Happens SQL Server Files needs more space SQL Server Requests the Server PC for more space The Server PC takes the request and asks the SQL request...
Post a Comment
Read more