jeudi 4 décembre 2014

SQL Replication With Always On Availability Group


We have an always-on SQL 2012 availability group with two replica servers and an AG listener. Doing merge replication via anonymous subscriptions through IIS between SQL and multiple remote clients running SQL Express 2012. We have a separate SQL server that serves as a full-time distributor.


Replicas: SQL01(Active) and SQL02 Listener: AG-SQL Distributor: SQLDST


Per the MSDN checklist, there is a publisher redirect set up and verified on the distributor, for SQL01 with AG-SQL as the alternate.


We can see the publication on both SQL01 and SQL02.


Replication works if we are not failed over, and the subscribers have SQL01 set as the publisher.


If we leave the subscribers set to SQL01 as the publisher while failed over, replication fails, indicating SQL01 is read-only.


If we try to point the publishers to SQL02 while failed over, the subscription will not create, indicating the publication does not exist.


If we try to point the publishers to the AG-SQL listener, the subscription will not create, indicating the AG is not available, not a publisher, or access denied.


If we fail back over, making SQL01 the primary again, replication starts working again.


All of this is conducted by our C# application. The subscriptions are created and replication is triggered programmatically.


And now the questions:


Can merge replication work against an AG group? (I presume yes, given the articles out there on configuring it).


Will merge replication fail over with the AG and work seamlessly, or do the subscriptions have to be reinitialized/recreated?


When not failed over, on the subscription, what should the publisher be? SQL01, AG-SQL, or something different


Do we need a different publisher when failed over?


Are there any special subscriber settings we need to apply in order to work against an AG?


Thanks





Aucun commentaire:

Enregistrer un commentaire