Share via

Azure SQL Database Backup and Restore Strategy

  • Article

Windows Azure SQL Database requires its own backup and restore strategy because of the environment and tools available. In many ways the risks have been mediated by the database being in the Microsoft data centers. The tools that we have today cover the other risk factors, however better tools are coming to make the job much easier.

For the latest information on SQL Database backup and restore strategy , see Windows Azure SQL Database Backup and Restore article in the Windows Azure SQL Database MSDN library.

Note

If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

Introduction

The purpose of creating database backups is to enable you to recover from data loss caused by administrative errors, application errors, or the total loss of a datacenter. Backing up and restoring data is different in SQL Database than an on-premise SQL Server and must work with the available resources and tools. Therefore, a reliable use of backup and restore for recovery requires a SQL Database backup and restore strategy. This article will highlight the backup and restore tools currently available and how to build a strategy around them.

A backup and restore strategy contains a backup portion and a restore portion. The backup part of the strategy defines the type and frequency of backups, the nature and speed that is required for them, how backups are to be tested, and where and how backup are to be stored (including security considerations). The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet your goals for availability of the database and for minimizing data loss. We recommend that you document your backup and restore procedures and keep a copy of the documentation in your run book.

Designing an effective backup and restore strategy requires careful planning, implementation, and testing. Testing is required. You do not have a backup strategy until you have successfully restored backups in all the combinations that are included in your restore strategy. You must consider a variety of factors. These include the following:

  • The production goals of your organization for the databases, especially the requirements for availability and protection of data from loss.
  • The nature of each of your databases: its size, its usage patterns, the nature of its content, the requirements for its data, and so on.
  • Constraints on resources, such as: hardware, personnel, space for storing backup media, the physical security of the stored media, and so on.

General Considerations

No matter what database you are constructing a backup and restore strategy for certain considerations are common. A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements. You must identify the requirements for the availability of your data in order to choose the appropriate backup and restore strategy. Your overall backup strategy defines the type and frequency of backups and the nature and speed of the hardware required for them.

It is strongly recommended that you test your backup and recovery procedures thoroughly. Testing helps to ensure that you have the required backups to recover from various failures, and that your procedures can be executed smoothly and quickly when a real failure occurs.

Transitioning from SQL Server

If you have a current backup strategy in place for your on-premise SQL Server it may not be directly applicable to your SQL Database backup and restore; they are different environments with different considerations. Just as you need to port your on-premise SQL Server schema and data to SQL Database, you need to port your backup and restore strategy as well. In many ways backing up with SQL Database is much easier than an on-premise SQL Server and Microsoft is making strides to make it even easier.

The customer ultimately is responsible for the schema and data on SQL Database, however Microsoft via the current SQL Database service level agreement has assumed some of the responsibilities of keeping the data highly available. For restores from force majeure and customer errors the customer is responsible for the restoration and taking backups that match their business model.

Infrastructure Backups

Microsoft maintains the underlying infrastructure of SQL Azure in the data center. The customer is not responsible for these common task performed on an on-premise SQL Server such as: backing up operating system, the master database, the transaction logs, or tempdb. In fact, we don’t actually backup the operating environment, it is imaged and machines are reinstalled and added to the data centers weekly from the image.

Besides taking care of the infrastructure backups, Microsoft prevents against data loss from hardware failures – greatly reducing the need for frequent backups.

Hardware Failure

The SQL Azure environment is designed to maintain the server available along with data integrity of your data in case of hardware failure. In other words, your backup and restore strategy doesn’t have to plan for hardware failure of your SQL Databases.

At any one time, we keep three replicas of data running – one primary replica and two secondary replicas. We use a quorum based commit scheme where data is written to the primary and one secondary replica before we consider the transaction committed. If the hardware fails on the primary replica, the SQL Azure fabric detects the failure and fails over to the secondary replica.

In addition to the redundant replicas, the SQL Azure fabric maintains a minimum of 14 days of backups in five minute increments for all the databases in the data center. These backup are stored in the data center as a safe guard against catastrophic software and system failures.

Force Majeure

The current SQL Azure service level agreement specifically has an exception for factors outside Microsoft’s reasonable control, i.e. Force Majeure. Force Majeure is extraordinary event or circumstance beyond the control of the parties, such as a war, strike, riot, crime, or an event (e.g., flooding, earthquake, volcanic eruption). The consequence is that the data center is damaged in such a way that databases can’t be recovered from the replicas or the on-site backups. Currently, SQL Azure doesn’t keep any off site backups; this is the customer’s responsibility.

One option for creating an offsite backup is using SQL Data Sync. With no code, you can configure your SQL Database to be synchronized with one or more SQL Databases in any of the Windows Azure data centers. By doing so, it provides you the ability backup to remote data centers prevent data loss against Force Majeure. Synchronization happens on a fixed schedule, and only moves the changes to the database, after a full move of the data is completed. In this sense it works like a differential backup. You can also use SQL Data Sync to synchronize to an on-premise SQL Server; providing you a local backup. See this blog post for more information about configuring SQL Data Sync service.

We understand that SQL Data Sync service might not be the most effective way to create off site database backup and that other options are needed. We are working on additional tools and features for offsite backups.

One thing to note about using SQL Data Sync is that it can synchronize as often as once every five minutes. If you have an undetected user error (covered later), that error will be synchronized to the backup offsite database and both databases will be corrupted. For this reason you need to adopt a different backup and restore strategy for dealing with user errors

User Error

A common cause of database restores is not from failure or catastrophe; it is caused by user error. Either a mistaken command like DROP DATABASE, schema changes that don’t get executed right (causing data loss), or code that corrupts the data. Creating backups to protect against user error is the responsibility of the customer and needs to be taken into consideration when writing the backup and restore strategy.

SQL Database provides 2 ways to mitigate such scenarios

  1. Database Copies
  2. Import/Export Service

Database Copies

In service update 4 of SQL Database the ability to copy the database was introduced. This feature allows you to copy a running database creating another fully functional SQL Database in the same data center. This is a strategy you can take before making any changes to the database or code calling the database to create a complete backup. One of the nice things about this technique is that the copy is a fully functional database and could be restored quickly. In fact, restoration might be as simple as changing the applications connection string to point to the copy of the database.

The Transact SQL looks like this:

CREATE DATABASE destination_database_name

AS COPY OF[source_server_name.]source_database_name

To copy the Adventure Works database to the same server, I execute this:

CREATE DATABASE [AdvetureWorksBackup] AS COPY OF[AdventureWorksLTAZ2008R2]

This command must be executed when connected to the master database of the destination SQL Database server.

Monitoring the Copy

You can monitor the currently copying database by querying a new dynamic managed view called sys.dm_database_copies.

An example query looks like this:

SELECT * FROM sys.dm_database_copies

Here is my output from the Adventures Works copy above:

Permissions Required For Database Copy

When you copy a database to a different SQL Database server, the exact same login/password executing the command must exist on the source server and destination server. The login must have db_owner permissions on the source server and dbmanager on the destination server. More about permissions can be found in the MSDN article: Copying Databases in Windows Azure SQL Database.

One thing to note is that the server you copy your database to does not have to be in the same service account. In fact you can give or transfer your database to a third party by using this database copy command. As long the user transferring the database has the correct permissions on the destination server and the login/password match you can transfer the database.

Import/Export Service

Post SQL Database Q4 2011 Service Release, a new service was introduced to directly import or export between a SQL Database and Windows Azure BLOB storage. This feature is a free service exposed through the Azure Management Portal and exports all supported database schema objects and table data in a single file package with a extension of .BACPAC.  One point to note is that the .BACPAC file is not equivalent to a Backup as it does not contain Transaction Log and History Data and is not transactionally consistent by itself.

Currently the destination of the Export is fixed to Blob Storage but this is set to be expanded in future. As for the need for automating it, the Service provided in Management Portal is just for On-Demand Exports/Imports and can be automated using the DAC Client Side Tools/Libraries. A detailed explanation of the same can be found at How to Use Data-Tier Application Import and Export with Windows Azure SQL Database.

Doing the Import/Export

Taking a Import/Export is as simple as expanding the Server Node in the Management Portal, choosing the database and clicking on either the Import/Export Button. Export stores the database to Blob Storage and Import restores the database back to SQL Database from Blob Storage. Once a Import/Export Request is submitted, the user is presented with a GUID to track the status of Import/Export

Monitoring the Service

Apart from the ability to store and retrieve from Blob Storage, the Service also provides a Status button to view detailed info on the progress of all submitted and completed Import/Export Requests. The Success or Failure Status of our current request can be seen from this window by checking the status against the GUID reference provided when we submitted the request.

Permissions Required For Import/Export

For doing both the Import/Export, a valid Blob Storage Account is required. When you export a database to Blob Storage, the SQL Login must have View Database permissions on the database being exported and for Import, the Login requires dbmanager permission.

The reason for higher permission for Import is because it creates a database.

  • [[Windows Azure SQL Database TechNet Wiki Articles Index]]