Backup & Storage|SQL Server|Windows Server

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.

Sponsored Content

Maximize Value from Microsoft Defender

In this ebook, you’ll learn why Red Canary’s platform and expertise bring you the highest possible value from your Microsoft Defender for Endpoint investment, deployment, or migration.

Log Shipping

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.

Database Mirroring

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.


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (0)

Leave a Reply

Michael Otey is president of TECA, a technical content production, consulting and software development company in Portland,
External Sharing and Guest User Access in Microsoft 365 and Teams

This eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure.

You will learn:

  • Who should be allowed to be invited as a guest?
  • What type of guests should be able to access files in SharePoint and OneDrive?
  • How should guests be offboarded?
  • How should you determine who has access to sensitive information in your environment?

Sponsored by:

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: