Creating a replica ensures you always have another copy of your database with the required server to present it. On its own, this protects your data; however, if a failure of a region occurs, failover to the replica is not automatic. For automatic failover between your databases, you need a failover group.

The following describes the critical differences between replication and failover groups:

• Active geo-replication: Geo-replication is a business continuity feature that allows you to replicate the primary database and up to four read-only secondary databases in the same or different Azure regions. You can use the secondary databases to query data or for failover scenarios when there is a data center outage. Active geo-replication has to be set up by the user or the application manually.
• Failover groups: Failover groups are a feature that automatically manages the failover for the database. They automatically manage the geo-replication relationship between the databases, the failover at scale, and the connectivity. The primary and secondary databases need to be created within the same Azure subscription to use failover groups. However, they must be in different regions.

When you create a failover group or a replica in the Azure portal, you have visibility of two separate SQL servers and SQL databases; a failover group simply connects them. You can still connect to either of the original connection strings for either database, but if you do, any failover will result in your application losing connectivity.

Therefore, when using failover groups, you are provided with an additional two connection strings in the failover blade – read/write and read-only, as in the following screenshot:

Figure 14.6 – SQL failover groups

By changing your connection string to the read/write listener endpoint, such as packtsqlfailover.database.windows.net in this example, in the event of a failover, your application will automatically be directed to the active server.

The read-only listener is useful for spreading a load across servers when you only need to read from your database. In this way, when combined with frontend user interfaces spread across regions, you can achieve faster response times by directing users to their closest replica.

Failover will occur automatically in the failure of the primary server, but you can also trigger a failover yourself.

Understanding the nuances between just having a replica or having a failover group depends on your requirements. The following table shows the main differences between the two options and why you might choose one over the other:

As we can see from the preceding table, geo-replication is therefore useful when you want multiple replicas either within a region or across regions, which in itself makes them ideal when you need reliable infrastructure. You want to leverage options such as read scale-out.

Important note

When dealing with data replication for DR purposes, we need to understand the term Recovery Point Object (RPO). RPO states the point in time when you can be confident your data is kept in sync. For example, an RPO of 5 seconds means there could be a 5-second lag between your data in the primary replica and the secondary. In Azure SQL, the Business Critical tier has a guaranteed 5-second RPO. The other tiers have no such guarantee, meaning there could be a more significant time difference between the replicas. This may be an essential factor for applications that must be kept consistent at all times.

Whereas failover groups are best when you need automatic switching from a primary replica to another, geo-replication is best when you need multiple replicas – for example, when using them for read scale-out for performance reasons.

You can see a complete walk-through of setting up an Azure SQL failover database by following the tutorial in Implementing Microsoft Azure Architect Technologies: AZ-303 Exam Prep and Beyond also by Packt Publishing.

Next, we will examine the options for another commonly used database in Azure, Cosmos DB.