Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Tuesday, 13 June 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, 13 June 2017 11:35:20 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
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