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?

  • Can you please try this from a command line? --sqlcmd -S listenerName -E -d databaseName -K ReadOnly -Q "Select @@servername" – SqlWorldWide Feb 1 '18 at 16:45
  • still comes back with ------------------ DEV-DB1 – Tomasz Feb 1 '18 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 '18 at 19:47
  • What error as you getting? – Nic Feb 1 '18 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 '18 at 19:56
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.

  • I'm not sure about the first bullet point but I did try restarting both instances. Didn't help. – Tomasz Feb 2 '18 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. – Masood Hashim Feb 6 '18 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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