SQL Needs a Secondary Region? Enable Geo-Replication

If your database’s region becomes unavailable, your application loses access to its data. Restoring a backup elsewhere takes time. Active geo-replication keeps a running copy in another region that can take over. Changes travel asynchronously, so the secondary can briefly lag behind the primary.

Start with One Database

Use sqldb-cloudtrips on sql-ctappweu in West Europe. If deleted, recreate it using Create Azure SQL Database. A Basic database is enough for this lab; an existing Hyperscale database also works, with a correspondingly priced secondary.

Azure creates the secondary from the primary. You need only the existing source database; sqldb-customer2 from the pool exercise is separate.

Create the Secondary

Open sqldb-cloudtrips → Data management → Replicas → Create replica. Use the same subscription and configure:

Resource group: rg-cloudtrips-sql-dr-neu (new)
Secondary server: sql-ctappneu (new; globally unique)
Server location: North Europe
Authentication: SQL authentication
Server admin: ctadmin
Database name: sqldb-cloudtrips (inherited)
Elastic pool: No
Compute + storage: Match the primary's tier and compute size

Choose a strong admin password and use your actual server names if these are taken. Select a readable geo-replica, rather than the standby option, if offered. Review the extra database cost, then select Review + create → Create.

Check that the target server is in North Europe and the database is named sqldb-cloudtrips. The two databases share a name but live on different servers.

After deployment, return to the primary’s Replicas page and wait for initial copying to finish.

Check both server names and the replication status. A healthy ongoing link can show Readable or CATCH_UP, depending on the view; asynchronous replication continues after initial copying.

Verify That Data Arrives

Connect to the primary database using your VS Code SQL connection and run:

IF OBJECT_ID('dbo.GeoCheck', 'U') IS NULL
    CREATE TABLE dbo.GeoCheck (Id int PRIMARY KEY, Note nvarchar(100));
DELETE FROM dbo.GeoCheck WHERE Id = 1;
INSERT INTO dbo.GeoCheck VALUES (1, N'Written in West Europe');

This creates a small test table and writes one row. Successful execution confirms the write; the next query checks replication.

On sql-ctappneu → Networking, allow your current public IP under selected networks and save. Server-level firewall rules must be configured separately. Keep Allow Azure services and resources to access this server disabled.

Create a second VS Code connection to sql-ctappneu.database.windows.net, database sqldb-cloudtrips, using the secondary server’s admin credentials. Keep encryption enabled and run:

SELECT DB_NAME() AS DatabaseName,
       DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS AccessMode;
SELECT Id, Note FROM dbo.GeoCheck;

Check the connection targets sql-ctappneu, the access mode is READ_ONLY, and row 1 contains Written in West Europe. If the table or row has not arrived, wait briefly and rerun.

What Happens During an Outage?

Failover promotes the secondary to accept writes. A planned failover synchronizes first; a forced failover during an outage can lose recent changes. With active geo-replication, you initiate failover and point the application at the new primary’s server. The next trip adds a failover group for automatic failover and a stable connection address.

Keep or Clean Up

Keep both databases for the failover-group trip; both incur charges. Otherwise, select Stop replication for the geo-secondary on the primary’s Replicas page, then delete rg-cloudtrips-sql-dr-neu. Stopping replication alone leaves a billable database. Keep the primary for later trips or delete its lab resources too.