I have configured an AlwaysOn Availability Group without any problem.
The database is mirrored synchronously and both SQL Server works correctly.
I have created an Availavility Group Listener and configured the primary server to accept only ReadWrite connections and secondary to accept only ReadOnly connections.
With .NET Framework 4.5 I use SqlClient.SqlConnection to connect to the Listener, this is the connection string:
Server=tcp:SQLListen.domain-name.com, 1435;Database=TestData;User id=user;Password=password;MultiSubnetFailover=True;ApplicationIntent=ReadOnly
Microsoft documentation says that with this ConnectionString, when I use ReadOnly, the connection will be made to Seconday Server and ReadWrite connection will be made to Primary Server, buy I always get connect to primary one.
When using ReadOnly I receive an error because primary server doesn't accept ReadOnly connections.
And when I connect with ReadWrite I alsa connect to primary server, as it should do.
I have tried connecting to domain name and also to direct availavility group listener IP address.
Could anybode tell me what I'm doing wrong?.
Thank you.
I've found the solution.
What I'm trying to do is called Read-Only Routing List.
To configure you should assign a new URL to any server with the command:
ALTER AVAILABILITY GROUP [Availavility Group]
MODIFY REPLICA ON N'Replica Node' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'tcp://route:1433'))
and then assign to the primary node a list of routing to secondaries:
ALTER AVAILABILITY GROUP [<your availabiliry group>]
MODIFY REPLICA ON N'<your availability group replica>' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'<first preference replica>', N'<second preference replica>')))
All information found here: