I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.
What I'm trying to do is to maintain a reporting (read only) version of a production transactional SQL Server 2005 database (180 Gb) on on a SQL Server 2012 server (and in the case of other clients 2008 R2). I can do simple back up and restore at any time but to improve latency between refreshes of the database, I'd like to see if I can do something like transaction log shipping.
If TLS is not available in this scenario what do the panel suggest as an alternative strategy.
Best Answer
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
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