March 6, 2018 at 11:41 am
We have two databases, one is 10TB in size and one is very highly transactional. Both have been dismissed as candidates for high-availability groups because of these qualities. The way I see it is as follows:
a. The only problem with a 10TB database in a HAG is the amount of space required in the initial build of the HAG when adding the database to the group. This space is only temporary and although a bit of a bind, still needs to be taken into consideration when designing a backup plan. That makes this a non-problem and is not a reason not to include a database of this size in a HAG.
b. A very highly transactional database in a HAG is only a problem when the database runs in synchronous mode with high latency between the endpoints. This is because every commit has to be acknowledged from the secondary before it can be committed in the primary. This leads to performance problems in databases with a high level of transactionality. To resolve this issue the database should be made highly available within the group asynchronously so that acknowledgements aren't required by the primary before they can be committed. This has the disadvantage of the possibility of lost records in the event of a failover but for two servers that as are close together as ours this possibility is so small as to be negligible.
These are the arguments that I will present in order to remove the worries and doubts our admins have with regards to putting these into HAGs.
Does anyone have any thoughts or ideas, maybe something I have missed or misunderstood?
Regards,
Kev
March 6, 2018 at 11:54 am
kevaburg - Tuesday, March 6, 2018 11:41 AMWe have two databases, one is 10TB in size and one is very highly transactional. Both have been dismissed as candidates for high-availability groups because of these qualities. The way I see it is as follows:a. The only problem with a 10TB database in a HAG is the amount of space required in the initial build of the HAG when adding the database to the group. This space is only temporary and although a bit of a bind, still needs to be taken into consideration when designing a backup plan. That makes this a non-problem and is not a reason not to include a database of this size in a HAG.
b. A very highly transactional database in a HAG is only a problem when the database runs in synchronous mode with high latency between the endpoints. This is because every commit has to be acknowledged from the secondary before it can be committed in the primary. This leads to performance problems in databases with a high level of transactionality. To resolve this issue the database should be made highly available within the group asynchronously so that acknowledgements aren't required by the primary before they can be committed. This has the disadvantage of the possibility of lost records in the event of a failover but for two servers that as are close together as ours this possibility is so small as to be negligible.
These are the arguments that I will present in order to remove the worries and doubts our admins have with regards to putting these into HAGs.
Does anyone have any thoughts or ideas, maybe something I have missed or misunderstood?
Regards,
Kev
A. Why do you need extra space? If you are already taking backups of the database, you can initialize the secondary from the existing backups. Where the issue arises is applying the logs. Assume you have a full backup, and 20 log backups to get it to the current point in time. By the time this is completed, you may then need to apply an additional 20 more logs!. A diff can make this simpler, but that will require space.
B. Valid points. Your mileage may vary. You need to test in your environment. I have one primary, and 2 secondaries. One is on the same subnet, the other is at a co-lo. The co-lo is asynchronous. The worst latency I have measured in just over a year has been about 2-3 minutes.
Good luck!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2018 at 12:08 pm
Michael L John - Tuesday, March 6, 2018 11:54 AMkevaburg - Tuesday, March 6, 2018 11:41 AMWe have two databases, one is 10TB in size and one is very highly transactional. Both have been dismissed as candidates for high-availability groups because of these qualities. The way I see it is as follows:a. The only problem with a 10TB database in a HAG is the amount of space required in the initial build of the HAG when adding the database to the group. This space is only temporary and although a bit of a bind, still needs to be taken into consideration when designing a backup plan. That makes this a non-problem and is not a reason not to include a database of this size in a HAG.
b. A very highly transactional database in a HAG is only a problem when the database runs in synchronous mode with high latency between the endpoints. This is because every commit has to be acknowledged from the secondary before it can be committed in the primary. This leads to performance problems in databases with a high level of transactionality. To resolve this issue the database should be made highly available within the group asynchronously so that acknowledgements aren't required by the primary before they can be committed. This has the disadvantage of the possibility of lost records in the event of a failover but for two servers that as are close together as ours this possibility is so small as to be negligible.
These are the arguments that I will present in order to remove the worries and doubts our admins have with regards to putting these into HAGs.
Does anyone have any thoughts or ideas, maybe something I have missed or misunderstood?
Regards,
KevA. Why do you need extra space? If you are already taking backups of the database, you can initialize the secondary from the existing backups. Where the issue arises is applying the logs. Assume you have a full backup, and 20 log backups to get it to the current point in time. By the time this is completed, you may then need to apply an additional 20 more logs!. A diff can make this simpler, but that will require space.
B. Valid points. Your mileage may vary. You need to test in your environment. I have one primary, and 2 secondaries. One is on the same subnet, the other is at a co-lo. The co-lo is asynchronous. The worst latency I have measured in just over a year has been about 2-3 minutes.
Good luck!
Hi Michael,
I only mentioned the first point because I didn't think about the existing backups! Duh........
During the build there is a freeze on data changes and to that end the transaction log backups will be stopped once the differential backup has been completed and the backup for the future HAG has been tested. If for some reason the build has to be done at another time we can consider reducing the log backup times from 10 minutes(!) to maybe 6 hours. That is something we can tackle as and when the time comes.
Thanks for your really quick answer. I feel somewhat vindicated. 🙂
Regards,
Kev
PS: 2-3 minutes.......wow!
March 6, 2018 at 12:50 pm
kevaburg - Tuesday, March 6, 2018 12:08 PMMichael L John - Tuesday, March 6, 2018 11:54 AMkevaburg - Tuesday, March 6, 2018 11:41 AMWe have two databases, one is 10TB in size and one is very highly transactional. Both have been dismissed as candidates for high-availability groups because of these qualities. The way I see it is as follows:a. The only problem with a 10TB database in a HAG is the amount of space required in the initial build of the HAG when adding the database to the group. This space is only temporary and although a bit of a bind, still needs to be taken into consideration when designing a backup plan. That makes this a non-problem and is not a reason not to include a database of this size in a HAG.
b. A very highly transactional database in a HAG is only a problem when the database runs in synchronous mode with high latency between the endpoints. This is because every commit has to be acknowledged from the secondary before it can be committed in the primary. This leads to performance problems in databases with a high level of transactionality. To resolve this issue the database should be made highly available within the group asynchronously so that acknowledgements aren't required by the primary before they can be committed. This has the disadvantage of the possibility of lost records in the event of a failover but for two servers that as are close together as ours this possibility is so small as to be negligible.
These are the arguments that I will present in order to remove the worries and doubts our admins have with regards to putting these into HAGs.
Does anyone have any thoughts or ideas, maybe something I have missed or misunderstood?
Regards,
KevA. Why do you need extra space? If you are already taking backups of the database, you can initialize the secondary from the existing backups. Where the issue arises is applying the logs. Assume you have a full backup, and 20 log backups to get it to the current point in time. By the time this is completed, you may then need to apply an additional 20 more logs!. A diff can make this simpler, but that will require space.
B. Valid points. Your mileage may vary. You need to test in your environment. I have one primary, and 2 secondaries. One is on the same subnet, the other is at a co-lo. The co-lo is asynchronous. The worst latency I have measured in just over a year has been about 2-3 minutes.
Good luck!
Hi Michael,
I only mentioned the first point because I didn't think about the existing backups! Duh........
During the build there is a freeze on data changes and to that end the transaction log backups will be stopped once the differential backup has been completed and the backup for the future HAG has been tested. If for some reason the build has to be done at another time we can consider reducing the log backup times from 10 minutes(!) to maybe 6 hours. That is something we can tackle as and when the time comes.
Thanks for your really quick answer. I feel somewhat vindicated. 🙂
Regards,
KevPS: 2-3 minutes.......wow!
2-3 minutes because of a process that updated 100 GB of records in a 450 GB database. In a loop. Written by a junior developer.
Normally, it's seconds at any point in time.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2018 at 12:53 pm
Michael L John - Tuesday, March 6, 2018 12:50 PMkevaburg - Tuesday, March 6, 2018 12:08 PMMichael L John - Tuesday, March 6, 2018 11:54 AMkevaburg - Tuesday, March 6, 2018 11:41 AMWe have two databases, one is 10TB in size and one is very highly transactional. Both have been dismissed as candidates for high-availability groups because of these qualities. The way I see it is as follows:a. The only problem with a 10TB database in a HAG is the amount of space required in the initial build of the HAG when adding the database to the group. This space is only temporary and although a bit of a bind, still needs to be taken into consideration when designing a backup plan. That makes this a non-problem and is not a reason not to include a database of this size in a HAG.
b. A very highly transactional database in a HAG is only a problem when the database runs in synchronous mode with high latency between the endpoints. This is because every commit has to be acknowledged from the secondary before it can be committed in the primary. This leads to performance problems in databases with a high level of transactionality. To resolve this issue the database should be made highly available within the group asynchronously so that acknowledgements aren't required by the primary before they can be committed. This has the disadvantage of the possibility of lost records in the event of a failover but for two servers that as are close together as ours this possibility is so small as to be negligible.
These are the arguments that I will present in order to remove the worries and doubts our admins have with regards to putting these into HAGs.
Does anyone have any thoughts or ideas, maybe something I have missed or misunderstood?
Regards,
KevA. Why do you need extra space? If you are already taking backups of the database, you can initialize the secondary from the existing backups. Where the issue arises is applying the logs. Assume you have a full backup, and 20 log backups to get it to the current point in time. By the time this is completed, you may then need to apply an additional 20 more logs!. A diff can make this simpler, but that will require space.
B. Valid points. Your mileage may vary. You need to test in your environment. I have one primary, and 2 secondaries. One is on the same subnet, the other is at a co-lo. The co-lo is asynchronous. The worst latency I have measured in just over a year has been about 2-3 minutes.
Good luck!
Hi Michael,
I only mentioned the first point because I didn't think about the existing backups! Duh........
During the build there is a freeze on data changes and to that end the transaction log backups will be stopped once the differential backup has been completed and the backup for the future HAG has been tested. If for some reason the build has to be done at another time we can consider reducing the log backup times from 10 minutes(!) to maybe 6 hours. That is something we can tackle as and when the time comes.
Thanks for your really quick answer. I feel somewhat vindicated. 🙂
Regards,
KevPS: 2-3 minutes.......wow!
2-3 minutes because of a process that updated 100 GB of records in a 450 GB database. In a loop. Written by a junior developer.
Normally, it's seconds at any point in time.
rotfl! Jeff Modens RBAR (Row by Agonising Row) seems to summarise that well!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply