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.

Read our blog to know why and how you should use SQL Server for data warehouse.

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 solution for less critical databases, offering customizable recovery points and times. It uses one primary database server and one or more secondary servers for reporting.
• Log Shipping requires the database to be in Full Recovery mode and works by syncing transaction log backups from the primary database to the secondary servers.
• The process is automated through three SQL Server Agent jobs: one to take transaction log backups, one to copy them to secondary servers, and one to restore the backups to the secondary databases.
• Secondary databases remain in restoring or standby mode, waiting for the next transaction log backup, and are not available for online use.
• Failover between the primary and secondary servers must be done manually using a T-SQL query.

2) Database Mirroring

• SQL Server Database Mirroring is a high-availability solution for databases with full recovery and adjustable settings. It involves a primary server (Principal Server), a secondary server (Mirror Server), and optionally a third server (Witness Server) to monitor connection and perform automatic failovers.
• Database Mirroring has two synchronization modes:
  1. High-safety (Synchronous Mode) ensures transactions are committed on both the principal and mirror databases, increasing latency but improving data safety.
  2. High-performance (Asynchronous Mode) commits transactions on the principal database without waiting for the mirror, reducing latency but increasing the risk of data loss.
• To start Database Mirroring, restore a full backup and transaction log backup from the principal database to the mirror server. Then, synchronize the mirror database by transferring and applying transaction log records from the principal.

3) Data Replication

• SQL Server Transactional Replication is a real-time, database-level high-availability solution. It involves a primary server (Publisher) that distributes tables (Articles) to one or more secondary servers (Subscribers), which can also be used for reporting.
• Transactional Replication relies on synchronization with SQL Server Transaction Logs to keep data consistent across the publisher and subscribers.

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 based on Windows Server Failover Clustering. It consists of multiple cluster nodes that share hardware and software to ensure SQL Server availability.
• When configured, SQL Server services and resource groups (including shared storage, network names, and virtual IPs) are owned by one cluster node at a time. If the active node fails or undergoes maintenance, the resource group ownership and SQL Server instance are moved to another node.
• The SQL Server transaction log does not play a role in Always On Clustering, as it operates at the instance level, meaning no synchronization of changes between nodes. The database is available on only one cluster node at a time.

5) AlwaysOn Availability Group (AG)

• An Availability Group is a collection of user databases that failover together, meaning a database failure (e.g., corruption or data loss) does not cause the entire group to failover. Other databases in the group remain unaffected.
• An Availability Group Listener simplifies connections by providing a virtual network name with a unique DNS, multiple IP addresses, and a TCP port. It routes read-write workloads to the primary replica and read-only workloads to secondary replicas.
• Data synchronization between primary and secondary replicas is based on the SQL Server Transaction Log, with logs sent from the primary to secondary replicas for processing.
• In asynchronous-commit mode, the primary replica does not wait for the secondary replica to write logs to disk, which can reduce latency but increase the risk of data loss.
• Server-level changes, such as logins and SQL Agent jobs, are not automatically synchronized and require manual updates on secondary replicas. This highlights the importance of the Transaction Log in 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! 
Hevo, a No-code Data Pipeline, supports SQL Server both as a Source and Destination. Hevo Data with its strong integration with 150+ sources (including 60+ 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!

FAQs