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

Threadpool - A deadly poison wait for SQL Server (The What, When and How)

Introduction  Threadpool is a  poison  wait. Yes, I mean it. Its poison for SQL Server, its poison for the Business and of course, the end-users! The most devastating thing about threadpool is you hardly recognize it because it comes in disguise, meaning you see no memory or cpu pressure in the system, yet you cannot run any query, it seems like your SQL Server is frozen solid. That scary, isn't it?
2 comments
Read more

Why not use Select * in SQL Server

  Select * We often use the Select *  to fetch data from tables of SQL Server.
2 comments
Read more

SQL Insider 01 : An Anatomy of SELECT

Introduction When we write queries, we tend to think about the internals very little. In the new series of SQL Insider, I shall try to demonstrate what your SQL Server has to go through when you write a specific query, more specifically a specific operator. In the series, we shall try to cover all the important operators in SQL. Our today's SQL participant in SELECT. SELECT  With the SELECT query, we can select one, some, or all the columns of a SQL table. The typical syntax for SELECT is like this  SELECT * FROM Sales.SalesOrderDetail SELECT sod.OrderQty, sod.UnitPrice FROM Sales.SalesOrderDetail sod Please note that we will not be dealing with WHERE clause in today's episode.  Database We will be using AdventureWorks2019 Database for the demonstration Important Configuration We will be setting STATISTICS IO ON like this - SET STATISTICS IO ON; SET STATISTICS IO ON ; We will turn on Actual Execution Plan to examine the query SQL Insider Let's sta...
Post a Comment
Read more

Maxdop and Cost theshold for parallelism SQL Server

 Maxdop  Maxdop stands for max degree for parallelism.  Let's say, the maxdop is set to 4, it means during parallel plan execution, SQL server is going to use 4 processors. If you set your Maxdop settings to 0, it means SQL server is going to use as many processors it needs to complete your request. 
Post a Comment
Read more

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