HA technologies- Microsoft SQL

Introduction

Databases have now become a vital part of our lives. Every organization, whether private or public, commercial or social, maintains a database to store every small detail of their firm. Databases can be Relational as well as Non-Relational. By the name, Microsoft SQL Server, it is clear that it has something to do with databases. Microsoft SQL Server is a software product whose function is to store, manage and retrieve data as per the request of concerned stakeholders. It is a Relational Database Management System (RDBMS) developed and provided by Microsoft.

HA Technologies

HA in HA Technologies stands for High Availability Technologies. Solutions or processes that have made databases available to the users 24*7 are known as HA Technologies or High Availability Technologies. This technology makes sure that applications reach their clients in each and every situation, planned or unplanned. With this solution, the work capabilities and capacities have increased. The completion time of various projects has increased which has incremented the efficiency of several applications supporting the current agile environment by fastening the access of data.

There are five options provided by Microsoft SQL Server that facilitates the setup of HA Technologies. These options help in achieving high availability solutions for accessing databases.

Here is the list of those five options that are the pillars of HA Technologies.

• Replication
• Mirroring
• Clustering
• Log Shipping
• AlwaysON Availability Groups

Now, we will understand these keys one by one.

Replication

As soon as we hear Replication, the first that comes to mind is copying or reproducing. The concept is similar here. Replication is an Object-level technology. The data from the source is copied to the destination. This work of replication is done by some agents, known as replication agents.

Basic Terminologies

1. Publisher

The Publisher is a source server. Its function is to store source data and allows its access to the Distributor. 

2. Distributor

The distributor is optional. Data can reach the Subscriber directly or indirectly. If the distributor stores the data for the Subscriber to access, then we can say that the data was accessed indirectly.

3. Subscriber

The Subscriber is the one to whom data is supposed to reach. It is the final or the destination server.

Mirroring

Mirroring is a Database level technology. It is also a form of copying. But, here data is transferred on the basis of a network transaction. The primary data which is present in the principal server is mirrored to the secondary with the help of the mirroring endpoint and port number. 

Basic Terminologies

1. Principal Server

As the name suggests, the principal server is the one that contains primary data. Thus, it is known as a principal server. It will store primary data until accessed directly or indirectly.

2. Mirror Server

The destination server which contains the mirrored data is known as Mirror Server. It is the final location where the data reach.

3. Witness Server

It is an optional server. In case any failure occurs in between the process of mirroring, then it is solved automatically by the witness server. 

Clustering

Clustering is an Instance level technology. Here, by clustering, we mean a grouping of data. The data is grouped together and is stored in a location shared by both primary and secondary servers. From this shared location, data is accessed by both the servers as per the requirements of the server. Along with the shared location, Windows Clustering Setup is also required to smoothen the clustering process.

Basic Terminologies

1. Active Node

In this node, all SQL services are running currently. Therefore it is known as Active Node.

2. Passive Node

It is the node where no SQL services are running. Thus, this node is called a Passive or inactive node.

Log Shipping

Log Shipping is a Database level Technology. In this process, the source or primary data is copied to the destination or final location. Since the copying is done by some agents known as Transaction Log backup jobs, the whole process is known as Log Shipping.

Basic Terminologies

1. Primary Server

By the word primary, it is clear that it is that server that contains primary data. It is the source server that stores source data which will be copied to the secondary server anytime when needed.

2. Secondary Server

The Secondary server contains a copy of the data stored in the primary server. It is the final location for the data to reach. Hence, it is known as the Destination or Final server.

3. Monitor Server

It is an optional server. As the name suggests, the monitor server monitors the process of Log shipping. The status is monitored so that if any error occurs in between the process, it is reported to the agents as soon as possible.

AlwaysON Availability Groups

In the previous four technologies, we were working with a single file. But in this technology, we will be working with a group of files. AlwaysON Availability Groups are groups of Database level technology. The primary data is copied to the secondary on the basis of a network transaction. The groups are formed using a Windows Clustering setup without any shared storage.

Basic Terminologies

1. Primary Replica

It is the source server that contains the primary data of each member of the group. Hence, it is known as Primary Replica.

2. Secondary Replica

The secondary replica contains the replication of the primary data. It is the destination server. Thus, it is also known as Final Replica.