I would like to set up log shipping between SQL Server 2014 (main) and SQL Server 2016 (secondary). The thing is I want to set the secondary DB in STANDBY. Unfortunalely this setup is not supported by MS, as the secondary is a higher version (2016) than primary (2014). To overcome that issue I have a plan to install a second instance of SQL Server on secondary in version 2014. Then in order to be able to access it I will have to create a linked server between those two instances on the same machine.
And now the main question. Provided that both instances reside on the same VM, should I expect a significant performance drop during queries between them (large joins)? Is there a way to overcome this?
Thank you for your feedback. A bit of clarification from my side:
Secondary (2016) will be used solely for reporting purposes. There is no need to support a fallback scenario. The database is quite large so daily restores to secondary are becoming a bit cumbersome.
Having the secondary in a STANDBY state is a must, as I want the database to be accessible for reporting. As far as I'm concerned it is not possible.