SQL Always On: not accessible for queries

Hi forum!

I'm monitoring SQL 2012 Always On Groups with the latest SQL MP. A couple of the database replicas are configured to not allow access. This is to avoid additional license costs for those minor important databases.

However, I get the following events in the SQL log:

Error 976, Severity 14

The target database, '%.*ls', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other ...

Of course, as soon as I configure read-only for those databases the events disappear.

Is there any other solution?

Has anyone identified already (all) the accessing workflows?

Thanks in advance,

Patrick

May 27th, 2014 1:54pm

Hi,

How about overriding the discovery of those databases?

Or overriding the corresponding monitors or rules for those database.

Regards,

Yan Li

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2014 10:29am

This issue is related with SQLcheck service broker. Because Availability group is using Service Broker to communicate between SQL Server then need to check Service Broker first. Open SSMS>Server Objects > Endpoints, there are two Service Broker folder>Expand both, and if the state is Stopped or Disabled, then can change the state to Started.

Also i recommended to ask this question in SQL Forum because they are more specialize.

May 30th, 2014 7:54pm

Hi Yan Li,

of course that is possible. Still I want to monitor these DBs, but only what is possible to monitor in the no-access-mode of the secondary databases.

And finding all monitors/rules is a long way of XML reverse-engineering/tracing. Maybe someone has done that already. Or, even better, knows how to fix that issue (without the need of additional SQL licenses).

Thanks, Patrick

Free Windows Admin Tool Kit Click here and download it now
June 2nd, 2014 3:01pm

Hi!

I've seen that before but there is only the Service Broker folder in SSMS without any further object in it. No clue what is wrong... neither the DBA knows it.

Any idea?

Thanks,

Patrick

June 2nd, 2014 3:02pm

I too have this same issue.

Due to licensing restrictions we wish to make the secondary replica not a readable replica but SCOM is complaining it can't access it.

I also have no objects under "Service Broker".

Patrick, did you manage to find a solution or suitable workaround?


Free Windows Admin Tool Kit Click here and download it now
November 3rd, 2014 3:10am

Hi All, I am also experiencing this issue.  Having a look at the statements SCOM is issuing, it's actually checking whether the AG secondary allows connections but it ignores the value and tries to connect anyway.  I believe this is a minor bug.  See below for these queries. 

You can vote to correct it through Microsoft Connect on: https://connect.microsoft.com/WindowsServer/feedback/details/1121330

--SCOM/MP: is this database a replica and does it allow connections?
SELECT d.name, d.database_id, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,         ar.secondary_role_allow_connections  
FROM sys.databases d  JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id  JOIN sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/ 
WHERE d.database_id = x  --current db id in sys.databases loop

--SCOM/MP: let's look into that db (regardless of whether it allows connections)
--EXCEPTION: The target database, 'x', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the 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.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.




February 9th, 2015 1:25am

Hi All, I am also experiencing this issue.  Having a look at the statements SCOM is issuing, it's actually checking whether the AG secondary allows connections but it ignores the value and tries to connect anyway.  I believe this is a minor bug.  See below for these queries. 

You can vote to correct it through Microsoft Connect on: https://connect.microsoft.com/WindowsServer/feedback/details/1121330

--SCOM/MP: is this database a replica and does it allow connections?
SELECT d.name, d.database_id, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,         ar.secondary_role_allow_connections  
FROM sys.databases d  JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id  JOIN sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/ 
WHERE d.database_id = x  --current db id in sys.databases loop

--SCOM/MP: let's look into that db (regardless of whether it allows connections)
--EXCEPTION: The target database, 'x', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the 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.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.




Free Windows Admin Tool Kit Click here and download it now
February 9th, 2015 9:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics