0

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/

  • 1
    You can log ship to a higher version, where does it say this is not supported? Is it just that you're not able to select it in the wizard? (The wizard is not necessary to set up log shipping. It's just a wizard.) – Aaron Bertrand Aug 29 at 11:14
  • I'm not sure where you're getting it's not supported. It's totally supported. I echo Aaron's statements. If there is a documentation change needed, please let me know. Also, linked servers are horrifically slow and i'd avoid them for almost everything. – Sean Gallardy Aug 29 at 11:39
3

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.

  • Server B is an analytical server with lots of ram, cpus and storage. I'm affraid that our OLTP server would not handle queries or analysts torture it with :) And linked server seems the most convenient option for migration of a ton of analytical scripts. A simple replace would do the trick - "... join database1.schema.tableName" to "...join serverB.database1.schema.tableName". Thats all. OPENQUERY would require a complete makover of scripts. – Jan W Aug 29 at 19:44
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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