Notes on SQL

Background

Because one of the nodes in our AG was having Windows updates applied, it was necessary to suspend data movements for all databases involved in the AG, on that server. Upon my return (after the maintenance was completed) I was unable to connect to the server, with a message that didn’t really match the circumstances – or so I believed.

The login failure

Attempting to connect to the server, I was faced with the response below:

“The target database, <db name>, 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. (Microsoft SQL Server, Error: 876)”

This was a bit of a puzzle, as I knew there were databases suspended, which is why I needed to connect to the server and this node of the AG was definitely set for read-only access.

The clue is at the start of the error message, where it says ‘target database’. Having looked at my connection properties it says ‘default’.

So, I decided to select a specific database, in case this was the issue. Within the drop-down list for ‘Connect to database’ I choose to browse the server, to see the list of databases.

Whereupon the same error message appears, making it appear that I can’t change this setting at all.

However, just because I can’t select a database name, it doesn’t mean I can’t just type a name straight into that field. In the ‘Connect to database’ I type ‘master’, hit return and the connection works fine – I’m logged into the server.

Checking my login properties

Now that I’m in the server, if I look at my login properties, the ‘Default database’ entry had the name of one the databases within the AG. Which of course means that if there is ever an issue with that database then I won’t be able to connect. Pretty much what the error message was trying to tell me.

So, I have made sure that my ‘Default database’ is set to a system database, master is an obvious choice. If master is unavailable I will have larger issues.

Conclusion

Ensuring that my connections all default to master will remove this issue, when connecting to AG servers. I usually do default to master but never had a reason for that – now I have.