April 11, 2018 at 11:10 am
Good Morning Experts,
We have configured read-only routing in Availability Groups , SQL Server 2014. So, the reporting workload is going to the secondary replica. But, load on this secondary replica has become huge, can we change the reporting workload to another secondary replica? If yes, could you please let me know how to do that
April 11, 2018 at 12:36 pm
coolchaitu - Wednesday, April 11, 2018 11:10 AMGood Morning Experts,
We have configured read-only routing in Availability Groups , SQL Server 2014. So, the reporting workload is going to the secondary replica. But, load on this secondary replica has become huge, can we change the reporting workload to another secondary replica? If yes, could you please let me know how to do that
This configuration is not only moving the reporting workload - it has moved *all* reads for the application to the secondary. If all you need is a secondary for reporting purposes - then you should set the secondary to read-only, remove the listener and direct the reporting systems to the secondary directly.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 11, 2018 at 1:31 pm
coolchaitu - Wednesday, April 11, 2018 11:10 AMGood Morning Experts,
We have configured read-only routing in Availability Groups , SQL Server 2014. So, the reporting workload is going to the secondary replica. But, load on this secondary replica has become huge, can we change the reporting workload to another secondary replica? If yes, could you please let me know how to do that
How many replicas do you have?
what are the settings for each when in the secondary role?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2018 at 3:10 am
Jeffrey Williams 3188 - Wednesday, April 11, 2018 12:36 PMcoolchaitu - Wednesday, April 11, 2018 11:10 AMGood Morning Experts,
We have configured read-only routing in Availability Groups , SQL Server 2014. So, the reporting workload is going to the secondary replica. But, load on this secondary replica has become huge, can we change the reporting workload to another secondary replica? If yes, could you please let me know how to do that
This configuration is not only moving the reporting workload - it has moved *all* reads for the application to the secondary. If all you need is a secondary for reporting purposes - then you should set the secondary to read-only, remove the listener and direct the reporting systems to the secondary directly.
Thanks for your reply Jeffrey. I think I did not put my question clearly. My question is:
We are using AG SQL Server 2014. It has 8 secondary replicas and 1 primary replica. By using read-only routing , we have moved all reads to the first second replica. But we observed the load and traffic on this first secondary replica has become very huge. So, we want to move all reads to second or third or fourth secondary replica. How to do this? Thanks in advance
April 12, 2018 at 3:18 am
Perry Whittle - Wednesday, April 11, 2018 1:31 PMcoolchaitu - Wednesday, April 11, 2018 11:10 AMGood Morning Experts,
We have configured read-only routing in Availability Groups , SQL Server 2014. So, the reporting workload is going to the secondary replica. But, load on this secondary replica has become huge, can we change the reporting workload to another secondary replica? If yes, could you please let me know how to do that
How many replicas do you have?
what are the settings for each when in the secondary role?
Hi Perry,
We have one primary replica and 8 secondary replicas. All the secondary replicas are readable.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply