I am trying to recommend my client a SQL server upgrade (from 2005-> 2014) which will allow the users to create record snapshot (via SSRS) from a read-only data source. Data latency should be kept to a minimum.
My understanding for transactional replication is a subscriber database schema can be a subset of the publisher, and can have different indexes; but I don't think an AG secondary replica can have a different schema from primary, even though a secondary replica can have different indexes?
I am also thinking about having the SSRS Report DB/ReportTempDB and operate independently rather than get replicated/synchronized. I think it is OK to have the secondary replica/subscriber to communicate to the SSRS Report DB/ReportTempDB via SSRS, right?
Note that for my client the DR is NOT a main concern.
Best Answer
The secondary replica can't be changed inside; and no you can't add indexes.
You can do subsets and add indexes on the transactional subscriber of something but you will likely encounter issues if you ever need to modify your source tables (such as upgrading the application) or reload data for any other reason; it's good practice to remove it before any change then reapply after and then apply your own changes.
Also replication often fails for any numbers of reasons. Basically just a warning that it's likely ongoing work and monitoring rather than set and forget.
For the latter part of your question, you don't have to add it to the AG if you don't want to. I'm not sure what you're getting at.