Enable Read-Only Routing using T-SQL in SQL Server 2014 AlwaysOn Availability Groups

Symptom:

C:\Users\administrator.DBAGLOBE>sqlcmd -S vmmag02,2433 -d reportserver -K readonly -Q "select @@servername"

Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : The target database ('reportserver') is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.

Enable Read-Only Routing using T-SQL in SQL Server 2014 AlwaysOn Availability Groups

select ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.primary_role_allow_connections_desc,
    ar.secondary_role_allow_connections_desc,
    ar.read_only_routing_url
from sys.availability_groups ag, sys.availability_replicas ar
where ag.group_id=ar.group_id
and ag.name='SSRS_AG'

ALTER AVAILABILITY GROUP SSRS_AG MODIFY REPLICA ON 'VMMSQL01' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL='TCP://VMMSQL01.dbaglobe.com:1433')) ALTER AVAILABILITY GROUP SSRS_AG MODIFY REPLICA ON 'VMMSQL02' WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL='TCP://VMMSQL02.dbaglobe.com:1433')) ALTER AVAILABILITY GROUP SSRS_AG MODIFY REPLICA ON 'VMMSQL01' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('VMMSQL02'))) ALTER AVAILABILITY GROUP SSRS_AG MODIFY REPLICA ON 'VMMSQL02' WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST =('VMMSQL01')))

 

Reference URL:

https://msdn.microsoft.com/en-us/library/hh710054.aspx

https://www.mssqltips.com/sqlservertip/2869/configure-sql-server-2012-alwayson-availability-groups-readonly-routing-using-tsql/