Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
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 !
