Managing Azure SQL Database Recovery

Azure SQL Database offers a number of benefits, built into the underlying cloud infrastructure, that leverage resiliency and redundancy. You can take advantage of this functionality in order to facilitate failover and failback in response to events that affect availability of an Azure region. In this article, we will step through a procedure illustrating this capability.

Each of the pricing tiers of Azure SQL Database supports Active Geo-Replication, which allows you to create up to four readable replicas in any of the Azure regions and which replicate asynchronously from one writable instance in a primary region. You can configure it by using the Azure portal as well as by Azure PowerShell and T-SQL. We will focus here on the first of these methods. For others, you can refer to the Microsoft Azure online documentation.

Start by navigating to the Azure portal and signing in with either a Microsoft account or work or school account to your subscription (note that the primary and secondary databases must belong to the same Azure subscription). From the Role Based Access Control perspective, your account should be granted the owner role on the subscription level in order to carry out the procedure described below.

Once you have successfully authenticated, navigate to the blade displaying your Azure SQL Database instance; from its blade, click the Geo-Replication entry. This will display the Create secondary blade, from which you need to specify the secondary region and secondary type (ensure its value is set to Readable if you want to provide read access to the secondary replica during normal business operations) as well as either create new or use an existing target server. Another server is necessary since the scope of an individual server is a single Azure region. The target database name will match the one assigned to the primary instance. Similarly, the secondary database must have the matching service tier, so there are obvious pricing implications of enabling this configuration. However, you have the option of making the secondary part of an elastic pool, even if the primary is set up as a standalone. In this case, a pool must already exist on the server in the secondary region. Once you click Create, the seeding process will begin. You can determine its completion by monitoring the status of the secondary database, which should at that point change to Readable.

In case of a planned or unplanned outage, you can initiate a failover directly from the Azure portal as well. To accomplish this, navigate to the blade of the primary Azure SQL Database instance, click Geo-Replication, on the Geo-Replication blade; in the list of secondaries, select the one you want to become the new primary, and click Failover. The failover should complete within 30 seconds, although note that it is expected that both databases might be unavailable during this short period of time. The failover will automatically update the relationship with any of the remaining secondaries, in order to take into account the change of replication source. In addition, keep in mind that since replication is asynchronous, there is a potential for data loss if the former primary has not committed all pending transactions before the failover took place.

The process is very straightforward and is capable of satisfying Recovery Point Objective (RPO) and Recovery Time Objective (RPO) requirements of the majority of customers. According to the information published by Microsoft, across all pricing tiers, their values should not exceed, respectively, 5 seconds and 30 seconds. However, there might be some additional provisions that you need to take into account regarding security and application access following a failover.

In regard to security, the specifics depend on whether you rely on server-level logins to define database-level users. This is not the case with contained users, which makes the database fully portable, eliminating the need for any extra configuration. More traditional arrangements, in which database users are associated with server logins, require creating matching logins on logical servers hosting secondary replicas. To accomplish this, you need to perform the following sequence of steps:

  • Run a T-SQL query to identify login names and the corresponding SID values on the server hosting the primary SQL Database instance.
  • Run a T-SQL query to identify database user principals and the corresponding SID values on the primary SQL Database instance.
  • Based on matching values of the SID property returned by the queries executed in the first and the second step, identify server logins associated with the user principals in the primary database.
  • Run the CREATE LOGIN T-SQL statement on each logical server hosting the secondary SQL Database instance for every login you identified in the third step.

There are additional, application specific considerations that you should take into account when planning for disaster recovery that relies on Azure SQL Database failover capabilities. We will explore them in one of upcoming articles published on this forum.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles