0

Can I have Always-on availability group primary node not identical to secondary? example: Primary Replica RAM 64, Cores 8 Secondary Replica RAM 256, Cores 32

As I estimate read-only queries to be 4 times writes, and I will use read routing to secondary I found a recommendation to have both always-on replicas to be comparable, and I don't know if it is a recommendation, or it means identical Thank you

2 Answers 2

1

It is supported, the recommendation from the docs is:

For a given availability group, all the availability replicas should run on comparable systems that can handle identical workloads.

Recommendations for Computers That Host Availability Replicas

Having a larger system host a secondary replica would qualify, as both systems can handle the workload. This is really no different than having multiple readable replicas.

In your estimate of read/write split, make sure you understand that even for a synchronous replica the redo of the log is asynchronous, and so the secondary will always be slightly behind the primary. Any read-only query that cannot tolerate this latency must be run against the primary. EG in an application if you write data, and then immediately query data to display to the user, expecting to see the data you just wrote, both queries must hit the primary.

Note that in case of a failure of the secondary, read-only routing will direct all traffic to the primary, which it may not be capable of handling. So if you do this you may want to have reporting workloads connect to the secondary replica directly, instead of through the Availability Group Listener.

Also note that the IO requirements of a secondary are sometimes more than the primary, as the secondary is writing the database through the redo process, while the primary is writing the database through checkpoint/lazy writer.

5
  • thank you We will use ApplicationIntent=readOnly with reporting /Bi , not ad-hoc queries that needs up-to-second data of course as you stated
    – Amr Waheed
    Apr 2, 2020 at 15:38
  • the point related to sizing secondary for reporting workload is there a method to measure on current standalone instance before having new HW for AOAG, the queries qualified for this, and capture its IO, CPU, Memory, so it could be input for sizing secondary thank you again
    – Amr Waheed
    Apr 2, 2020 at 15:41
  • what exactly: " even for a synchronous replica the redo of the log is asynchronous, and so the secondary will always be slightly behind the primary.", this as in microsoft document " The secondary database remains SYNCHRONIZED as long as data synchronization continues. This guarantees that every transaction that is committed on a given primary database has also been committed on the corresponding secondary database. When every secondary database on a given secondary replica is synchronized, the synchronization-health state of the secondary replica as a whole is HEALTHY"
    – Amr Waheed
    Apr 2, 2020 at 22:12
  • "committed" means saved on disk in the log file. On the secondary transactions become visible to readers only after the committed transactions are applied to the database by the REDO process. Apr 2, 2020 at 22:36
  • Thank you again, I see details about your valuable input here : learn.microsoft.com/en-us/sql/database-engine/…
    – Amr Waheed
    Apr 2, 2020 at 23:18
0

The reason why the doc says all the nodes should be same spec - hardware and software wise is that when a failover happens, the secondary should be able to handle the workload normally. In your case, secondary seems to have more resources, so it should be fine.

I would still go with same specs as the plan choice (and query performance) also depends on Max memory setting and maxdop settings to some extent.

Also, licensing will impact you .. the more cores .. the more costly it gets !

1
  • so if i set maxmemory for primary as 56 GB, and for secondary 200 GB and cores on primary instance 16, and on secondary 64, it will work with no error, nor limitation on each other , could you tell me where in Microsoft documents I could find such info state it will work fine
    – Amr Waheed
    Apr 2, 2020 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.