Always ON Secondary database Read Only Connection.....Please help.

• Ratheesh4sql

  SSCommitted

  Points: 1811

  More actions

  March 21, 2013 at 9:17 am

  #270071

  Dear All,

  I have configured SQL always on with two server and enabled SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing by using below query.

  But when i am checking the Read only connection using (sqlcmd -S X.X.X.X -E -d DatabaseName-K ReadOnly) the connection is still routing to primary serever only...

  Please help if any additional things to do

  --Specify a read_only_routing_url

  ALTER AVAILABILITY GROUP AlwaysOnAGProd

  MODIFY REPLICA ON

  'ALWAYSON-AG1'

  WITH

  (

  SECONDARY_ROLE

  (

  READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG1.TESTDOMAIN.local:1433'

  )

  )

  -------------------------------------------------------------------------------------------------

  ALTER AVAILABILITY GROUP AlwaysOnAGProd

  MODIFY REPLICA ON

  'ALWAYSON-AG2'

  WITH

  (

  SECONDARY_ROLE

  (

  READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG2.TESTDOMAIN.local:1433'

  )

  )

  -------------------------------------------------------------------------------------------------

  --Specify a read-only routing list

  ALTER AVAILABILITY GROUP AlwaysOnAGProd

  MODIFY REPLICA ON

  'ALWAYSON-AG1'

  WITH

  (

  PRIMARY_ROLE

  (

  READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2')

  )

  )

  -------------------------------------------------------------------------------------------------

  ALTER AVAILABILITY GROUP AlwaysOnAGProd

  MODIFY REPLICA ON

  'ALWAYSON-AG2'

  WITH

  (

  PRIMARY_ROLE

  (

  READ_ONLY_ROUTING_LIST =('ALWAYSON-AG1')

  )

  )

  -------------------------------------------------------------------------------------------------

  Testing Method:

  C:\ sqlcmd -S X.X.X.X -E -d DatabaseName -K ReadOnly

  : Select @@serverName

  : Go

  Primary server Name am getting.!