What is High Availability SQL Server
High Availability means that the SQL Server instances, or databases will be available and reachable, with the least possible downtime, in case of any server crash or failure.
Below are the listed ways of achieving HA
- Log shipping
- Replication
- Mirroring
- Always On Failover Cluster
- Always On Availability Groups
Log shipping
Replication
Mirroring
Always On Failover Cluster
Always On Availability Group
SQL Server tree view of Always On Availability Group
“Read Only routing” feature
•Always
On secondary replica has feature to handle read-only connection requests
•This
feature is called as Read-only routing feature
•By
default both read and write operations goes to primary server
•If
Application Intent is mentioned as ReadOnly in request connection, then
request goes to secondary database
•Supports
max 8 secondary databases
•Depending
on the version, requests are served either from same secondary server or in
round-robin fashion
Steps for trying from SSMS
Application code changes
•Make
sure the HA method is AlwaysOn AG method
•Create
new connection string by adding ApplicationIntent=ReadOnly in connection string
•While
making read-only stored procedure DB calls, use the read-only connection string
Code snippet
<add
name="ReadOnlyDBConnection"
connectionString="data
source=XXXXXX;initial
catalog=XXXXX;integrated
security=True;MultiSubnetFailover=True;ApplicationIntent=ReadOnly"
providerName="System.Data.EntityClient"
/>
public static string ReadOnlyConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings[“ReadOnlyDBConnection”].ConnectionString;
}
}
private
DbConnection
GetReadOnlyConnection()
{
var
connection = this.providerFactory.CreateConnection();
connection.ConnectionString
= ReadOnlyConnectionString;
return
connection;
}
Big benefits
•Taking
off the read load from primary database
•Reduces
lock situations
•Both
read and write operations will be faster as they happen in different
databases.
