Implement Log Shipping in prod env

Question

We have a 3.5 TB DB which is already involved in log shipping to two different servers, as one will be in restore sate only and one will be in stand by mode which helps for reporting needs. All three instances are SQL Server 2005.

Now we want to implement log shipping to third server (SQL Server 2008) without downtime. We restored a full backup of primary db on the 2008 server and it took 2 hours to restore and later on we took a differential back up to of primary (since we have too many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server. How can we implement log shipping in this scenario? Is it possible to implement with out any downtime.

Answer 1

We have a 3.5 TB DB which is already involved in log shipping to two different server, as one will be in restore sate only and one will be in stand by mode which helps for reporting need. all source and destinations are sql 2005.

Make sure you have Instant File Initialization ON. This helps to drastically cut down the backup/restore times.

we took a differential backup to of primary (since we have to many log backups which run every 15 mins) and restored it and started log shipping. Here it fails by skipping the log file since it is looking for previous log file which is already shipped to other servers.

A differential or FULL backup does not break the log chain. Also, logshipping would not skip the files. There has to be something that might be taking ad-hoc log backup and then breaking logshipping.

I have set up a 3 way logshipping (dont know what to call .. so calling 3 way - one primary ships to 3 other standby servers) and it works without any issues.

Finally we stopped log backups and followed the same procedure it succeeded but it took 3 hours downtime which we don’t want to do since it is a prod server.

As I mentioned earlier, Instant File Initialization will help with the restoring of full backup - cutting down the restore time.

Setting up log-shipping won't involve a downtime on your primary server. It will only on your secondary server - if you are doing reporting and this is your case. It is by design, that during the restore - you cannot access the database.