AS with the majority of projects, the original scope may not be a viable option. We were using Azure Data Sync and transaction replication to keep the 4 site databases in sync. However, the customer will have user-defined datatypes which are not compatible with Azure Data Sync and, they are now wanting to utilize Azure with AlwaysOn AG groups. I’ve setup a couple SQL Server AG’s, however, not to this extent and I’m really hoping someone has some good advice on how to go about this.
- Databases must remain accessible my all sites if a site should lose connectivity.
- Databases must be synchronized at all times.
- Minimal impact on changing connection strings to over 100 applications. 24 x 7 operation.
- New database after ETL will contain the schema used by all sites.
- 1 Physical SQL Server per site (customer is aware they’ll need additional SQL Servers).
- Number of sites = 4
To achieve a solution in which all sites are able to remain functional should a connection, server etc. go down. In addition to utilizing Azure in the process. Each site should also maintain an updated version of the database.