none
Enable Replication/CDC from High Availability Group

    Question

  • Hello Experts,

    First of all, I should confess that I have very minimal knowledge about both HA and Replication. So, pardon me if I am posting in the wrong forum and feel free to move it to the respective forum.

    I would need your expertise in setting up few things.

    I have a HA Group where our Application is sitting or communicating with the Databases. Obviously this group will have a Primary node and a Secondary node based on the definition of High Availability Group. The Application could be reading/writing data to the database either through a Listener/Load Balancer.

    So, now my requirement is, I would like to setup Replication from the HA Group to a Staging Database server so that I can enable CDC on my Staging Database Server. I know that CDC can be enabled on HA Group itself but, due to some organization policies, I would like to enable CDC ONLY on Staging Database Server.

    As part of this, I would like to script out all the Replication scripts from the Publisher (HA Server) to Subscriber (Staging DB Server). Could someone please help me with scripts as most of the internet talks about doing it through SSMS? The reason I would like to script out this is because, every time the Application does a build, I would like them to disable the Replication, do their build, and then re-enable the Replication at the end. It was also my understanding that, in order to enable/disable Replication one should have sysadmin privileges. If that is the case, can I setup a SQL Agent Job and allow the application to run the Job to enable/disable Replication whenever they're doing the build? Please advise.

    Thanks!

     

    Known is a DROP, Unknown is an OCEAN.

    Wednesday, November 14, 2018 7:05 PM

All replies

  • Hi Bangaaram,

     

    >>Could someone please help me with scripts as most of the internet talks about doing it through SSMS?

     

    The replication should be created according to your requirements, so please refer to the following document to script the replication which meets your demands.

     

    Document link: Scripting Replication

     

    >>If that is the case, can I setup a SQL Agent Job and allow the application to run the Job to enable/disable Replication whenever they're doing the build?

     

    Yes, you can set up a SQL Server Agent job to run the script to enable the replication.

     

    Best Regards,

    Emily


    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

    Thursday, November 15, 2018 9:25 AM
  • Basically, your requirement is to have an automated way to stop and restart replication. You can simply create a SQL job (on the distributor, if yours is a push subscription or subscriber, if pull subscription) to stop the "distribution agent job". That would stop data from being replicated to the destination (subscriber). Note that any new changes would still be tracked on the publisher via log reader agent (assuming transactional replication), it's just that data will stop replicating until you resume the distribution agent which you can do by starting the job (the one you stopped) again and it would resume replication from where it left off.

    The following commands can be incorporated in your stop and start distribution agent job.

    --stopping replication. You can find the job name by querying the SQL jobs or from the "distribution_agentname" column in sp_replmonitorhelpsubscription stored procedure.
    
    exec msdb.dbo.sp_stop_job 'YourDistributionAgentJobName'
    
    --starting replication
    exec msdb.dbo.sp_start_job 'YourDistributionAgentJobName'
    
    

     

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Thursday, November 15, 2018 12:12 PM
  • Hi Emily,

    Thank you for your reply. Unfortunately, the document you provided also talks about creating the scripts again through SSMS but doesn't provide the sample scripts unlike other topics.


    Known is a DROP, Unknown is an OCEAN.

    Thursday, November 15, 2018 1:01 PM
  • Hi Moshin,

    Thank you for the reply. Sounds like a good idea. I have few questions.

    Should I create the job on the Subscriber (Stage DB Server) or the Provider (Application Server)? If I create the job on Subscriber, would the Provider be able to execute that job as part of their build? Does it require any Linked Server or some sort of connection in order to do that? 

    I don't mind if it is a push or pull as long as I am able to get all the changes from the Publisher to Subscriber. Please advise.



    Known is a DROP, Unknown is an OCEAN.

    Thursday, November 15, 2018 1:07 PM
  • Should I create the job on the Subscriber (Stage DB Server) or the Provider (Application Server)? If I create the job on Subscriber, would the Provider be able to execute that job as part of their build? Does it require any Linked Server or some sort of connection in order to do that? 

    I don't mind if it is a push or pull as long as I am able to get all the changes from the Publisher to Subscriber. Please advise.

    The jobs (one to stop and the other to start replication) can be created on the server where the distribution agent runs. Now, a distribution agent runs on the distributor in case of a push subscription and on the subscriber in case of a pull subscription. You would have to find out if yours is a push or pull. 

    If, on the other hand, if you find that your distribution agent actually runs on the publisher, then that would mean your publisher is also a distributor, in which case the start/stop jobs and the application would be on the same server (publisher). 

    If it turns out to be a pull (wherein the jobs would be on the subscriber) and you want to be able to call them from the publisher, there are ways such as PowerShell, SSIS, SQLCMD etc to invoke a job on a remote server. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Bob_FT Sunday, November 18, 2018 3:31 PM
    Thursday, November 15, 2018 1:21 PM
  • Thanks for more clarification, Moshin. I'm interested to see the PowerShell script rather than the other two options.

    In this case, it would be a Pull (please see below) and correct me if I am wrong. This is what I am seeing on the Source Server (Publisher) and Target Server (Subscriber). Based on this, could you please be able to tell me where I can find the information for Distributor so that I can script out everything and try to run through PowerShell/Job.

    Source (Publisher)Target (Subscriber)

    Thanks!


    Known is a DROP, Unknown is an OCEAN.

    Thursday, November 15, 2018 6:30 PM
  • Execute sp_helpdistributor to return information about the Distributor, distribution database, and working directory.

    Execute sp_helpdistributiondb to return properties of a specified distribution database.
    Sunday, November 18, 2018 3:31 PM
  • Hi Bob,

    Upon trying to execute sp_helpdistributiondb, it doesn't return any results at all.


    Known is a DROP, Unknown is an OCEAN.

    Tuesday, November 20, 2018 8:14 PM
  • https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpdistributor-transact-sql?view=sql-server-2017
    Wednesday, November 21, 2018 5:20 PM