SQL Server 2016 Always On Distributed Availability Groups -- MS SQL version of Multi master ?

hi,

I am reading this :https://www.mssqltips.com/sqlservertip/5053/setup-and-implement-sql-server-2016-always-on-distributed-availability-groups/

anyone tried this Distributed AOG ? can it simulate multi master role  ?

like this statement :

"Scaling out readable secondary replicas. Traditional Availability Groups allow for one primary and eight secondary replicas (from SQL Server 2014 and higher). With two Availability Groups, you get a total of eighteen (18) potential readable copies of the database – sixteen (16) secondary replicas, the primary replica of the first Availability Group and the primary replica of the second Availability Group. I don’t take this lightly because the licensing cost is not cheap. You have to really think about this before considering implementing Distributed Availability Groups mainly to scale out readable secondary replicas."

so we have 2 x primary replica for write operation at the SAME time ?

is it doable ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
>> so we have 2 x primary replica for write operation at the SAME time ?

No, I dont think that is what it means. Could be wrong and prepared to eat my words. Certainly OK for readable....

That Article does go on to say
Only one read-write copy of the database. Don’t be fooled – you can’t do load-balancing of read-write workloads. Just because you see two Availability Groups doesn’t mean you get two primary read-write replica databases. You only have one read-write copy of the database. The other primary replica on the secondary Availability Group functions similar to a distributor in a replication topology – it only receives transaction log records from the primary replica of the primary Availability Group and sends them to the other secondary replicas of the secondary Availability Group.

In the process of investigating this topic myself. As attractive as it might be, not yet convinced. Cost is huge, and the environment is something that I dont have need for, so, at this stage mainly / quickly becoming an academic exercise.

Seems to be a big jump from what I can remember for you :)
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
First of all, you're reading an article written by Ed Sarmiento, for me the guy that really knows A LOT of AG in the world, so be very careful when reading his articles, as they are mostly written on a very high level to experienced professionals, meaning that you might no need to implement a similar solution at all.

anyone tried this Distributed AOG ?
I didn't.

can it simulate multi master role  ?
What's that?

so we have 2 x primary replica for write operation at the SAME time ?
Would be good, right? But the answer is NO.
What I understood for Ed's article, is that Distributed AOG is a solution when you can't (or don't want to) create a Regional WFC, so you'll have replicas in a different Windows Cluster. IMHO, if you can deploy a regional Windows Failover Cluster, then go for it, as it should even more easy to maintain.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Mark Wills,

"No, I dont think that is what it means. Could be wrong and prepared to eat my words. Certainly OK for readable...."

ahahahah, you here too.

"Seems to be a big jump from what I can remember for you :)"

hey man.. don't joke. good to see you again... you should come back. I saw you face from EE's introduction.

Victor,

"can it simulate multi master role  ?
What's that?"

multi write and it seems it is not now...

recently I found out maxscale by Maria DB is not better than MS SQL AOG, only latest version of it can auto failover ! so MySQL is not better !

it just free !

it is doing something SQL AOG is already doing!

and MySQL's multi master is just trouble making ! Maria DB just automate the process of reconnecting replication . here no option to try it out further.

" Regional WFC"

you mean single WSFC across different country/site ? so has to use diff WSFC ?...


" for me the guy that really knows A LOT of AG in the world, so be very careful when reading his articles,"

so has to really word by word to fully understand his statement..?  but he already that article interesting.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark WillsTopic AdvisorCommented:
@marrowyung,

Me joke ? Nah, still the same serious, pedantic person - just with a smile and casual attitude :)

I am giving it a reasonable try for a few months, and then I'll decide about coming back.

Seeing your question brought back memories :)

>> so has to really word by word to fully understand his statement..?

Yes, something that I would read *laughing* - but seriously, need to have the environment to be able to explore what he has to say as well.


>> so we have 2 x primary replica for write operation at the SAME time ?

Why would you want to do that ? You begin to lose control the moment they are treated / written to as if they were separate.

Can you explain why, or, what you hope to gain / advantages ? I cant think of any - but then I am looking at it from a SQL Server perspective...
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
man,

"I am giving it a reasonable try for a few months, and then I'll decide about coming back."

must be sth make you disappoint ! and other reason I can think about is you already know everything as you are the guru !

"I am giving it a reasonable try for a few months, and then I'll decide about coming back."

agree.

"Why would you want to do that ? You begin to lose control the moment they are treated / written to as if they were separate."

you are referring to split brain situation ?

Exactly and this makes DB solution very expensive ! e.g. IBM purescale. Oracle RAC.


we tried sybase multi master too and it dead ! data mixed up

"I cant think of any - but then I am looking at it from a SQL Server perspective..."

we want to see if scale out write is ok on other product and I think MS SQL AOG and Maria DB + Maxscale already fair ! one write many read.
0
 
Mark WillsTopic AdvisorCommented:
>>  one write many read.

Exactly...
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
and if try to turn write operation, fine tune index.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks all, already good answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.