October 2, 2020 at 8:35 am
Hi, all
I understand that in always-on availability group, synchronous mode commit in almost near time. and for asynchronous mode it is not.
How we can determine how long the asynchronous mode is fall behind the primary database? Is there any configuration can be set to like performing commit every 10min or 1hour?
Thank you
October 2, 2020 at 2:39 pm
There is automatic seeding and lag time. Can you be more specific as to what you need?
October 2, 2020 at 4:30 pm
You can review the AG dashboard - there are 2 queues that you want to look at...the send queue and the redo queue. The send queue is from the primary system and shows how much data needs to be sent - and the redo queue shows how much data needs to be applied on the secondary.
There is no facility for creating a lag in an AG - the only reason asynchronous will fall behind will be the number of transactions and amount of data modified on the primary, the network between primary and secondary - and how fast the secondary can apply those changes.
Note: in synchronous mode - SQL Server will switch to asynchronous if the system falls far enough behind and exceeds the threshold. At that point, the system will work in asynchronous mode until the system has caught up.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 5, 2020 at 3:46 am
Hi Jeffrey,
thank you for the reply.
So in short, can I conclude that the latency between the primary and secondary in asynchronous commit is affected by the network bandwidth and the resources on the secondary?
One side question, I added column on the AG dashboard to view the send queue and redo queue, but both show empty even I tried to add new table. What can I do(test) to have the visibility?
Thanks and Regards,
Jia Kai
October 5, 2020 at 6:55 pm
There is more than just latency - but in general, yes - the latency between the primary and secondary will affect the availability group regardless of asynchronous/synchronous.
If your availability group is setup as synchronous - the databases will show up in the dashboard as synchronized, meaning they are current. If the availability group is setup as asynchronous - they will show up as synchronizing.
If there is no data in the send/redo queues that just means the system is caught up and there isn't any backlog. If you want to see what a backlog looks like - you need to make enough changes to cause the queues to back up. One way to try to force a backlog would be to rebuild all indexes.
Another option - suspend data movement on the database for some time, then resume. This will force a backlog and you can then see that in the dashboard.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply