Understanding SQL Server’s Disaster Recovery Options
SQL Server is the core database platform for a majority of today’s business-critical applications and it has a number of built-in features that you can use to protect your mission-critical applications and data from disasters and extended outages. Let’s take a closer look at some of SQL Server 2017’s built-in disaster recovery (DR) options.
Backup is and has always been the most fundamental DR technology. SQL Server provides the ability to backup databases using either the SQL Server Management Studio (SSMS), T-SQL BACKUP commands or PowerShell. SQL Server supports full database backups, differential backups as well as log file backups. Most organizations perform a full database backup regularly and then perform differential backups in between full backups and then transaction log backups frequently throughout the day. Transaction log backups give you the ability to restore your database to a point-in-time you choose. SQL Server’s backup supports both compression and encryption.
Log Shipping has been included with SQL Server since the SQL Server 2000 release but you could enact the same functionality on earlier versions using T-SQL scripts and SQL Agent jobs. Log Shipping is supported on both the Standard and Enterprise editions of SQL Server 2017. Log Shipping works by first taking backups of the protected databases, restoring them to the target servers and then periodically running a stored procedure to forward and apply transaction log backups to one or more target servers. Log Shipping allows a user-specified delay between when the primary server backs up the log files and when the target servers apply the log backups. Log shipping does not provide automated failover.
Failover Clustering Instances
Primarily a high availability (HA) technology, AlwaysOn Failover Clustering Instances (FCI) provide server-level protection from unplanned failure with automatic failover and no data loss. Like you might imagine, On Windows Server, AlwaysOn FCI requires a Windows Server Failover Cluster and then SQL Server must be installed on each node using the SQL Server clustered installation option. On Linux you need to user Pacemaker. AlwaysOn FCI is supported on the Standard and Enterprise editions of SQL Server 2017 but it is limited to two nodes on the Standard edition. AlwaysOn FCI can be used for DR by using geo-clustering where the different cluster nodes are in separate physical locations sometimes in completely different regions. Windows Server 2008 and higher supports multi-site clusters.
AlwaysOn Availability Groups and Basic Availability Groups
AlwaysOn Availability Groups (AG) are the premier SQL Server HA and DR technology. AlwaysOn AGs are only provided in the SQL Server Enterprise edition and they provide protection for multiple databases with automatic failover. AlwaysOn AGs require a Windows Failover Cluster on Windows Server or Pacemaker on Linux. AlwaysOn AGs work by taking a backup of the primary database and restoring it to one or more secondary systems and then when the AG is started all of the transactions from the primary database are forwarded to one or more secondary databases. AlwaysOn AGs enable you to have both synchronous secondaries for high availability and asynchronous secondaries for DR.
The SQL Server 2017 Standard edition provides Basic AGs which work exactly like AlwayOn AGs in the Enterprise edition except that they are more restricted. Basic AGs are limited to a single database and a single secondary server target but you can have multiple Basic AGs on a single server. Basic AGs require you to choose between synchronous replication for HA or asynchronous replication for DR.
SQL Server 2017 also includes a technology called Database Mirroring that can provide HA or DR. It is limited to a single database and you can choose between synchronous or asynchronous replication. Unlike AGs, Database Mirroring does not require a Windows Server Failover Cluster or Pacemaker. However, Microsoft has depreciated the Database Mirroring feature which means it will not be included in future versions of SQL Server. Instead, Microsoft recommends the use of AlwaysOn AGs or Basic AGs.