Azure SQL Database Business Continuity
Updated: June 12, 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 section below gives you a quick overview of Microsoft Azure SQL Database features and functionality that can be used to protect your data:
Point in time Restore within the past 7 days.
Point in Time Restore within the past 14 days.
Point in Time Restore within the last 35 days.
Database Copy + Manual Export
Database Copy + Manual Export
|Geo-Replication with passive replica is currently not available. Also restoring a database to a different region is currently not supported.|
- Point in Time Restore
- Although Microsoft Azure SQL Database has built in protection for failures of individual machines and devices, you still need to protect against accidental changes to your data. Microsoft Azure SQL Database creates backups of your data, and gives you the ability to recover your data from unwanted deletions or modifications. The backup retention period varies between the different service tiers: 35 days for Premium, 14 days for Standard, and 7 days for Basic. For more information, see Azure SQL Database Backup and Restore.
- Active Geo-Replication
- With Active Geo-Replication, you can create and maintain up to four readable secondary databases across geographic regions. All transactions applied to the primary database are replicated to each of the secondary databases. The secondary databases can be used for read workloads, database migration, and protection against data loss during application upgrade as a failback option. For more information, see Active Geo-Replication for Azure SQL Database.
- Database Copy
- Database Copy allows you to create a copy of a database either on the same server or on a different server but within the same region. 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, and with Basic and Standard databases to provide a disaster recovery solution. These backups can be imported to restore the databases in case of the accidental data changes or for archiving purposes. Since the Export service does not guarantee the transactional consistency of the database you may want to create the database copy first and then export the data from it. This way you will guarantee the 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).
For more information on the different service tiers, see Azure SQL Database Service Tiers (Editions).
Business continuity can be affected by one of the following three major categories of issues:
Failure of individual servers, devices or network connectivity
Corruption, unwanted modification or deletion of data
Widespread loss of data center facilities
Protection from Failure of Individual Servers and Devices
Windows Azure Database has a built-in high availability subsystem that protects your database from failures of individual servers and devices. By storing your data in Microsoft 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 covers the things Microsoft Azure SQL Database does for you without any additional expense.
High Availability through Infrastructure Redundancy
Microsoft 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, Microsoft Azure SQL Database keeps three database replicas running—one primary replica and two or more secondary replicas. Microsoft Azure SQL Database uses a quorum based commit scheme where data is written to the primary and one secondary replica before the transaction is considered committed. If the hardware fails on the primary replica, the Microsoft Azure SQL Database detects the failure and fails over to the secondary replica. In case of a physical loss of a replica, Microsoft Azure SQL Database creates a new replica automatically. Therefore, there are always at least two physical transactionally consistent copies of your data in the data center. The following diagram illustrates how Microsoft Azure SQL Database keeps three replicas in the physical server racks in the data center. This behavior is common to all Microsoft Azure SQL Database service tiers.
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 the connection-loss errors, see Connection Management in SQL Database article in the TechNet Wiki.
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.
Microsoft Azure SQL Database has automatic backup enabled for all Basic, Standard, and Premium databases. The backup service creates periodic databases backups and transaction log backups. The backups of Premium databases are retained for 35 days, Standard databases for 7 days, and Basic databases for 24 hours. The Point in Time Restore service allows the users to revert an accidental change by restoring the database from the backup. Premium and Standard databases can be restored to any point in time within the retention period. Basic databases can be restored to the most recent daily backup –protects against accidental deleted of the database.
|For an overview of the backup and restore options provided by Microsoft Azure SQL Database, see Azure SQL Database Backup and Restore.|
How to Protect against Widespread Loss of Data Center Facilities
For Microsoft Azure SQL Database Premium databases, Active Geo-Replication can be used to create geographically redundant replicas of the source databases. By creating the secondary databases on a different region than the source database, you are protected from a disaster occurring in the primary region. In addition these active 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 the databases not in the Premium service tier, you can create a database copy, and then export a BACPAC file from the copy. Delete the database copy once you are done with the export to avoid billing charges for the copy.
You can use the automated export option on the management portal to schedule regular exports for Web and Business service tiers. To ensure trasacational consistency, the automate 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 deleted automatically.
|Business and Web service tiers will be retired in 12 months from April 24, 2014. For more information, see Web and Business Edition Sunset FAQ.|