November 14, 2014 at 3:40 am
Morning All,
I’m interesting in using the WSFC/always on 2012 enterprise in a two node asynchronous configuration primarily to support a the secondary node for real time reporting purposes. The reporting server would be used for heavy reporting runs, and ad hock throughout the 24/7. The Failover capability is not my interest as I have that covered through ZERTO, and SAN block level replication, and backup and restore worst case. My focus is real time reporting. I’m about to set up a lab using a network file share to accommodate the shared storage and build this environment. I wondered how affective this scenario is and reaching out to other DBAs/experts who have implemented this concept that can share their experience. Maybe there are white papers you can point in the direction of.
Is it an effective solution for real time reporting?
Thanks in advance
Russ
November 14, 2014 at 4:16 am
In my experience, scaling out the reporting workload to the secondary is a very sleek process that saves lots of headache.
I've never had issues with that. It just works and it's very effective.
-- Gianluca Sartori
November 14, 2014 at 5:43 am
You'll need to check out the licensing implications of enabling a read only secondary. I Am Not A MS Sales Weasel But ... I believe that if you enable a secondary as read only it's classed as active rather than passive and you have to license it.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 14, 2014 at 5:45 am
Your dead right about the licensing Andrew.
November 14, 2014 at 5:46 am
HI Gianluca, This is what I was hoping to here. Thanks for your comment.
November 14, 2014 at 6:29 am
Russell.Morgan-813114 (11/14/2014)
I’m about to set up a lab using a network file share to accommodate the shared storage and build this environment.
what do you want shared storage for?
All instances in the AlwaysOn group will usually be stand alone instances, no shared storage required. All nodes will however, need to be part of the same Windows Server Failover Cluster
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2014 at 6:41 am
Understood. I'm still finding my feet with this. I was referring to AlwaysOn using SQL Server’s existing mirroring technology, and therefor I need a location for the initial full syn and then incremental feeds, I'd planned to used a network file share for this. I understand the database are independent unlike the traditional mirrored setup. Does that seem right?
November 14, 2014 at 7:20 am
Russell; I'm looking to implement something similar to this and I'd be very curious to see how you implement this (seems like a great step-by-step article that you could post to SSC!) - if you don't mind sharing, I'd love to learn the process you used to implement this
🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 14, 2014 at 7:35 am
MyDoggieJessie (11/14/2014)
Russell; I'm looking to implement something similar to this and I'd be very curious to see how you implement this (seems like a great step-by-step article that you could post to SSC!) - if you don't mind sharing, I'd love to learn the process you used to implement this🙂
Have you started reading my stairway series on this site?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2014 at 7:41 am
Thanks Perry - I will read through this - thanks for sharing it
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 14, 2014 at 7:41 am
MyDoggieJessie, sure.
November 14, 2014 at 7:42 am
Russell.Morgan-813114 (11/14/2014)
I was referring to AlwaysOn using SQL Server’s existing mirroring technology
Database mirroring basic concept is exactly similar to AlwaysOn groups, they dont require shared storage.
Russell.Morgan-813114 (11/14/2014)
I need a location for the initial full syn and then incremental feeds
Sounds like you're talking log shipping here, not database mirroring.
Russell.Morgan-813114 (11/14/2014)
I'd planned to used a network file share for this. I understand the database are independent unlike the traditional mirrored setup. Does that seem right?
AlwaysOn at the basic level is database mirroring, except that it allows up to 8 secondarys (SQL Server 2014) and the databases may be readable. Mirroring only provides a 1-1 solution in which the secondary is not readable
Take a look at my stairway to AlwaysOn on ]this site[/url] for more info
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 14, 2014 at 7:43 am
That's an interesting read. Thanks for this.
November 14, 2014 at 7:49 am
(log shipping ref:) sync set up, every replica needs to have access to a shared location to access the database backups created and used for synchronising the secondary databases. The initial sync feels like log shipping. The one thing I don't understand is the transport mechanism as to how the secondary replica is kept in sync....its proving a little elusive.
November 14, 2014 at 8:03 am
The databases are NOT "independent" when you read from an AG secondary. You wind up modifying the PRIMARY records under the covers with the 14-byte row-version pointer. Most don't know this and it can REALLY cause issues (massive page splits and index fragmentation and tlog activity and locking/blocking and dirty pages that must be written to disk, etc, etc). Other issues can be affected too (ghost cleanup IIRC, etc).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply