carloscastilla - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

SQL Azure automatic backup keeps it 'business as usual'

Robert Sheldon explains the business continuity features added to SQL Azure, including automatic backup, self-service restore, manual copy, and export and geo-replication. These features are meant to keep the data available, even when the system fails.

When Microsoft launched its new Azure SQL Database service tiers, the company introduced the concept of business continuity. Business continuity comprises a set of features that allows an organization to continue its data-related operations in the event of application errors, data center failures or human blunders.

At the heart of these features are the tools needed to protect against and prepare for disruptions in service. To this end, SQL Database supports automatic backup and self-service restore capabilities, manual copy and export features and geo-replication across regional data centers. These features are mean to ensure customers can continue business operations, even in the event of system failures.

SQL Database automatic backup and self-service restore

For each of the new service tiers (Basic, Standard and Premium), SQL Database automatically backs up every active database hosted by the service. SQL Database creates full backups once a week and differential backups once a day. In addition, the service performs transaction log backups every five minutes.

Microsoft replicates the full and differential backups across regions to ensure availability. However, the retention period, or length of time Microsoft maintains those backups, depends on the service tier: Basic is 7 days, Standard is 14 days and Premium is 35 days.

For any of the three tiers, customers can use the self-service Point in Time Restore feature to recover a database that was backed up within the allotted retention period. The database is restored to the same service tier that was used at the time of the backup and at the minimum performance level available to that tier.

All three new service tiers also support the Geo-Restore feature, the most basic disaster recovery option available in SQL Database. The feature lets you recover a database based on the most recent full and differential backups. One big perk of this feature is that you can restore the database to any specified Azure region. However, since PowerShell doesn't support Geo-Restore, you cannot restore a database to a specific point in time.

Manual copy and export in SQL Database

The new service tiers also provide features that let you manually control your database copy and export operations, which can be useful as part of a comprehensive disaster recovery strategy. For example, you can use Database Copy to create a copy of your database on the same server, on a different one or even in a different region. This feature is handy for migrating data or creating ad hoc copies. Best of all, when the copy operation is complete, the new database is transactionally consistent with the original one.

You can also use the SQL Database Export service to generate a BACPAC file that contains a logical copy of the database schema and its data. The BACPAC file can then be used to restore the database when and where you need to. However, the export service does not ensure transactional consistency in the same way the Database Copy feature does. If the database is modified during the export operation, your final BACPAC file might not be consistent with the source database. Instead, you can first use Database Copy to create a copy and then generate your BACPAC file from that copy. This way, you can identify the point of transactional consistency.

The Automated Export option is another feature for the new data tiers. It lets you schedule regular exports based on your own scheduling needs. This allows you to implement long-range storage strategies that exceed the limitations of the supported backup retention periods. With the Automated Export option, you can schedule backups based on the frequency and retention periods that best meet your organization's requirements for archiving data.

SQL Database Geo-Replication

With the introduction of the new service tiers, SQL Database now also offers the Geo-Replication feature, which comes in two varieties: Standard and Active. Standard Geo-Replication is available to both the Standard and Premium data tiers. It provides a single offline secondary database hosted in a region different from the one where the primary database resides. The feature asynchronously replicates committed transactions from the primary database to the secondary. If the primary database becomes unavailable for any reason, you can terminate the relationship between the primary and secondary databases and activate the secondary. The secondary database will then run independently of the original primary.

Active Geo-Replication is available only to the Premium service tier. This feature lets you implement multiple, readable secondary databases across regions. You can use the secondaries to support database migrations, application upgrades, read-only workloads and disaster recovery strategies. Active Geo-Replication asynchronously replicates committed transactions from the primary database to as many as four secondaries. You can configure all four secondaries as active databases. In other words, you can set up the secondaries as accessible for read-only operations, or configure three to be active and one to be offline. The latter option helps ensure you always have a transactionally consistent backup ready when you need it.

Next Steps

Check out the new Windows Azure SQL Database subscription model, now with service tiers

Follow the evolution of Windows Azure SQL Database from the beginning to today

Take a look at the new Windows Azure integration features in SQL Server 2014

Dig Deeper on SQL Server Backup and Recovery