Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Adding new files to a log-shipped database (SQL Spackle) Expand / Collapse
Author
Message
Posted Friday, June 07, 2013 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
i've never done log shipping, but with mirroring all you have to do is leave the database in a restoring state and then start mirroring. can you do the same with log shipping?

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1461085
Posted Friday, June 07, 2013 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 46, Visits: 217
Yes. Its very similar. The difference is that with log shipping you need to have 3 agent jobs. On the primary there's one to run log backups. On the secondary there's one to copy the log backup from the primary, and another to restore the log backup (with recovery/standby). The latter IMO is the big advantage with log shipping. You can leave the DB in standby mode and run queries against it. There's even a bit of a hack that will let you run BACKUPs of it.

You can optionally set up a log shipping monitor too to alert you if the log shipping ever breaks or falls seriously behind.
Post #1461095
Posted Friday, June 07, 2013 8:28 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:55 PM
Points: 349, Visits: 904
Careful with those alerts! If you have a slower connection to your DR site or if maintenance causes larger than normal T log backup file size you will get alerts and plenty if the frequency of the check is short. I setup the schedule for the alert to not run during that time. To me, one of the best features of log shipping besides the obvious is how forgiving it is. There have been times I needed to take down either the source or destination and I've never had issues with things coming back online. I know the way it works is why this is possible but I appreciate the simplicity. We use log shipping and replication and generally things run smooth.


Cheers
Post #1461111
Posted Friday, June 07, 2013 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 46, Visits: 217
Couldn't agree more. By default I think the alert threshold is 45 minutes. I set it to 3 hours, and like you, I schedule the alert job not to run at times when I know its going to fall behind more than that due to circumstances that are beyond my control.

And yes log shipping is VERY forgiving.
Post #1461115
Posted Friday, June 07, 2013 8:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:55 PM
Points: 349, Visits: 904
Another major plus is if someone makes a mistake and alters or deletes something it may be possible to scrape out what I need from the DR site (assuming they say something fast enough). That totally beats restoring the DB and rolling the logs up to a point in time on another server as all that is required is stopping the restoration job.

Cheers
Post #1461121
Posted Friday, June 07, 2013 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 46, Visits: 217
Thats one of the things I always liked about MySQL. The logs contained essentially just plain old SQL. If you needed to undo something that someone did you could just restore/recover and skip that statement in the logs. Unless subsequent changes were affected by that change (which was rare) you could back what you needed pretty easily.
Post #1461124
Posted Friday, June 07, 2013 2:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 4,128, Visits: 5,837
chuck.hamilton (6/7/2013)
Thats one of the things I always liked about MySQL. The logs contained essentially just plain old SQL. If you needed to undo something that someone did you could just restore/recover and skip that statement in the logs. Unless subsequent changes were affected by that change (which was rare) you could back what you needed pretty easily.


I'm pretty beat right now from a rough week, but I can't see how that kind of logging can ensure rigorous data recoverability in the event of a restore. Different hardware can have VASTLY different performance characteristics (or even the same hardware depending on concurrent usage) and just allowing a stream of sql statements to replay sure sounds like it could allow stuff to COMPLETE out of order, leading to invalid data states.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1461235
Posted Monday, June 10, 2013 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:20 AM
Points: 46, Visits: 217
I'ts really not much different than the way SQL or Oracle does it. DDL and DML is recorded in the log in the order in which it occurred. Recover it in the same order and you should end up with a database in the same condition as before recovery in regards to committed transactions.

I don't think it's actually ASCII text that it uses to record the statements. Its actually stored as a binary file but there's a command used to translate it into SQL commands that get piped into the command line program.

If you're thinking about non-deterministic updates, the log takes that into account as well. IOW statements like "update account set balance = balance + @x" will be converted to a deterministic value.
Post #1461546
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse