In this article, I would like to instruct you how we use Geo-Replication to set High Availability up for Microsoft SQL Azure Database.
The following article gives some basic steps to create a SQL Database in Microsoft Azure
Active Geo-Replication is Azure SQL Database feature that allows you to create readable replicas of your database in the same or different data center (region).
This feature is applied to Database level, not Server level.
1- Login to Microsoft Azure Portal and connect to the database create. In my example, I created the database named as ddazuredemo
2- On Settings panel, click on Geo-Replication. I planned to replicated data from East US region to South Central US region
3- Click on South Central US region. Create Secondary panel appears ==> Click on Target Server. In this step, we create a new secondary database server
Server Name: southcentralusdemo.database.window.net
Server admin login: adminacc
Check on Allow Azure Services access server
Click on Select button
Pricing Tier: Basic, 1GB
Click OK and wait for a few of minutes for initializing deployment
4- Create a new table and insert data into Primary Database Server and then observing how data is replicated to the Secondary server
4.1 Connect to the Primary Server ddazuredemo.database.window.net with SQL Server Management Studio
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
4.2 Insert data into StgSales table
INSERT INTO [dbo].[StgSales]
4.2 Connect to the Secondary Server southcentralus.database.window.net and query data.
4.3 The secondary server is only Read-Only mode so we cannot insert data
With Geo-Replication, we must manually configure Fail-Over through our application or we do it manually in case the Primary Server is crashed or need to be upgraded.
Auto-failover groups is an extension of active Geo-Replication. It is designed to manage the failover of multiple Geo-replicated databases simultaneously using an application initiated failover or by delegating failover to be done by the SQL Database service based on a user defined criteria.
Auto-failover is applied at Server level, different with Geo-Replication is at Database level
1- Connect to the database server ddazuredemo
2- Click on Failover groups —> Add group
Failover group appears
Failover group name: ddfailoverdemo
Secondary serveer: southcentralus
Read/Write failover policy: Automation
Database within this group: ddazuredemo. This is the place where allow us to select what databases we want to auto-failover
After finishing the Auto-failover, we can see there are two connection end-point
Read/write listener endpoint: ddfailoverdemo.database.windows.net
Read-only listener endpoint: ddfailoverdemo.secondary.database.windows.net
When you connect to ddfailoverdemo.database.windows.net, it means we connect to the Primary database server, it can be either in East US database server or South Central US database server. In case, East US database is down, South Central US will be the Primary.
In case, we want to use data for reporting purpose or for load balancing of read-only workloads, we can use Read-only listener endpoint.
3- Connect to ddfailoverdemo.database.windows.net
4- Connect to ddfailoverdemo.secondary.database.windows.net
Because Microsoft Database Azure does not support start/stop server so it’s quite difficult for use to verify Auto-Failover.
Instead, we use Forced Failover on Azure Portal. After forcing Failover, the Primary database server is South Central US
Obviously, the Read/Write listener endpoint and Read-Only listener endpoint connection have no changes. We still work with these endpoint and don’t care which database server is PRIMARY.