sqlskills-logo-2015-white.png

Transactional Replication Publications and Availability Groups

Books Online documents a few scenarios regarding Replication and Availability Group interoperability.  Today I tested out the process detailed here:

Configure Replication for AlwaysOn Availability Groups (SQL Server)

It worked as advertised and I tested this on a five replica AG topology with three synchronous replicas (including the primary) and two asynchronous replicas.  I won’t rehash the BOL steps – but I did want to mention a few observations about the process:

  • One of my AG replicas was also the same SQL Server instance as my subscription database (non-AG database), so I skipped the sys.sp_addlinkedserver step for that particular SQL Server instance.  Collocation of the primary replica and subscriber worked fine.
  • While it is possible to make one of your participating replica SQL Server instances the distributor, it doesn’t make sense to do so from an HA/DR perspective.  But if your distributor is indeed remote and not collocated with the AG replicas, think about FCIs for providing HA.
  • The publications show up in SQL Server Management Studio under the Replication\Local Publications folder.  Hovering over the publication from a secondary replica will still show a yellow (tooltips-like) dialog box showing the original SQL Server instance where you created the publication – even if that replica is currently a secondary.
  • The New Publication Wizard doesn’t stop you from creating a Peer-to-Peer publication for an availability database, even though this combo is not supported by Microsoft.  I didn’t finish P2P configuration – but now I’m curious if it actually works (even though it wouldn’t have support).
  • Deleting a publication for an availability database raises the error 18752 “Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time”.  This error was repeatable with or without existing subscribers.  The error also gets followed up with a “change database context to” message. Even after the message, the publication does indeed get removed.  This message is seen both with the GUI and with sp_droppublication.  I’ll likely put out a Connect item on this one (I didn’t see one that matched my scenario).

Why consider replication when you have AG readable secondaries?  There are several use-cases that I could think of – for example if you want to have a sub-set of the overall data and use customized indexing on the subscriber.  Another case would be to have access to replicated data if there is an outage of the AG.

I’m going to write about testing the AG subscriber scenario in another post.

5 thoughts on “Transactional Replication Publications and Availability Groups”

    1. Thank you Joseph. I appreciate the heads-up on the link.

      Correct – FCIs are the out-of-the-box HA method for the distributor at this point. You could argue that a VM could be as well.

  2. Hi Joseph, you mentioned about testing the AG subscriber scenario in another post. Can you point me the correct location of that post please if you can? Thank you

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course