Post reply

AG db Synchronized but not accessible

  • April 14, 2021 at 2:46 pm

    #3871060

    Hello experts,

    We have a report of a failed login for an application. The error is like this one:

    Login failed for user 'sql_login'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: ]

    This database is in an Always On Availability Group. The db is accessible on the secondaries, but not on the primary.

    It says (Synchronized) for the database on the listener/primary, but there is no + sign for it to expand the database contents. When I try to open the db in SSMS, I get this error (connecting to either the listener name or directly to the current primary node):

    The database mydatabase is not accessible. (ObjectExplorer)

    Finally, in the Availability Group Dashboard, I  see the following warnings:

    Policy Evaluation Result on Availability Group ...

    Detected Issue

    X Availability group is not ready for automatic failover.
    The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover, however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavail [cut off in the actual dialog]

    ! Some availability replicas are not synchronizing data.
    In this availability group, at least one secondary replica has a NOT SYNCHRONIZING synchronization state and is not receiving data from the primary replica.

    ! Some synchronous replicas are not synchronized.
    In this availability group, at least one synchronous replica is not currently synchronized. The replica synchronization state could be either SYNCHONIZING or NOT SYNCHRONIZING.

    Could someone help me understand what is happening here, and how I can fix it? I am relatively new to AOAG and although I know the basics of adding/removing databases, restores, jobs, and permissions, I don't always know the exact troubleshooting steps for issues like these regarding synchronization problems.

    Thanks for any help.

    -- webrunner

     

     

     

     

    • This topic was modified 3 years, 7 months ago by  webrunner. Reason: Modifying Topic Tags
    • This topic was modified 3 years, 7 months ago by  webrunner.
    • This topic was modified 3 years, 7 months ago by  webrunner.
    • This topic was modified 3 years, 7 months ago by  webrunner.
    • This topic was modified 3 years, 7 months ago by  webrunner.
    • This topic was modified 3 years, 7 months ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 14, 2021 at 6:26 pm

    #3871186

    It appears that one or more databases in the AG are not synchronizing - you need to fix that before you can do anything else.

    As for the application, did you setup read-intent on any of the secondary replicas.  If that is the case - and the application is using ApplicationIntent in their connection string, then reads are going to be routed to the secondary.  If that database is not synchronizing then you will get the above error.

    Note: I do not recommend read-intent for normal application environments.  That should only be set when you have identified that for that application offloading reads to a secondary can improve performance for the application.  Using read-intent to 'route' read-only users (e.g. report users) to a secondary is not how that setting was meant to be implemented.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply