Today while poking around the AlwaysOn Availability Groups feature I came across the above error when testing the read-only access routing configuration. That configuration allows connections with declared ReadOnly intent to be routed to one of the read-only secondary replicas.
The configuration looked like this:
- 3-node Windows 2008 R2 Core cluster
- 3 instances of SQL 2012 each on one node
- 2 databases in the Availability Group
- Each of the replicas was set to allow only ReadWrite connections when in primary role
- Each of the replicas was set to ReadOnly intent connections when in secondary role
- Read-only URL access for each replica was set correctly
- Read-only routing list was also set correctly
I was testing connectivity from Management Studio to open a new Database Engine Query window (not the Object Explorer) and was using the following options:
- Connect to database: <default>
- Additional Connection Properties tab has ‘ApplicationIntent=ReadOnly’
However when I tried to USE any of the databases in the AG, it would disallow me. When I checked @@SERVERNAME it actually reported the instance name of the PRIMARY replica! So clearly the routing was not working.
Then it dawned on me that the default database for this login being master, it was causing an issue with the routing. On changing the ‘Connect to database’ to XYZ (the name of the AG database) it was routing me correctly to the read-only secondary.
Of course, this is only ONE reason for the above error. Other possibilities include the fact that your AG configuration is incorrect and does not allow read-only secondary in any case.
Hope this helps you!