Business Continuity in SQL Azure

A basic requirement for business continuity is the availability of a well-managed data center infrastructure in different geographic locations. If individual data centers are not properly managed, the most robust application designs may become unsuccessful and not effective. With SQL Azure, customers take advantage of the extensive experience Microsoft has in designing, managing and securing world-class data centers in diverse locations around the world. These are the same data centers that run many of the world’s largest online services.

Infrastructure Redundancy

SQL Azure mitigates outages due to failures of individual devices, 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, SQL Azure keeps three replicas of data running – one primary replica and two secondary replicas. SQL Azure uses 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 detects the failure and fails over to the secondary replica. In case of a physical loss of the replica, SQL Azure creates a new replica automatically. Therefore, there are at least two physical transactionally consistent copies of your data in the data center. The following diagram illustrates how SQL Azure keeps three replicas in the physical server racks in the data center.

In addition to the redundant replicas, SQL Azure maintains internal copies of your data for the last 14 days for all your databases within the data center. These copies provide a safe guard against simultaneous or catastrophic hardware and system failures but are not available to customers. We recommend that you implement your own backup and restore solutions as described in the following sections.

• The goal is that failures within the data center will not result in data loss, but they will result in temporary disconnects and transaction failures.
  
  
• Your application should be resilient to the temporary disconnects. We recommend that you implement the 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 Azure article in the TechNet Wiki.
  
  
• We do not provide an SLA for the typical recovery point objective (RPO) and the recovery time objective (RTO) currently. For more information on Windows Azure Service Level Agreements, see Service Level Agreements.
  
  

To make a separate copy of your SQL Azure database, run a Transact-SQL command to create a database copy on a periodic basis and then manage these database copies. Execute the Transact-SQL CREATE DATABASE statement with the AS COPY OF clause to create an independent copy of your database in SQL Azure. Note that a copy operation may take a long period of time, potentially hours, depending on the workload within the data center, how much workload is currently being executed against the original database being copied, and the size of the original database being copied.

The following code example demonstrates how to execute a database copy by using the Transact-SQLCREATE DATABASE (SQL Azure Database) statement:

CREATE DATABASE destination_database_name AS COPY OF [source_server_name].source_database_name

You can create a copy of your database in a different SQL Azure server but the server must be in the same data center. A copy operation might take a long time but the final copy of your destination database will be transactionally consistent with the original database when the copy process completes. For example, if you start a copy process at 14:00 in the 24-hour time notation, SQL Azure will immediately create the new copy based on the system backup taken nearest 14:00. Note that this wouldn’t be longer than 5 minutes prior to 14:00. SQL Azure will replicate transactions committed to the source database to the destination database until the destination database catches up to the source database. Once the destination database catches up, the copy operation is considered complete and the replication link between the source and the destination is severed leaving the new destination database isolated from any future changes to the source database. At this point, the new destination database becomes accessible. As an example, let’s assume that a database copy process takes several hours to complete. The database copy process will mirror the source database at the point in time when the copy operation completes. Therefore, when thinking about the restore as a mechanism to recover from any user error at a point in time, you must know that if the user error occurs while the copy operation is in progress, the error gets replicated to the destination database as well.

Besides enabling a simpler recovery process, such as maintaining the original connection string, copying the database to a different server doesn’t provide you with any additional disaster protection. The SQL Azure server is a logical server and while the source database and the destination database may be grouped under the same logical server, the two databases aren’t necessarily physically located on the same machine. Furthermore, when you consider the two secondary replicas for both the source and the destination databases, the likelihood of physical co-location of the 6 different copies becomes remote. In summary, copying to a separate logical server does not provide you any stronger protection.

After the database copy begins, you can query the sys.databases (SQL Azure Database) and sys.dm_database_copies (SQL Azure Database) views on the master database of the destination server to retrieve more information about the copying progress.

SELECT [databases].[name], [copies].* 
FROM sys.dm_database_copies copies 
JOIN sys.databases databases 
ON copies.database_id = databases.database_id

The frequency at which you choose to copy your database can vary and depends on business needs. To recover from user or application errors, we recommend that you create a daily copy and maintain two or three running copies on a rotating basis by dropping the oldest copy every day after a fresh copy completes.

Note that although we recommend daily copies, you can copy your database more frequently. We recommend that you perform a database copy operation once per hour maximum because each database copy process can finish at different times but each is transactionally consistent with the source database at the point in time when the copy completes. If you start two database copies 5 minutes apart, they may both finish almost at the same time. This creates an identical database copy and you would be billed for the same or similar copy of the data twice.

For more information on the database copy, see Copying Databases in SQL Azure, How to: Back Up Your Database (SQL Azure), and How to: Copy Your Database to a Different Server (SQL Azure).

You can implement restore for your database and its copy on the same server by renaming them. For example, consider a database named “Database1” and a copy “Database1_copy_02_01_2012”. The following Transact-SQL script demonstrates how to swap the database names in the same server when a source database and its copy are called as “Database1” and “Database1_copy_02_01_2012” respectively. After the script runs successfully, new database traffic is directed to the copy rather than to the original database.

ALTER DATABASE Database1 
MODIFY NAME = Database1_OLD
GO
WAITFOR DELAY '00:00:30'
GO
ALTER DATABASE Database1_copy_02_01_2012
MODIFY NAME = Database1
GO

While the service runs the rename process, the existing connections to the original database are killed. Therefore, it’s important to ensure that your application is resilient to connection-losses to SQL Azure and will be able to re-establish the connection when such an event occurs. The Transact-SQL script includes WAITFOR DELAY statement between the two ALTER statements. This helps ensure that the new connections established during the recovery period do not accidentally connect to the old database before renaming is done. If you need to copy your database to a different SQL Azure server, renaming will not work. In such cases, modify your application to point to the database copy on the separate server.

• Database copies might take a long time and are variable in duration.
  
  
• Your bill will include charges for each database copy. But you are charged for the new database copy only after the copy process completes successfully. Each database copy is billed at the same rate as the source database.
  
  
• You are responsible for managing the copies and dropping them when appropriate.
  
  
• Database usage is calculated daily, so if your database copy is active only for one day, you will be charged a prorated fee for one day for your copy database.
  
  
• Restoring to a different logical server requires modifications in connection strings.
  
  
• Restoring to a different logical server doesn’t guarantee a better data-loss protection and improved system performance.
  
  
• While a copy process is in progress, all changes to the original database are replicated to the copy. Therefore, deciding which database copy to restore is an important issue.
  
  
• The SQL Azure Database November 2011 release introduced Federations in SQL Azure (SQL Azure Database). At this point you cannot copy a database that contains federations by using the database copy operation. Conversely, creating a federation fails if a database copy operation is active in the database. Database copy cannot be performed on federation members either.
  
  
• The recovery time objective (RTO) should be equal to “time to recognize the error + time to rename the database + time interval between the two ALTER statements.
  
  

With this approach, you have to create a storage account in a data center in the same region but separate from the data center that you have your SQL Azure account. For example, if your application and database run in the North Europe data center, we recommend that you maintain a copy of your database or BACPAC in another data center in the same region, such as West Europe. For more protection, you can also maintain a copy of your database or BACPAC in another data center in a different region, for example South Central US.

Once the DAC export operation is complete, you can immediately use the DAC import operation to recreate the database and data on another SQL Azure server in the same or different data center. Then, you can delete the used Windows Azure Blobs to reduce costs. Importing into SQL Azure at the targeted data center validates the operation and reduces the overall duration of recovery. In this case, you will get charged for the database. Alternatively, you can defer the import operation until the actual failover completes. In this case, Microsoft charges you for the Blob storage. Note that you would still need to perform periodic imports into the database in each backup data center to validate the operation and test the disaster recovery procedure. Note that you also will be charged for the bandwidth cost of copying the BACPAC to one or more data centers.

The following diagram illustrates an enhanced data protection. The diagram demonstrates performing a database copy in the data center A, and then exporting it to the Windows Azure Storage in the data center B. Next, the diagram shows importing the BACPAC to recreate the database in data center B and copying the BACPAC to the data center C. Finally, it shows importing the BACPAC to recreate the database in data center C.

If the data center A fails, the recovery point objective (RPO) or potential data loss in your application will be determined by how often you perform export. For example, if you export data once a day, your data loss will be 24 hours of data. The recovery time objective (RTO) of the database itself will be minimal if you perform an immediate database import. If you defer the import, the RTO will be determined by the size of the BACPAC and the time it takes to import into the new database.

With this approach, you need to have a storage account in the same data center where you have your SQL Azure account. This recovery solution relies on the automatic geo-replication of Windows Azure storage objects, such as Windows Azure Blobs and Tables, to another data center in the same region, at no additional cost. For example, if your storage account is in the North Europe data center, your Windows Azure storage objects are automatically replicated to the second data center in the same region, such as West Europe.

With this approach, you do not have an option of immediately importing the BACPAC to the new database in the second data center. This is because the decision to failover is made by Windows Azure. Therefore, the geo-replicated BACPAC can be accessible only after Windows Azure executes the failover operation. You will be billed for the usage of the Windows Azure storage account but there is no bandwidth charge because the BACPAC copy is done within the same data center and the cost of geo-replication is included in the storage cost. It is important to note that the Import/Export service produces the BACPAC using the Block Blobs, which helps to preserve its integrity by replicating the entire BACPAC.

The following diagram illustrates copying a database in data center A, and then exporting it to the Windows Azure storage in the same data center. Next, the diagram shows that Windows Azure performs geo-replication of Windows Azure storage objects from the data center A to data center B automatically. Finally, the diagram illustrates importing the BACPAC to recreate the database in the data center B.

With this approach, the typical recovery point objective (RPO) should be equal to “archiving interval time x 2”. If the failure occurs right after the archive is created, the Blob data may be lost. In that case, only the previous archive will be available. The typical recovery time objective (RTO) should be equal to “24 hours + time to import”. Although the second option can take longer than then first option, it provides reduction in storage costs.

Warning

Windows Azure Blobs and Tables are geo-replicated between two data centers 100s of miles apart from each other on the same continent, to provide additional data durability in the case of a major disaster, at no additional cost. For more information, see Introducing Geo-replication for Windows Azure Storage blog post. With this first release of geo-replication, we do not provide an SLA for how long it might take to asynchronously geo-replicate the data, though transactions are typically geo-replicated within a few minutes after they have been committed in the primary location and the estimated time that the data will be accessible to customers after a disaster is 24 hours.

In addition, you can also download the BACPAC from your storage account to a local client machine and then import the archive into a local SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, or SQL Server 2005 instance by using the Import/Export functionality provided by provided by SQL Server Management Studio available in SQL Server 2012. Importing the archive to a local instance of SQL Server will help you ensure that you maintain a local copy of your data without needing an internet connection or the availability of Windows Azure data centers. To import an archive file to a local instance of SQL Server, download the SQL Server 2012 product here and install the Management Tools. For more information, see Install SQL Server 2012 in the SQL Server Books Online. After the Microsoft SQL Server 2012 Management Tools is installed, download the archive export file from your storage account to your local client. Once the download is complete, connect to the target database by using SQL Server Management Studio. In the Object Explorer, right-click on the server and select Import Data-tier Application… and then follow the steps to import the archive export file.

• These options require a Windows Azure Storage account.
  
  
• Your application should manage failover events.
  
  
• Your bill will include charges for each database copy.
  
  
• Your bill will include charges for offline archives stored in your Windows Azure Storage account.
  
  
• You are responsible for managing the database copies and dropping them when appropriate.
  
  
• You might need an on-premise SQL Server instance.
  
  
• After failover, you might lose some data.
  
  
• The SQL Azure Database November 2011 release introduced Federations in SQL Azure (SQL Azure Database). To implement a recovery strategy for your federated data, you can either write your own custom export scripts using the bulk copy utility (BCP.exe) or System.Data.SqlClient.SqlBulkCopy class or use the SQL Azure Migration Wizard on Codeplex currently.
  
  

  Warning
  The SQL Azure Migration Wizard tool is built by the community and not supported.