none
AG Listener losing connectivity RRS feed

  • Question

  • I have 2 SQL server 2016 standard Servers. One for reporting (DB2) and one for production (DB1).

    They are both nodes on a WSFC.

    DB1 replicates the production database to DB2 with the distibutor being on DB2 (this is for reporting purposes since secondary replicas aren't readable in SQL 2016 Standard).

    I have and an availability group (AG_DIST) on DB2 for the distribution database with primary replica on DB2, secondary replica on DB1 and a listener (Dist_lsnr). This AG ensures that the distribution database is always available.

    On DB1 I have another AG (AG_PROD) for the PROD database with primary replica on DB1, secondary replica on DB2 and a listener (PROD_lsnr). (This is for HA)

    All is well when I fail over WSFC or fail AG_DIST to DB1. The problem arises when failing over AG_PROD to DB2. When this is done the application connections using PROD_lsnr lose connectivity with the PROD database. When I fail the AG back the applications can again connect to the database.

    What am I overlooking here?

    Friday, November 15, 2019 7:42 PM

All replies

  • Is it possible that the Apps are connecting directly to the node instead of the cluster?

    Is it only the apps that fail? Can you connect using SSMS? 

    Also, we have 1 (just one of many) apps that needs to be restarted after we failover.

    Friday, November 15, 2019 11:01 PM
  • Hi Rbcheath,

     

    Would you please try to connect to the ag by ssms via listener after failing over AG_PROD from DB1 to DB2? Show us the error message. As mentioned by Comeau, Would you please check if your Apps are connecting directly to the primary replica?  Are you adding

    MultiSubnetFailover=True  in the connection string in each app? For more details, please refer to https://docs.microsoft.com/en-us/sql/database-engine/listeners-client-connectivity-application-failover?view=sql-server-2014#SupportAgMultiSubnetFailover

     

    Best regards,

    Dedmon Dai


    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, November 18, 2019 7:01 AM
  • Hi Rbcheath,

     

    Have you solved this problem ?  In order to close this thread, please kindly mark your replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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, November 19, 2019 1:59 AM