CODEWITHSUNDEEP
sqlazureazure-sql-databasegeo-replication
Brent
Brent

Reputation: 521

Speed and Impact of Azure SQL Geo-Replication Sync

I have a client who's Azure SQL database was accidentally created in a European datacenter (the client is in the US). I was hoping to move it to the US using Geo-Replication to minimize downtime as outlined here: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-geo-replication-portal

The database is moderately large at around 25GB and I can't seem to find anything on the following questions:

1) Is there anyway to gauge how long the initial sync will take (ie. all data is synced and we can failover to the new DB in the US)? 2) Is there anyway to view the progress of the initial sync? 3) Will the performance of the database be substantially impacted by the initial sync (and subsequent synchronization)? 4) Will bumping the server tier up improve any of these outcomes (ie. speed of sync and performance impact)?

Upvotes: 3

Views: 1667

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15658

You can use sys.dm_operation_status to know the progress of failover. Below gives you the number of open transactions to clear

select count(*) as OpenTX from sys.dm_operation_status where major_resource_id= 'DatabaseName' and state < 2

You can learn more about progress of a failover here.

The following query gives you the replication state and replication lag information also.

select 
  partner_server,
  partner_database,
  replication_state,
  replication_state_desc,
  role_desc,
  secondary_allow_connections_desc,
  last_replication,
  replication_lag_sec
from sys.dm_geo_replication_link_status
go

The following query gives you the operation status during the first sync.

select
  major_resource_id,
  operation,
  state,
  state_desc,
  percent_complete,
  start_time,
  last_modify_time
from 
  sys.dm_operation_status;
go

Keep the secondary database with the same tier and objective as the primary to avoid performance penalty.

Upvotes: 3

Related Questions