SQL Server High Availability 101 | The Complete How-to Guide

With the growing demand for key applications to be available virtually 24×7 to businesses and end-users, all businesses must carefully plan how to ensure this service availability while minimizing scheduled downtime in the event of a disaster. Several SQL Server High Availability and Disaster Recovery strategies are supported by Microsoft SQL Server. Despite the fact that SQL Server High Availability (HA) and Disaster Recovery (DR) are two distinct terminologies, they both strive to provide continuous access to services or data with the least amount of downtime.

This article will tell you about SQL Server High Availability. It will also provide some pointers that can help you carry out SQL Server High Availability efficiently.

What is Microsoft SQL Server?

SQL Server is a Microsoft Relational Database Management System that allows individuals and companies to store data in rows and columns. Since it supports transaction processing and Business Analytics applications, it is widely utilized by organizations. SQL Server enables users to store and retrieve data via API Calls and Apps. The SQL language can be used to access and manipulate the structured data in SQL Server.

Microsoft SQL Server is available in a variety of editions that can handle workloads ranging from single-machine applications to scalable applications with numerous concurrent users. TDS stands for Tabular Data Stream and is used to communicate for all SQL Server actions such as update, delete, and so on.

Key Features of Microsoft SQL Server

The following are some of the SQL Server’s most important features.

• Polybase: SQL Server includes Polybase as part of the SQL module, which allows users to conduct fast and parallel T-SQL queries and even connect to external storage.
• Data Recovery: SQL Server has the ADR (Accelerated Data Recovery) technology, which is a novel technique to recover a database in the event of a system or transaction rollback.
• Resumable Index Construction: SQL Server can pause index construction and resume it at a later time.

What is SQL Server High Availability (HA) & Disaster Recovery?

SQL Server High Availability 

The fundamental purpose of SQL Server High Availability is to assure service availability by reducing the impact of scheduled and unplanned downtime. Almost all SQL Server High Availability techniques will have their infrastructure in a single geographic location and the ability to automatically swap over.

These are some of the supported SQL Server High Availability Strategies.

• Windows Failover Cluster Instance (within single datacenter)- Till SQL Server 2008 R2 
• Transactional Replication 
• Log Shipping (within or nearby data center with fast data transfer options)
• Database Mirroring (Synchronous)
• Always On Failover Cluster Instance (within a single data center) — Starting with SQL Server 2012, this feature is available.
• Groups with Always-On Availability (within or nearby data center with fast data transfer options)

Disaster Recovery

Disaster Recovery’s main purpose is to ensure service continuity in the event of a Geographical Disaster by recovering or resuming services from a new location. In addition to the primary location, DR can be found in one or more locations.

The Disaster Recovery Techniques listed below are supported by SQL Server.

• Peer to Peer Replication 
• Backup and Restore
• Log Shipping (across geographically separated Data Centers)
• Database Mirroring (Asynchronous)
• Replication of VMs or Storage
• Always On Failover Cluster with Storage Replication 
• Always On Failover Cluster Instance with Storage Replication 

What are the Key Concepts associated with SQL Server High Availability?

Everyone should be aware of the terminology before getting into a discussion regarding SQL Server High Availability or Disaster Recovery solutions.

Planned Maintenance 

After contacting all required workers, Planned Maintenance is a coordinated/scheduled downtime to undertake maintenance activities such as

• Patching or Updating an Application.
• Patching or Securing the Windows Operating System,
• Upgrading Hardware or applying Firmware Fixes.
• Organizational DR Solutions are being tested.

Unplanned Outages

An Unplanned Outage is downtime that occurs without warning and can result from any of the causes listed below.

• Issues at the Application Level.
• Issues at the Infrastructure Level (VM, OS, Server or Storage Issues).
• Natural Disasters can be devastating.
• Corruptions at the Storage or Database level.

Recovery Time Objective (RTO)

The permissible downtime for the application, whether due to planned or unforeseen outages, is defined as RTO. If an application’s RTO is 12 hours, the maximum downtime for that application is also 12 hours, and the application should be back up and running in that time.

Recovery Point Objective (RPO)

RPO is the maximum amount of data that an organization or system can afford to lose, usually measured in time. RPO differs from one database to the next and from one application to another. For example, a production database may have a minute recovery point, whereas a test or development database may afford to lose days to weeks of data.

Recovery Level Objective (RLO)

RLO refers to the level of granularity required to restore data at the instance, database, or table level. Recovering from corruption at the database level is a great illustration of this.

What are the Advantages of SQL Server High Availability Mechanism?

• Up to nine Availability Replicas are supported. An Availability Replica is an instance of an Availability Group hosted by a single instance of SQL Server that keeps a local copy of each availability database in the availability group. Each Availability Group can have up to eight secondary replicas in addition to one primary replica. See Overview of Always On Availability Groups for additional details (SQL Server).
• Supports Automated Failover, planned Manual Failover (often referred to as “Manual Failover”), and forced manual failover for availability groups (generally referred to as simply “Forced Failover”). See Failover and Failover Modes for further details (Always On Availability Groups).
• Each Availability Group has its own Availability Group Listener. An Availability Group Listener is a server name to which clients can connect in order to access a database in an Always On Availability Group’s primary or secondary replica. Incoming connections are directed to the primary replica or a read-only secondary replica via Availability Group Listeners.
• Alternative availability modes are supported, as follows:
  • Asynchronous Commit Mode: This availability mode is a disaster-recovery strategy that works effectively when availability replicas are spread out over a large area.
  • Synchronous Commit Mode: This is a type of synchronized-commit mode. At the cost of increased transaction latency, this availability option prioritizes SQL Server High Availability and data security over performance. Up to five synchronous-commit availability replicas, including the current primary replica, can be supported by a single availability group.

How does Microsoft Handle SQL Server High Availability? 

1) Log Shipping

SQL Server Log Shipping is a Database-level High Availability Solution that is suitable for less mission-critical databases and offers a customizable recovery point and time. It is made up of one primary Database Server and one or more standby secondary servers for reporting purposes.

Since the Log Shipping functionality is heavily reliant on Transaction Logs, it requires the Database to be in Full Recovery mode. It begins by restoring the primary database to the secondary Servers, after which the secondary database will be synced with the primary database’s cumulative SQL Server Transaction Log Backups.

Three SQL Server Agent jobs will be used to automate this process. The first task will take a Transaction Log Backup from the primary database, the second job will copy it to the secondary Servers, and the third job will restore these Transaction Log Backups to the secondary databases without going through the recovery procedure. As a result, the secondary databases will not be made available online. Instead, it will be in restoring or standby mode, waiting for the primary server’s next SQL Server Transaction Log Backup, as seen below:

Role change, or failover between the primary and secondary Servers in Log Shipping, should be done manually using a T-SQL query.

2) Database Mirroring

SQL Server Database Mirroring is a high-availability solution for databases with a full recovery mechanism that can be adjusted. It consists of at least two Servers: the primary SQL Server, also known as the Principal Server, and the secondary SQL Server, also known as the Mirror Server, with a third Server, also known as the Witness Server, as an option. The Witness Server will keep track of the connection between these two Servers, as well as its availability, and will perform an automatic failover, or role switch, between them.

SQL Server Database Mirroring has two modes of synchronization:

• High-safety mode, also known as Synchronous Mode, increases the likelihood of transaction latency by committing the transaction on the principal database after committing and writing it to the Transaction Log file on the mirror database.
• The transaction will be committed on the principal database without waiting for it to be committed on the mirror server in high-performance Synchronization mode, also known as Asynchronous mode, reducing transaction latency but raising the risk of data loss.

Without bringing the mirror database online, Database Mirroring is begun by restoring a full backup and a SQL Server Transaction Log backup from the principal database to the mirror Server. The mirror database will be synchronized after configuring Database Mirroring by transferring the active Transaction Log records to the mirror database and redoing all of these activities on the mirror database, as indicated below:

3) Data Replication

SQL Server Transactional Replication is a Real-time, database-level SQL Server High Availability solution that consists of one primary Server, known as the Publisher, that distributes all database tables, or selected tables known as Articles, to one or more secondary Servers, known as Subscribers, which can also be used for reporting.

Transactional Replication is based on a synchronization mechanism with the SQL Server Transaction Logs, as the name implies.

It employs three agents:

• You can think of the SQL Server Snapshot Agent as a full backup file that prepares the initial snapshot file that contains the Database objects to be replicated.
• The Distribution Agent is in charge of distributing the initial snapshot as well as the cumulative logs to the subscribers.
• As shown below, the Log Reader Agent is responsible for monitoring the SQL Server Transaction Log in the publisher database and copying transactions from that database’s Transaction Log file to the distribution database, where they would be copied to subscribers later by the distribution agent:

4) Failover Clustering

SQL Server Always on Failover Cluster is a high-availability solution for SQL Server instances that are based on Windows Server Failover Clustering. It is made up of a number of computers, referred to as Cluster Nodes, that share the same hardware and software components in order to ensure SQL Server High Availability for the failover Cluster Instance.

The SQL Server Services and Resource Groups, including Shared Storage, Network Names, and Virtual IPs, can only be held by one of the Cluster Nodes at a time when the SQL Server Failover Cluster is configured and started.

If any failure occurs on the active node that owns the resource groups and has the SQL Server Service Online, or simply a planned reboot or upgrades for that active node, the ownership of the Resource Group will be completely moved to another Cluster Node, with the SQL Server Instance being taken offline on the previously active node and brought online on the new node that owns the Resource Group.

You can see that the SQL Server Transaction Log has no role in the SQL Server Always On Cluster because it will be set at the SQL Server Instance level, eliminating the requirement to synchronize changes between nodes. In other words, the SQL Server database will be located and available on only one Cluster Node at a time, with no replicas on the other Cluster Nodes.

5) AlwaysOn Availability Group (AG)

The Availability Group is a collection of user databases that failover to availability replicas collectively. To put it another way, a Database-level problem, such as Database Corruption or Data Loss, will not compel the availability group to failover to another replica, because a database failure will not affect other databases in the Availability Group.

An Availability Group Listener can be configured to make it easier for Database Users to connect to the Availability Group without having to know which SQL Server Instance is running the primary replica. If the read-only routing list is configured on that availability group, an availability group listener is a virtual network name that consists of a Unique DNS Name, one or more Virtual IP Addresses, and a TCP Port Number that provides a direct connection to the appropriate replica of the Availability Group by routing the read-write workload to the primary replica and the read-only workload to the secondary replica.

Data Synchronization between the primary and secondary copies takes place at the database level and is heavily reliant on the database’s SQL Server Transaction Log. The Transaction Log records from each database in the availability group are sent to all secondary replicas by the primary replica. Secondary replicas, on the other hand, will cache these Transaction Logs in the Database Transaction Log file before redoing them in the associated secondary database.

The primary replica will not wait for the secondary replica to write incoming Transaction Log records to disc if the secondary replica is configured for asynchronous-commit mode. Take into account that server-level changes or changes that are not written to the SQL Server Transaction Log file, such as Logins, Linked Servers, and SQL Agent Jobs, will not be automatically synchronized between the availability group replicas and will require manual synchronization in the secondary replicas.

For this reason, you must comprehend the significance of the Transaction Log in various SQL Server High Availability and disaster recovery systems.

How to set up SQL Server High Availability using AlwaysOn Availability Groups? 

Except for Clustering, AlwaysON Availability groups, and Replication, the steps to configure HA technologies (Mirroring and Log shipping) are as follows.

Step 1: Take two backups of the source database: one full backup and one T-log backup.

Example: 

Write the following query to take full and T-log backups on the Source (TESTINSTANCE) Server to configure mirroring log shipping for the database ‘TestDB’ in ‘TESTINSTANCE’ as main and ‘DEVINSTANCE’ as secondary SQL Servers.

Connect to SQL Server ‘TESTINSTANCE’, create a new query, and write the following code, as seen in the screenshot.

Backup database TestDB to disk = 'D:testdb_full.bak'
GO
Backup log TestDB to disk = 'D:testdb_log.trn'

Step 2: Transfer the backup files to the new server.

There is no need to copy the following two files because you only have one Physical Server and two SQL Server Instances installed. However, if two SQL Server Instances are installed on different Physical Servers, you must copy the following two files to any location on the Secondary Server where the ‘DEVINSTANCE’ instance is installed.

Step 3: With the ‘norecovery’ option, restore the database from backup files on the Target Server.

Example: 

Open New Query on the ‘DEVINSTANCE’ SQL Server. For Database Mirroring, write the following code to restore the database with the name ‘TestDB,’ which is the same name as the Primary Database (‘TestDB’). However, you can give the Log Shipping Configuration another name. Let’s use the database name ‘TestDB’ in this scenario. For two restores (Full and t-log backup files), use the ‘norecovery‘ option.

Restore database TestDB from disk = 'D:TestDB_full.bak'
with move 'TestDB' to 'D:DATATestDB_DR.mdf',
move 'TestDB_log' to 'D:DATATestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:TestDB_log.trn' with norecovery

Refresh the databases folder on the ‘DEVINSTANCE’ Server to see the restored database ‘TestDB’ with the restoring status as indicated in the screenshot below.

Step 4: Configure the HA (Log shipping, Mirroring) as needed, as seen in the screenshot below.

Example: 

Right-click the primary ‘TESTINSTANCE’ SQL Server database ‘TestDB’ and select Properties. The screen below will appear.

Step 5: Select either ‘Mirroring’ or ‘Transaction Log Shipping‘ from the red color boxes as indicated in the above screen, depending on your needs, and then follow the wizard steps to complete the setting.

How to Design a Disaster Recovery Plan in SQL Server?

During a SQL Server Disaster Recovery, a documented plan is your first line of defense. This will save a lot of guesswork and ensure that Recovery Point Object (RPO) and Recovery Time Objective (RTO) are met. The following is a list of items that must be documented. This isn’t the holy grail, and additional pertinent information can be added as needed.

• Full System Architecture must be noted which means Database and Application Integration Overview.
• Determine the system’s Service Level Agreements (SLAs) and choose an acceptable technology solution.
• Identify and document all involved systems.
• All System Assets, such as Server Discs, Operating Systems, IP Addresses, File Locations, and more, should be documented.
• Logins, Certificates, Job/Schedule Information, and SQL Server Configurations should all be documented.
• All System Stakeholders, such as DBAs, Developers, Network Administrators, and others, should have their contact information documented.
• Document step-by-step instructions for recovering the SQL Server, together with projected timelines, depending on the disaster scenarios that have been discovered.
• Set up a Change Management Mechanism to ensure that any revisions to this DRP document are reflected.
• Finally, run the document through a dry run to confirm that everything is covered.

Best Practices to Follow while Setting SQL Server High Availability 

• Full transactional Log Backup should be in place for all user Databases. If the database you’re working with is quite vast and Full Backup is too massive, Differential Backup can be used.
• A thorough Backup of all system databases should be performed at least once a day or once a week.
• All Database Backups should have a restore verification process/plan in place, and it should be done on a regular basis.
• To avoid Data Loss due to server Hardware Failures or Storage Failures, Database Backups should be relocated to network shares or shared folders rather than being stored on the same Server.

Conclusion

This article teaches you about the SQL Server High Availability concepts. It talks about each of the concepts in-depth behind it. In case you want to export data from a source of your choice such as SQL Server into your desired Database/destination then Hevo Data is the right choice for you! 

Visit our Website to Explore Hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo supports SQL Server both as a Source and Destination. Hevo Data with its strong integration with 150+ sources (including 40+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice, but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about the SQL Server High Availability! Let us know in the comments section below!