April 18, 2019 at 2:58 pm
I've been trying for several days to determine if this is possible or not. I'm implementing an AlwaysOn setup at 4 separate locations. Each location requires the data to be replicated to other sites. I'm wondering if I can use Azure Data Sync with an AlwaysOn setup.
Current Setup:
Requirements \ Objectives:
April 19, 2019 at 8:42 am
Hi Dude,
You are looking at the wrong product for this, since there is no way you can have bi-directional HA using SQL server, readable secondaries is what the current state of the art allows.
What you are describing sounds like Cosmos DB, but that has no on-premises server, or the future SQL hyperscale product Raghu Ramakrishnan talked about in the SQL Pass summit keynote 2 which is also cloud based alas.
So my bet would be a circular setup for minimal traffic and costs and protects you against 1 loss A to B, B to C, C to D, D to A, or a full mesh if all data need to be everywhere A to BCD, B to CDA, C to DAB, D to ABC, but this will probably become very expensive very fast. But that will also mean app changes, because reads can be local, but 3/4th of the writes need to be done to a off-site primary.
grtz,
Theo (NL)
April 19, 2019 at 9:36 am
You could also consider merge replication, you can combine it with with AlwaysOn https://repltalk.com/2016/05/31/walkthrough-of-merge-replication-with-alwayson-high-availability-2/ or check the Peer-to-Peer - Transactional Replication.
Azure SQL Data Sync is only available for Azure SQL.
April 19, 2019 at 12:06 pm
Well...
it's been close to 10 years ago that for an oil company i have setup a redundant central SQL server in NL (using mirroring), and merge replication of regional data sections to about 14 locations over the globe, with internet links as bad as 128 kbit in the less developed parts of the world. the use case was that the regional supplies could be maintaned from central, and local.
I can tell you that i have no fond memories about that project, and had to deal with *really bad* merge conflicts after network outtages.
If i would build that now, i would use Async AG, with reads from local DB, and writes to the central DB. And if the link was out... too bad.. because having to explain to your customer you sold the local stock twice is even harder 🙁
I would rather invest a bit more in redundant network infra than in two way sync that is frought with problems by design
April 19, 2019 at 1:33 pm
I'm currently using Azure Data Sync and transactional replication. It works great with the AdventrueWorks DB that I'm doing testing with. However, now the customer is wanting HA and, I've determined there are user-defined data types as well as other user-defined objects that will not work with Azure Data Sync. I'm looking into AlwaysOn AG's however, I have concerns. The first one is: If I have an AlwaysOn setup, what happens if a site should lose Internet, for instance, if someone cut their fiber line? The second is, would a distributed AlwaysOn be in play here, or would I setup each of the 4 sites with an AlwaysOn setup? I'm hoping the below will help to provide additional answers.
Again, I'm fully in agreement that AlwaysOn would be a good configuration. But, which would be the primary? Should I make an Azure SQL Server VM and use that as the primary? I think my biggest concerns are what happens during an outage and keeping a site fully functional without Internet.
Requirements:
Workflow Example:
April 19, 2019 at 1:54 pm
Hi Dude,
SQL AG will buffer transactions when connections are lost and send them to the sites that are behind on a later time, so no worries there.
If every site has its own local data set, i.e. Site 3 needs RW on local data and that local data is RO on site 12 and 4 (with a slight delay), and RW to site 3 REMOTELY for site 12 and 4 (with a slight delay) your'e good.
If you have data that needs to RW on all 4 sites your'e screwed if you have network outtage, because only 1 AG member can be primary, and you need to pick ONE site for the primary AG role (usually headquarters).
Hence my remark, if you make the network robust by allowing each site to have a primary network link and a backup link coming into the building through TWO (or more) internet providers on physically separate lines, your'e good again, and you don't incur downtime if one internet link fails
(close to) Zero loss of data is possible using AG with some care, good recovery procedures and good knowledge of AG
How close depends on the criticality of your business need to be work if parts of the servers or network fail. Redundancy costs serious money, en the closert to the mythically ZERO dataloss and ZERO downtime you get the higher the bill. 🙂
April 19, 2019 at 2:14 pm
I agree with you Theo. The customer is a DoD sub-contractor and therefore extreme caution must be used when dealing with that data, hence zero data loss. I know the customer is getting a fiber line ran to the locations that have unreliable Internet. So, maybe utilize the fiber line as primary (duh right?) and have the other as a backup?
So if there are 4 sites I would want to configure something like the following correct? I understand AG to a certain level and learning even more very quickly LOL. Baptism by fire I suppose.
April 19, 2019 at 3:11 pm
You could also use file share witness (read up on the pro's and con's)
I'd say 2 servers (vm or phy) per site where each site has it's own local AG on the 2 local servers in synchronous mode (zero dataloss) and the 3 offsite readable secondaries on the second server of each location.
That way each office reads and writes to their local primary AG, and all changes are replicates in the background to the local sync secondary and the 3 remote async secondaries.
Assuming the usual read to write ratio of 98%-2% only 2% needs to go offsite.
And if each site is setup the same way, each location has ALL the data available to read, even if both internet links go down.
Writing however is a different beast, and will be the trickiest part.
The connection strings of SQL AG's allow for an INTENT SETTING, that way you can split READS to go for the (local) secondary, and WRITES to go the the PRIMARY.
But this will mean your application has to be built for split read and write conn strings to take full adavantage of this architecture, and therein lies the rub for near all the legacy stuff that only supports a single conn string.
Do you see the big picture i'm trying to paint here?
You use 4 AG's to spread the data around, and use the special conn strings to connect to the (offsite) primary for writes, and let SQL deal with the hassle of figuring out where the pri and sec are and what status they might have.
🙂
April 19, 2019 at 3:25 pm
Theo, I greatly appreciate your help. I was figuring of setting things up the way you described in your recent response. I was working on a Visio diagram of the setup actually. As far as applications go, the applications are to be rewritten as they are written in VB6 and connecting to Access (YUCK!). I think the plan for the applications would be to create a monolith. I can see that the biggest obstacle with this setup will be the connection strings, so that will have to be addressed with the customer.
At some point we will be implementing a SQL DW for the customer. I have so much training to do before that and some certs to obtain, but... Anyway, again, I greatly appreciate your insight. You've been most helpful.
April 20, 2019 at 8:42 am
VB6 - MS Access, wow.... 🙂
That was me (old fart here-- i'm 55), somewhere around 1998 - 2003, and for it's time VB6 was pretty powerfull, now.... not so much.
Access however is still a force in the small business and small workgroups, and has some nice features i bet you don't know (MS Access enthousiast here).
Do you know it is *very* simple to move the tables from Access into SQL and in have Access pointers to the tables in SQL? This is effectively demoting Access to a front-end UI, and taking the most of the benefit of having the data in an HA SQL back-end. But unless you use Access basis for all table IO, it's not possible to set it up like we spoke about earlier.
But.... if Access is *only* used for table storage, you *might* be able to move the Access data to SQL and point VB to SQL, vastly increasing your options.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply