Geo Replication,Failover Group and DB Copy, DB backup and restore

Question

Hello Everyone,

As I and my team already know that,

When you re-enable geo-replication between DB1 (the primary) and DB2 (the secondary), the replication process begins asynchronously. The time it takes for DB1 to replicate changes from DB2 after re-enabling geo-replication can vary based on several factors, including:

1. Initial Synchronization: Upon re-establishing geo-replication, the primary database (DB1) will start synchronizing its data to the secondary database (DB2). This initial synchronization can take time depending on the amount of data that needs to be replicated.
2. Network Latency: The geographical distance between the two databases and the network conditions can affect replication speed. Higher latency may lead to longer synchronization times.
3. Database Size and Load: The size of the databases and the current load on the server can impact how quickly changes are replicated. Larger databases or those under heavy load may experience slower replication.
4. Change Volume: If there are significant changes made to DB1 after re-enabling replication, it may take additional time for those changes to be fully replicated to DB2.

In general, while there is no fixed time for replication completion, Azure's geo-replication is designed to handle changes efficiently, and most initial synchronizations complete within a reasonable timeframe, often within minutes to a few hours depending on the above factors. It's essential to monitor the status of geo-replication using Azure's monitoring tools to assess progress and ensure that both databases are in sync.

Now I would like to know

1. How will I monitor the Database Sync process happening in DB2
2. We will be running 150 queries to update few data in DB1 as it was not reflecting in our website, and then enabling sync again with DB2 once these queries have ran, currently the size used for the DB are 140 GB, we would like to know what could be the approx time that will take to complete the sync process. Any estimated time will do as well just to be prepared for our task.
3. Backup and Restoring - Currently our DB is in S6 tier and as mentioned size used is 140 GB, how much time will it take to backup and restore DB in a higher premium tier. usually it takes 9 hours for backup and 9 for restore in S6, requesting to provide an estimated time for this as well.
4. if we create a replica of the DB/server how much time does that take, again estimated time would be helpful

Answer 1

Hi @Aryaa Kotian

Thanks for the Question and using Microsoft Q&A platform.

Monitoring the Database Sync Process:

To monitor the database synchronization process in Azure SQL Database, utilize Azure Monitor. It offers metrics and alerts to help you track the status of your geo-replication. You can configure alerts to notify you upon synchronization completion or in case of any issues. Additionally, the Database Watcher (preview) feature provides more detailed monitoring.

Estimated Time for Sync Process:

Considering your database size of 140 GB and the execution of 150 queries before re-enabling synchronization, the time required can vary. However, based on similar cases, initial synchronization generally takes 1 hour to up to 12 hours, depending on network latency, database load, and the volume of changes.

Backup and Restore Time Estimate:

For a 140 GB database in the S6 tier, backup and restore times are typically around 9 hours each. Upgrading to a higher premium tier may slightly improve performance, but it is still safe to estimate approximately 9 hours for both backup and restore operations.

Estimated Time for Creating a Replica:

Setting up geo-replication for large databases typically takes longer than a few minutes. Considering your database size and Azure’s infrastructure, the process may take between 30 minutes to 4 hours, and could extend up to 6 hours due to factors like network conditions and database load. For a database of 140 GB, the replication setup generally falls within the 2-4 hour range, especially when syncing data across regions. Therefore, a more accurate estimate would be 2-4 hours, potentially extending to 6 hours in case of unexpected delays.

Please refer to these documentations that might help you:

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-sql-database-azure-monitor?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-metrics-alerts?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql

https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql&tabs=azure-portal

https://www.sqlservercentral.com/blogs/how-to-get-estimated-completion-time-of-sql-server-database-backup-or-restore

Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.