Testing Read-Only Routing using SSMS

There are much better and easier ways to test a read-only routing configuration of an Availability Group. But here is my walk-through using SQL Server Management Studio.

  1. In SSMS connect to “Database Engine”
  2. Type in the name of the listener, a comma, then the port used by the listener
  3. Click the “Options” button
  4. On the Connection Properties tab choose “Connect to Database” then “Browse Server”
  5. Choose a database protected by the AG
  6. On the Additional Parameters tab type “ApplicationIntent=ReadOnly”
  7. Click “Connect”
  8. Run a read-only query like “SELECT @@SERVERNAME”
  9. The result should be the Secondary instance (AKA secondary replica)
  10. Delete the query-pane and connection
  11. Then set it up again – but without “ApplicationIntent=ReadOnly”
  12. Now the result of the query should be the Primary replica.

BONUS

The “much better ways” include using SQLCMD from another server. For example running this should return the name of the Secondary replica.

sqlcmd -S (ListenerName),(ListnerPort) 
-d (ProtectedDatabaseName) 
-q "SELECT @@SERVERNAME;" 
-K ReadOnly

Then, running it again without the last line should return the name of the Primary replica.

Note: It does not matter which Listener/Port you choose if there is more than one.