SQL Server HA and DR Solutions

The following are the most commonly used HA and DR solutions available in Microsoft SQL Server.

Windows Server Failover Cluster Installation

Commonly known as FCI, this requires SQL Server to be installed as a cluster service on top of the Windows failover cluster.

The SQL Server service is managed by a Windows cluster resource. The example we took to explain HA and DR earlier in this lesson was largely based on this.

This book covers creating a Windows Server Failover Cluster; however, it doesn't cover troubleshooting a failover cluster.

Log Shipping

Log shipping is one of the oldest SQL Server solutions, and is mostly used for DR and SQL Server migration. It takes transaction log backups from the primary server and restores them on one or more secondary servers. It is implemented using SQL Agent jobs.

Log shipping is covered in more detail later in this book.

AlwaysOn Availability Groups

Introduced in SQL Server 2012, AlwaysOn AG is one of the newest and most impressive HA and DR features in SQL Server. When launched, it worked on top of Windows Server Failover Cluster; however, this restriction has been removed in Windows Server 2016 and SQL Server 2016.

AlwaysOn Availability Groups allows you to manually or automatically fail over one or more databases to a secondary instance if the primary instance is unavailable. This book talks about AlwaysOn in detail in a later lesson.

Replication

Replication is one of the oldest SQL Server features that replicates data from one database (commonly known as a publisher) to one or more databases (known as subscribers) in the same or different SQL Server instances.

Replication is commonly used for load balancing read and write workloads. The writes are done on the publisher and reads are done on the subscriber. However, as it replicates data, it is also used as an HA and DR solution.

Hybrid Scenarios

The solutions described here can be used together as well. Using one feature doesn't restrict you from using others. Consider a scenario where a company has a transactional database and logging database. The transactional database is of more importance and has stringent RTO and RPO compared to the logging database. A company can choose AlwaysOn for the transactional database and log shipping/replication for the logging database.

Note

There are other solutions such as database mirroring and third-party solutions. Database mirroring is deprecated and will be removed in future SQL Server versions. This book only talks about SQL Server features and not any third-party HA and DR solutions.

In this section, you have learned about high availability and disaster recovery concepts and terminology.

The next section talks about replication and how it can be used as an HA and DR solution.

Replication is one of the oldest features in SQL Server. It allows you to sync or replicate data from one or more databases on the same or different SQL Server instances. In this section, we will cover replication concepts and terminology. We will also talk about the different types of replications available in SQL Server. We will then cover snapshot replication in detail.