The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Sunday, November 10, 2024 Login
Public

Blog posts for the month of June,2017.
Asplundh Delivers !4/28/2017 8:51:13 PM

Asplundh delivers, wood chipper mulch dropped right to you by the truck load.

 

 
Bull Calf 20176/14/2017 8:41:36 PM

Faith has first calf of 2017, a Bull calf.

 

 
Always On - Configure Read only routing6/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