SALES: 1-800-867-1380

Azure SQL Database Business Continuity

Updated: July 31, 2014

Business Continuity addresses the ability of an organization to continue business operations when a crisis or disaster occurs. Business Continuity planning requires processes, procedures, and measures to be put in place by an organization to ensure that business operations can continue without interruption. This article does not cover all aspects of business continuity planning, but focuses on data protection and recovery. It describes Microsoft Azure SQL Database technologies that enable high availability and recoverability.

The following section gives you a quick overview of Azure SQL Database features and functionality that can be used to protect your data:

 

  Basic Standard Premium

Database Recovery

Point in Time Restore within the last 7 days

Geo-Restore

Point in Time Restore within the last 14 days

Geo-Restore

Database Copy + Manual Export

Automated Export (Preview)

Standard Geo-Replication

Point in Time Restore within the last 35 days

Geo-Restore

Database Copy + Manual Export

Automated Export (Preview)

Disaster Recovery

Database Copy + Manual Export

Automated Export (Preview)

 

Standard Geo-Replication

Active Geo-Replication

Terms and Definitions

Point in Time Restore
Although Azure SQL Database has built-in protection for failures of individual machines and devices, you still need to protect against data corruption or unintended modifications and deletions. Database backups are automatically created for you. The backup retention period varies between the different service tiers: 7 days for Basic, 14 days for Standard, and 35 days for Premium. For more information, see Azure SQL Database Backup and Restore.

Geo-Restore
This is the most basic disaster recovery option. It is available on Basic, Standard, and Premium service tiers. In the event of a datacenter failure affecting the availability of your primary database, Geo-Restore allows you to recover your database using the last available daily backup. There are no charges for the additional backups that are stored, but if you use Geo-Restore, you will be charged for the restored database at normal rates once the restore is complete. For more information, see Azure SQL Database Backup and Restore.

Geo-Replication
With Active Geo-Replication, you can create and maintain up to four secondary databases across geographic regions. All transactions applied to the primary database are replicated to each of the secondary databases.

Geo-Replication has two options:

  • Standard Geo-Replication: A single offline secondary database is created in a different predetermined region than your primary database. The secondary database becomes available to client connections only when the datacenter hosting the primary database fails.

    ImportantImportant
    The target region is fixed based on the region of your primary database. For a list of the paired regions, see Standard Geo-Replication for Azure SQL Database.

  • Active Geo-Replication : Multiple readable secondary databases can be created across regions. These secondary databases can be used for read workloads, database migration, and protection against data loss during application upgrade as a failback option. With Active Geo-Replication, you can select the region for the secondary database as well as control when to failover to these secondary databases. You also have the flexibility to create up to a maximum of four readable secondaries or one offline secondary and up to three readable secondaries. Active readable secondaries are charged at the same service tier and performance level as your primary database. For more information, see Active Geo-Replication for Azure SQL Database.

Both offline and active secondaries count toward the total number of databases per server. There are cost implications as well. For more information on pricing, see SQL Database Pricing Details

Database Copy
Database Copy allows you to create a copy of a database either on the same or different servers in the same or different regions. The copy is transactionally consistent with the source when the database copy operation is complete. It is however a one-time copy, and not continuously replicated from the source like Active Geo-Replication. The feature can be used for data migration or ad hoc copy needs. For more information, see Copying Databases in Azure SQL Database.

Import and Export Service
The Azure SQL Database Export service can be used to export a BACPAC file from a database containing a logical copy of data and schema of the database. Export can be used for Web and Business databases which do not support Point in Time Restore, as well as with Basic, Standard, and Premium databases. These backups can be used to restore the databases as well as for archiving purposes. The Export service does not guarantee transactional consistency of the copy if the original database is changed during the export process. To work around this, consider creating a database copy first and then exporting from the copy. This way you will guarantee transactional consistency without impacting the availability of the original database.

Automated Export: The Automated Export option is best suited for business continuity scenarios like long term storage of daily backups beyond the supported backup retention period. You can schedule regular exports by using the Automated Export option. For more information, see How to: Import and Export a Database (Azure SQL Database).

Business Continuity Strategies – Data Protection and Recovery

Business continuity can be affected by one or more of the following three major categories of issues:

Protection from Failure of Individual Servers and Devices

Azure SQL Database has a built-in high availability subsystem that protects your database from failures of individual servers and devices. By storing your data in Azure SQL Database, you are taking advantage of many fault tolerance and secure infrastructure capabilities that you would otherwise have to design, acquire, implement and manage. This section gives you an overview of the built-in features that are available at no additional expense.

High Availability through Infrastructure Redundancy

Azure SQL Database mitigates outages due to failures of individual server components, such as hard drives, network interface adapters, or even entire servers. Data durability and fault tolerance is enhanced by maintaining multiple copies of all data in different physical nodes located across fully independent physical sub-systems such as server racks and network routers. At any one time, three database replicas are running—one primary replica and two or more secondary replicas. Data is written to the primary and one secondary replica using a quorum based commit scheme before the transaction is considered committed. If the hardware fails on the primary replica, the Azure SQL Database detects the failure and fails over to the secondary replica. In case of a physical loss of a replica, a new replica is automatically created. Therefore, there are always at minimum two physical transactionally consistent copies of your data in the datacenter. The following diagram illustrates how three replicas are kept in the physical server racks in the datacenter. This behavior is common to all Azure SQL Database service tiers.

Physical server racks in a data center

How to Protect against Temporary Disconnects

You should design your application to be resilient to temporary disconnects. We recommend that you implement retry logic in your application to prevent connection losses. For more information on how to handle connection loss errors, see Connection Management in SQL Database.

How to Protect against Unwanted Deletions or Modifications

Users or applications may make unwanted or unintended changes to data. The ability to restore application data in the event of a corruption or unwanted modification or deletion is a fundamental requirement for software applications. The Point in Time Restore service supports this business requirement.

noteNote
For an overview of the backup and restore options provided by Azure SQL Database, see Azure SQL Database Backup and Restore.

How to Protect against Widespread Loss of Datacenter Facilities

Business Continuity planning should include the possibility of datacenter failures. The Geo-Restore feature provides basic disaster recovery protection by providing the ability to restore a database to a server in a different region using the backups that are automatically created for you by Azure SQL Database.

In addition to the basic option, databases in the Standard and Premium service tier can be used to create geographically redundant secondaries using Geo-Replication. By creating secondary databases in a different region than the source database, you are protected from a prolonged datacenter outage. When using Active Geo-Replication, the secondary databases can be used for read workloads. Both the source and the secondary databases have the built-in high availability, thus supporting a broader business continuity strategy. For an overview of Active Geo-Replication and how to set it up, see Active Geo-Replication for Azure SQL Database and Configure Active Geo-Replication (Continuous Copy). For an overview of Standard Geo-Replication and how to set it up see <link to new topic>

Another option when planning Business Continuity is to create a database copy, and then export a BACPAC file from the copy. Remember to delete the database copy once you are done with the export to minimize billing charges for the copy.

Lastly, you can use the Automated Export option to schedule regular exports of your database. To ensure transactional consistency, the Automated Export option creates a database copy first and then exports the BACPAC file from the copy. Once the export is complete, the database copy is automatically deleted.

WarningWarning
Business and Web Editions will be retired in 12 months from April 24, 2014. For more information, see Web and Business Edition Sunset FAQ.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft