setting up multiple WSFC using Windows 2016 and SQL 2016.

The Distributed AOG is meant to be used with 2 different WFC, meaning different nodes, so you'll need at least 4 servers to create 2 minimal WFC.

Mark Wills,

"But think I like your link better ! Been a while :)"

tks, yeah been a while, I thought about you .. where have you been ?

"And eagerly approached part 4 only to be taken back to 2008 adding a node ... Hmmm ..."

part 4 or point 4 ?

Victor,

yes, you are write by that article, did you try it before ? I am not sure if we can make SQL 2016 Multi master in this way ! then I can have sth to play with.

2 x primary replica man...

Been well, and took a bit of time off from EE :)

Meant Part 4 - was reading a 4 part series of articles - starting at part 3

You wont be able to get to a multi-master-writable-primary and quite frankly, cannot imagine why you would want that.

What are you thinking the advantages would be ?

"You wont be able to get to a multi-master-writable-primary and quite frankly, cannot imagine why you would want that."

have a project on testing multi master DB technology, e.g. can MySQL do it and under what situation it has problem.

a lot of people is asking why can't we have SQL serve AOG, e.g.

"What are you thinking the advantages would be ?"

just like Oracle RAC, why a lot of company use it? real scale out but expensive.

 I knew the real world is , we shouldn't do it as it is dangerous to the data integrity , easy to kill data.

one write and many read still the best and cheap solution now,

MySQL's multi master replication is a joke ! I don't  think we should do it.

ALSO, actually I think AOG can suit a lot of case we need and FCI is older technologY and I think AOG can replace it.

Well, it is real scale out and yes, it is expensive, and yes it will work to regionalise a lot of read access.

But control of writing is the key, and that must be properly managed otherwise any other failover wont work. That includes both hardware and software.

>> one write and many read still the best and cheap solution now,

Absolutely agree.

>>  think AOG can suit a lot of case we need and FCI is older technologY

Well FCI really has bean underwritten WSFC so, wouldnt really paint it in that picture. More a progression...
An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes
And , Always On availability groups requires a Windows Server Failover Clustering (WSFC) cluster. So, does go hand in hand.

Worthwhile reading : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server

"An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes"

AOG too.

reading this : https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/windows-server-failover-clustering-wsfc-with-sql-server

it seems AOG on top of SQL instance and SQL instance on top of each nodes of WSFC , and it seems that AOG on top of FCI, but seems still confuse as in this case, auto failover seems not working well ?

AOG is more on logical failover an FCI more on physical failover.

>> AOG too.

Yes, that's why I said it was more of a progression. And in being so, there is a lot more logic as to when and why and how and where a failover could happen. But that doesnt make it "logical" versus "physical".

It isnt "either / or", it is all about your environment, and how sophisticated you need to be to ensure continuity of services.

Some companies are fulfilled with "recover from backup" and others are "must be Always Available and fully Automated" there are a lot of options from one extreme to the other. It must match the corporate KPI for delivery of service, and "we" as DBA's have to be able to present risk/benefit/cost scenarios and recommendations to match the KPI.

Victor.

"So multi master is a MySQL's feature. That's why I never heard about it."

No, other DB also has multi master, e.g. Oracle RAC! very expensive, but it has problem too, e.g. from node to node , can't spread more than 50km, after that Oracle suggest you try it yourselves.

DB2 also has multi master support, need to purchase separately , called purescale.

"Not really. AOG exists across two or more SQL Server instances."

yeah, I mean on top of SQL installation, but I want want to find out what is diff between FCI and AOG, now I should say AOG can setup on top of FCI, FCI need shared this but not AOG.

"Besides that, as opposite from a FCI solution, AOG doesn't have a single point of failure. Only thing logical on an AOG solution is the Listener, that is a virtual IP. All the rest are all physical (database files)."

so I am thinking why we need FCI once AOG is here, should not much situation need FCI, when we have old technology like optical fiber and SAN and AOG is not come up yet, FCI is the case.

Mark,

"It isnt "either / or", it is all about your environment, and how sophisticated you need to be to ensure continuity of services.

I agree,

so I am thinking why we need FCI once AOG is here, should not much situation need FCI, when we have old technology like optical fiber and SAN and AOG is not come up yet, FCI is the case.

You don't need FCI at all if you're going to an AOG solution. Even it's technical possible, I wouldn't recommend it. It will only add more complexity to your solution and worst, it will maintain the storage single point of failure.

" It will only add more complexity to your solution and worst, "

I agree make it more complex  ! that's why I am wondering !.. why and FCI has the storage as the single point of failure.

why and FCI has the storage as the single point of failure.

Because Microsoft didn't build a proper Cluster server, so they needed to share the storage.

what should be the proper cluster server  ? Oracle RAC also use shared storage. MySQL NDB cluster also doing in that way.

I can't talk about others DBMS as I don't know them.

I think SQL Server's approach og shared nothing is a big advantage. Think of RAC with shared storage, but what happens with disk failure in that scenario. A single point of failure in storage could be catastrophic.

But consider SQL cluster - then that can be considered shared storage. So once again, it is hard to compare because of the variety and depth that SQL offers.

They are different to be sure, and I guess subjective as to which is "best"

I know which one I prefer in regards to "bang for bucks" and that is (obviously) SQL Server

"I think SQL Server's approach og shared nothing is a big advantage."

yes, last friday has a team meeting and I have a taked before to compare MySQL multi master and MS SQL AOG, and someone ask how I comment oracle RAC and MS SQL AOG,  this is exactly the answer, it is a new approach, it is called "horizontal scale out " ,  has a copy of DB in a lot of OTHER LOCATION.

"Think of RAC with shared storage, but what happens with disk failure in that scenario. A single point of failure in storage could be catastrophic."

exactly !

"They are different to be sure, and I guess subjective as to which is "best""

nothing can be the best ,right?

"I know which one I prefer in regards to "bang for bucks" and that is (obviously) SQL Server"
yeah, cost low enough to let you build big picture.

Victor":

"The Distributed AOG is meant to be used with 2 different WFC, meaning different nodes, so you'll need at least 4 servers to create 2 minimal WFC."

by this means, I want to save the no. of VMs I have to use, another 3 x nodes using difference SQL instance to simulate that distributed AOG.

will it work .. ?

If they belong to another WFC, then yes, it will work.

why same WSFC can't work ,I just setup another SQL AOG with the 3 x news SQL instance on the same node.

hi,

this is the step he will setup the distributed AOG:

Here’s a high-level overview of the steps for your reference.

1) Create the primary Availability Group (AG_DC1) with a corresponding listener name (AG_DC1_LISTENER)
2) Create the Availability Group endpoint on all the replicas in the secondary Availability Group
3) Create login and grant the SQL Server service account CONNECT permissions to the endpoint
4) Create the secondary Availability Group (AG_DC2) with a corresponding listener name (AG_DC2_LISTENER)
5) Join the secondary replicas to the secondary Availability Group
6) Create Distributed Availability Group (DistAG_DC1_DC2) on the primary Availability Group (AG_DC1)
7) Join the secondary Availability Group (AG_DC2) to the Distributed Availability Group

I think 1 to 3 can all be create using a single AOG creation wizard, right? why use so much script ? this can create error.

again, any idea on why 4 and 5 is not also using one create AOG wizard to finish it use so much script ?

why same WSFC can't work ,I just setup another SQL AOG with the 3 x news SQL instance on the same node.

Because the idea of the Distributed AOG is to work with two different WSFC.
What's the benefit in going to a solution as you just presented above?

"What's the benefit in going to a solution as you just presented above?"

save HW/VMs, if it works, even more read replica on the same amount of HW.

You're not saving nothing if you're putting more complexity.
With the presented scenario, you don't need Distributed AOG. Just create the 2 AOGs and the respective database replicas cross the 3 nodes. It will give you the same with less complexity.

"Just create the 2 AOGs and the respective database replicas cross the 3 nodes. It will give you the same with less complexity."

you mean 2 x AOG , each with 3 x sAME nodes, (3x 3xexisting SQL 2016 default instance ,  with 3x SQL 2016 named instance), then chain 2 x AOG together?

Forget about chaining the 2 AOG. Is what I'm trying to say to you these last 2 days. You won't win nothing with that operation.

ahahha ok.

tks all.