August 18, 2015 at 3:55 pm
I have a requirement to setup Always on with SQL 2014.
Lets assume its Server A and Server B.
My client wants a reporting solution attached to this setup.
For instance if the availability group is on Server A, he wants a reporting solution(may be SQL Server replication) which can help him pull the reports.
In case the availability group is failed over to Server B, the reporting functionality still needs to be in place..
what should be proposed?
August 19, 2015 at 6:05 am
Why not consider offloading read-only workloads to readable secondary replicas.
Unless I am reading your query wrong?
August 19, 2015 at 7:33 am
I was wondering the same thing as BLOB_EATER. The AG would need to span both instances, of course.
August 19, 2015 at 2:09 pm
what's driving the AO setup, something else or the reporting replica requirement?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2015 at 5:42 pm
All, thanks for the replies.
The primary aim is to have a DR solution as well as readability/reporting..
Multiple secondary replicas sounds like a plan..
Can someone confirm please..what could be the best scenario..
August 20, 2015 at 6:14 am
how much lag can be tolerated on the reporting copy?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2015 at 1:31 pm
as minimal as possible:-)
August 21, 2015 at 9:18 am
can you be a little more specific?
it seems that log shipping may be better suited if you can tolerate some lag, easier to setup and maintain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 21, 2015 at 1:07 pm
The client currently has log shipping and they arent quiet happy with it.
Can I have secondary replicas in always on for reporting as well?
August 26, 2015 at 12:29 pm
Friends,
I got some clarity on the requirement.
The plan is to have high availability plus reporting solution.
That means Server A and Server B could be in always on purely as a HA solution.
The reporting from Server A needs to be separated out to another instance(through mirroring or replication or log shipping).
When the Availability group fails over to Server B, the reporting model needs to be similar. I mean the production servers needs to be offloaded from reporting.. which is what the case is today.
August 27, 2015 at 2:58 am
If you availability group can support Synchronous secondaries then you should be able to setup SSRS to point to the secondary and report off of it with very little lag of data.
I would check the following BOL article:
Reporting Services with AlwaysOn Availability Groups (SQL Server)
Joie Andrew
"Since 1982"
August 27, 2015 at 6:21 am
Why not have the reports hit the secondary replication? The secondary could either by synchronous or async.
For the reports, use the paramenter "applicationintent=readonly" and that will send the reports to the secondary replica.
I would use this technique in lieu of log shipping.
Steve
August 27, 2015 at 8:38 am
We do exactly what you are saying here.
We have a NODEA and a NODEB and we report only from NODEB using application intent= readonly.
Works fine, but there are the maintenance issues with High Availability.
Do a test setup in your dev environment first - there can be some tricky things.
September 1, 2015 at 5:53 pm
krypto69 (8/27/2015)
We do exactly what you are saying here.We have a NODEA and a NODEB and we report only from NODEB using application intent= readonly.
Works fine, but there are the maintenance issues with High Availability.
Do a test setup in your dev environment first - there can be some tricky things.
can you share a document if you have one?
September 2, 2015 at 5:16 am
Steve-3_5_7_9 (8/27/2015)
For the reports, use the paramenter "applicationintent=readonly" and that will send the reports to the secondary replica.
Note that this must be preceded by accurate configuration of the read only routing within the Alwsyson Availability group
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply