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.


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.

  • I am just trying to stay away to replicate ReportDB/TempDB. It appears to me AG replicas, once it's setup properly, has less problem than transactional replication. – WML Apr 21 '16 at 7:34
  • Disagree with replication comments. It doesn't need on going work. It should be subject to monitoring as AG is for latency and subject to monitoring as SQL Agent jobs are for success/fail etc. – Hpk Apr 21 '16 at 10:08
  • AG would be better but I'm a little confused when you talk about ReportDB/TempDB. Are you referring to the ReportServer and ReportServerTempDB databases, or a source database they're reporting on? I'm not sure what you'd get by replicating the RS ones at all. – Cody Konior Apr 21 '16 at 12:54
  • My bad..yes I am talking about the SSRS ReportServer and ReportServerTempDB. I agree I don't see the point of replicating ReportServer DBs. It seems to me transactional replications (TR) and AG both have their own fan based. There are a lot of reports created by my clients; either ad-hoc or overnight and I think TR might be better for that. I am presenting both TR and AG solutions to my clients and see what they think. – WML Apr 21 '16 at 23:35

Don't forget you will need Enterprise Edition licensed for both nodes if you go down AG route with read only secondary, if your client is not going to use it for DR then its massive overkill in my opinion and very expensive.

Transactional replication just requires standard edition and its very reliable if you set it up correctly and understand it, if there are constant issues then you've done something wrong rather than the technology being at fault.

Your SSRS databases should be separate. They only contain the report server configuration details and are used to run the report server. They will not contain your report data.

  • Our report server database is > 150Gb. Lots of snapshots – WML Apr 23 '16 at 8:58
  • We have TB's of replication movement daily, size shouldn't be a constraint. – Hpk Apr 25 '16 at 14:01

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.