2

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.

2

Your only valid options would be transactional replication. No changes since your downstream version is 2008 R2. When dealing with features like AGs, it generally is same MAJOR version of SQL Server.

  • 1
    While I completely agree with your answer, it might be beneficial to the OP to understand WHY replication is the best option here. Also, stressing the limitations and the operations on the publisher that invalidate the subscriptions and/or snapshots would be of great help. – spaghettidba Apr 5 '16 at 8:00
  • Thanks Allan for pointing me to Transactional Replication. Would I setup the TR on the 2008 R2 instance [source] or on the 2016 instance [target]? i.e. is it a "push" method from source, or a "pull" method from target? – John G Hohengarten Apr 5 '16 at 14:48

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.