marrowyung
asked on
number of database can be host by SQL 2019 enterprise AG
Hi,
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ähm...yes, for around 32.000 databases you may need some racks with harddisc...
Each application has it technical limits somewhere, but most of them doesn't play a practical role, because you can't really reach them..., for the moment...
So you can enlarge your discs as long as your file storage system doesn't set other limits, and if you reach these limits, you can attach new drive spindels until you ran out of drive letters.
But as David also said...
a single traditional harddisc can handle around 200 IO/s, a common SSD more than 50.000 IO/s. So SSDs are more or less mandatory for the data drives to provide enough IOs to handle all your DBs.
And as moreDBs you host, as more RAM memory you need. But it depends from the size of the databases.
>active active
this means first at all that you create more than one AAGs and each node can be active for one of them while the other node is passive. So Node1 can be active For AAG1 and passive for AAG2, while Node2 is passive for AAG1 and active for AAG2
According constructions, see
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15
Each application has it technical limits somewhere, but most of them doesn't play a practical role, because you can't really reach them..., for the moment...
So you can enlarge your discs as long as your file storage system doesn't set other limits, and if you reach these limits, you can attach new drive spindels until you ran out of drive letters.
But as David also said...
a single traditional harddisc can handle around 200 IO/s, a common SSD more than 50.000 IO/s. So SSDs are more or less mandatory for the data drives to provide enough IOs to handle all your DBs.
And as moreDBs you host, as more RAM memory you need. But it depends from the size of the databases.
>active active
this means first at all that you create more than one AAGs and each node can be active for one of them while the other node is passive. So Node1 can be active For AAG1 and passive for AAG2, while Node2 is passive for AAG1 and active for AAG2
According constructions, see
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15
ASKER
tks.
The link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15
is not saying anything like active active, AGs never focus on active active on the SAME AGs, 2 x write master is dangerous !
The link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15
is not saying anything like active active, AGs never focus on active active on the SAME AGs, 2 x write master is dangerous !
The link is not related to active active at all.
I posted ther link, because you were talkinga about merge replication...
active active, again, what I wrote before...
this means first at all that you create more than one AAGs and each node can be active for one of them while the other node is passive. So Node1 can be active For AAG1 and passive for AAG2, while Node2 is passive for AAG1 and active for AAG2
If you replace active by primary and passive with secondary, then you are in the wording of the article.
Not sure what should be dangerous.
I posted ther link, because you were talkinga about merge replication...
active active, again, what I wrote before...
this means first at all that you create more than one AAGs and each node can be active for one of them while the other node is passive. So Node1 can be active For AAG1 and passive for AAG2, while Node2 is passive for AAG1 and active for AAG2
If you replace active by primary and passive with secondary, then you are in the wording of the article.
Not sure what should be dangerous.
ASKER
Not sure what should be dangerousif it is like that,: So Node1 can be active For AAG1 and passive for AAG2, while Node2 is passive for AAG1 and active for AAG2 , it is not in dangerous! but is more than one node in the single AAG is write master, this is dangerous !
your active active is not about merge replication but that is 2 x different AAG, right?
I am referring to WITHIN single AAG.
Yes sure, 2 different AAGs. Inside one AAG, there is only one primary node,all others are secondary.
ASKER
tks.
ASKER
As David said, is it hardware limitation? so if I keep extending the hardware, like disk, is ok ?
" Always on can be setup as active - passive (with one AAG) or active - active (with two or more AAGs) so you can split traffic onto both nodes. You will run out of disk iops, and memory first."
in this active active, is that mean 2 x AGs and link together via merge replication ?
David,
but the link from Bembi seems saying it is SW limit! even for SQL enterprise .. .?
" You will run out of disk iops, and memory first. "
can limit the RAM it use.....