Azure SQL Geo-Replication and Failover Groups

Azure SQL offers different types of business continuity solutions. One of these solutions is Geo-Replication that provides an asynchronous database copy. You can store this copy in the same or different data center locations (regions). There can be four readable database copies. In the documentation of Microsoft notes, the recovery point objective (RPO is the maximum acceptable amount of data loss measured in time) is less than 5 seconds. If we want to automate and make (users will not affect) failover mechanism transparent, we have to create the auto-failover group.

At this point, we need to pay attention that when the primary database is down, we are at risk of losing data, because data transfer works asynchronously to a secondary database. In the asynchronous replication, transactions are committed on the primary server and then replicated to the secondary database. If you need to transfer data synchronously between the primary and secondary databases, Microsoft recommends a solution for this case, the sp_wait_for_database_copy_sync stored procedure. We have to run it after the transaction is committed in the primary database. At the same time, the application waits until all the committed transactions are replicated and acknowledged by the active secondary database.

Automatic Failover

The image below shows the Read/Write grace period (hours) parameter that defines how long the system waits before initiating the failover that is likely to result in the data loss.

Once these steps are performed, the Read/write listener endpoint and Read-only listener endpoint addresses appear. The listener address is not changed after the failover. For this reason, you have to use this listener address for application connection strings.

If you have a read-only workloadMicrosoft recommends using the ApplicationIntent=ReadOnly parameter.

When we need the automatic failover, our application has to connect to a new primary server. For this reason, we will create a contained user.

To do this, run the following query. In addition, it will assign the db_owner role to a user.

1

2

3

CREATE USER CodingSightUser WITH PASSWORD='xxxxxxxx';

ALTER ROLE db_owner ADD MEMBER CodingSightUser;

Note that the contained user will be automatically created in the secondary database. Therefore, we will avoid the login dependency at the database level.

Now, we can connect to the failover group listener endpoint.

Another best practice about Geo-Replication is to apply firewall settings to the database level because this will not affect the secondary server.

To create the database level firewall rule, execute the following query:

The query below will help you to delete the database level firewall rule.

1

EXECUTE sp_delete_database_firewall_ruleN' Example DB Rule2'

To list database rules, use the following query:

1

select *from sys.database_firewall_rules

When we run this query in the primary database, it will return some information about all the replication databases and server information.

Now, we will explore how to trigger the manual failover in the Azure portal. First, select Failover groups.

Then, click Failover.

As you can see, the geo-replication line changes to the dashed one.

Conclusions

In this article, we explored Azure SQL Geo-Replication and Failover groups. In my opinion, it is an advantage when you have an opportunity to easily configure up to four secondary replicas. It is great to be able to scale out your read-only workload on the Azure.

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.