Recent Posts | - May, 2023-5,(1)
- February, 2023-2,(1)
- November, 2022-11,(1)
- July, 2022-7,(2)
- March, 2022-3,(1)
- November, 2021-11,(2)
- August, 2021-8,(2)
- July, 2021-7,(2)
- June, 2021-6,(1)
- May, 2021-5,(1)
- March, 2021-3,(1)
- February, 2021-2,(2)
- January, 2021-1,(7)
- December, 2020-12,(3)
- March, 2020-3,(2)
- February, 2020-2,(1)
- December, 2019-12,(2)
- November, 2019-11,(1)
- October, 2019-10,(1)
- September, 2019-9,(1)
- August, 2019-8,(1)
- May, 2019-5,(1)
- April, 2019-4,(2)
- March, 2019-3,(2)
- December, 2018-12,(1)
- November, 2018-11,(4)
- July, 2018-7,(1)
- May, 2018-5,(3)
- April, 2018-4,(2)
- February, 2018-2,(3)
- January, 2018-1,(3)
- November, 2017-11,(2)
- August, 2017-8,(1)
- June, 2017-6,(3)
- May, 2017-5,(5)
- February, 2017-2,(1)
- December, 2016-12,(1)
- October, 2016-10,(2)
- September, 2016-9,(1)
- August, 2016-8,(1)
- July, 2016-7,(1)
- March, 2016-3,(2)
- February, 2016-2,(3)
- December, 2015-12,(5)
- November, 2015-11,(5)
- September, 2015-9,(1)
- August, 2015-8,(2)
- July, 2015-7,(1)
- March, 2015-3,(2)
- February, 2015-2,(1)
- December, 2014-12,(4)
- July, 2014-7,(2)
- June, 2014-6,(2)
- May, 2014-5,(3)
- April, 2014-4,(3)
- March, 2014-3,(1)
- December, 2013-12,(2)
- November, 2013-11,(1)
- July, 2013-7,(1)
- June, 2013-6,(2)
- May, 2013-5,(1)
- March, 2013-3,(3)
- February, 2013-2,(3)
- January, 2013-1,(1)
- December, 2012-12,(3)
- November, 2012-11,(1)
- October, 2012-10,(1)
- September, 2012-9,(1)
- August, 2012-8,(1)
- July, 2012-7,(6)
- June, 2012-6,(1)
- April, 2012-4,(1)
- March, 2012-3,(3)
- February, 2012-2,(3)
- January, 2012-1,(4)
- December, 2011-12,(3)
- October, 2011-10,(3)
- September, 2011-9,(1)
- August, 2011-8,(10)
- July, 2011-7,(2)
- June, 2011-6,(7)
- March, 2011-3,(2)
- February, 2011-2,(3)
- January, 2011-1,(1)
- September, 2010-9,(1)
- August, 2010-8,(2)
- June, 2010-6,(1)
- May, 2010-5,(1)
- April, 2010-4,(3)
- March, 2010-3,(2)
- February, 2010-2,(3)
- January, 2010-1,(1)
- December, 2009-12,(3)
- November, 2009-11,(3)
- October, 2009-10,(2)
- September, 2009-9,(5)
- August, 2009-8,(3)
- July, 2009-7,(9)
- June, 2009-6,(2)
- May, 2009-5,(2)
- April, 2009-4,(9)
- March, 2009-3,(6)
- February, 2009-2,(4)
- January, 2009-1,(10)
- December, 2008-12,(5)
- November, 2008-11,(5)
- October, 2008-10,(13)
- September, 2008-9,(10)
- August, 2008-8,(7)
- July, 2008-7,(8)
- June, 2008-6,(12)
- May, 2008-5,(14)
- April, 2008-4,(12)
- March, 2008-3,(17)
- February, 2008-2,(10)
- January, 2008-1,(16)
- December, 2007-12,(6)
- November, 2007-11,(4)
|
|
|
|
Always On - Configure Read only routing
|
6/13/2017 12:35:20 PM
|
|
Configure read only routing for always on.
Necessary so that applicationintent=readonly goes to correct server.
Use SQLCMD to test, if it doesn't work (meaning return the read only replica server name), than you still need to configure the readonly routing, here is sqlcmd command:
sqlcmd -Svlnpaoam -E -d EAM -K READONLY -Q "Select @@servername"
Here is example to configure, ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH
(PRIMARY_ROLE (ALLOW_CONNECTIONS = all));
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH
(PRIMARY_ROLE (ALLOW_CONNECTIONS = all));
-----------------------------------------------------------------
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = Read_Only));
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://LNPCLXSQL10.HRBINC.HRBLOCK.NET:1600'));
-----------------------------------------------------------------
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = Read_Only));
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\eam11' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://lnpclxsql11.HRBINC.HRBLOCK.NET:1600'));
-----------------------------------------------------------------
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\eam10' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql11\eam11','lnpclxsql10\eam10')));
ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql10\eam10','lnpclxsql11\eam11')));
GO
|
Blog Home
|
|