Chapter 5: Working with Disaster Recovery Options

High availability (HA) and disaster recovery (DR) are important solutions for an enterprise strategy concerned with data availability. SQL Server has several different features available for implementing HA and DR scenarios. These will help you increase the availability metric of your SQL Server environment and the applications that are using the data stored on SQL Server.

High availability and disaster recovery are frequently mixed up as many people think they are the same; however, they are not. HA helps you eliminate a single point of failure and provides features and services to keep your environment online, even in case of an incident. DR, on the other hand, is a process you would use to recover from larger accidents. Even if you have a solution for HA in place, this does not mean that you can recover from a disaster or that you have a DR plan.

In this chapter, we'll explore the disaster recovery basics to understand...

Understanding the basics of disaster recovery

Disaster recovery is a set of tools, policies, and procedures that help us while recovering our systems after a disastrous event. DR is just a subset of a more complex discipline called business continuity planning, where more variables come into place and you expect more sophisticated plans on how to recover the business operations. With careful planning, you can minimize the effects of the disaster – however, you must keep in mind that it's nearly impossible to completely avoid disasters. Such a set of tools, policies, and procedures would be used, for example, during natural disasters when your data center or site of operations is compromised. Another great example that's very relevant today is all kinds of cyberattack, where your infrastructure may be compromised.

The main goal of a DR plan is to minimize the downtime of our service and to minimize data loss. To measure these objectives, we use special...

SQL Server options for high availability and disaster recovery

SQL Server has many features that you can put in place to implement a HA/DR solution that will fit your needs. These features include the following:

• Always On Failover Cluster (FCI)
• Always On Availability Groups
• Database mirroring
• Log shipping
• Replication

In many cases, you will combine one or more of these features together since your HA and DR needs will overlap. HA/DR does not have to be limited to just one single feature. In complex scenarios, you'll plan for a primary HA solution and secondary HA solution that will work as your DR solution at the same time.

Always On Failover Cluster

An Always On Failover Cluster (FCI) is an instance-level protection mechanism that is based on the Windows Server Failover Cluster (WSFC) feature. A SQL Server instance will be installed across multiple WSFC nodes, where it will appear in the network...

Configuring replication on SQL Server

In this section, we will focus on SQL Server replication in detail and we'll learn how to configure replication for a database between different servers. Like with many other features, the configuration can be done with SQL Server Management Studio (SSMS) or with Transaction-SQL (T-SQL) code, which sometimes provides greater flexibility. Be aware that replication is one of the features that you can configure immediately during installation so that it's available on your system. If you haven't installed the feature, you can always add replication to your existing SQL Server instance, as follows:

Fig. 5.6 – SQL Server Replication setup

The replication topology includes several roles for servers and offers various scenarios. The primary roles we will be working with are as follows:

• The Publisher: The Publisher is the primary server in the replication topology and hosts the source...

Understanding database mirroring

Database mirroring is a technology used for both HA and DR, which allows for rich configuration. Mirroring is largely replaced by other features of MS SQL Server, mainly Always On Availability Groups. Mirroring should not be considered a primary HA/DR option for new deployments; however, it's still important to understand mirroring, especially because of migrations from older SQL Server installations, which may still use mirroring for HA/DR.

Mirroring uses up to three server roles:

• Principal
• Mirror
• Witness

The principal server holds the database that is mirrored. This database has to be configured with the Full Recovery model in order to participate in the mirroring configuration. The mirror server holds a mirrored copy of the database, but unlike replication or log shipping, this database is not available for user access. The witness server can be configured in case you would like to utilize automatic failover...

Configuring log shipping

Log shipping is a DR technology as there is no automatic failover available with this solution. With a database configured for log shipping, there are several automated jobs that periodically back up the transaction log and restore the log to a different server. This server can be used for many scenarios, such as the following:

• Warm standby for Disaster Recovery
• Reporting offloads

DR configuration is mainly used for a warm standby server. This server has data in near sync – which is not 100% the same as the primary server. One of the advantages of such a warm standby is that you have the option to bring up the server quickly during a disaster. Log shipping has quite a simple architecture, where you have only two main server roles – primary and secondary. With log shipping, you can configure more secondary servers that will host the database for DR scenarios for warm standby or reporting. The same configuration can be achieved...

Summary

In this chapter, we introduced you to the DR discipline and the big picture of business continuity on SQL Server. DR is not only about having backups, but about the ability to bring the service back so that it can perform operations after severe failures.

We have looked at several options that can be used to implement part of DR on SQL Server, such as log shipping, replication, and mirroring. An HA/DR configuration is an essential step in most SQL Server deployments. All three features we discussed in this chapter are still in use today, although mirroring has been retired in many environments and is being replaced with the more flexible Availability Groups, which will be discussed in Chapter 9, Configuring Always On High Availability Features. Throughout this chapter, you have seen the configuration options for all three features, which can help you deploy them in to your environments.

In the next chapter, we'll focus on indexing strategies and introduce various...