April 24, 2019 at 6:51 pm
I was thinking to configure Always on to 2 different secondary, one in DR Async and one in same site with Sync mode and then configure replication from secondary replica. I was thinking this would not be a best option when there is a fail-over the replication redirection would be question? However, checking to see if configure replication from primary is the best option instead of secondary replica right? Would there be any additional overhead adding replication to AG? Does Always on with sync mode is reliable will there be any issues i should be aware of. Is there a better way of scaling other than running backups on secondary replica(which is Sync mode)? Any thoughts?
April 24, 2019 at 7:19 pm
For what reason are you installing replication on top of an Availability Group?
If you can provide us with the goals of what you are trying to accomplish, we can probably help you architect it.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 24, 2019 at 8:38 pm
Replicate data for Reporting needs(not the entire database couple of tables from different databases). Secondary Sync node is for offloading the maintenance.
April 25, 2019 at 2:43 pm
I've an AG with replication for this purpose, but I do wonder why you'd want a third node for it as you'd not get AG awareness.
Rather, look at using the third server as a separate publisher: Link it to the AG listener so whichever node is active, your replication continues. Then you can publish articles to the reporting server without having to worry about node failure. There is an overhead but I only notice it as my instance has the publisher on the Primary node (and so isn't HAG aware - hence why I was reading up on how to fix this - when I have time and a 'spare' server...).
The following link gives some info on 2017 enhancement to replication on AGs:
I did have a link to an article that went through how to set it up (was on the same site) - but the link's expired (hence the above) - may be able to track down the original article later.
But replication on AG's: It's a thing and clearly you (and I) are not the only ones who see a use for it.
April 25, 2019 at 3:13 pm
Replicate data for Reporting needs(not the entire database couple of tables from different databases). Secondary Sync node is for offloading the maintenance.
Here are a couple things.
Assuming you are going to have a 4th server to contain the replicated data, your licensing would be higher.
If this is the scenario, Primary, Secondary one, secondary two, and subscriber, then you need to fully license the primary and subscriber, and only pay for SA on the 2 secondaries.
If you make one of the secondaries read-only, and use it for reporting, you would save on licensing. That would be 2 full licenses and one SA, and opposed to 2 full and 2 SA. You would need to configure read-only routing so that whatever server in the AG that is secondary gets the reporting traffic. You do lose the ability to tune the secondary specifically for reporting however.
Also, when you say "offload your maintenance", have you considered the ramifications of that? Is this just backups, or is it backups, integrity checks, and other things?
You need to perform integrity checks on all of the nodes in the AG. Corruption can occur on any one of them. We do not perform backups on the secondaries, we do them on the primary only.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply