Performance of a linked server between two instances of SQL Server on the same virtual machine

Question

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?

edit:

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.

https://www.brentozar.com/archive/2015/01/reporting-log-shipping-secondary-standby-mode/

Answer 1

should I expect a significant performance drop during queries between them (large joins)? Is there a way to overcome this?

This answer addresses directly your question about using a linked server to run queries through. And the answer is YES, you can expect a performance drop, especially with large joins.

Usually when SQL processes a query, it can use statistics, indexes and other things to define an optimal execution plan, using later joins and where statements to eliminate required rows before they are even read (or at least making a good estimate for how much memory is needed). But the linked server is a black box for those things. With queries of even minimal complexity, you will quickly see a drop in performance. This is due to SQL bringing the entire table contents over the wire into a worktable and then performing the join operation(s).

So, a few thoughts for you:

You can do log shipping of Database A on Server A to Database B on Server A (the same server, but the database is a different name). You will have to roll your own, but it's just RESTORE LOG WITH STANDBY. You won't be able to separate cpu/memory for each instance, but you would keep reporting transactions from interfering with your OLTP transactions. If these are going to live on the same host (just as different instances) then this is no great loss.

What joins are you doing through the linked server? If all the data you need is available in the reporting database, then you could use OPENQUERY() to make sure that the linked server handles all of the query. Otherwise, you could use OPENQUERY to pull minimal data from the remote server to the local (temp table) for later joining/work.

Answer 2

should I expect a significant performance drop during queries between them (large joins)? Is there a way to overcome this?

Usually, it's not a problem to have multiple instances in same VM/Physical server, but you must not leave all settings default in both instances, especially Min and Max Memory.

In your case, it's not an issue as the side by side installation of different versions is supported. Aside from that you must already aware the work-load and resource utilization of existing SQL Instance, so that you could plan for resource upgrade where it's required, with new instance you may going to have same kind of load eventually the VM required more resources (CPU, Memory, Disk IO throughput) to give same level of performance for both SQL Instances.