Geo-Replication in SQL Azure Database

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.

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

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.

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.

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”

1

2

3

ALTER DATABASE AzureGeoRepl

ADD SECONDARY ON SERVER rbc2

WITH ( ALLOW_CONNECTIONS = ALL )

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.

1

ALTER DATABASE AzureGeoRepl FAILOVER

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.

T SQL Statement:

1	ALTER DATABASE AzureGeoRepl FORCE_FAILOVER_ALLOW_DATA_LOSS How to Remove Geo replication? refer below link. https://technorhythms9.blogspot.com/2020/05/how-to-remove-geo-replication-in-sql.html

Troubleshooting transient connection errors to Azure SQL Database

Troubleshooting transient connection errors to Azure SQL Database Dear friends, I'm Yogesh. At my work place, I have faced these type of issues at times. Let us understand what is this transient error? A transient error has an underlying cause that soon resolves itself. It causes occasionally is when the Azure system quickly shifts hardware resources to better load-balance various workloads. Most of these reconfiguration events finish in less than 60 seconds. During this reconfiguration time span, you might have connectivity issues to SQL Database. To handle them, implement retry logic in their code instead of surfacing them to users as application errors. If your client program uses ADO.NET, your program is told about the transient error by the throw of SqlException . What is the solution? Can Retry logic works? When your program communicates with SQL Database through third-party middleware, ask the vendor whether the middle ware contains retry logic for tr

Techno Rhythms

Search This Blog