Active Geo-Replication for Azure SQL Database

By Eric Rouach, DBA & Data Engineer at Madeira Data Solutions

July 2023

With Active Geo-Replication, you can create a read-only secondary replica of any Azure SQL Database with just a few simple steps. This way, you may re-direct any "heavy-reader" or analytical workload and free-up your Azure resources to the benefit of your application.

It can also be a very useful method for easily migrating a database between Azure SQL logical servers, across different regions and even across different subscriptions.

Pre-requisites:

• An existing Azure SQL Database.

• A dedicated Azure SQL (logical) Server to which the read-only secondary replica will connect.

From SSMS, follow the below steps:

1. Run the following script on the “master” database in the primary (source) server:

CREATE LOGIN geodrsetup WITH PASSWORD = 'StrongPassword!@#';
CREATE USER geodrsetup FOR LOGIN geodrsetup;
ALTER ROLE dbmanager ADD MEMBER geodrsetup;

SELECT sid FROM sys.sql_logins WHERE [name] = 'geodrsetup';

2. Take note of the SID of the new login returned from the script above.

Copy it to the script below, and run it on the “master” database in the secondary (target) server:

CREATE LOGIN geodrsetup WITH PASSWORD = 'StrongPassword!', SID = 0x123456789123456789;
CREATE USER geodrsetup FOR LOGIN geodrsetup;
ALTER ROLE dbmanager ADD MEMBER geodrsetup;

3. Run the following script on the user database in the primary (source) server:

CREATE USER geodrsetup FOR LOGIN geodrsetup;
ALTER ROLE db_owner ADD MEMBER geodrsetup;

4. Connect as “geodrsetup” to the “master” database in the primary (source) server,

and run the script below after replacing the user database name and secondary server name:

ALTER DATABASE [YourDatabase] ADD SECONDARY ON SERVER [secondaryserver.database.windows.net];

5. Run the query below to track the progress of the replication:

SELECT * FROM sys.dm_operation_status;

6. Once the progress has reached the "COMPLETED" status, you're done!

Fail-Over

If you want to take this a step forward, you can also fail-over to your new replica to switch the roles between the primary and secondary databases:

7. Connect to the secondary replica database, and run the FAILOVER command:

ALTER DATABASE [YourDatabase] FAILOVER

Congratulations! Your new secondary replica is now the primary replica, and your old primary replica is now the secondary replica! 🎉

Cut-Off

If you want to take this even further and cut off the old replica from the synchronization (this could be useful for migration scenarios), then you need to do the following:

8. Run this command on the primary (target) database to remove the old replica server:

ALTER DATABASE [YourDatabase] REMOVE SECONDARY ON SERVER [oldserver];

Important note: [oldserver] needs to be the logical server name of the secondary (source), without the ".database.windows.net" postfix.

Rename

If after the cut-off you need to rename your newly created database (again, useful for various migration scenarios), then you can run the command below when connected to the "master" database in the server that hosts the database you want to rename:

ALTER DATABASE [YourDatabase] MODIFY NAME = [YourNewDatabaseName];

You need to make sure that no one is using the database before renaming it.

Cleanup

When you're done and no longer need the replica, then you can go ahead and drop the geodrsetup user and login from both servers and databases.

DROP USER geodrsetup;
GO
DROP LOGIN geodrsetup; -- this must be run in "master" only

Additional resources:

• Active geo-replication - Azure SQL Database - Cross-subscription geo-replication (Microsoft Learn)