compare SQL server FCI and AOG

hi,

we have SQL server FCI and SQL server AOG, just want to know what is the benefit of still using FCI as now AOG can do what it can do .
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
but SAN shared storage is much expensive as it involve an extra SAN storage, right?

for AOG, other server can be very cheap, storage very cheap today if it is an individual server. so AOG setup cost should be much cheaper than FCI ?
I might gave you my own experience as I always use SAN storage to store my database files. Never used local disks, but if you're thinking to use it, then yes, AOG might be somehow cheaper. It all depends on how much storage do you need.

but the way, in SQL 2016, listener also can be HA too ?

what if the SQL nodes host the listener die ?
It's not CAN but MUST. The Listener must be a virtual network resource of your Windows Cluster, so if a node shutdowns, the resource will failover to another node.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only benefit of FCI over the AOG is the costs, if you're using SQL Server Standard Edition.
When you're using SQL Server Enterprise Edition, then the license price are the same but FCI will still be a more cheaper solution because requires less storage (share resource in Cluster) while with AOG you will need to have the same amount of storage for each Secondary Replica.
But FCI isn't a real High Availability solution because it requires down time during the failover, something that AOG doesn't. AOG is the only real High Availability for SQL Server and the one that's more reliable for none or very few data loss when using it in a Disaster Recovery solution.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"but FCI will still be a more cheaper solution because requires less storage (share resource in Cluster) while with AOG you will need to have the same amount of storage for each Secondary Replica."

but SAN shared storage is much expensive as it involve an extra SAN storage, right?

for AOG, other server can be very cheap, storage very cheap today if it is an individual server. so AOG setup cost should be much cheaper than FCI ?

"But FCI isn't a real High Availability solution because it requires down time during the failover, "

you mean it is HA but not that good HA, right? FCI is a HA solution as it has failover.

"AOG is the only real High Availability for SQL Server and the one that's more reliable for none or very few data loss when using it in a Disaster Recovery solution."

 I can accept that.

but the way, in SQL 2016, listener also can be HA too ?

what if the SQL nodes host the listener die ?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
marrowyungSenior Technical architecture (Data)Author Commented:
"It all depends on how much storage do you need."

 exactly ! the company I work with always start with individual PC size as the server, just install Windows on it , right ? and storage can be 3TB and so on and so cheap !

even by  a 2U - 3U server already can have a lot of HDD slot for HDD, then just buy 3-4 mores, we can then setup AOG. External SAN is good when we want to share to a lot of diff server or other team so save storage cost, so when we share with a lot of team , each with small 1U server, then it is cost saving as SAN storage can chain up and expand much easier.

for standalone DB serve which do not share, which we don't want to share as it can make DISK read/write latency very high ! e.g. SQL server has a disk latency of less than 8ms for none cache storage. in large environment Shared SAN can have 120ms to 240ms disk latency, which is not good for SQL server operation then I would like to say no shared storage for that.

if isolated, then should be individual server as server got cheaper and cheaper quickly .

this is the environment I worked before.

just like VM concept, very bad for SQL server as VM shared HW resource with other team.

"so if a node shutdowns, the resource will failover to another node."

so listener still a resource? I don't see it in WSFC manager.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
so listener still a resource? I don't see it in WSFC manager.
Yes but it doesn't show as "listener". Should appear as "network name" and "virtual IP".
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Should appear as "network name" and "virtual IP"."

oh.. whoa, very hard to understand why this! then it is just network and Virtual IP failover,  totally WSFC node failover.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it is. That's why you still need a WSFC when working with AOG.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks victor.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I thought about one thing, as quorum can't be lost and as file share witness is a share some where else, what if that share ALSO dead, cluster gone too !

can distributed file system's file share solve this problem ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can always rebuild a quorum disk or a file share witness. They are only needed to know that all nodes are responsible.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"You can always rebuild a quorum disk or a file share witness."
but the point is , the serve host file share also can die, what if that happen ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What for you need that file share?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
as the file share quorum witness. I don't want WSFC die and SQL AOG die..
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, then I already answered to that:
You can always rebuild a quorum disk or a file share witness. They are only needed to know that all nodes are responsible.

If dies, just rebuilt it. Simple as that.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, but if file share die, assuming all file inside can't be recover ( we didn't back it up e.g.) ,

but by your meaning, how to rebuild the quorum disk and file share ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The information in the Quorum disk or the Witness File Share aren't recoverable and not necessary for SQL Server to work. Is like you loosing tempdb database. It will be recreated as soon as you restart SQL Server.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
" Is like you loosing tempdb database. It will be recreated as soon as you restart SQL Server."

yes.

"The information in the Quorum disk or the Witness File Share aren't recoverable and not necessary for SQL Server to work."

but if WSFC die because of this the SQL AOG also die very soon specially when failover, right?

i tried the workgroup AOG, when enabling SQL server for AOG feature, it will say I can't detect a quorum if WSFC doesn't works at all.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
someone send me this : https://www.acumenitsupport.com/blog/microsoft/removing-a-failed-file-share-witness-from-microsoft-failover-clustering/

this can be one of the answer, I worry about that as Windows administrator can forget that file share.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you loose the quorum or the file share witness you'll only loose the ability of automatic failover.
But since a failover isn't something that you expect to occur often it will give time for the System Administrator team to fix the issue.
Anyway, it won't affect nothing on your AOG. It will continue working properly unless a failover happens but then, you can perform a manual failover instead.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Anyway, it won't affect nothing on your AOG. It will continue working properly unless a failover happens but then, you can perform a manual failover instead."

oh you mean if the WSFC die already SQL server AOG failover will still works but manual mode?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
oh you mean if the WSFC die already SQL server AOG failover will still works but manual mode?
Yes.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok. let's see. tks victor.
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.