November 2016 - Posts

When It’s Gone It’s Gone !

Let me say that I like log shipping, for me as a DBA it ensures I have a set of backups in a different location, hopefully a different site to my main data centre.
Mostly I like log shipping because all of my backups get restored as soon as after being taken and as we all should know “ The only good backup is one that’s been successfully restored”

I’ve log shipped since SQL 6.5, I don’t use a third party solution, I mean it’s not difficult is it?

1.    Take backup
2.    Copy to new location
3.    Restore backup

Yup really is as easy as 1, 2 ,3 .   However there are some serious issues with how I’ve seen log shipping implemented and I’m often amused at how folk seem to miss the whole point concerning why we log ship, in fairness quite often it won’t be a DBA who has deployed log shipping  in this manner.
The problem with not getting a DBA involved? Mostly I’d say not actually understanding log shipping and why you’d use it.

In fairness I usually find that differential backups are not fully understood by non DBAs either.

So what is my gripe then?   Well almost every implementation of log shipping set up this way doesn’t ship the daily full backups and restore them – heaven forbid the system which only has one full backup a week and relies on differentials.
Meanwhile I’m told that “we’ve put in log shipping for our DR solution”
Well …. Not really in fact not in my world  .. the really good point why log shipping is better than mirroring, replication or availability groups is that you can step back to a point in time restore .. BUT .. you will need a full backup for a start point for the log restores.
In a failover situation where the log shipped server has to become the live server I have seen a copied file cause the database to go suspect, yes it’s rare but I’ve seen it happen- the problem can be if the file was being copied at the time of the failure.
Likewise I’ve seen a standby database turn out to be corrupt, only once and SQL 2000, but it did happen.
I discovered that in fact the logs continued to restore but the moment a full backup was restored , well it failed , the database threw an error.
In this particular instance the database corruption had occurred a couple  of weeks  earlier but the logs happily continued to restore, as soon as we attempted to restore a full backup the whole process failed.
The reason we were not copying the full backups every day was down to bandwidth, leased lines are costly even now but back then there just wasn’t the bandwidth to sensibly copy the full backups every day.
And yes .. we increased our bandwidth.

Lack of bandwidth is the usual reason for leaving out the full backups, I’ve seen so many ways to attempt to get around a lack of bandwidth .. and some of the explanations for slow copy times have been truly priceless – I’ll just include one to give flavour “ Ah yes you only get 500 mb on a gigabit network as it’s full duplex “  500mb in each direction then ?

So log shipping protects against all types of problems including accidental or malicious data damage, the problem with mirroring and replication – “when it’s gone it’s gone” ,  If I delete a table by mistake on my primary availability group node it’s gone on the secondary a few milliseconds later.

Log shipping allows for point in time restores, but you need your full backups in place.

Posted by GrumpyOldDBA with no comments
Filed under: ,