3

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)?

Brent
  • 501
  • 3
  • 13

1 Answers1

3

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • Thanks for your answer, it ended up going so quick that I didn't really get a chance to track the progress.For anyone looking at this after the fact the database was actually 14 gigs and took around 40-45 minutes to fully sync from North Europe to US East. The only problem I had was during the switchover, the application user was based on a server Login, which didn't sync, of course. All I had to do was drop the user and recreate it based from a Login. – Brent Apr 07 '18 at 14:39
  • 1
    Try using contained database users instead of server logins. Server logins are disconnected during failovers while contained database user not. – Alberto Morillo Apr 07 '18 at 16:57