none
Replication distribution database in Always On availability group RRS feed

  • Question

  • Hi,  Configured distributor  in Availability Group, there are 2 replicas for distributor server,configured listener name. When I create a publication , snapshot agent is created with Primary Replica Instance name in the Snapshot Agent Job step 2, however Log reader agent job is getting created with Distributor Listener name in the Log Reader Agent Job Step 2. 

    How can I force the snapshot agent to use listener name instead of using Instance name of the primary replica. 

    This is how snapshot agent is getting created, which will not work once I fail-over the distributor, since primary role will change, I want Listener name in the Distributor Parameter.

    -Publisher [Publisher/InstanceName] -PublisherDB [Data] -Distributor [DISTRIBUTOR/InstanceName] -Publication [TestPub] -DistributorSecurityMode 1

    Even If I change the Distributor to Listener Name, I am losing those changes once I fail-over the distributor.

    Please advise any ideas on how I can fix this.

    Thanks


    • Edited by Florida DBA Tuesday, April 28, 2020 4:48 PM
    Tuesday, April 28, 2020 4:45 PM

All replies

  • Hi Florida DBA,

    >How can I force the snapshot agent to use listener name instead of using Instance name of the primary replica.

    If my understanding is correct, you want to change the snapshot agent to one fixed name(listener name)?

    Try step as next:

    To view and modify replication agent command line parameters from Management Studio
    Connect to the appropriate computer in Management Studio, and then expand the server node:

    For the Distribution Agent and Merge Agent for pull subscriptions, connect to the Subscriber.

    For all other agents, connect to the Distributor.

    1.Expand the SQL Server Agent folder, and then expand the Jobs folder.

    2.Right-click a job, and then click Properties-->General-->Name (input the name what you want to show)

    More information please reference: view-and-modify-replication-agent

    Use code as next to check the jobs information:

    SELECT
        [jop].[job_id] AS 'JOB ID'
       ,[jop].[name] AS 'JOB NAME'
       ,[dp].[name] AS 'JOB AUTHOR'
       ,[cat].[name] AS 'JOB CATEGORY'
       ,[jop].[description] AS 'JOB DESC'
       , CASE [jop].[enabled]
            WHEN 1 THEN 'YES'
            WHEN 0 THEN 'NO'
          END AS 'ENABLE OR NOT'
       ,[jop].[date_created] AS 'CREATE DATE'
       ,[jop].[date_modified] AS 'LAST CHANGE DATE'
       ,[sv].[name] AS 'JOB RUN IN THE SERVER'
       ,[step].[step_id] AS 'JOB START STEP'
       ,[step].[step_name] AS 'STEP NAME'
       , CASE
            WHEN [sch].[schedule_uid] IS NULL THEN 'NO'
              ELSE 'YES'
          END AS 'WHETHER DIS JOB OR NOT'
       ,[sch].[schedule_uid] AS 'JOB SCHEDULE ID'
       ,[sch].[name] AS 'JOB SCHEDULE USER NAMED NAME'
       , CASE [jop].[delete_level]
            WHEN 0 THEN 'NOT DELETE'
            WHEN 1 THEN 'DELETE AFTER SUCCESS'
            WHEN 2 THEN 'DELETE AFTER FAILURE'
            WHEN 3 THEN 'DELETE AFTER FINISHED'
          END AS 'OPTIONS TO DELETE AFTE FINISHED'
    FROM [msdb].[dbo].[sysjobs] AS [jop]
    LEFT JOIN [msdb].[sys].[servers] AS [sv]
             ON [jop].[originating_server_id] = [sv].[server_id]
    LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]
             ON [jop].[category_id] = [cat].[category_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]
             ON [jop].[job_id] = [step].[job_id]
                AND [jop].[start_step_id] = [step].[step_id]
    LEFT JOIN [msdb].[sys].[database_principals] AS [dp]
             ON [jop].[owner_sid] = [dp].[sid]
    LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [jsch]
             ON [jop].[job_id] = [jsch].[job_id]
    LEFT JOIN [msdb].[dbo].[sysschedules] AS [sch]
             ON [jsch].[schedule_id] = [sch].[schedule_id]
    ORDER BY [jop].[name]
    

    Best Regards.

    yuxi


    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, April 29, 2020 6:49 AM
  • Thanks Yuxi for your reply.

    Distributor is on AlwaysOn. I have already tried modifying the job step but as I posted in my question, once I fail-over job is losing the changes. 

    Thanks

    Padma

    Wednesday, April 29, 2020 12:53 PM
  • Hi Florida DBA,

    Could you please show your configuration code,and I will test that on my side and try to monitor the issue.

    And please check whether this is helpful:  walkthrough-publisher-distributor-subscriber-in-alwayson-availability-groups

    Best Regards.

    yuxi


    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

    Monday, May 4, 2020 7:54 AM