SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restrictions Regarding High Availability Groups


Restrictions Regarding High Availability Groups

Author
Message
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16468 Visits: 1284
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
Michael L John
Michael L John
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22064 Visits: 10562
kevaburg - Tuesday, March 6, 2018 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

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/
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16468 Visits: 1284
Michael L John - Tuesday, March 6, 2018 11:54 AM
kevaburg - Tuesday, March 6, 2018 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

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!


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. Smile

Regards,
Kev

PS: 2-3 minutes.......wow!
Michael L John
Michael L John
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22064 Visits: 10562
kevaburg - Tuesday, March 6, 2018 12:08 PM
Michael L John - Tuesday, March 6, 2018 11:54 AM
kevaburg - Tuesday, March 6, 2018 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

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!


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. Smile

Regards,
Kev

PS: 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/
kevaburg
kevaburg
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16468 Visits: 1284
Michael L John - Tuesday, March 6, 2018 12:50 PM
kevaburg - Tuesday, March 6, 2018 12:08 PM
Michael L John - Tuesday, March 6, 2018 11:54 AM
kevaburg - Tuesday, March 6, 2018 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

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!


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. Smile

Regards,
Kev

PS: 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search