Transactional replication subscriber vs AG secondary replica for reporting

Posted on

Question :

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.

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.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *