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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL 2012 - General » Always ON Secondary database Read Only...

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

Rate Topic

Display Mode

Topic Options

Author

Message

ratheesh4sql

Posted Thursday, March 21, 2013 9:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 1:59 AM
Points: 48, Visits: 152

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 »

Permissions