March 21, 2013 at 9:17 am
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.!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply