Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
HA Technologies
2.1.
Replication
2.2.
Mirroring
2.3.
Clustering
2.4.
Log Shipping
2.5.
AlwaysON Availability Groups
3.
Steps to configure HA Technologies
4.
Frequently Asked Questions
4.1.
List down the milestones in the history of Microsoft SQL Server.
4.2.
Mention all the Mainstream editions of Microsoft SQL Server.
4.3.
What is SSRS?
5.
Conclusion
Last Updated: Mar 27, 2024

HA technologies- Microsoft SQL

Author Rupal Saluja
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

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.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Steps to configure HA Technologies

The steps to configure HA Technologies are mentioned below. Here, Example is the source database. Server1 and Server2 are primary and secondary SQL server instances, respectively.

Step-1:

Firstly, you have to take one full and one T-log backup of the source database. Backup is taken so that if any data is lost in the process of configuring HA Technologies, it can be retrieved back.

To perform this backup, the query below is written in the primary server Server1 code area.

Backup database Example to disk = 'D:\example_full.bak'
GO
Backup log Example to disk = 'D:\example_log.trn'

 

source

Step-2:

The backup files are then copied to the destination server if the two SQL server instances, that is, primary and secondary are in two different physical servers.

In this case, both the SQL server instances are in the same physical server. So, there is no need to copy the backup files to the destination server.

Step-3:

Now, you have to restore the files that have been copied to the destination server in the previous step using the ‘norecovery’ option.

To do this process of restoring, write the code below in the code area of the secondary SQL server instance. 

Restore database Example from disk = 'D:\Example_full.bak'
with move 'Example’' to 'D:\DATA\Example_DR.mdf',
move 'Example_log' to 'D:\DATA\Example_log_DR.ldf',
norecovery
GO
Restore database Example from disk = 'D:\Example_log.trn' with norecovery

 

source

Step 4:

Refresh the databases folder of Server2, that is, the secondary SQL server instance to see the restored database along with its status.

Step-5:

Then, as per your requirements, configure the HA process you were performing till now. To do this, you have to use the Properties section of Server1, that is, the primary SQL server instance. Take the help of the wizard steps guided by the system itself and complete the configuration process.

Check out Microsoft Interview Experience to learn about their hiring process.

Frequently Asked Questions

List down the milestones in the history of Microsoft SQL Server.

The various milestones in the history of Microsoft SQL Server are listed below.

  • 1989- Microsoft SQL began as a project.
  • 1993- SQL Server 4.2 for NT.
  • 1995- SQL Server 6.0 ending the collaboration with Sybase.
  • 1998- SQL Server 7.0 marking the conversion of source code from C to C++.
  • 2005- SQL Server 2005 completing the conversion of Sybase code to Microsoft code.
  • 2012- SQL Server 2012 adding columnar in-memory storage.
  • 2017- SQL Server 2017 adding Linux support.
  • 2019- SQL Server 2019 adding Big Data clusters

 

Mention all the Mainstream editions of Microsoft SQL Server.

All the mainstream editions of Microsoft SQL Server are mentioned below in the list.

  • Enterprise
  • Standard
  • Web
  • Business Intelligence
  • Workgroup
  • Express


What is SSRS?

SSRS in Microsoft SQL Server stands for SQL Server Reporting Services. SSRS facilitates report generation from data gathered from SQL Server databases. These reports are created in the form of RDL files and administered with the help of a web interface.

Conclusion

In a nutshell, we understood what are HA technologies and learned about various HA technologies. We also saw the steps to configure these High Availability Technologies.

We hope the above discussion helped you understand HA Technologies of Microsoft SQL Server in clearer terms and can be used for future reference whenever needed. For a crystal understanding of Microsoft SQL Server, you can refer to our blogs on Microsoft SQLSQL Server Interview Questions, and Azure SQL by clicking on the respective links.

To grasp more knowledge about MongoDBDatabasesOperational Databases and Non-Relational Databases, you can pay attention to our blogs on those topics by clicking on the respective links. Visit our website to read more such blogs. Make sure that you enroll in the courses provided by us, take mock tests and solve problems available and interview puzzles. Also, you can pay attention to interview stuff- interview experiences and an interview bundle for placement preparations. 

Do upvote our blog to help fellow ninjas grow.

Happy Coding!

Previous article
Services of Microsoft Database SQL Server
Next article
Integration Services in Microsoft Services
Live masterclass