Sql server 2005 mirrored database transaction log file maintenance?

A. If your server is important enough to mirror it, why isn't it important enough to take transaction log backups? SAN snapshots are point-in-time images of just one point in time, but they don't give you the ability to stop at different points of time along the way.

When your developers truncate a table, you want to replay all of the logs right up until that statement, and stop there. That's what transaction log backups are good for B. Set up a maintenance plan (or even better, T-SQL scripts like Ola Hallengren's at ola.hallengren.com ) to back up all of the databases, but check the boxes to only back up the online ones.(Off the top of my head, not sure if that's an option in 2005 - might be 2008 only.) That way, you'll always get whatever ones happen to fail over Of course, keep in mind that you need to be careful with things like cleanup scripts and copying those backup files.

If you have half of your t-log backups on one share and half on the other, it's tougher to restore.

A. If your server is important enough to mirror it, why isn't it important enough to take transaction log backups? SAN snapshots are point-in-time images of just one point in time, but they don't give you the ability to stop at different points of time along the way.

When your developers truncate a table, you want to replay all of the logs right up until that statement, and stop there. That's what transaction log backups are good for. B.

Set up a maintenance plan (or even better, T-SQL scripts like Ola Hallengren's at ola.hallengren.com) to back up all of the databases, but check the boxes to only back up the online ones. (Off the top of my head, not sure if that's an option in 2005 - might be 2008 only. ) That way, you'll always get whatever ones happen to fail over.

Of course, keep in mind that you need to be careful with things like cleanup scripts and copying those backup files. If you have half of your t-log backups on one share and half on the other, it's tougher to restore.

Thanks Brent, Our developers do not have access to the live kit and it has been decided by the powers that be (against my wishes) that the only sort of backups we will be performing are SAN snapshots. Apparently the customer is happy losing a few hours of data... I'm not sure I would be but hey. B) The check box options appear to be a 2008 option only unfortunately, but that script looks pretty awesome.

I'll give it a go and have a nose around what it's doing. – Blootac Nov 2 '10 at 13:35.

A) no, you cannot truncate a log that is part of a mirrored database. Backing the logs up is your best option. I have several databases that are setup with mirroring simply based on teh HA needs but DR is not required for various reasons.

That seems to be your situation? I would really still recommend keeping the log backups for a period of time. No reason to kill a perfectly good recovery plan that is added by your HA strategy.

:) b) My own solutions for this are to have a secondary agent job that monitors based on the status of the mirror. If the mirror is found to change, the secondary job on teh mirror instance is enabled and if possible, the old principal is disabled. If the principal was down and it comes back up, the job is still disabled.

The only way the jobs themselves would be switched back is the event of again, another forced failover.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.