Azure SQL Databases:Configure Active Geo-Replication for Azure SQL Databases using Portal and T-SQL.
Introduction
Azure keeps 3 local highly available database copies in the server region by default. If the region is completely lost we can use Geo-restore or Azure Database Geo-Replication to recover the databases.
Active Geo-replication enables us to configure up to four readable secondary databases in the same or different data center locations (regions) for ensuring the high availability of an Azure SQL database, and for disaster recovery.
Secondary databases are available for querying\read-only scale and for fail-over in the case of a data center outage or the inability to connect to the primary database.
Now, in this article, we will see how to configure Geo-replication using the Azure portal and Transact-SQL.
Prerequisites
- Ensure all databases part of Geo-replication within the same subscription.
- Ensure Databases belongs to different logical server that server may same or different data center locations (regions).
- Ensure databases belongs to the same service tier but may be different performance tiers.
- Ensure secondary database has the same name as the primary database.
Active Geo-replication Synchronization Modes
Synchronous Mode: Active Geo-replication does not support synchronous replication due to the high latency of wide area networks.
Asynchronous Mode: Active Geo-Replication asynchronously replicates committed transactions from a primary to the secondary database, using read committed snapshot isolation (RCSI) for isolation. In this mode, there would be some data loss unavoidable if a failure occurs.
To sidestep the data loss, we can call the sp_wait_for_database_copy_sync system procedure immediately after committing the transaction.
Observation:
Calling sp_wait_for_database_copy_sync blocks the calling thread until the last committed transaction has been replicated to the secondary database. The procedure will wait until all queued transactions have been acknowledged by the secondary database.
Setup active Geo-replication for Azure SQL databases using Azure Portal
Step 1: Login to Azure portal**>>>Browse to the database that you want to set up for Geo-replication>>> On the SQL database page, select Geo-replication>>> **and then select the region to create the secondary database.
Note: We can select any region other than the region hosting the primary database, but Microsoft recommends the paired region.
Step 2: On “create secondary” blade >>>Select or configure the server and pricing tier for the secondary database>>>Optionally, we can add a secondary database to an elastic pool.
To create the secondary database in a pool, click elastic pool and select a pool on the target server. A pool must already exist on the target server. This workflow does not create a pool>>>Click OK to add the secondary.
Step 3: Once the secondary database is created >>>seeding process begins. It represents in dotted lines.
Step 4: Once the seeding process is complete, the secondary database displays its status as Readable and it is ready.
Step 5: The same process needs to be repeated in order to add new secondaries (up to four).
Setup active Geo-replication for Azure SQL databases using T-SQL
Execute below Transact-SQL script from the primary server named GRS1, which is located in South Central US, to create a database name called GRDB1 on the secondary server named GRS2, which is located in Northcentral US.
-- Create secondary (North central US)
-- Connect primary server
-- Execute from master
ALTER DATABASE GRDB1
ADD SECONDARY ON SERVER GRS2
WITH (ALLOW_CONNECTIONS = ALL);
GO
Note: To monitor the status of the secondary database execute below T-SQL script from the master database.
Select replication_state_desc from sys.geo_replication_links
PENDING state **>>**Waiting to create a secondary database.
SEEDING state **>>**Copying data from primary to the secondary server.
CATCH_UP state **>>**Primary and secondary databases are consistent with a few seconds of delay.
Other scenarios where Active Geo-Replication can be used
In addition to disaster recovery active Geo-replication can be used in the following scenarios:
Database migration: We can use Active Geo-Replication to migrate a database from one server to another online with minimum downtime.
Application upgrades: We can create an extra secondary as a fail-back copy during application upgrades.
Summary
In this article, we saw various ways to configure and enable the Geo-Replication feature. In addition to disaster recovery, active Geo-replication can be use database migration and application upgrades also.
References
/en-us/azure/sql-database/sql-database-active-geo-replication
/en-us/azure/sql-database/sql-database-active-geo-replication-portal
https://azure.microsoft.com/en-us/updates/active-geo-replication-now-available-on-all-azure-sql-database-service-tiers/