locked
Problem connecting to Always On Availability Group Replica's in SSMS RRS feed

  • Question

  • I am using SSMS (version 17.9.1) connected to an enterprise SQL Server (version 2016).  I have enabled Always On (AO) and created an Availability Group (AG) with the SQL server on which I am connected as the Primary replica and another server (same version) as the secondary. 

    When the replicas were created , my windows authenticated login on both servers (verified as successful and granted to the sysadmin group on both servers) was used.  This worked fine, but due to some network problems, the replica connections to the servers were lost.  The network poroblems have been fixed, but when I connect  to the primary server again (with the same id), it shows the connections to both replicas in the AO AG as down.  I have tried to refresh and nothing happens.  If I connect to the replica with a different sysadmin login, it opens another connections to that sever in Object explorer and the status on the aforementioned replica does not change.

    What I want is for the requested connection on the AOAG to be successful and the status of the replica updated to opened.

    Friday, March 1, 2019 6:05 PM

Answers

  • I think I stumbled on the answer when I was trying to solve my problem of not being able to add databases to the AG.  I needed to add the HDR port to the firewall rule that I already created for the other SQL connections. I'm so happy this solved both problems. 
    • Marked as answer by Susan Brower Friday, March 1, 2019 7:14 PM
    Friday, March 1, 2019 7:14 PM

All replies

  • What does this return?

    select operational_state_desc, connected_state_desc, recovery_health_desc, synchronization_health_desc,last_connect_error_description , *  From sys.dm_hadr_availability_replica_states
    where role<>1
     

    Friday, March 1, 2019 6:29 PM
    Answerer
  • I am also having trouble adding databases to the group, but that is a different problem...  Here are the results returned for your query

    operational_state_desc                                       connected_state_desc                                         recovery_health_desc                                         synchronization_health_desc                                  last_connect_error_description                                                                                                                                                                                                                                   replica_id                           group_id                             is_local role role_desc                                                    operational_state operational_state_desc                                       connected_state connected_state_desc                                         recovery_health recovery_health_desc                                         synchronization_health synchronization_health_desc                                  last_connect_error_number last_connect_error_description                                                                                                                                                                                                                                   last_connect_error_timestamp
    ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ ------------------------------------ -------- ---- ------------------------------------------------------------ ----------------- ------------------------------------------------------------ --------------- ------------------------------------------------------------ --------------- ------------------------------------------------------------ ---------------------- ------------------------------------------------------------ ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------
    NULL                                                         DISCONNECTED                                                 NULL                                                         NOT_HEALTHY                                                  NULL                                                                                                                                                                                                                                                             84C47726-77DD-456A-99FA-B931E35A2AD1 A483D9E2-E5F1-4A52-B1CF-AAD63E3820D4 0        2    SECONDARY                                                    NULL              NULL                                                         0               DISCONNECTED                                                 NULL            NULL                                                         0                      NOT_HEALTHY                                                  NULL                      NULL                                                                                                                                                                                                                                                             NULL
    NULL                                                         DISCONNECTED                                                 NULL                                                         NOT_HEALTHY                                                  NULL                                                                                                                                                                                                                                                             CD6CB982-D156-4F17-926C-BD2618FB4477 F7F1A158-7A58-4358-B27C-4BC2F0364807 0        2    SECONDARY                                                    NULL              NULL                                                         0               DISCONNECTED                                                 NULL            NULL                                                         0                      NOT_HEALTHY                                                  NULL                      NULL                                                                                                                                                                                                                                                             NULL
    Warning: Null value is eliminated by an aggregate or other SET operation.

    (2 rows affected)

    Thank you for your time.

    Friday, March 1, 2019 6:54 PM
  • I think I stumbled on the answer when I was trying to solve my problem of not being able to add databases to the AG.  I needed to add the HDR port to the firewall rule that I already created for the other SQL connections. I'm so happy this solved both problems. 
    • Marked as answer by Susan Brower Friday, March 1, 2019 7:14 PM
    Friday, March 1, 2019 7:14 PM