How can I use Transactional Replication with Basic Availability Groups?

Question

How can I use Transactional Replication with Basic Availability Groups?

C-M-A

SSC-Addicted

Points: 407
More actions
March 26, 2019 at 11:15 am

#2031625

Hi All,
Say I have two datacenters. Each one has two instances of SQL Server. The pairs use transactional replication between the two at each datacenter. DC1 is the primary and DC2 would be the secondary. SO like:
DC1: SQL1 -> SQL2
DC2: SQL3 -> SQL4
Now what I'd like to know is if SQL1 and SQL3 are part of the same AG and SQL2 and SQL4 are part of their own AG, is there any way to get replication to function at DC2? I understand that DC2's databases would be in standby mode because they're secondary. If SQL3 and SQL4 both become the primary and replication was already setup on it before being part of the availability group, would replication just start back up and be happy or would it need to be set back up? Would this setup even be possible?
I think the part that confuses my search elsewhere is that we only have 2016 Standard and a lot of features mentioned seem to be Enterprise only.
Thanks everyone

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 1

You can configure an Always On database as a Publisher or a Subscriber in a transactional replication topology and they automatically handle failover. See here for more info.

So in essence, AG1 (SQL1 and SQL3 replicas) would be configured as your Publisher and AG2 (SQL2 and SQL4 replicas) would be configured as your Subcriber.

If no failover has occurred, then replication would be published from SQL1 to SQL2.
If AG1 failed over but AG2 did not, then replication would be published from SQL3 to SQL2.
If AG2 failed over but AG1 did not, then replication would be published from SQL1 to SQL4.
If both AG1 and AG2 failed over, then replication would be published from SQL3 to SQL4.

Provided you have configured your publisher and subscriber correctly, you would not encounter any issues following failover, replication should start up and continue functioning like normal post-failover.

IMPORTANT: The distribution database is not supported in an AlwaysOn AG until recently - https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-distribution-database-in-availability-group/

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

There's a trace flag (1448) you may want to consider using if you're doing this. See what you think.

See here

C-M-A SSC-Addicted Points: 407 More actions · Answer 3

Thanks HandyD,

I can't believe I missed these articles. Your four scenarios are exactly what I want to account for. The only other question is whether this is all truly supported in Standard edition.

Beatrix, thank you also. I'll review that trace and see if its appropriate. I guess if there are no down sides I'll just turn it on.

I'll follow up here with my test results.

Thanks again.

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 4

Replication and AlwaysOn should be fully supported in Standard Edition. See here and here.

There are some caveats and design decisions that are important. For example, as per Docs:

For transactional replication push subscribers, the distribution agent will continue to replicate automatically after a failover if the subscription was created using the AG listener name. For transactional replication pull subscribers, the distribution agent will continue to replicate automatically after a failover, if the subscription was created using the AG listener name and the original subscriber server is up and running.

So for this reason, you would probably want to go with Push subscriptions to simplify things.