Ranga Babu
Geo-Replication

Azure SQL database Geo-Replication

July 15, 2019 by

In this article, we will review how to set up Geo-Replication on Azure SQL databases. Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server. We can also use this feature to migrate a database from one server to another server in the same or cross region with minimal downtime. Geo-replication uses the Always-on feature to replicate committed transactions to the secondary database asynchronously.

Let us go step by step to configure Geo-Replication on an Azure database. In this demo, I am using Azure single database.

Configuring the Geo-Replication on Azure SQL database

Log in to the Azure portal and navigate to SQL databases. Click on the Azure SQL database on which you are going to configure the Geo-Replication.

Azure single SQL database

Click on the Geo-Replication tab in the database details page.

Geo-Replication

Select the target region from the list of target regions shown. In this case, the primary database is located in West India and I selected the target region (secondary database region) as South India.

Click on the Target Server. All the available Azure database servers in the target region are shown in the list. Select one if you want to use an existing Azure database server or create a new Azure database server by clicking on Create a new Server. Enter the Azure SQL server name, admin login, and password. Click on Select and Ok.

secondary server in geo replication

The Geo-Replication setup is progressing, and you can see it on the map. It shows a dotted line as shown in the below image when the setup is in progress. Once the setup is complete it shows a full line between the source and target regions.

map representation of geo replication

In the Geo-Replication page under the map, we can see the details of primary and secondary databases and the status of the database.

primary and secondary database in geo replication

We can also create Geo-Replication using below T-SQL script. The following T-SQL script should be executed on the master database at the Azure SQL primary server. Replace the database name and the secondary server name in the T-SQL script. If the database with the same name already exists on the secondary server, the below T-SQL script throws an error “The destination database name ‘ActiveGeoRepl’ already exists on the server

We can create up to four secondaries for each primary database. As the transactions are replicated asynchronously to the secondary database may lag the primary database at any point in time.

Geo-Replication supports only manual failover and the end-point connection must be changed in the application after the failover. The secondary must have the same firewall rules and the logins to run applications successfully without any discrepancies after the failover to the Azure SQL secondary database.

Failover

Execute the following T-SQL script for a planned failover which makes the secondary database online without data loss. This T-SQL script should be executed at the secondary server on the master database.

Once the above command is executed on the secondary server, it stops all the connections to the existing primary database, replicate all the transactions to the secondary database and make the secondary database online.

Forced Failover

For a forced failover, click on the secondary database to which you want failover and click on Forced Failover. This will make your secondary database immediately online and start accepting connections. Forced failover may result in data loss.

Azure SQL Geo-Replication forced failover

Alternatively, we can use the following T-SQL script on the master database at the secondary server for a forced failover which immediately brings the secondary database online.

Removing Geo-Replication

To remove Geo-replication on an Azure database, navigate to the database details page and click on Geo-Replication. Just under the map locate the secondary server and click on Stop Replication. Please refer to the below image.

remove geo replication

Alternatively, you can also use the following T-SQL script to remove Geo-Replication between primary and secondary databases. This T-SQL script should be executed on the master database at the primary server.

Replace the database and the secondary Azure SQL server name with yours.

Geo-Replication on a secondary database.

In case we want to have more than four secondary databases for a given primary database, configure the Geo-Replication on a secondary database so that the secondary database itself will have another secondary database. Execute the following T-SQL script on the master database at the secondary server to create a Geo-Replication from a secondary database to another secondary database.

Conclusion

In this article, we explored how to create Geo-Replication on an Azure SQL database, how to manually do a planned failover without data loss and forced failover which may have a data loss. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
168 Views