1

We have a 2 Node AlwaysOn Availability Group in SQL Server 2014 running on Hyper-V (VM).

Current setup:

  • SRV01: Role: PRIMARY READABLE SECONDARY: NO

  • SRV02: Role: SECONDARY READABLE SECONDARY: YES

We do Manual Failover to Secondary Replica (SRV02 becomes primary and SRV01 becomes secondary). During testing our Application uses the AG Listener connection (INSERT/UPDATE TRANSACTION) and we have encountered an error stating:

"The target database XXXXXX is participating in an availability groups and current not accessible for queries. Either the data movement is suspended or availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. (Microsoft Error: 976)

Has anyone encountered the same error, and what are your solutions?

  • I think you should validate connection string in application and make sure it usage listener name to connect database. Apart from this check whether listener name is pointing to right instance/server. How long it takes to fail-over database prom primary and secondary. – Rajesh Ranjan Aug 9 '18 at 9:33
  • Can you please add the output of select @@version in the question. Have you made sure the connection is not going to secondary replica SRV01 as that is non readable secondary and you would get the message if you try to access the replica with DML queries. Plus to confirm you are getting this issue after failover and SRV02 is primary replica and this is happening when you are trying to connect via listener ? – Shanky Aug 9 '18 at 10:07
  • 1
    To Resolve this error: Right Click on the primary availability group and configure the “Readable Secondary” to “YES”. – CR241 Aug 9 '18 at 23:43
  • @CR241 I would not do that "just like that", may be that was done because of licensing issue. May be changing secondary replica read only violates license. – Shanky Aug 10 '18 at 8:00
  • Hi, Anyone encountered the issues, even we try different combination in primary role ( read/write both replica, previously ALL) and readable secondary (yes both replica), the availability group failover is setup to "synchronous commit", configure the read only routing correctly. No problem with connecting and committing transaction in application when primary is Node1 but when manual failover to Node2 (becomes primary replica), it doesn't allow to commit transaction (DML) in application side. – Joel Aug 23 '18 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.