setting up multiple WSFC using Windows 2016 and SQL 2016.

can 3 x Windows 2016 nodes setup 2 x diff WSFC, so that, each WSFC setup with diff 3x SQL 2016 nodes AOG?

I want to test out the SQL Server 2016 Always On Distributed Availability Groups !

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

so I want to know if I can use the same 3 x Win2016 nodes to setup 2 x diff WSFC and 2 x diff SQL 2016 AOG .
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Funny you should mention that. I happened to be reading : https://www.mssqltips.com/sqlservertip/4813/stepbystep-installation-of-sql-server-2016-on-a-windows-server-2016-failover-cluster--part-3/

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

Havent done it myself in 2016, but wouldnt think it was overly different as far as wizards go.

Given the coincidences, I thought I would share it with you :)

But think I like your link better ! Been a while :)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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...
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Mark WillsTopic AdvisorCommented:
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Mark WillsTopic AdvisorCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
MySQL's multi master replication is a joke
So multi master is a MySQL's feature. That's why I never heard about it.

it seems AOG on top of SQL instance a
Not really. AOG exists across two or more SQL Server instances.

AOG is more on logical failover an FCI more on physical failover.
Also not quite true. AOG is also an advanced Mirroring solution. It let you have more Secondary Replicas and those Replicas are all readable. 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).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
>> 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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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,
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
" 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
what should be the proper cluster server  ? Oracle RAC also use shared storage. MySQL NDB cluster also doing in that way.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I can't talk about others DBMS as I don't know them.
0
Mark WillsTopic AdvisorCommented:
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
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
1
marrowyungSenior Technical architecture (Data)Author Commented:
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 .. ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If they belong to another WFC, then yes, it will work.
0
marrowyungSenior Technical architecture (Data)Author Commented:
why same WSFC can't work ,I just setup another SQL AOG with the 3 x news SQL instance on the same node.
0
marrowyungSenior Technical architecture (Data)Author Commented:
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ahahha ok.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.