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.

closed as too broad by Max Vernon, Marco, kevinsky, Colin 't Hart, marcello miorelli Apr 21 at 6:33

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 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 at 16:03