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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy