SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding new files to a log-shipped database (SQL Spackle)


Adding new files to a log-shipped database (SQL Spackle)

Author
Message
alen teplitsky
alen teplitsky
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 4670
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?
chuck.hamilton
chuck.hamilton
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 395
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.
jfogel
jfogel
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 1168
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
chuck.hamilton
chuck.hamilton
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 395
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.
jfogel
jfogel
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 1168
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
chuck.hamilton
chuck.hamilton
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 395
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.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12341 Visits: 8546
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 on googles mail service
chuck.hamilton
chuck.hamilton
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 395
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search