Share via

Two node Always On cluster setup

Carlos D 46 Reputation points
2023-02-03T05:58:02.5366667+00:00

Hi All,

SQL Server 2019 - CU18

Windows 2019 - 2 Node Cluster.

8 CPU Cores - 96GB RAM --> Would this CPU and RAM be sufficient ?

Have around 120 databases with total size of 350GB and a very busy processing with over 2000 connections to the databases at any given time.

1- Is is better to setup 2 Always On group and have 1 group run on one node and the second group run on another node splitting the databases 60 database on each Always On group ?

or

Have the 2 Always On group with 60 database on each group but run on the same node leaving the second node only as the replica ?

2- Would it help to set the trace flag -T3459 to avoid parallel redo operations to have the replica databases be in sync faster when failed over thus reducing the downtime or time to recover databases on replica ?

Thanks for any advice.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,363 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,461 Reputation points
    2023-02-03T07:04:06.28+00:00

    Hi @Carlos D,

    Question 1:
    Yes. It's better. You can also add another node if your situation allows it. The ideal environment is to have a primary replica on one node and only secondary replicas on the last node.

    Node1: primary replica secondary replica

    Node2: secondary replica primary replica

    Node3: secondary replica secondary replica

    Question 2:

    Yes. Trace Flag 3459 disables parallel redo without a SQL restart. Please refer to this blog: IRL #6: Availability Group Performance

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

  2. Erland Sommarskog 107.2K Reputation points
    2023-02-03T22:49:56.9566667+00:00

    8 CPU Cores - 96GB RAM --> Would this CPU and RAM be sufficient ?

    That is very difficult to say. But with a total size 6 times exceeding that RAM, and many databases that are busy, I am not optimistic. Had it been a single databases, we could assume that there are archive tables that are not touched that often. But with these small databases, maybe the entire database should be in cache. Which it can't be if there only space for one sixth of them.

    1- Is is better to setup 2 Always On group and have 1 group run on one node and the second group run on another node splitting the databases 60 database on each Always On group ?

    This is appealing since you are spreading the load, and you don't have a machine that is idle. Also, now you are only under-provision by factor of three, instead of six.

    On the other hand, if there is a failover, all databases will be on the same node, which then could start to crumble. So you would need to test that single node can take all databases and at least be functional, if slow.

    In any case, my gut feeling is that you should have 400 GB of RAM, possibly split over the two nodes.