SQL Server 2012: Always On Read Only Routing

Nel post precedente abbiamo visto come il Listener gira le connessioni in entrata sul server corretto. Sempre grazie al Listener è possibile fare in modo che, automaticamente, le connessioni che devono effettuare attività di sola lettura si connettano ad una replica secondaria, alleggerendo di fatto il principal da attività come la reportistica etc....


Prima di tutto dobbiamo configurare il Read-Only Routing, attività implementabile solo con T-SQL o Powershell.
Per farlo usando il T-SQL dobbiamo eseguire il comendo ALTER AVAILABILITY GROUP

--In caso il server WIN8SERVER sia replica secondaria consento le connessioni in sola lettura
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8SERVER' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

--In caso il server WIN8SERVER sia replica secondaria il suo URL d'accesso sarà WIN8SERVER.HyperV.Domain:1433
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8SERVER' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WIN8SERVER.HyperV.Domain:1433'));

--In caso il server WIN8MIRROR sia replica secondaria consento le connessioni in sola lettura
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8MIRROR' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

--In caso il server WIN8MIRROR sia replica secondaria il suo URL d'accesso sarà WIN8MIRROR.HyperV.Domain:1433
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8MIRROR' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WIN8MIRROR.HyperV.Domain:1433'));

--In caso il server WIN8MIRROR1sia replica secondaria consento le connessioni in sola lettura
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8MIRROR1' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

--In caso il server WIN8MIRROR1 sia replica secondaria il suo URL d'accesso sarà WIN8MIRROR1.HyperV.Domain:1433
ALTER AVAILABILITY GROUP MyAvailabilityGroup
 MODIFY REPLICA ON
N'WIN8MIRROR1' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://WIN8MIRROR1.HyperV.Domain:1433'));

--Creo routing list per WIN8SERVER quando è il server primary
ALTER AVAILABILITY GROUP MyAvailabilityGroup 
MODIFY REPLICA ON
N'WIN8SERVER' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN8MIRROR','WIN8MIRROR1','WIN8SERVER')));

--Creo routing list per WIN8MIRROR quando è il server primary
ALTER AVAILABILITY GROUP MyAvailabilityGroup 
MODIFY REPLICA ON
N'WIN8MIRROR' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN8SERVER','WIN8MIRROR1','WIN8MIRROR')));
GO

--Creo routing list per WIN8MIRROR1 quando è il server primary
ALTER AVAILABILITY GROUP MyAvailabilityGroup 
MODIFY REPLICA ON
N'WIN8MIRROR1' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN8SERVER','WIN8MIRROR','WIN8MIRROR1')));
GO

Prendiamo il caso in cui WIN8SERVER sia il principal mentre WIN8MIRROR e WIN8MIRROR1 siano le repliche secondarie.
L'applicazione si presenta al Listener con una connectionstring creata come la seguente:

SqlConnectionStringBuilder _cnB = new SqlConnectionStringBuilder();
SqlConnection _cn = new SqlConnection();

_cnB.DataSource = "MyAgListener";
_cnB.InitialCatalog = "MyTestDatabase";
_cnB.IntegratedSecurity = true;
_cnB.ApplicationName = "MyTestApplication";
_cnB.ApplicationIntent = ApplicationIntent.ReadOnly;
_cn.ConnectionString = _cnB.ConnectionString;

Specificando nel SqlConnectionStringBuilder la property _cnB.ApplicationIntent = ApplicationIntent.ReadOnly indichiamo che la nostra connessione deve effettuare solo attività di lettura. Avendo configurato precedentemente per il server WIN8SERVER la routing list
PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('WIN8MIRROR','WIN8MIRROR1','WIN8SERVER')) la connessione verrà aperta sul server WIN8MIRROR, alleggerendo di fatto WIN8SERVER dalle letture effettuate dalla nostra connessione. Qualora WIN8MIRROR non fosse disponibile il routing verrebbe effettuato su WIN8MIRROR1. Se anche quest'ultimo non fosse disponibile la connessione verrebbe effettuata su WIN8SERVER. Il tutto in modo automatizzato. Il client è solo a conoscenza del listener e non delle repliche secondarie.

Ho fatto una piccolissima applicazione in Visual studio 11 beta per testare i SQL Native Client e l'always On.
L'applicazione effettua letture e scritture su un database. Il server principal è WIN8SERVER, WIN8MIRROR e WIN8MIRROR1 sono repliche secondarie.
La attivo per effettuare operazioni di scrittura connettendomi al Listener ed effettuo 1000 Insert into. In questo caso la connectionstring non specifica la
keyword ApplicationIntent=ReadOnly.

Mi connetto al listener specificando anche ApplicationIntent=ReadOnly

Come potete vedere nella prima immagine l'applicazione connessa in read/write si connette a WIN8SERVER l'istanza Principal, l'applicazione connessa in Read-Only si connette a WIN8MIRROR, replica secondaria in Read-Only specificata nella Routing List di WIN8SERVER.

Ciao

Luca