target database participating in availability group error
-
Sharepoint connects to the database using the cluster through an alias defined in cliconfig.
Two server in availability group - DB1 and DB2. Currently, db2 is the primary. But when the failover is initiated (either from the windows cluster level, or alwayson dashboard) it completes successfully with all databases synced. However, it causes service outage and in the logs of the secondary we see "target database xxx is participating in an availability group and is currently not accessible for queries" for all the databases in the group.
Any help in troubleshooting would be appreciated.
-
Do you have a listener configured in your AG?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
No listener configured.
-
That's you answer. Create a listener, and have everything connect to that
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Will give that a shot and update the forum. Thank you!
- noobdba wrote:
Will give that a shot and update the forum. Thank you!
Hope the Listener did the trick for you.
The listener act as a single name to both the nodes. Use read-only routing in your application to make use of secondary for read connections.
-
You have already installed and configured SharePoint, you cannot change the connection string. You will have to change the alias to point to the listener
-
Created a listener. The first time it failed over there was still one database that was registering the errors on the secondary. It cleared later. A couple of other failover seemed to be clean.
But now getting Service Broker endpoint is in disabled or stopped state every 15 minutes on the secondary. I haven't seen that in other alwaysOn in the environment.
- noobdba wrote:
Created a listener. The first time it failed over there was still one database that was registering the errors on the secondary. It cleared later. A couple of other failover seemed to be clean.
But now getting Service Broker endpoint is in disabled or stopped state every 15 minutes on the secondary. I haven't seen that in other alwaysOn in the environment.
Check the owner of the endpoint.
Look at this article
http://www.dbafire.com/2017/03/15/change-the-owner-endpoints-on-availability-groups/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
The owner belongs to a domain account of person who set it up. The account is still active. However, the message occurs every 15 minutes.
- noobdba wrote:
The owner belongs to a domain account of person who set it up. The account is still active. However, the message occurs every 15 minutes.
The owner should be sa
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thank you. Looking at the databases about three have service broker set as false or 0. Would this be the cause of the message? Also, does it need to be enabled for the databases? Just trying to understand the context of databases in AG and their service broker attribute.
- noobdba wrote:
Thank you. Looking at the databases about three have service broker set as false or 0. Would this be the cause of the message? Also, does it need to be enabled for the databases? Just trying to understand the context of databases in AG and their service broker attribute.
None of the databases in any of my AG's have service broker set to on.
When you have an AG, there is the primary, which is read/write. The secondaries can be read-only, in which case you would want to set up read-only routing. If that is set up, you add "ApplicationIntent=ReadOnly" to a connection string, and SQL Server will automatically route any queries that have that set to the read-only secondary. A good example for this is reporting. The app connects to the listener, and performs read/write operations. A report uses the connect string with ApplicationIntent=ReadOnly in it, and it gets offloaded to the secondary.
If you have an application that is connecting directly to one the secondary servers, and is attempting to write to the database, you will see this error. You will also see this error if the secondary does not allow read-only access.
During a failover, if an application is attempting to connect when the database(s) are not fully back online, you will also see this error.
Are the databases set to synchronous, or async?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
It is set to synchronous commit on both. I do not see "target database participating in availability group" at this time. The listener was set up. But after setting that up receiving the messages on service broker every 15 min. The databases are all online
- noobdba wrote:
Thank you. Looking at the databases about three have service broker set as false or 0. Would this be the cause of the message? Also, does it need to be enabled for the databases? Just trying to understand the context of databases in AG and their service broker attribute.
If the database on the primary has service broker enabled - you need to enable the service broker on the secondary.
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 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply