SQL2012 - AlwaysOn & Log Shipping

Hi Guys,

Calling all DBA guru's!
Need some assistance - right now we have a SQL 2012 server running on SP2 with about 9 databases (so far). We want to setup HA and DR for this box. We were thinking of setting up another SQL 2012 box in the same site, naming it SQL 2012 NODE 2 and renaming the production one SQL 2012 NODE 1. Then put both of these in a AlwaysOn Group in the same site. Once this is setup, log ship from the primary node to a DR site.

Thoughts? We currently do this for another environment but I also wanted assistance with getting my head around its configuration. I can supply screen shots etc..

thanks guys.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Let me see if I understood.
You'll have a cluster of 2 nodes, being Node1 the Primary site and Node 2 the Second site of an AlwaysOn process, so this will be your HA solution.
Then you'll have a stand-alone on a remote site, log shipping from Primary site, being this your DR solution.

If the above is true why don't you add the remote site as 3rd Node of the cluster and then configure it as part of the AlwaysOn process, setting the 2nd Node to be Synchronous and the 3rd Node asynchronous? You won't need to have AlwaysOn and Log Shipping solutions at same time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
out2getyouAuthor Commented:
Hi Vitor,

Thanks for your suggestion. We have considered this but have listed the following for DR over WAN:

AAG (async):
•      Higher day to day administration
•      Typically more day to day failures than log shipping
•      Probably less than 15 minutes data loss
•      Requires initialisation back to Prod in failback mode

AAG (sync)
•      Adds double ping time plus processing time latency to Production i.e. a 1ms transaction will take 21+ms on a 10ms network
•      better suited for same site nodes

Log Shipping
•      Low day to day admin, it is basically a file copy
•      Automatically recovers from network outages
•      Requires initialisation back to Prod in failback mode

what are your thoughts?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why do you say that AAG (async) has an "Higher day to day administration"?
Don't forget that Log Shipping also has data loss.

My idea is only have one solution for HA+DR (AlwaysOn only), instead of having on for HA and another one for DR.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

out2getyouAuthor Commented:
Yeah log shipping has up to 15 minutes of data loss but AAG can have up to 30 min or more. I think cluster over a WAN its quite complex and could introduce more complexities, especially if we need to invoke DR.

Do you have async AAG over a WAN set up now? what has been your experience? have you had to failover and back? what would be the process?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have async AAG over a WAN set up now? what has been your experience? have you had to failover and back? what would be the process?
Unfortunally we are only in testing phase so our 3 cluster nodes are all in the same LAN. I know that is not the same but at least our tests shows no issues until now.
out2getyouAuthor Commented:
Hi Vitor, no problem. Thanks for your help!
out2getyouAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.