1

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.

Improve this question
2
  • What's stopping you from upgrading your production server?
    – James Anderson
    Commented Jul 28, 2014 at 7:39
  • We don't own the production servers as they are a third party product with their own upgrade cycles. We are another third party provider.
    – Preet Sangha
    Commented Jul 28, 2014 at 8:15

2 Answers 2

Reset to default
2

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

Improve this answer
4
  • thank you. this is not for backup purposes - those are done as per usual. I'm just using the the technology to get my reporting more up to date during the day.
    – Preet Sangha
    Commented Jul 28, 2014 at 8:34
  • Can you please explain ' reduction in data staleness' sorry i did not get exact meaning of this. I could refine my answer better
    – Shanky
    Commented Jul 28, 2014 at 8:37
  • I want to avoid large backup and restore cycles which will be left to weekends and overnight if I can't achieve this.
    – Preet Sangha
    Commented Jul 28, 2014 at 8:39
  • @preet: I edited my answer please check
    – Shanky
    Commented Jul 28, 2014 at 9:05
0

Yes it is possible to achieve what you are asking with sql server database replication. The target server must be the same version or higher than the source.

Replication can be run at regular intervals or continuously if you need updates "up to the minute".

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.