none
Can Replication be enabled on a Primary Replica of an AlwaysOn Availability group using a publisher only without negatively impacting the Primary or Secondary replica?

    Question

  • We are looking into using a product called Attunity for a Data Warehouse solution. 

    Our current production environment  is a 4 node WSFC.  3 of the nodes belong to the SQL Server Failover cluster. The  4th node, which is the AlwaysOn secondary replica, is used for reporting purposes only.  The secondary node will never take on the primary role for any of the SQL instances so failover to that node will never take place which is why it's not part of the SQL Cluster.  

    The Attunity product  reads from the transaction log  via the Microsoft Sql Server API and requires MS Replication to be enabled however according to them we only need to set up the publisher side of things. We have never used replication so I'm  unsure if this is even a possible scenario. 

    Does anyone know if Replication can be set up on an AlwaysOn primary database without  "breaking" the secondary replicas? Can it be set up to only enable a publisher and what impact could  that have? We have a call with the vendor on Tuesday and I have several questions however before that call, I need to understand if  their requirements are feasible and what are the possible implications.

    Any guidance/advice is greatly appreciated.

    Friday, November 2, 2018 4:08 PM

All replies

  • Yes, it can. You will need to do some configuration to get this to work.

    Basically you create CDC for attunity running on the current primary.

    On failover to the secondary you need to configure it there as well and any other secondary that might be a primary. You will do it when that secondary is the primary.

    Please refer to this document for how it is to be done.

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-2017

    Friday, November 2, 2018 9:06 PM
    Moderator
  • Thank you for the reply and the information. In our current AlwayasOn set up the primary will never fail over to a secondary, the secondary is not part of the SQL cluster and it strictly for reporting purposes. We don't use AlwaysOn for a HA solution. Attunity can use  either CDC or Replication. We had CDC enabled several months ago but we disabled it as it turned out not to be a good fit for our Data Warehouse project. 

    Can we turn on Replication without actually having a subscriber in place meaning only set up a publisher to basically do nothing? According to the vendor, they only require that a publisher be in place. Is this feasible and if so are there any implications?

    Thanks.

    Monday, November 5, 2018 2:32 PM
  • Yes, you will likely need a remote distributor for this to work however.
    Monday, November 5, 2018 2:40 PM
    Moderator
  • Thank you, I appreciate your help/advice.
    Monday, November 5, 2018 7:10 PM
  • Hi adoptapet,

    Could you please tell us if you have solved this problem? If it is, please mark Hilary's reply as Answered.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 7, 2018 6:51 AM
    Moderator