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.
I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.
Let me tell you transaction log shipping is nothing but trasfer of log records from one server to another its quite light and you can create your own 'logshipping' by creating backup,copy and restore jobs. What you are trying to do is one way possible may be using TSQL. You are making SQL Server(SS) 2005 as primary and SS2012/2008R2 as secondary this can be achived but think of a scenario where 2005 database server fails due to any reason and you bring SS2012/2008R2 online all is fine now BUT would you be able to bring SS 2005 online with updated data from SS2012/2008R2, you cannot because higher version backups cannot be restored on lower version database. So this would lead to data loss which I guess you dont want. So I would advise you not to go for this scenario.
I would say upgrade your SQL Server 2005 to 2008 R2 or SQL Server 2012 this is the best thing to do. In all cases of High availability version mismatch can cause you problem. Having said that if this is immediately not possible, as per RTO,RPO, schedule a proper backup of SQL Server 2005 database. Of course configuring logshipping with 2005 as secondary is also good.
EDIT: After User asked below
I want to avoid large backup and restore cycles which will be left to weekends and overnight if I can't achieve this.
Well in such case you have differential backups a very nice way to lower down RTO. This will surely decrease your total time taken to restore all backups after disaster. The restore sequence would be full backup followed by LATEST differential backup followed by all log backups in sequence taken after latest differential backup. Suppose you do daily full backup at 12 AM diff backup every 4 hr and every 1 hr log backup. Lets say database faied at 3:50 PM. You would restore
1.Full backup at 12 AM.
2.Differential backup at 12 PM
3.Log backup taken after 12 PM differential backup ( 1PM, 2PM and 3PM log backups) I dont think it would be big deal.
This is minimum you have to do Logshipping is not a solution here. You can create your own script to automate the restore process.
There are file system backups which can also do this job, they take file system backup of mdf anf ldf file and after disaster you just need to attach those mdf and ldf files but poiint in time recovery is not possible with file system backups
Best Answer
If you are using SQL Server 2012 as reporting, then you can use logshipping and configure your secondary as standby - that will allow users to read data from secondary when logs are not being restored. You can delay the restore of log files on secondary as per your needs.
Note that from lower to higher version, you are doing a one way. You wont be able to restore the sql server 2012 databases to sql server 2005 or 2008R2 easily (if you failed over).
Database transactional replication wont be supported as your subscriber (reporting server) would be on a higher version than publisher (sql server 2005 and 2008R2). See my answer here.
Also refer to SQL Server Many-to-One replication