none
Service broker with alwayson RRS feed

  • Question

  • Hi,

    i had a problem with a db server with windows 2016 and sql 2016 iaas on azure in AlwaysON with some data base with service broker enabled. 

    I setup this database with alwayson on server "A" and application works fine. I did a failover of this database on server "B" but our application did not work. We failover another time the instance on server "a" and application continue to not work. I had to remove db from alwayson replication and application came back to work.

    There is a particular configuration to do?

    Regards

    Friday, May 3, 2019 1:28 PM

Answers

  • Hi,

    If you have resolved your issue, please close the thread by marking the useful reply as answer.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution:-)

    Best Regards,
    Puzzle
    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

    • Marked as answer by Emanuele86 Friday, May 17, 2019 2:05 PM
    Wednesday, May 15, 2019 1:25 AM

All replies

  • Hi Emanuele86,

    Have you created an AG listener for the client application to connect to the AG? And could you check the database status in the Always On Dashboard when you performed a failover? Please refer to Configure the listener and Connect to an Always On availability group listener

    Best Regards,
    Puzzle
    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 6, 2019 1:59 AM
  • Hi Emanuele86,

    Have you created an AG listener for the client application to connect to the AG? And could you check the database status in the Always On Dashboard when you performed a failover? Please refer to Configure the listener and Connect to an Always On availability group listener

    Best Regards,
    Puzzle
    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

    Hello,

    yes i created a listener for Availability Group and db status after failover are ok and synchronized.


    Monday, May 6, 2019 10:09 AM
  • Hi Emanuele86,

    What do you mean the application did not work? Any error message?

    Best Regards,
    Puzzle
    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

    Tuesday, May 7, 2019 1:16 AM
  • Hi,

    application after switch have blank page (i don't remember now the error sorry :))

    So, i did this modify following this link , https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017 :

    1) on both node:

    CREATE ENDPOINT [EndpointTest]

    STATE = STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL ) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

    2) on both node:

    GRANT CONNECT ON ENDPOINT::[EndpointTest] TO [PUBLIC]

    after i put db under alwayson. Now the application seems working, i did some failover and it works now.

    THis part is mondatory to enable?

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/service-broker-with-always-on-availability-groups-sql-server?view=sql-server-2017#SendRemoteMessages

    or i have to enable if some db have to communicate with different db server?

    Tuesday, May 7, 2019 4:20 PM
  • Hi Emanuele86,

    That part is for seeding messages from outside the AG to the database in the AG. If some databases have to communicate with a remote service in AG, you will have to create the route targets the availability group listener.

    Best Regards,
    Puzzle
    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, May 8, 2019 5:21 AM
  • Hi Emanuele86,

    That part is for seeding messages from outside the AG to the database in the AG. If some databases have to communicate with a remote service in AG, you will have to create the route targets the availability group listener.

    Best Regards,
    Puzzle
    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

    Ok now is more clear this part.

    So i have other question. Is better to enable service broker with alwayson on before to create availability group? Or i can do this also with alwayson active(db have already broker enabled)?

    Wednesday, May 8, 2019 9:09 AM
  • Hi,

    You should first make sure that your AG possesses a listener. Then you can target the route to the availability group listener. 

    Best Regards,
    Puzzle
    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, May 8, 2019 9:30 AM
  • Hi,

    You should first make sure that your AG possesses a listener. Then you can target the route to the availability group listener. 

    Best Regards,
    Puzzle
    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

    yes i have listener. My question was referred to these steps:

    1) on both node:

    CREATE ENDPOINT [EndpointTest]

    STATE = STARTED AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL ) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS)

    2) on both node:

    GRANT CONNECT ON ENDPOINT::[EndpointTest] TO [PUBLIC]

    if i have already db under availability group i can launch that command to enable broker for alwayson? Or i have to reconfigure availability group removing and putting db?

    Wednesday, May 8, 2019 9:51 AM
  • Hi,

    You don't need to remove the db from your AG. You can launch that command if the db is already in AG.

    Best Regards,
    Puzzle
    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, May 9, 2019 1:29 AM
  • Hi,

    You don't need to remove the db from your AG. You can launch that command if the db is already in AG.

    Best Regards,
    Puzzle
    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

    ok thank you :)
    Monday, May 13, 2019 2:53 PM
  • Hi,

    If you have resolved your issue, please close the thread by marking the useful reply as answer.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution:-)

    Best Regards,
    Puzzle
    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

    • Marked as answer by Emanuele86 Friday, May 17, 2019 2:05 PM
    Wednesday, May 15, 2019 1:25 AM