High Availability (HA) in SQL Server
Last Updated :
24 Sep, 2020
It is the solution or process or technology to make the service or application or database availability 24x7 and 100% through needless and fault-tolerant components at the same location under either planned or unplanned outages.
There are mainly five options in MS SQL Server to setup high availability solution for the databases:
- Replication
- Log Shipping
- Mirroring
- Clustering (Failover Cluster)
- AlwaysON Availability Groups (AG)
Replication :
Source server is called
Publisher. Destination server is called
Subscriber. There's an optional server that stores replicated data for the subscriber called
Distributor.
The source data are copied to the destination through replication agents (jobs). Replication's main purpose is to repeat and distribute information from one database to a different one.
There are four varieties of replication that we'll outline :
- Snapshot replication
- Transactional replication
- Merge replication
- Peer to Peer replication
Snapshot :
Snapshot replication happens when a snapshot is taken of the database, and snapshot is copied to the subscriber. Snapshot replication could be used as an initial data set in circumstances to begin subsequent replication processes that has minimal changes.
- Transactional :
Transactional replication begins with a snapshot of the publisher database that's applied to the subscriber. Once the snapshot is in place all transactions that occur on the publisher are going to be replicated to the subscriber.
- Merge :
Merge replication begins with a snapshot of the publisher database that's applied to the subscriber. Changes made at the publisher and subscriber are shadowed offline. Once the publisher and subscriber are back online simultaneously, the subscriber synchronizes with the publisher and the other way around.
- Peer to Peer :
Peer to Peer replication is often used as transactions occur they're executed on all of the nodes involved in replication in order to keep the data in sync in near real time.
Log Shipping :
Source server is called
Primary server. Destination server is called
Secondary server. There is an optional server and will be monitored by log shipping status is called
Monitorserver.
The secondary databases are restored from the primary database with no recovery leaving the database inaccessible to end users by using the SQL Server Agent and job schedules at a definite interval. While it's nice that log shipping supports multiple secondary servers, it's probably the smallest amount used for HA because before the fail-over can occur, the secondary database must be brought fully up to date by manually applying not restored log backups.
Mirroring :
Source server is called
Principal server. Destination server is called
Mirror server. There is an optional server that is used to make automatic fail-over is called
Witness server.
Database are inaccessible to the end-users when the mirror database is restored from the principal with no recovery. Once mirroring is enabled, all transactions happening at the principal are copied to the mirror. Fail-over with this option usually only takes seconds to finish. If the principal server was down the mirror server would automatically become the principal.
Clustering(Failover Cluster) :
The node where SQL Services are running is called
Active node. The node where SQL Services are not running is called
Passive node.
Prerequisite to setup Clustering -
Windows Clustering setup with shared storage.
Clustering -
It involves a minimum of two servers. Clustering will allow one physical server to require over the responsibilities of another physical server that has failed. This is often used in crucial environments that require near 100% up-time. When a server's resources fail, the other server will automatically obtain where the failed server left off causing little or no downtime.
The 2 kinds of clustering are :
- Active/Active :
When running in Active/Active mode, SQL Server is running actively on both servers. If one among the SQL Server's fail then the opposite SQL Server will fail-over.
- Active/Passive :
When running in Active/Passive mode, SQL Server runs on one server while the opposite server waits just in case of a failure. This can be the foremost popular choice because it doesn't affect performance.
AlwaysON Availability Groups :
Source server is called
Primary replica. Destination server is called
Secondary replica.
Prerequisite to setup AlwaysON -
Windows Clustering setup without shared storage.
AlwaysON -
Availability Group is a new feature introduced with SQL Server 2012. It uses groups called Availability Groups, which are groups that contain selected databases that will fail over together if a failure should occur. It consists of one primary replica, in which the databases will be available to read-write connections, and up to eight secondary replicas, that can be used to read-only connections for reporting purposes. To make it easier for the database users to connect to the availability group, an Availability Group Listener can be created. An availability group listener is a virtual name, that consists of a unique DNS name, virtual IPs, and a TCP port number, which provides a direct connection to the replica on that availability group.
Similar Reads
Difference between High Availability and Disaster Recovery for SQL Server
1. SQL Server High Availability (HA) : It is the technology that could be used to provide 100% service availability and 24x7 up-time by using extra and backup components like a copy of the database at the same location. Benefits of High Availability : Keeping databases online from the circumstances
2 min read
Displaying Department Name Having Highest Average Salary in SQL Server
In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the name of the department having the highest average salary of employees working in it. We shall use the TOP, AVG, ORDER BY, AS, GROUP BY, and DESC clauses to ac
3 min read
How to Limit Rows in a SQL Server?
To limit rows in SQL Server, use the TOP clause in the SELECT statement. Using the TOP clause in SQL Server, users can limit the number of rows in the results set. Here, we will understand how to limit rows in SQL Server with the help of different examples. Steps to Limit Rows in SQL ServerLet's che
3 min read
Primary key in MS SQL Server
A table has a particular number of columns and each column has n number of rows. At times, there might be a probability of repeated rows in a column. For example, a column named identification number has repeated rows. To avoid the duplication of rows, the concept of a key has been introduced. A key
2 min read
Dirty Read in SQL
Pre-Requisite - Types of Schedules, Transaction Isolation Levels in DBMS A Dirty Read in SQL occurs when a transaction reads data that has been modified by another transaction, but not yet committed. In other words, a transaction reads uncommitted data from another transaction, which can lead to inc
6 min read
SQL Server Architecture
Microsoft SQL Server is a widely used relational database management system (RDBMS) that organizations around the world rely on for managing and processing their data. It provides a scalable and reliable platform for managing large volumes of data, supporting a wide range of applications from small-
5 min read
Displaying Department Name Having Most Number of Employees in SQL Server
In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the name of the department having the most number of employees working in it. We shall use the TOP, COUNT, ORDER BY, GROUP BY, and DESC clauses to achieve this. T
3 min read
Displaying Department Name Having Least Number of Employees in SQL Server
In SQL, we need to find out the department-wise information from the given table containing information about employees. One such data is the name of the department having the least number of employees working in it. We shall use the TOP, COUNT, ORDER BY, and GROUP BY clauses to achieve this. This i
3 min read
Introduction of MS SQL Server
Data is a collection of facts and figures and we have humungous data available to the users via the internet and other sources. To manipulate the data, Structured Query Language (SQL) in short has been introduced years ago. There are different versions of SQL available in the market provided by diff
2 min read
How to Identify Slow Running Queries in SQL Server?
Identifying and troubleshooting slow-running queries is crucial for maintaining optimal performance in SQL Server. Slow queries can lead to degraded application performance, increased resource consumption and a poor user experience. In this article, we will covers key methods to identify slow-runnin
5 min read