Business Continuity with Azure SQL Database

Azure SQL Database has been built from the ground up as a robust highly available database service using a system in which three or more replicas of each database are maintained at all times, with updates committed to at least two replicas before responding.  This HA system addresses local hardware and software failures. In spite of such a high redundancy, it is still susceptible to planned as well as unplanned outages. Microsoft provides an SLA of 99.99% (Four nines) which translates to downtime of 52.56 minutes per year but there can still be unplanned outages like human error where DBA accidently deleted the data while performing an maintenance operation or application upgrade went bad or regional disaster or there can be planned outages where you would like to run database from different location while the current database is under service maintenance or application upgrade.

As discussed earlier, the following business continuity features are available in V12 version of Azure SQL Database.

Table 14: Business Continuity Features in Azure SQL Database

ERT refer to estimated recovery time which is estimated time to restore and recover the database until recovery point.

RPO stands for Recovery Point Objective which measures the amount of data lost by the time the database is restored and brought online.

The combination of geo-restore, standard geo-replication and active geo-replication provides you with a range of options to implement a business continuity solution that meets the needs of your application and business. The following table summarized the Business Continuity scenarios and which solution would useful under each scenario.

Table 15: Business Continuity scenarios

In this chapter we will discuss all the above business continuity solutions available in Azure SQL Database in detail.

Point in Time Restore

A database in Azure SQL Database is continuously backed up within azure data centers using full database backups, incremental and transaction log backup. The Point in time restore allows you to be able to restore the database from the backups to the transaction occurred at the specified point in time. This capability is available in all the service tiers but the restore point depends on the retention policy supported by each service tier. You can go back 7 days with Basic tier, 14 days with Standard tier, and 35 days with Premium tier. Point in time are specifically useful to recover from human error where a user has accidently dropped a table or overwritten a data which need to be recovered. You can perform point in time restore using Azure Portal or PowerShell.

You can follow the steps mentioned in this section to restore a database at a specified point in time

1. Logon to Azure Portal, Click Browse all, Select SQL database, Select the database which you would like to restore and at the top of the database blade click restore as shown in the figure

Figure 32: Perform Point in time restore of Azure SQL Database

2. Specify the database name, point in time and click create to restore the database with a new name at the specified point in time.
3. The restore process can be monitored from the notifications tab on the left.
4. After the database is restored, you can see and connect to the database like any other azure sql database from SSMS and recover the desired data.

You can also restore the database at a specified point in time using Start-AzureSqlDatabaseRestore PowerShell cmdlet.

Geo-Restore

Geo-restore provides the ability to restore a database from a geo-redundant backup to create a new database. The database can be created on any server in any Azure region. Because it uses a geo-redundant backup as its source, it can be used to recover a database even if the database is inaccessible due to an outage. Geo-restore is automatically enabled for all service tiers at no extra cost.

You can follow the steps mentioned in this section to perform a Geo-restore of Azure SQL Database

1. Login to Azure Portal and Click New at the top left corner of the portal
2. Select Data and Storage and then select SQL Database
3. Provide Database Name and Logical Server Name where you would like to restore and select Backup as the source and in the backup blade select the geo-redundant backup which you would like to restore as shown in the figure

Figure 33: Performing a Geo-Restore of Azure SQL Database

4. Specify the rest of the database properties and click Create
5. The database restore process will begin and can be monitored using NOTIFICATIONS tab on the left side of the screen.
6. After the database is restored, you can connect to the database using SSMS by configuring the server-level and database-level firewall rules for your client IP.

You can also perform geo-restore of Azure SQL Database using Start-AzureSqlDatabaseRecovery PowerShell cmdlet.

Standard Geo-Replication

Standard Geo-Replication allows you to configure your database to asynchronously replicate committed transactions from the primary database to the secondary in a predefined Azure region. Standard geo-replication is built on the same technology as active geo-replication but is optimized for applications that use geo-replication only to protect the application from regional failures. The following list shows how standard geo-replication is different from active geo-replication:

1. Only one secondary database can be created in a Microsoft defined  “DR paired” Azure region.
2. The secondary is visible in the master database but cannot be directly connected to until failover is completed (offline secondary).
3. The secondary database is charged at a discounted rate as it is not readable (offline).

Standard Geo-Replication is useful when you want to recover from a regional disaster or during DR drills for compliance.

You can follow the steps mentioned in the section to configure Standard or Active Geo-Replication

1. Login to Azure Portal, Click Browse All, select SQL Database and select the database for which you would like to configure geo-replication.
2. In the database blade, scroll down at the bottom until you see configure Geo-replication and click on it.
3. In the Geo Replication blade, you will see the Azure regions where you could host your secondary along with a recommended region. A recommended region is a preferred region for replication in terms of performance but you can choose the region which you have in mind. If you do not have any preference for Azure location for secondary, you can choose the recommended Azure region.
4. Choosing the region will open up a Create Secondary blade where you can specify the Secondary type as readable or Non-readable. Choosing Non-readable makes it a Standard Geo-replication while readable makes it an Active Geo-replication.
5. Finally you can configure the Server settings where the secondary database will be hosted and click Create to configure Secondary for the geo-replication.

Figure 34: Configuring Standard or Active Geo-Replication

6. Once the Secondary replica is created, you will see it listed under secondaries section in the Geo-replication blade.

In the event of the outage on the primary database when you want to perform failover, you would come to the same Geo-Replication blade as shown in the previous figure and under SECONDARIES section, right click on the row with the name of the database you want to recover to and click Stop to stop and terminate the continuous copy relationship and bring the secondary database online. For a full description on termination of continuous copy relationship, you can refer to the following MSDN article

https://msdn.microsoft.com/library/azure/dn741323.aspx

You can also terminate the continuous copy relationship to stop and failover to secondary using Stop-AzureSqlDatabaseCopy PowerShell cmdlet.

Active Geo-Replication

Active geo-replication, available for Premium databases, provides the richest solution with the least risk of data loss and the most rapid recovery time.  It extends standard geo-replication with up to 4 geo-replicated secondaries that are online and readable at all times, and which can also be used for load balancing or to provide low-latency access to replicated data anywhere in the world. 

Using active geo-replication you can create a continuously replicated copy of a database that can be frozen immediately prior to applying updates or maintenance to the database or an application.  Should any errors be detected during or after the process it is then easy to fallback quickly to this copy.

Tip: While Active Geo-replication is available on in premium service tier. You can still leverage the benefits of active geo-replication for performing application or service upgrade by switching to premium service tier just before the upgrade operation and perform the upgrade with active geo-replication and after the upgrade is completed you can switch back to lower service tier to save cost.

You can configure Active geo-replication using the steps mentioned above when you configure standard geo-replication but with active geo-replication you will configure secondary type as readable and you can have up to 4 geo-replicated secondaries that are online and readable at all times. Similar to Standard geo-replication, when you would like to failover, you would go the Geo-replication blade for that database and right click on the secondary to which you want to failover and click Stop to terminate the continuous copy relationship and bring database online in read-write mode.

Once you have managed the failover process you will want to reconstruct the same pattern of geo-replication relationships you were using previously only with the new production database as the primary to ensure you have the geo-redundancy and load-balancing that your applications and business continuity policies require.

For further information on Active replication for Azure SQL Database, you can refer to the following MSDN article.

https://msdn.microsoft.com/en-us/library/azure/dn741339.aspx