I'm a BI Developer investigating options for this scenario.
I'm going to preface this question by saying I don't know the proper terms, so I will put "quotes" around terms that are probably not the official SQL terms.
Let's say I have a SQL 2008 R2 SP1 Standard instance that runs a 3rd party vendor's transactional application. We are unable to create or modify indexes on this database to performance tune for reporting queries, due to it being a vendor-controlled database (a common problem, yes?).
We want to have a "mirror" (for lack of better/accurate SQL term) of just the DATABASE (not the whole application) from the source instance onto a SQL 2016 Enterprise instance.
The 2016 "mirror" would have INSERTS/UPDATES/DELETES that happen on the 2008 R2 source database "pulse copied" to it on a very frequent basis, say every, 1 minute (could be 5 minutes, could be 10, don't know what's feasible yet).
The 2016 "mirror" would be used for reporting purposes, that we could tune and hit without negatively impacting the source database with a bunch (potentially up to 2,000) of queries simultaneously.
The "mirror" would therefore become the data source for reports (no load balancing from mirror versus source is necessary), instead of the live database.
Failing Over from 2008 R2 [source] to 2016 [target] is irrelevant, and is not the goal.
The goal is a "copy" or "mirror" of the source database to a target "mirror" that is 1-10 minutes behind the live database, and that "mirror" is to be used for reporting (SSRS, SSAS Tabular, Power BI, etc.) and that allows us the freedom to performance tune via indexes, et cetera that would otherwise not be allowed on the vendor's database.
Given this info, does the "mirror" must be READ-WRITE for the index creates / alters / rebuilds / reorgs, or can it be READ ONLY?
What are our valid options for this mixed SQL Server version scenario?
Log Shipping?
Replication?
Database Mirroring?
Availability Groups?
Others??
Does the answer change if the source system gets upgraded from SQL 2008 R2 Standard to SQL 2014 Enterprise?
I consider my question to be distinct and different from How to accomplish SQL Server 2012+2014 high availability since the requirements are different, and thus is not a duplicate.