We have 2 x SQL 2019 enterprise database connect to each other using linked server and query get data between them IN ORDER TO balance the loading, it not a good method as query thought linked server is ALWAYS slow.
now each server host 25x user database on AWS and user report it is slow from time to time suddently, o we
if I combine 2 x SQL server to single SQL server 2019 always on while running in SQL 2017 compatibility mode, is one single SQL server 2019 AG can operate 500 user database while sync with other peer ?
I just plan to have 2 x sync SQL node at this moment and separate read and write operation can be spilt between nodes.
or what is other way you can suggest and I will also look at how to tune SQL server in AWS.
we are also considering to upgrade AWS instance type as recommended by Amazon.