We are looking to try to implement the following in Azure SQL Server / databases. Our solution we provide has the following resources:
- 2 azure app services
- database backend in Azure SQL Server with SQL Databases within an elastic pool
Goal:
We would like to have the above resources in the West and in the UK, so basically complete solution in each area of the globe listed
Have the databases be able to be read/write in each region we setup the solution while having bi-directional replication
(Not so important right now) ultimately, we would have azure front door in front of this to direct users based on their location where they get directed to. Obvious reason we need the databases to replicate to each other in order to ensure if a user is traveling, they get their tenants data as expected no matter where they log in from.
What we looked at so far:
- Azure SQL Geo Replication will not do what we need as the replicas are read only which means we would have to have the Azure App Service in the UK or West point to the SQL server databases in the US East 2 region. We attempted that once and it was super slow but thats expected I would think.
- Azure Data Sync, this has some caveats and issues which were that certain types of data do not replicate, certain tables are not replicable, if we add tables theres an added complexity with that.
- Side Note: I tried setting this up just with the azure sample database and there we even tables in that you could not data sync.
I cant seem to find a solution that literally mirrors the databases without stipulations or caveats that require database changes on our end or some complexities being added.
