Introduction to SQL Server High Availability

This post is about the introduction to SQL Server high availability. Here we would be talking about the various replication options available in SQL Server using which you can achieve High availability locally, and disaster recovery across sites. With the rise of hybrid cloud deployments, we have got an interesting set of new topologies to achieve HA and DR. Read along to know more about these methodologies to know more.

Introduction to SQL Server High Availability

Microsoft SQL Server provides services to a lot of mission critical applications across verticals. It becomes an important factor to ensure that all the servers are up and running continuously with minimal disruptions.

Mission Critical Requirements

• Zero data loss is critical
  for the business
• Fast application recovery during planned and unplanned downtime
• Redundancy across multiple data-centers.
• Application databases and dependencies failover together.

SQL Server Downtime causes customer revenue loss and decreased staff productivity.

On a high level, “Downtime” can be categorized into two types:

Planned Downtime:

• Server Patching and Service Pack Installation
• Hardware and Software Upgrade
• System Reconfiguration
• Database Maintenance
• Application Upgrade

Unplanned DOWNTIME:

• Human Error is the main “Cause of Failure”
• Site Level Disasters (Network Failure, Natural Disaster etc.)
• Hardware Malfunction
• Data Corruption
• Software Crash (SQL or Operating System or Hypervisor Failure)

With High Availability (HA) and Disaster Recovery (DR) planning, you can:

• Increasing Availability Time.
• Decreasing Downtime for SQL Server Services.
• Improving Manageability for Admins.

Till SQL Server 2008 R2 We had the following HA and DR Options available:

Database Mirroring

Log Shipping

SQL Server Clustering on a Windows Cluster

Replication Services (Snapshot, Merge, Transactional, P2P Transactional)

Database Backups

In SQL Server 2012, Microsoft introduced SQL Server Always On Availability Groups:

With Always On Availability groups, you have the following advantages:

• You can have multiple secondary Replicas of up to 4. You can have 2 Synchronous and 2 Asynchronous replicas.
• You can create an availability group and assign multiple databases which can failover across the secondary replicas.
• Backups can be done on any secondary replicas of a database. As an option, backups on primary replica still works.
• Log backups on all replicas form a single consistent log chain
• Read Intent Queries can be offloaded on the secondary replicas.
• No Application changes required.
• Implement Availability Groups without purchasing expensive SAN’s
• 100% availability during all online operations.

With the release of SQL Server 2014, Microsoft introduced some minor enhancements in Always On Availability groups feature.

SQL Server 2014 Improvements on HA & DR

• Additional secondary replicas of up to 8 (2 Synchronous and 6 Asynchronous)
• Multi-DB failover, manual or automatic
• Improved Deployment & Management wizards
• Availability Groups monitoring dashboard within SSMS & System Center
• Add Azure Replica Wizard for storing replica on an Azure VM.

With SQL Server 2016, Microsoft Removed “Database Mirroring” with an Availability group Standard Edition.

Here are some of the new features in SQL Server 2016 Availability Groups:

• Support for Network load balancing in readable secondaries.
• Increased number of auto-failover targets.
• Enhanced log replication Performance & throughput
• Support for group managed service accounts.
• Support for Distributed Transactions (DTC)
• Basic HA in Standard edition.
• Direct seeding of new database replicas.

You can also use Azure replica Wizard to deploy SQL Server Availability Groups to the Azure Cloud. They have the following benefits:

• Select and deploy to any Azure region (America, Europe, Asia)
• Azure Storage guarantees no data loss.
• Azure Virtual Machines is highly available.

In my next post, I will talk about all the HA and DR technologies in detail.

We hope you like this post. If you have any comments or suggestions, please drop us a comment below in the comments section. Thanks!