January 23, 2013 at 11:58 am
Hello everyone,
We have 4 Servers which have SQL SERVER 2012 and "AlwaysOn" have been enabled on all 4 servers:
Server1,Server2,Server3,Server4
Server1 is the Primary node and thr rest are secondaries. There is a Sync relation between Server1 and Server2 and also there is aSync relation between Server1 and Server3 & Server4.
Is it possible to setup log shipping from Server2 & Server3(secondaries) to two new servers?Does this have any problem?
Thanks
January 24, 2013 at 1:29 am
If you have availability groups set up and working why on earth do you want to use Log Shipping?
This details how to migrate from Log Shipping to AO groups, this details how to add a LS primary to an existing AO group, although quite why you would want to is beyond me.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2013 at 10:38 am
We have 10 severes and on each server,we have 2-3 databases.We log ship all of these databases from all servers to one centralized place,so the Developers can run their ad-hoc queries.
January 24, 2013 at 6:05 pm
Perry Whittle (1/24/2013)
If you have availability groups set up and working why on earth do you want to use Log Shipping?This details how to migrate from Log Shipping to AO groups, this details how to add a LS primary to an existing AO group, although quite why you would want to is beyond me.
There is a difference between High Availablity and Disaster Recovery. AOHA doesn't cover you against an administrator who accidently deletes every record on a table by mistake because he didn't highlight the WHERE clause while fixing an issue. Log Shipping with a delayed restore can.
It's a mistake to think if you have HA you have DR.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 25, 2013 at 1:57 am
Leo.Miller (1/24/2013)
There is a difference between High Availablity and Disaster Recovery.
No! Really, you think?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2013 at 8:29 pm
Perry Whittle (1/24/2013)
If you have availability groups set up and working why on earth do you want to use Log Shipping?This details how to migrate from Log Shipping to AO groups, this details how to add a LS primary to an existing AO group, although quite why you would want to is beyond me.
There are some good reasons to log ship even when using AOHA. Your comment here implied you don't think so!
It wasn't really neccessary to get sarcastic about my setting the record straight. That's a sure way to put people off commenting on the forum.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 28, 2013 at 11:35 pm
I wasnt being sarcastic i'm genuinely surprised!
you're right, i dont think theres a good reason, AO replaces log shipping and mirroring and pretty much makes them redundant 😉
Leo.Miller (1/24/2013)
AOHA doesn't cover you against an administrator who accidently deletes every record on a table by mistake because he didn't highlight the WHERE clause while fixing an issue. Log Shipping with a delayed restore can.
Hmm, naaa.
The LS secondary will at best have old data, say i have a heavy transactional database with restores on the hour every hour and an admin whacks records at 11:59. I'm going to go back to table data 59 mins old.
My preference would be restore the db using stopat to an offline sever and recover the data i need.
In the organisation i work we have strict control over what is run by who and when. Everything is tested through 3 environments, you give out way to much control imho.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2013 at 8:10 pm
Perry Whittle (1/28/2013)
I wasnt being sarcastic i'm genuinely surprised!you're right, i dont think theres a good reason, AO replaces log shipping and mirroring and pretty much makes them redundant 😉
Hmm, naaa.
The LS secondary will at best have old data, say i have a heavy transactional database with restores on the hour every hour and an admin whacks records at 11:59. I'm going to go back to table data 59 mins old.
My preference would be restore the db using stopat to an offline sever and recover the data i need.
In the organisation i work we have strict control over what is run by who and when. Everything is tested through 3 environments, you give out way to much control imho.
I'm sorry if I misunderstood your comment.
I can’t however agree with you that AO replaces LS and mirroring. Mirroring yes, but I still think there is a place for LS with delayed restores.
Say backing up every 15 minutes, delay restore by 45 minutes. Then if an admin whacks the records at 11:59, I would take one last log backup and copy it to my DR server, then restore the last 3-4 logs with a STOP AT on the last one. All this would take me about as long as it would take for the DNS change to replicate through the domain, and I’d have the database up and running with minimal or no data loss in about 15-20 minutes. While you would still be doing the base restore and working your way through multiple log backups:-) Then you still have to get the lost data back to the prod database.
Once I'm up I start LS in the reverse direction and at an appropriate/agreed time fail back.
I’ve had to do this twice in 19 years, where there wasn’t a warm standby (the client table had been deleted in both cases). In both cases the system was down the better part of a day doing it your way. I’ve also done it once my way, and I was up in 20 minutes and that included doing multiple restores of the last log with STOP AT and STANDBY because we had to pinpoint the time the delete occurred. I’ll pick LS any day.
LS is a low cost DR option, with no SQL licensing needed, and it can go on relatively low spec servers since only one or two DBs may ever actually run here. One company I worked for always moved their old prod hardware into DR when they upgraded and it was always enough.
I agree, strict controls are the way to go, and if I worked for a single company I’d have these, but as a consultant you live with what you get. We support about 50 clients, and I’ve got about 15 clients I could be called on to help, with as many as 800 SQL Instances at one company. On Tuesday at one client a recently upgraded application caused one of the databases to blow out, causing significant issues down the line. When I told the application manager which tables were the issue he just TRUNCATED THEM! The company policy is: If the PM approves it, it’s OK; despite my repeated warnings. Despite best intentions there may be little control and I’m only a consultant, I can’t force them to do the right thing.
The Silver Bullet would be AO with a delayed restore and STOP AT capability 🙂
As background: I’ve been a DBA for about 19 years, first on main frames, then in 1998/9 when SQL 6.5 was being replaced with 7.0 I moved over to SQL. 15 years has been in various banks in South Africa and New Zealand, and I’m now a consultant with SSL www.sqlservice.com. Here I support power utilities, airways, engineering, manufacturing,…
I’m a regular (2-3 times a year) presenter at the Auckland SQL User Group and do in house training at SSL and present our (still unofficial) client training course. I don’t do a blog but I am on LinkedIn, not that I ever make changes.
Go Log Shipping
Cheers and I will try not to take offence so easily next time.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 31, 2013 at 11:00 pm
LS does not provide a true read only standby whereas AO does, up to 4 of them in fact.
If you're not convinced that AO replaces LS read the MS articles I linked above around converting an LS configuration to an AO group. Mirroring will likely be gone in the next SP of 2012 and once AO is seasoned and stable enough it's my guess LS will be gone too.
Unless its a really huge database it's unlikely to take 24 hours lets be honest you're just inflating the figures to make LS look good. Don't get me wrong I'm a fan of LS, but when you have AO running and available IMHO it's redundant. An AO group is designed to provide a read only reporting replica, an offsite back replica and a DR database all in one config. All read only with no disconnections due to LS restores taking place. With read only routing you're able to send read only connections to your reporting replica.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 25, 2013 at 8:52 am
Maybe,
Not every database that you want to log ship is part of an AG.
Not every server you want to log ship to is part of the same cluster.
Not every server need transactional synchronization. There may be good reason to bundle up the transactions and ship them in intervals.
September 22, 2014 at 5:01 pm
We have been using AlwaysOn at my company now for 12 months and the issues we face with Analysts executing long running queries on the secondary causing blocking due to SCH_M / SCH_S blocking has gotten so bad that we now have to look at perhaps using Log Shipping for them instead - so I would have to agree that there certainly is still a place for Log Shipping even with AlwaysOn.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply