Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Always ON Secondary database Read Only Connection.....Please help. Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 11:35 AM
Points: 49, Visits: 168
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.!



Post #1433856
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse