Log Shipping - Best Practices

kdixon

I'm just starting to setup log shipping on a couple of servers. Our main purpose is to have a warm spare. I have a couple of questions I am hoping those with more experience can answer.
1) What is the best way to backup the database - it is possible to lose BOTH servers.
2) Is there ever a reason to stop log shipping, re-sync the databases and start fresh or do we continue the log shipping process to infinity until the warm spare is needed?

Thanks for your help in advance!
Kerry

fatherjack2

Kerry,

I am also just starting to look at Log Shipping in our network. I am stuck choosing between native SQL Server Log Shipping and SQL Backup to set up the process.

To be fair there are more plus's for the SQL Backup option when we go live as we will be shipping over the internet to a DR site so encryption and compression will be almost 100% required. They work slightly differently from what I can see. Mostly in the way that the log backups are created, stored and archived. SQL Backup has a handle on synching the RESTORE job a few minutes after the BACKUP so as to have the data moved more quickly to its destination. Only downside I think is that this means a SQLBackup license is needed for the standby server, if its a dedicated standby server you dont need a SQL license though.

On our hardware/network SQL Backup ran a full backup and verification of a 1.4GB database in 21s and compressed it to 109MB.

make sure you download the Log Shipping Monitor tool from here:
http://labs.red-gate.com/index.php/Log_Shipping_Monitor
its a 'RedGate Labs' tool but is agnostic so will keep tabs on LogShipping with SSMS and SQLBackup origins.

Not sure if this answers your questions to any great extent but hope it helps.

Jonathan

kdixon

Thanks for the reply Jonathan. I have pretty much decided to use SQL Backup for log shipping. I was not real clear on my question. Log Shipping is NOT a backup strategy. It will definetly help if a server goes down, but it will not solve the backup problem. Such as, "I accidently deleted a company from our database about 2 days ago. Can you tell me what the CompanyID was?"
What have others done to continue backing up without breaking the Log chain?

Thanks,
Kerry

fatherjack2

Hi Kerry,

The 'accidental' update is a good one. In my opinion, if it was two days ago then its pretty much their fault for not telling you sooner...

To have a 'standalone/adhoc' backup, of a database that is in the middle of Log Shipping or other backup cycle I use the WITH COPY_ONLY option. This doesnt break the chain of LSN numbers in the log files and lets the other process have a full and contiguous set of files to restore. Check BOL for details. I guess you could schedule a job to run one of these on a routine that would let you find lost details if you restore these databases of use SQL Data Compare to view data in the backup files directly.

Its a pretty high cost to set up and maintain (your time, HDD space etc) compared to the number of times it will be needed though isnt it?