0

We have a current setup where we have two SQL 2008R2 database servers in production. Our reporting team requested a single place they could query data from both servers (to avoid both hitting production directly and the performance issues with linked servers). So we set up log shipping where we ship several databases from both sources to a target server. It works great, the databases on the target server are read only during the day and only unavailable when the log shipping restores are scheduled at night. However, the vendor applications for both source servers are going through an upgrade project, and they will unfortunately have to be be different versions (SQL 2014 and 2016 respectively).

Most of what I have researched seems to say that you cannot log ship from a different source version, but I've also found information suggesting that you can ship from a lower to a higher version, but am unable to find anything official.

So, my question is, is it possible to log ship from both 2016 and 2014 sources into a 2016 destination, AND leave the databases read only when they are not restoring? And is there official Microsoft confirmation of this?

2
  • To an extent, thanks. I also found this. Unfortunately it seems that from restoring from lower to higher means you can't use standby mode (can't make it read only): dba.stackexchange.com/questions/229694/…
    – BrianC
    Aug 14, 2020 at 16:46
  • Correct. Standby means recovery, which would mean a physical upgrade. Which would be impossible since you expect forthcoming log backups and also the db is readonly. Catch 22,, quite simply. Aug 14, 2020 at 18:21

1 Answer 1

0

As per the link in the comments, it looks like you can do a log shipping restore from a lower to higher version, but cannot make it standby (read only): log ship from older version to newer one

I'll probably write up a separate question to see what alternatives there are (other than transactional replication... ideally looking for something uncomplicated).

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