2

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.

Requirements:

  • 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.

Site Setup:

  • 1 Physical SQL Server per site (customer is aware they’ll need additional SQL Servers).
  • Number of sites = 4

Goal:

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.

  • I'm no expert on SQL Server, but it seems to me that your question is (maybe) a bit too broad for this forum. This seems to require some sort of analysis/position paper which isn't a good fit - the whole "ethos" here is "short and snappy - get it in, get it out" rather than going into great depth. This is something I regret personally, but I steer clear of the politics of the site. +1 for well phrased first question. p.s. welcome to the forum! :-) – Vérace Apr 16 '19 at 6:55
  • @Vérace Thank you for the compliment and the insight. I am in agreement with you though. After long consideration, I'm going to direct my customer to a multi-subnet AlwaysOn setup with BLOB storage in Azure, most likely. Thanks again. – Trent Adams Apr 16 '19 at 16:03