Windows cluster setup + SQL server 2019 AG of 2 x nodes.

1) For WSFC, I'd recommend https://www.youtube.com/watch?v=xJ_WJ_p0qjk&list=PLiwKZO7LRtPByaV3Ngkj7yJVgFabqfjf0
Though it is for active-active FCI, you can check out the Windows Cluster setup portion
2) For AG, it is rather straight forward, there are many out there, just list one
https://www.youtube.com/watch?v=C_upGmeTjz0 

arnold ,

Did you not setup a failover clusters into an AG?

building AG need cluster servivce, we have to build that first before AG, e..g when you

https://social.msdn.microsoft.com/Forums/en-US/c2b319fb-2f66-471f-a3d1-4e9137baf565/two-single-node-clusterag-with-distributed-availability-group 

seems not !  

I want to revise the steps to buidl AG afrom scatch .starting from Windows cluster. then SQL server , then AG>

waynezhu ,

2) For AG, it is rather straight forward, there are many out there, just list one

it should be, what I recalled is, just say SQL server need to be in the AG group, this is it.. but I really forgot.

Once you receive your pair of servers, the WSFC (no need shared drives) should have already been installed by another team.

I know, but this time it is not, that's why I am asking that, I cant' see why ....

Install SQL Server on each node and enable Always On feature by selecting the cluster.

yes!

Create database and build AG.

yes.

but didn't do it for a long long time...

this is":

SQL Server 2019 Cluster - 1 : Installation of Win Server 2019 - YouTube 

active active cluster, MS do not have active /active cluster, right?

and this one:

https://www.youtube.com/watch?v=C_upGmeTjz0   SQL Server 2019 Failover Cluster Configuration on Windows Server 2019, it seems saying it is only for FCI cluster but not AG, right?
it need a quorum drive on each SQL Server? 

waynezhu ,

You are right. For some reason, "active-active" is used in the SQL world, it is really active-passive in nature.

write and read only at different node at the same tme...


arnold ,

You then combine the single node clusters as part of AG.

yes.

I think this youtube is close to that: https://www.youtube.com/watch?v=nPQxD5kBuLQ

one thing, do each node need a drive for quorum when building cluster?

File share witness requirements

so any network share.....

but if we don't have files share in the network, only 1 x quorum disk for each node is possilbe? and what is the recommended size of the quorum disk?

how can I check the quorum path from failover cluster manager?

tks. that one is not under SQL server and I have a check on the other failover cluster (someone created before) I don't see where is the quorum configuration is.

actually if we have a UAT SQL server standalone setup, can it convert to a cluster , AG group ? just install one more nodes and then install cluster service on each and create cluster?

If odd number of nodes, may not have to create quorum.

node majority ...

even if even number of nodes, people may forget configure quorum.

can't auto failover right?

and I click on that and it shows me:


and then

is it say that this AG do not have cluster quorum defined ? it seems do not have the option for us to change existing configuration at all.

for another AG we have long time ago, it seems don't even have cluster created at all but SQL AG operate for a long time.

Cluster is not absolute necessary for AG

what good is AG with cluster and without .. ?

I recalled that AG with cluster can't autofailover.. .?

>> what good is AG with cluster and without .. ?
It depends. AG aka HADR, it is HA + DR.
In some case, we even do not need HA.
For example, I can migrate a DB on Windows from premise to cloud on Linux using AG.

>> can't auto failover right?
It depends. If the primary node down, it will auto failover.
The difficult is the split-brain situation.

In some case, we even do not need HA.

in our case we need , so need cluster I belive

yeah, I forget since what SQL server version we don't need AG but what I saw is , can't auto failover.

, I can migrate a DB on Windows from premise to cloud on Linux using AG.

why is it related to cluster?

waynezhu ,

The difficult is the split-brain situation.

split brain is handled by 3 x node. perfectly speaking, one nodes on each site.. right?
node majority... 

DB migration   of course do not need HA.

 distributed AG you mean is SQL data replication to another primary in another AG ?

DB migration and distributed AG do not need HA,

you mean you only use AG to replicate everything to the new DB server you want to migrate to and that new DB server temporary join AG for this purpose.

once replicated, you remove that one from the AG?

One cluster across two centers or two clusters sparately
https://www.dcac.com/microsoft/azure/distributed-availability-groups-the-good-the-bad-and-the-ugly/

yeah, but still one primary across the group.
for us, seems still need quorum as we need HA. it also need DR failover later.

It depends. If the primary node down, it will auto failover.

it seems MS want to separate cluster from AG ...? no need cluster anymore?

Cluster is not absolute necessary for AG
https://www.sqlshack.com/read-scale-availability-group-in-a-clusterless-availability-group/ 

it say:

SQL Server failover clustering increases the complexity to deploy and sometimes we do not want to configure it. For example, suppose we have a system in the demilitarized zone (DMZ) for reporting purpose. We do not want to configure failover clustering as it involves opening multiple ports between networks.

Select allOpen in new window

the concern in that article cluster is hard to setup....

Actually for real scale ONLY we don't need. but usually as a total solution for HA, in a single site we need ....

Microsoft's AlwaysOn AG is very feature rich and meets all kinds of needs if you compare it with other RDBMS counterpart products. To my opinion:
Oracle's data guard is strong on DR portion but weaker on HA portion.
Db2's HADR is strong on HA portion but weaker on DR portion.

For your case, just keep it simple: WSFC with Quorum without shared storage + AG, that's good enough.

actually when doing cluster check I have this message:



and it is a warning.. usually 1 x NIC is ok and we can ignore it ?

For your case, just keep it simple: WSFC with Quorum without shared storage + AG, that's good enough.

yes with Quorum ! without shared storage means file share?

2 NIC is more common: 1 for connection and another for heart beat.

yes, usually.

Shared storage is for SQL Server FCI
For AG, do not need it.

yes. that concerpt

Unless you want to build a AG between a FCI and a standard alone SQL.

you tried that before FCI + single node as a AG is working fine for all HA + DR case, even read reroute/scale out ?

yes, but NOT in production though, FCI + single node architecture-wise same as single node + single node
It is well documented in https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-2017
Also see a diagram in https://clusteringformeremortals.com/2019/03/13/achieving-sql-server-ha-dr-with-a-mix-of-always-on-availability-groups-and-sanless-sql-server-failover-cluster-instances/

 architecture-wise same as single node + single node

exactly, as what else.. but are they see as the SAME SINGLE cluster ? FCI must need a cluster...

now I have SQL server on each node but one interesting thing is, why in SSMS I can't connect to the other node? ping the other AG member is ok.

e.g.