Sql Azure Database Mirroring for Traffic Manager Failover

Question

My objective is to implement Azure Traffic Manager for failover of our web site and its databases.

To achieve this, I have two identical Sql Azure databases deployed in different data centres.

The database exhibits 450 tables, 4000 columns, ~8 million records, 3GB in size and frequently written to.

1. Is Sql Data Sync a viable option to implement mirroring or in Sql Azure terms, bi-directional synchronization between them?

My initial concern besides efficiency and cost, and regardless of bi-directional vs one-way, is the time required to setup Sql Data Sync, maintenance overhead when schema evolves and debugging a complex schema when sync fails. There is the added issue of Sql Data Sync still in state of preview.

2. Perhaps one-way Geo-replication is the better option and upon failover, one would manually reinstate the original database to be synchronized to - I'm wondering if such a course of action is what usually ensues for a database Admin?

My concern here is the departure from a fully automated failover solution governed by Traffic Manager.

Answer 1

I have concluded Sql Data Sync is not ideal for my MAWS and Sql Azure failover strategy.

Active Geo-Replication for Azure SQL Database proves the better option.

Here are some points I've considered against Sql Data Sync.

1. With 450 tables and over 8 million records, my database is large enough to bring additional complexity and cost setting up, executing and maintaining bi-directional or one-way Sql Data Sync.

2. Sql Data Sync does not appear to be designed too strongly around the idea of cross-data centre mirroring of large database. With limitations such as 100 tables per Azure Sync Group.

3. Setting up Sql Data Sync properly for my database would take time because it involves spreading 450 tables across multiple smaller and manageable Sync Groups with well-tuned sync frequencies and each one tested to ensure synchronization occurs without conflicts. A deep analysis of database is required so the correct tables are grouped together to avoid foreign key conflicts in the target database:

   http://www.mssqltips.com/sqlservertip/3062/understanding-sql-data-sync-for-sql-server/

4. It appears that Sql Data Sync is not a transaction sync model:

SQL Azure failover / backup strategy for web app

5. SQL Data Sync has been in preview for over 2 years and the last update was December 2012

6. Sql Data Sync has inherent problems coping with large schema. This was experienced first-hand with my database schema.

http://social.msdn.microsoft.com/forums/azure/en-US/9c679a74-9a7c-48e7-b4c9-95f6f7cfafd9/sql-azure-data-sync-refresh-schema-not-working

7. Emphasizing the first point, two-way replication across data centers is tricky and generally not recommended without intuit knowledge of the data and database schema. One could end up creating synchronization loops.

8. Best Practice states: Only include the tables which are required as per your business needs in the sync group; including unnecessary tables can have impact on the overall cost as well as on efficiency of the synchronization.

http://www.mssqltips.com/sqlservertip/3062/understanding-sql-data-sync-for-sql-server/

9. Sql Data Sync doubles up on tables, creating an even larger schema adding to my 450 tables.

In regards to using Active Geo-Replication for Azure SQL Database, upon failover, one would simply terminate the continuous copy relationship on the active secondary so that it becomes read-write.

From http://msdn.microsoft.com/en-us/library/azure/dn741331.aspx

In the case of a widespread failure in the primary region, you might need to fail over your application to a secondary region. First, force terminate the continuous copy relationship on the active secondary. After the termination, replication will stop and all transactions that were not yet replicated from the primary database will never be copied to the active secondary. The former active secondary will become a standalone database. At this point, the application can failover to the former active secondary and resume its function. If the primary database was set to read-write, after termination, the active secondary is also set to read-write.

As far as my Database SLA goes, I have the following scenarios covered

1. Recovering from accidental data corruption or deletion: Sql Azure Premium offers free and automatic Point in Time Restore out of the box so there is no need to setup nightly backups to blob storage. http://azure.microsoft.com/blog/2014/10/01/azure-sql-database-point-in-time-restore/

2. Monitoring for regulatory compliance, understanding database activity and insight into discrepancies and anomalies: Sql Azure offers database auditing on many aspects http://azure.microsoft.com/en-us/documentation/articles/sql-database-auditing-get-started/

3. Cross-region redundancy to recover from a permanent loss of a datacenter caused by natural disasters, catastrophic human errors, or malicious act: Sql Azure offers Active Geo-Replication http://msdn.microsoft.com/en-us/library/azure/dn741339.aspx