marrowyung
asked on
MS SQL AAG vs FCI
hi,
I heard about MS SQL Always on group and FCI, FCI is shared storage failover cluster and it is old school, right?
and what is AAG ? or is it simply means AOG ? other than AAG, what else name SQL server always on group has ?
why this link : https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/
seems say FCI also always on ? just new name ? and it seems AOFCI can allow 2 x nodes to form a cluster but AAG cna't ?which need 3 x nodes to form!
I heard about MS SQL Always on group and FCI, FCI is shared storage failover cluster and it is old school, right?
and what is AAG ? or is it simply means AOG ? other than AAG, what else name SQL server always on group has ?
why this link : https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/
seems say FCI also always on ? just new name ? and it seems AOFCI can allow 2 x nodes to form a cluster but AAG cna't ?which need 3 x nodes to form!
ASKER
but you say FCI does not allow, right?
so what is this means: Redundant data allow e.g. switching the failed database instance to a replica in seconds so users do not even notice it.?
and it only means no data replicate to other server at all and only instance level failover, right? but single storage only and data only has one copy .
so what is this means: Redundant data allow e.g. switching the failed database instance to a replica in seconds so users do not even notice it.?
and it only means no data replicate to other server at all and only instance level failover, right? but single storage only and data only has one copy .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"FCI means redundancy for the SQL engine (software and computers where this software is running) but not for data. FCI requires shared storage where just one copy of the data resides."
yeah !
but
how valuable it is ? I may wish to have more than one copy of the database in other location .
also form this link:https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/, I don't understand this":
the naming problem ! in my mind only FCI and AOG, and I am now thinking there are no AOG but AAG, right?
yeah !
but
FCI does not allow redundant data which is the most valuable part of the solution today.
how valuable it is ? I may wish to have more than one copy of the database in other location .
also form this link:https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/, I don't understand this":
In nutshell:
AlwaysOn = {SQL Server Failover Cluster Instances, Availability Groups}
AlwaysOn != SQL Server Failover Cluster Instances != Availability Groups
Availability Groups != Database Mirroring
WSFC != SQL Server Failover Cluster Instances
the naming problem ! in my mind only FCI and AOG, and I am now thinking there are no AOG but AAG, right?
Of course, if we are talking about one copy of the data it still can be stored in RAID which ensures data redundancy internally so if one or two discs fail you may replace them w/o data loss or even w/o work interruption if you have hot swappable drives in the RAID.
ASKER
but
how valuable it is ? I may wish to have more than one copy of the database in other location .
also form this link:https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/, I don't understand this":
the naming problem ! in my mind only FCI and AOG, and I am now thinking there are no AOG but AAG, right?
FCI does not allow redundant data which is the most valuable part of the solution today.
how valuable it is ? I may wish to have more than one copy of the database in other location .
also form this link:https://www.mssqltips.com/sqlservertip/4717/what-is-sql-server-alwayson/, I don't understand this":
In nutshell:
AlwaysOn = {SQL Server Failover Cluster Instances, Availability Groups}
AlwaysOn != SQL Server Failover Cluster Instances != Availability Groups
Availability Groups != Database Mirroring
WSFC != SQL Server Failover Cluster Instances
the naming problem ! in my mind only FCI and AOG, and I am now thinking there are no AOG but AAG, right?
If you wish to have database copy in another location then you may do it several different ways. The cheapest and the slowest solution are regular backups. Then you may read about replication which can be asynchronous or synchronous with many variants and then the most expensive solution is named HA DR solution where synchronous copies of the database are maintained at two or more locations.
The price is related to the requested recovery time and accepted data loss during the recovery. If you accept 10 minutes data loss with several hours downtime during the recovery then backups are sufficient for you.
The price is related to the requested recovery time and accepted data loss during the recovery. If you accept 10 minutes data loss with several hours downtime during the recovery then backups are sufficient for you.
I cannot say how valuable are your data.
The naming problem exists. Some names are related to OS others are for SQL Server and they are changing during the time. Just look at the article date first and then read the contents. I would guess wiki pages are good starting point here.
The naming problem exists. Some names are related to OS others are for SQL Server and they are changing during the time. Just look at the article date first and then read the contents. I would guess wiki pages are good starting point here.
Basic explanation of High Availability terms: https://www.tutorialspoint.com/ms_sql_server/ms_sql_server_ha_technologies.htm
Documents for deep learning: https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver15
Documents for deep learning: https://docs.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr?view=sql-server-ver15
ASKER
"I cannot say how valuable are your data."
what i meant is I am not sure how goods is FCI. but data commit can be fast as no replication at all.
but FCI and AAG can't exists on the same AAG, right?
what i meant is I am not sure how goods is FCI. but data commit can be fast as no replication at all.
but FCI and AAG can't exists on the same AAG, right?
AAG does not mean commits slowness. Depends on the topology used. If you have synchronous replica behind the slow network then AAG is slow. If you have data storages on the same network then commits are not delayed.
If you install several SQL Server instances then you may use FCI for two of them and AAG for the other two. Such a mix is not obvious.
If you install several SQL Server instances then you may use FCI for two of them and AAG for the other two. Such a mix is not obvious.
ASKER
"If you install several SQL Server instances then you may use FCI for two of them and AAG for the other two.
you are saying there are 2 x Alwayson Group, one is FCI and other other is AAG, but they are not connect to each other right? they are 2 x clusters, rihgt?
but seems AAG need 3 x nodes to operate the cluster/group, right? node majority, right?
you are saying there are 2 x Alwayson Group, one is FCI and other other is AAG, but they are not connect to each other right? they are 2 x clusters, rihgt?
but seems AAG need 3 x nodes to operate the cluster/group, right? node majority, right?
Yes, you may create as many Availability Groups as the OS allows. AGs are created as individual roles within a (Windows) cluster.
AAG does not need 3 nodes, just two - one primary and one replica. Of course, you can have more replicas.
AAG does not need 3 nodes, just two - one primary and one replica. Of course, you can have more replicas.
ASKER
"AAG does not need 3 nodes, just two - one primary and one replica. Of course, you can have more replicas."
tks. but all cluster with nodes majority needs 3 nodes to operate safely !
here a lot of company use 3 x nodes per AAG, 2 in primary site and 1 in DR site.
there will be problems if we setup 2 x nodes only, right?
"Yes, you may create as many Availability Groups as the OS allows. AGs are created as individual roles within a (Windows) cluster."
I mean FCI and AAG together in the SAME group, can we ? seems not possible as their technology is different !
tks. but all cluster with nodes majority needs 3 nodes to operate safely !
here a lot of company use 3 x nodes per AAG, 2 in primary site and 1 in DR site.
there will be problems if we setup 2 x nodes only, right?
"Yes, you may create as many Availability Groups as the OS allows. AGs are created as individual roles within a (Windows) cluster."
I mean FCI and AAG together in the SAME group, can we ? seems not possible as their technology is different !
I am not able to answer the last question as I did not try it yet. But if you have more SQL Server instances then you may setup some of them as FCI with shared storage and place other ones into AAG. To have just one SQL Server instance in both FCI and AAG does not make sense.
1. Nodes (computers). FCI can have 2,3,... nodes. AOG Can have 2,3,... nodes. One node is active, the other node(s) are passive.
2. Disks. FCI have the only set of data disks. AOG have as many copies of disk set as it has nodes.
3. Read/Write node. FCI and AOG has the only Read/Write node.
4. Read Only nodes. FCI does not have Read Only node. AOG has read only node(nodes). AOG can use it for backup or read/only access (additional MS SQL license is required).
5. Switch active node. FCI switch is slower - it depends from disk types. Solid State System Disk makes switch time almost the same.
6. Current Transaction. It must be either completed or rolled back. It could take significant time as for FCI as for AOG (longer than (5)).
7. Resource utilization. Between switches FCI works as a stand alone instance. AOG has continuous stream of changes between nodes. It takes ~15% of CPU and continuosly uses cluster network between nodes. High load of changes can block/delay the data stream (synchronization) between nodes.
8. Disk storage. If disk storage has protection like RAID or Virtual Disk environment, then FCI is quite reliable. If nodes are physical computers with physical disks then AOG plays role of RAID keeping 2+ copies of disks for the price of (7).
Bottom Line - because of (7) FCI has better performance for high load.
2. Disks. FCI have the only set of data disks. AOG have as many copies of disk set as it has nodes.
3. Read/Write node. FCI and AOG has the only Read/Write node.
4. Read Only nodes. FCI does not have Read Only node. AOG has read only node(nodes). AOG can use it for backup or read/only access (additional MS SQL license is required).
5. Switch active node. FCI switch is slower - it depends from disk types. Solid State System Disk makes switch time almost the same.
6. Current Transaction. It must be either completed or rolled back. It could take significant time as for FCI as for AOG (longer than (5)).
7. Resource utilization. Between switches FCI works as a stand alone instance. AOG has continuous stream of changes between nodes. It takes ~15% of CPU and continuosly uses cluster network between nodes. High load of changes can block/delay the data stream (synchronization) between nodes.
8. Disk storage. If disk storage has protection like RAID or Virtual Disk environment, then FCI is quite reliable. If nodes are physical computers with physical disks then AOG plays role of RAID keeping 2+ copies of disks for the price of (7).
Bottom Line - because of (7) FCI has better performance for high load.
ASKER
one thing , as AAG has user database copy to many different replica, how about user account in master DB, master DB is not part of user database, if I add one user account in one of the SQL server, it is not going to sync to the rest of the SQL server in the AAG, right?
how we handle this situation ?
how we handle this situation ?
ASKER
pcelba,
"To have just one SQL Server instance in both FCI and AAG does not make sense."
for me it make sense ! as I get a question from an interviewer and he said if AAG can't fit into our requirement as data replication will lag behind while in FCI it will not! how can we design the HA and DR for this case!
I said must use SAN replication in this case ! but this one, as it is FCI, we need 2x differenet FCI and therefore 2 x SQL clusers ! application then needs to take care failover.
so I am thinking about can be done based on FCI on HA and DR.
Andrei Fomitchev,
"3. Read/Write node. FCI and AOG has the only Read/Write node."
yeah! but always only one write master ! this is database technology need to keep ACID
"5. Switch active node. FCI switch is slower - it depends from disk types. Solid State System Disk makes switch time almost the same.'
good point, it not also depends on the Disk but DB size, as during failover, SQL instance offload a lot of User database and then the passive load them up so all user database mount again on the new primary, this takes a lot of time.
"~15% of CPU and continuosly uses cluster network between nodes. High load of changes can block/delay the data stream (synchronization) between nodes.:
take 15% CPU to keep more than one copy of DB worth! and yes the replication actually even sync mode still have data lag behind ! FCI do not have this problem as the logical and physical storage is the SAME unit! no need replication at all!
FCI, Stroage is the SPOF!
"Bottom Line - because of (7) FCI has better performance for high load"
yeah , no need replicatoin at all ! the more replica the slower it is !
oracle RAC has the same WRITE and READ problem! it ask all RAC nodes on if the current nodes can write to that cell before it happens ! so the more RAC nodes the slower it is !
usually here 3 x AAG node on production server is common here! more DB copies enough to keep protection and speed lag behind due to replication is minimized !
"Nodes (computers). FCI can have 2,3,... nodes. AOG Can have 2,3,... nodes. :"
from some kind of articles, 2 is enought.
but actually all MS doc I read said 3 is minimum for AAG! FCI 2x nodes is ok as the storage is the SAME. no need other storage to standby and get the copy !
"To have just one SQL Server instance in both FCI and AAG does not make sense."
for me it make sense ! as I get a question from an interviewer and he said if AAG can't fit into our requirement as data replication will lag behind while in FCI it will not! how can we design the HA and DR for this case!
I said must use SAN replication in this case ! but this one, as it is FCI, we need 2x differenet FCI and therefore 2 x SQL clusers ! application then needs to take care failover.
so I am thinking about can be done based on FCI on HA and DR.
Andrei Fomitchev,
"3. Read/Write node. FCI and AOG has the only Read/Write node."
yeah! but always only one write master ! this is database technology need to keep ACID
"5. Switch active node. FCI switch is slower - it depends from disk types. Solid State System Disk makes switch time almost the same.'
good point, it not also depends on the Disk but DB size, as during failover, SQL instance offload a lot of User database and then the passive load them up so all user database mount again on the new primary, this takes a lot of time.
"~15% of CPU and continuosly uses cluster network between nodes. High load of changes can block/delay the data stream (synchronization) between nodes.:
take 15% CPU to keep more than one copy of DB worth! and yes the replication actually even sync mode still have data lag behind ! FCI do not have this problem as the logical and physical storage is the SAME unit! no need replication at all!
FCI, Stroage is the SPOF!
"Bottom Line - because of (7) FCI has better performance for high load"
yeah , no need replicatoin at all ! the more replica the slower it is !
oracle RAC has the same WRITE and READ problem! it ask all RAC nodes on if the current nodes can write to that cell before it happens ! so the more RAC nodes the slower it is !
usually here 3 x AAG node on production server is common here! more DB copies enough to keep protection and speed lag behind due to replication is minimized !
"Nodes (computers). FCI can have 2,3,... nodes. AOG Can have 2,3,... nodes. :"
from some kind of articles, 2 is enought.
but actually all MS doc I read said 3 is minimum for AAG! FCI 2x nodes is ok as the storage is the SAME. no need other storage to standby and get the copy !
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"If the user exists in master DB on AAG then such user exists in all master databases on all r/o replicas."
so Master login also got replicated ? that one is system DB but not user DB.
"FCI cannot have lags in data replication because is has just one copy of data. If the disk subsystem crashes you cannot recover from such failure except the backup restore"
yes ! as I said I prefer AAG as they protect data better by keep more than one copy !
"The lag in AAG depends on the network topology and if we are talking about one data center then the lag is almost zero and users do not notice it."
you mean all DB in the AAG in the same DC ? and that one is sync mode? as Andrei said the more busy is the system on changing data, the more chance data lag behind.
"Switching between two instances in one data center is also very fast and users do not notice it."
most likely if in the SAME DC as network very fast but if data change a lot ! data change seems in ms across all nodes !
so Master login also got replicated ? that one is system DB but not user DB.
"FCI cannot have lags in data replication because is has just one copy of data. If the disk subsystem crashes you cannot recover from such failure except the backup restore"
yes ! as I said I prefer AAG as they protect data better by keep more than one copy !
"The lag in AAG depends on the network topology and if we are talking about one data center then the lag is almost zero and users do not notice it."
you mean all DB in the AAG in the same DC ? and that one is sync mode? as Andrei said the more busy is the system on changing data, the more chance data lag behind.
"Switching between two instances in one data center is also very fast and users do not notice it."
most likely if in the SAME DC as network very fast but if data change a lot ! data change seems in ms across all nodes !
OK, it seems you are ready!
ASKER
hi,
so FCI and AAG can't setup together and the only thing we can do is to, e.g., primary site we have FCI and DR site we have a separate AAG, they don't know each other.
then we setup SAN replication between 2x clusters and application needs to handle failover ?
so FCI and AAG can't setup together and the only thing we can do is to, e.g., primary site we have FCI and DR site we have a separate AAG, they don't know each other.
then we setup SAN replication between 2x clusters and application needs to handle failover ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Andrei Fomitchev,
"Sun replication does not have any relation to FCI or AOG"
SAN replication right ?
"We used PowerShell (or TSQL script of Login) to create Logins/Users with the same SID on every SQL Cluster Node"
so you share the script ? download site also welcome. I am not sure why MS seems do not takes care this problem when they design AAG.
if it is SQL login, no need to take care SID, right? but if we create AD login ACROSS all SQL server it should be ok ? they link to the SAME AD, right?
and I also tell the people who ask me this what if we create a user database contain user login name across the AAG, then it should be fine ?
"SQL FCI cluster must be installed on Windows cluster. When you failover FCI it fails over Windows cluster as well."
AAG also depends on Windows AD cluster or it can't failover automatically !
"Regular failover of AOG starts from SSMS Cluster management or by TSQL script"
I don't know that as all AOG I setup can auto failover, probably I setup as 3 x nodes AAOG.
"AOG will recover, but some data could be lost.
"
in Asycn mode? if data could be lost then it is not a good tech...
"Sun replication does not have any relation to FCI or AOG"
SAN replication right ?
"We used PowerShell (or TSQL script of Login) to create Logins/Users with the same SID on every SQL Cluster Node"
so you share the script ? download site also welcome. I am not sure why MS seems do not takes care this problem when they design AAG.
if it is SQL login, no need to take care SID, right? but if we create AD login ACROSS all SQL server it should be ok ? they link to the SAME AD, right?
and I also tell the people who ask me this what if we create a user database contain user login name across the AAG, then it should be fine ?
"SQL FCI cluster must be installed on Windows cluster. When you failover FCI it fails over Windows cluster as well."
AAG also depends on Windows AD cluster or it can't failover automatically !
"Regular failover of AOG starts from SSMS Cluster management or by TSQL script"
I don't know that as all AOG I setup can auto failover, probably I setup as 3 x nodes AAOG.
"AOG will recover, but some data could be lost.
"
in Asycn mode? if data could be lost then it is not a good tech...
ASKER
hi,
any update for me on:
?
for me I just thought using AD login for user and create a separate user login table in the user database and let application to access that table when an user login via application, and the application will validate the user based on the record in that table.
other suggestion is good .
any update for me on:
so you share the script ? download site also welcome. I am not sure why MS seems do not takes care this problem when they design AAG.
if it is SQL login, no need to take care SID, right? but if we create AD login ACROSS all SQL server it should be ok ? they link to the SAME AD, right?
and I also tell the people who ask me this what if we create a user database contain user login name across the AAG, then it should be fine ?
?
for me I just thought using AD login for user and create a separate user login table in the user database and let application to access that table when an user login via application, and the application will validate the user based on the record in that table.
other suggestion is good .
ASKER
Andrei Fomitchev,
please teach me how to do it.
We used PowerShell (or TSQL script of Login) to create Logins/Users with the same SID on every SQL Cluster Node.
Is this your solution?
please teach me how to do it.
Just a note: The last wish is a little bit outside the scope of this question but the answer could become a search term here on EE. Please create a new question asking for this script.
ASKER
tks all for it.
ASKER
in order to have a complete picture of it, I need to know how to make this answer more finished. please help.
Just place a link to the successive question here.
ASKER
sorry ?
OK, it seems I did not understand your question "how to make this answer more finished".
This whole question was about the AAG and FCI differences which have been described and the question is finished by selecting some of the answers as the solution. From that point we are done here.
Then you've asked for the script "to create Logins/Users with the same SID on every SQL Cluster Node" and I've proposed to create a new question for it at EE because the script is outside of the scope of this question. In other words the script is a rather different topic than the AAG vs. FCI differences.
And my last wish was to place a link to your new question here which makes it easier for other EE readers to find what they could possibly be interested in.
Did you ask the new question already?
This whole question was about the AAG and FCI differences which have been described and the question is finished by selecting some of the answers as the solution. From that point we are done here.
Then you've asked for the script "to create Logins/Users with the same SID on every SQL Cluster Node" and I've proposed to create a new question for it at EE because the script is outside of the scope of this question. In other words the script is a rather different topic than the AAG vs. FCI differences.
And my last wish was to place a link to your new question here which makes it easier for other EE readers to find what they could possibly be interested in.
Did you ask the new question already?
ASKER
ASKER
hi,
Please give some suggestion to my new question .
Please give some suggestion to my new question .
Redundant data allow e.g. switching the failed database instance to a replica in seconds so users do not even notice it.