Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, June 13, 2017

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
Tuesday, June 13, 2017 11:35:20 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Agent will not start when a use...
SQL Instance will not fail back to ...
Hash Join vs. Merge Join
Recent Posts
Archive
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll