September 21, 2015 at 9:59 pm
Dear all,
recently I've built a new Windows 2012 R2 cluster running SQL 2012 FCI. I have decent experience with Windows FC and SQL, been running and managing a SQL 2008 R2 Windows 2k8 R2 cluster for the past 5 years. The solution has been good and always met our needs.
Now my management want as live reporting from our PROD database. currently I take a nightly backup of our main live DB and restore this to another SQL server that our report writers can hammer to their hearts content. It seems the only real way to achieve an as live fully offloaded DB for reporting would be to implement a SQL AG on top of our cluster instance?
Now I've read plenty of documentation on AG and FCI but just want some guidance and got yas to look out for? The new cluster isn't production yet still have 3 more weeks, I have another new server that is to host our reporting DBs and am not sure whether to tell them to suck it up and stick with the nice simple nightly restore method or is the AG as simple as it says on the box so to speak?
I'm not concerned about automatic failover (not available with FCI) or synchronous rep don't care if it lags behind a bit.
Just keep in mind we are a small team that manage the storage hardware/OS and SQL as well as Citrix servers so not interested in overly complicated solutions.
September 22, 2015 at 8:42 am
Firstly, ready through my stairway to HA on this site starting at this link
http://www.sqlservercentral.com/articles/Failover+Clustered+Instance+(FCI)/107536/[/url]
PretendDBA (9/21/2015)
It seems the only real way to achieve an as live fully offloaded DB for reporting would be to implement a SQL AG on top of our cluster instance?
It's a viable option, what edition of sql server 2012 are you using?
PretendDBA (9/21/2015)
Now I've read plenty of documentation on AG and FCI but just want some guidance and got yas to look out for?
see my stairway 😉
PretendDBA (9/21/2015)
The new cluster isn't production yet still have 3 more weeks, I have another new server that is to host our reporting DBs and am not sure whether to tell them to suck it up and stick with the nice simple nightly restore method or is the AG as simple as it says on the box so to speak?I'm not concerned about automatic failover (not available with FCI) or synchronous rep don't care if it lags behind a bit.
Just keep in mind we are a small team that manage the storage hardware/OS and SQL as well as Citrix servers so not interested in overly complicated solutions.
The AO group will be fairly simple to setup, it's wizard driven and not too much of a headache.
Depends if you want to start enabling listeners and read only routing, however, this is all detailed in my stairway. Read through it and if you're still stuck come back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 22, 2015 at 9:21 am
PretendDBA (9/21/2015)
I have another new server that is to host our reporting DBs and am not sure whether to tell them to suck it up and stick with the nice simple nightly restore method or is the AG as simple as it says on the box so to speak?
The AG setup is just as simple as the restore process and seems more stable in my mind. However, my bench-marking shows that adding availability groups reduces total capacity about 17 percent with out of the box configuration settings, synchronous commit, automatic fail-over, and readable secondary.
September 22, 2015 at 5:52 pm
Hi Perry, I have actually already looked through some of your guide mainly part 7 which focuses on what I am asking, FCI + AG. I've also read a lot of Microsoft docs.
Really all I want is an async readable replica. I'm guessing if I don't setup a listener service it just means my report writers connect to the \\servername\instance\dbname of the replica to write their reports? I don't actually need the listener service for the DB to be accessible just for failover to work without deploying major app changes?
Not interested in read offload, sync etc etc. Our application vendor doesn't support these features anyway. The vendor said they are happy for us to use AG on top of our cluster as long as we test it and confirm the replica db never writes back to the primary. I said the only situation I think this could happen is torn page recovery? Not sure if it does torn page recovery in async mode though?
This concerns me as they claim the DB always caused them issues as the mirror would write back and change the primary? To me AG just seems like a beefed up version of mirroring.
We are using SQL 2012 Ent core licensing mode.
As I said we are pretty comfortable with clustering and it has served us well. I'm very interested in AG and see this as a good option to hopefully not over complicate our lives yet give the business what they want/need.
I also like the Idea of having a very close to live copy of the DB.
September 22, 2015 at 5:54 pm
Hi Jeephound, really 17% performance hit? What about async? I can't see how the secondary being readable would impact performance of the primary? It should improve if your report writers are like mine and once a report works it's published no tuning required? 🙂
September 23, 2015 at 4:06 am
I think since you're already paying for Enterprise Edition, and you have enough servers, you might as well give it a try. Since you'll be using aysnchronous availability mode, there's less risk of any impact on the primary. Obviously you'll have the extra licensing cost for the readable secondary, but you know that already.
You don't need a listener device, no, but equally I see no reason why you shouldn't use one if you want. For your purposes as explained above, it should be fine either way.
I'm not sure about the automatic page repair in your scenario though. I expect Perry Whittle will know :-D.
September 23, 2015 at 10:39 am
I did not benchmark async as we were required to have automatic failover, but I might if I get some time. The drop occurs only after we add the nodes to an AG, adding a listener has no effect. It is a very predictable and consistant drop, again with an out of the box setup. This was determined using Hammerdb and the TPC-C specification. About the environment: 2 datacenters 1 mile apart connected by fiber, xtreme io flash storage at each end, Virtual machines running 4 2.8ghz procs 32gb of ram at each end connected using ISCSI. Windows Server 2012 R2 Datacenter, MSSQL Enterprise 2014 sp1. I am curious to hear results from anyone who has done their own benchmarking.
And just so we are straight, I still think you should try it if you are not constrained. The MANY benefits far outweigh this loss of overhead. Considering you probably don't need synchronous, you should be in the clear.
September 23, 2015 at 11:11 am
PretendDBA (9/22/2015)
Hi Jeephound, really 17% performance hit? What about async?
Ran a quick ad-hoc benchmark of each during the lunch hour today so it won't be as accurate. Since my initial benchmarking, there has been a small amount of dev load added to the Storage and quite possibly the VM's are located on different hosts. Today with synchronous mode I got a 14% performance hit and with Asynchronous mode I am seeing about 6% performance hit. These are all calculated against what I get when I drop the db from the AG. Again the disclaimer is that this was a quick check using only 2 minute benchmarks, but it was during lunch so I expect the load to be fairly consistent.
September 23, 2015 at 7:26 pm
We have plenty of SQL 2008 r2 enterprise license which our MS licensing rep has told me via software assurance we can upgrade to SQL2012/14 licenses as long as the server has less than 20 physical cores.
So we've bought new servers with 2 x 10 core xeons :). The actual cluster though we had to relicense as it's running on 4 x 8 core xeons hp dl560's and yes this hurt.
We keep all our SQL servers physical, as I stated before small team many hats keep it simple. Plus all of our apps and DBs are vendor provided so they aren't exactly optimised, so throw hardware at it? Plus our server life cycle is 5 years, An over provisioned server today is a correctly provisioned server in the 3-4 year mark.
September 23, 2015 at 7:29 pm
Is building an AG on top of the FCI as easy to roll back as it is to implement?
We plan to migrate to our new cluster in two and a half weeks so ideally if we don't like it. It's as simply as untick that box and the environment is back to just a plain Jane FCI with no need for uninstall this reinstall that.
September 24, 2015 at 4:33 am
It's fairly straightforward, but obviously you should test it somewhere first that isn't Production.
September 24, 2015 at 6:55 am
Beatrix Kiddo (9/24/2015)
It's fairly straightforward, but obviously you should test it somewhere first that isn't Production.
I agree, very easy once you learn the working parts. No un-installation or restart of the instances needs to occur.
September 24, 2015 at 9:24 am
You're in conflict here
PretendDBA (9/22/2015)
Really all I want is an async readable replica.
PretendDBA (9/22/2015)
Not interested in read offload
PretendDBA (9/22/2015)
as long as we test it and confirm the replica db never writes back to the primary.
They don't understand AlwaysOn groups, give them my stairway link and ask them to read it. The replicas are readonly or unreadable
PretendDBA (9/22/2015)
To me AG just seems like a beefed up version of mirroring.
Essentially it is at the base architecture, but there are some major differences
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 24, 2015 at 3:25 pm
To clarify, I'm not interested in the "read intend" offload feature. More those that need access to a DB for reporting will be told to directly connect to the replica DB.
October 1, 2015 at 7:45 pm
Anyone ever had an issue with Windows 2012 R2 clustering where it says that a server is missing windows updates but manualling checking and trying to reinstall shows the updates are installed?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply