February 1, 2015 at 2:56 pm
Hi All,
We have a 2 node clustered instance(SQL 2014) with 26 databases and we would like to enable alwayson for one of the databases for reporting (only one secondary and do not need high availability setup). I'm thinking if the reporting application/queries can explicitly connect to the secondary database(Instance name\database name) without using a listener and setup the secondary in asynchronous commit mode. Read about the REDO thread blocking due to reporting workload. How does this affect if I implement the secondary in this way.
Please share your thoughts and advise.
Many thanks!
February 3, 2015 at 8:38 am
SQL!$@w$0ME (2/1/2015)
Hi All,We have a 2 node clustered instance(SQL 2014) with 26 databases and we would like to enable alwayson for one of the databases for reporting (only one secondary and do not need high availability setup).
Setting up an AlwaysOn group with an FCI as a group replica will not provide high availability anyway, failover will be a manual process even when running in synchronous mode.
SQL!$@w$0ME (2/1/2015)
I'm thinking if the reporting application/queries can explicitly connect to the secondary database(Instance name\database name) without using a listener and setup the secondary in asynchronous commit mode.
You absolutely don't need the listener it's optional and the reports can connect directly to the secondary if preferred
SQL!$@w$0ME (2/1/2015)
Read about the REDO thread blocking due to reporting workload. How does this affect if I implement the secondary in this way.Please share your thoughts and advise.
Many thanks!
It can be an issue, to help the redo all isolation levels on the readable secondary are mapped to snapshot isolation. You may still face issues when the reporting workload is attempting to access the same resources as the redo thread. More may be read here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 3, 2015 at 10:39 am
Thanks Perry.
Does the REDO blocking occurs even if there is only one secondary replica which is configured for asynchronous commit?
February 3, 2015 at 4:11 pm
Yes it possible. The link I provided and other articles linked within it detail this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply