SQL Server 2012 AlwaysOn: Configurazione e Read-Only Routing – Parte 2

Ben ritrovati. A qualche giorno dalla pubblicazione dell’articolo di Ruggiero Lauria su una delle novità di Sql Server 2012, AlwaysOn, concludiamo l’argomento illustrandone la configurazione e il Read-Only Routing.

Configurazione

Entriamo nelle proprietà del nostro Availability Group

Impostiamo MIA-CLUST2 in modalità sincrona ed abilitiamo il failover automatico.
Lasciamo MIA-CLUST3 in modalità asincrona, e quindi il failover può essere solo manuale.
Rendiamo ambedue le Repliche Secondarie leggibili ed analizziamo meglio le opzioni disponibili.
Ricordiamo che ogni server può potenzialmente agire in due modalità: Primario e Secondario; qui abbiamo le opzioni disponibili per ogni server in base al ruolo che svolge (la situazione può essere dinamica):
Connections in primary role: quando agisce da primario, può accettare tutte le connessioni o solo quelle in scrittura.

Readable Secondary: quando agisce da secondario può non accettare connessioni, accettarle tutte (ma dare errore se si prova a modificare i dati) o accettare solo le connessioni che abbiano esplicitamente Intent=Readonly

Andiamo poi nella sezione Backup Preferences.
Lasciamo il default a Prefer Secondary, in questo modo i backup verranno sempre fatti sui secondari, se disponibili.
Cambiamo la priorità del backup mettendo avanti il Server MIA-CLUST3

I risultati delle nostre impostazioni sono anche visibili dalla Dashboard dell' AG

Read-Only Routing

Dopo aver reso le nostre repliche secondarie leggibili, è possibile accedervi puntando direttamente al nome del Server.
Esempio di accesso al MIA-CLUST2

Esempio di accesso a MIA-CLUST3 (impostato read-intent only, notare l'errore nel primo tentativo)

Ma se vogliamo creare un meccanismo che indirizzi automaticamente le richieste di sola lettura verso le Repliche Secondarie dobbiamo implementare il Read-Only Routing.
Per la configurazione potete fare riferimento a questo articolo ma riassumendo:

· Dobbiamo avere configurato un Listener e noi lo abbiamo fatto nel wizard:

DNS: MIA-SQL-CLUSTER
          IP: 10.10.0.40
          TCP Port: 1433

· Identifichiamo la URL per connessione ad ogni Server membro del nostro AG

           Nel nostro caso: TCP://MIA-CLUST1.AdventureWorks.msft:1433
                                     TCP://MIA-CLUST2.AdventureWorks.msft:1433
                                     TCP://MIA-CLUST3.AdventureWorks.msft:1433
          Potete utilizzare questo script  realizzato da Matt Neerincx che fornisce un output del tipo

· Configuriamo i nostri Read_only_routing dando una serie di comandi sulla nostra Replica Primaria, riassunti nel seguente script:

___________________ Start ____________________________
-- Configure Read_Only_Routing
-- This script need to be paramatrized with your deployment data!
--  Ruggiero Lauria
-- Enable Server as Readable when acting as Secondary Replica  
-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL
ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST1.AdventureWorks.msft:1433'));

-- Enable Server as Readable when acting as Secondary Replica
-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST2.AdventureWorks.msft:1433'));

-- Enable Server as Readable when acting as Secondary Replica   
-- This command is not strictly necessary because we have already configured it
-- trough AG propriety. But for completeness I preferred to include it in the script

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Define Server Read_only_routing URL 

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://MIA-CLUST3.AdventureWorks.msft:1433'));

-- Define Read Routing List when Server is acting as Primary Replica

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));

-- Define Read Routing List when Server is acting as Primary Replica 

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST2' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST3')));

-- Define Read Routing List when Server is acting as Primary Replica

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST3' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST1','MIA-CLUST2')));
GO
_________________End ________________________

Nota: I Server indicati nella routing list verranno usati in sequenza in base alla disponibilità, non esiste alcuna forma di Load Balancing o round robin.

Test di Connettività

Di seguito il processo utilizzato dal Read Only Routing:

1. Il client si conne al Listener del nostro Availability Group

2. Il client viene reindirizzato (sempre!) verso la Primary Replica

3. Il client ha specificato nella stringa di connessione ApplicationIntent=ReadOnly

4. Il sever controlla che il database di destinazione faccia parte di un Availability Group

5. Se è vero il punto 4, il server controlla se è impostata una read_only_routing_list sulla Primary Replica

6. Se è vero il punto 5 il server controlla in ordine che i server elencati nella Routing_List stiano sincronizzando ed accettino connessioni (allow_connections=read_only or all)

7. Il server legge la read_only_routing_url della prima replica secondaria pronta ad accettare connessioni e la passa al client

8. Il client legge la URL e si ridireziona verso l'istanza secondaria leggibile

Per eseguire un test di connettività si possono utilizzare i Reporting Services, quindi nel nostro esempio farò un semplice report (l'esempio è banale!) sulla tabella sales.dbo.orders includendo il nome del server che sta rispondendo con la seguente query:

Select OrderDate, OrderTotal, @@ServerName as Server from Orders

Primo Test: accediamo con ApplicationIntent=ReadWrite

E riceviamo risposta dalla Primary Replica

Adesso cambiamo il tipo di connessione in ApplicationIntent=ReadOnly

E rilanciando il report

Siamo stati reindirizzati verso il primo server della routing list definita per Primary Replica:

ALTER AVAILABILITY GROUP [MIA-SQL-AG]
MODIFY REPLICA ON
N'MIA-CLUST1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('MIA-CLUST2','MIA-CLUST3')));

Comandi Utili

Esempio di stringa di connessione:

Server=tcp:MIA-SQL-CLUST,1433;Database=Sales;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
Restart del listener
ALTER AVAILABILITY GROUP [MIA-SQL-AG] RESTART LISTENER 'MIA-SQL-CLUST';
Related  System Views 
SELECT * FROM sys.dm_tcp_listener_states
SELECT * from sys.availability_replicas
SELECT * from sys.availability_read_only_routing_lists