2

I've set up a dev AlwaysOn environment on SSMS 2017. I have my AG set up:

enter image description here

enter image description here

I also ran the following scripts:

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB1.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://Dev-DB2.test.com:5022'));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB2','DEV-DB1')));

ALTER AVAILABILITY GROUP [DEV-SQLAG] MODIFY REPLICA ON N'DEV-DB2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('DEV-DB1','DEV-DB2')));

now when I connect to the listener (using SSMS2017) with ApplicationIntent=ReadOnly and I run select @@SERVERNAME it still shows me DEV-DB1. I thought it would show me that I'm getting data from the secondary DEV-DB2

What am I missing?

7
  • Can you please try this from a command line? --sqlcmd -S listenerName -E -d databaseName -K ReadOnly -Q "Select @@servername" Feb 1, 2018 at 16:45
  • still comes back with ------------------ DEV-DB1
    – Tomasz
    Feb 1, 2018 at 19:31
  • CORRECTION: the -K readOnly is causing an error. the rest of the script (when ran without the -K) returns DEV-DB1. with the -K I get an error "Error: Microsoft ODBC Driver 13 for SQL Server".
    – Tomasz
    Feb 1, 2018 at 19:47
  • What error as you getting?
    – Nic
    Feb 1, 2018 at 19:50
  • when I run sqlcmd -S DEV-Listener -E -d devdb -K readOnly -Q "Select @@servername" the command prompt returns a long error "Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login...."
    – Tomasz
    Feb 1, 2018 at 19:56

2 Answers 2

0

Found my issue: I was under the impression that my End Point URL should be the same as my routing URL, which was going to port 5022. after reading the following article read_only_routing_url I was able to identify my actual routing URL port which was 1433.

0

2 more things to be noted here while you are trying to connect to the Listener via the Management Studio for ReadOnly routing:

  • Make sure that you are also providing the database Name along with "ApplicationIntent=ReadOnly" in the Additional Connection parameters section of SSMS

  • SSMS sometimes has sticky connections so it could be possible that it is still connecting to an existing connection that was made via SSMS before providing the parameters "ApplicationIntent=ReadOnly". Try restarting the SSMS.

2
  • I'm not sure about the first bullet point but I did try restarting both instances. Didn't help.
    – Tomasz
    Feb 2, 2018 at 18:53
  • Regarding the first bullet point, you'll need to add the following parameters in the additional Connection parameters section of SSMS: "ApplicationIntent=ReadOnly;Database=Db1". And about the second point, you need to restart the management studio (SSMS) to avoid the sticky connections. Feb 6, 2018 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.