I currently have a setup where I have a primary region and multiple secondary regions, each region has its own Azure Sql Server. The secondary regions only have a webfront end, they dont have the backoffice. I thought that I would be able to keep the secondary databases (those in secondary regions) in sync with the primary database use Azure Sql Data Sync (https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql) I setup the primary db as the hub, with HubWins, and then setup a secondary database as a member (where the data should be replicated to) but some of the tables cause issues such as cmsContentNu, with the below message:

Database provisioning failed with the exception "Column 'dataRaw' in table 'dbo.cmsContentNu' is of a type that is invalid for use as a key column in an index.Inner exception: SqlException ID: aba23bf3-96ca-4464-a3c7-c01cb9a3fb2e, Error Code: -2146232060 - SqlError Number:1919, Message: SQL error with code 1919  For more information, provide tracing ID ‘d758e94b-8749-4f07-915e-0c375e09293c’ to customer support."

Does anyone have any experience with this situation, and have a solution to the above problem, OR a better solution than using SQL Data Sync for this setup?

SQL Data sync is not a supported setup. Your setup is a load-balanced setup since your secondary region does not have a backoffice (good, only 1 backoffice instance should be available). Load balancing on Azure is described here: https://docs.umbraco.com/umbraco-cms/v/13.latest-lts/fundamentals/setup/server-setup/load-balancing/azure-web-apps Make good use of Azure SQL's options to have (near-live) backups in multiple regions.

@Sebastiaan thanks for the response. I had initially architected the setup to have an event based system (eventbus and an api) to update some data in the primary region's database, from any of the frontends in any regions. I then thought this was over kill as I could use SQL DataSync. The index on that one column seems to be the only issue stopping me from using Datasync, which is a shame. Having one sql instance feels wrong, I need to ensure there are no latency issues when using a single datasource in a different region, especially when a secondary region is in Asia. Would you suggest that I will need to implement the event setup to allow the frontend to update the data, right now this isn't updating published content as such, but updating the extended values of members, there will be some other functionality in the future though. I couldnt see anything on that page about multiregion backups?

Umbraco currently is not architected to work with more than 1 database. > Would you suggest that I will need to implement the event setup to allow the frontend to update the data, right now this isn't updating published content as such, but updating the extended values of members, there will be some other functionality in the future though. You will need to follow the load balancing documentation https://docs.umbraco.com/umbraco-cms/v/13.latest-lts/fundamentals/setup/server-setup/load-balancing/azure-web-apps > I couldn't see anything on that page about multiregion backups? I'm talking about SQL Azure's native ability to do geo-redudant storage: https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql

Thanks again for the responses, I'll update my architecture diagrams, and terraform and hopefully this will sort everything out.