SQL Server 2012 AlwaysOn

english version


Introduzione

Una delle più grandi novità introdotte da SQL Server 2012 sotto il profilo dell' alta disponibilità è la tecnologia AlwaysOn.
Questa si suddivide in due tipologie:

1. AlwaysOn  Failover Cluster Instance: Protezione a livello di singola istanza, miglioramento della tradizionale installazione in Cluster di SQL Server nel monitoraggio dello stato di salute e nelle implementazioni Multi-Subnet
2. AlwaysOn Availability Group: Protezione a livello di Database, evoluzione del DB mirroring delle versioni precedenti di SQL Server 

Source: Microsoft Learning

 La prima soluzione in termini archietturali poco aggiunge alle precedenti versioni se non l'eliminazione della necessità di creazione di una VLAN per i nodi del cluster; invece i gruppi di diponibilità sono una vera novità, forse uno di quei particolari per cui varrebbe la pena di migrare ad SQL 2012.

 AlwaysOn Availability Group

Generale

Questa tecnologia sfrutta i vantaggi offerti dal Windows Server Failover Cluster (WSFC) per fornire una soluzione di alta disponibilità per i Database SQL Server. E' un evoluzione della tecnologia di DB mirroring, introdotta con la versione SQL Server 2005, ma offre questi fondamentali vantaggi aggiuntivi:

1. Il numero dei nodi non è più limitato a 2
2. Il failover non è più fatto dal client, ma avviene lato server in maniera trasparente per i client
3. E' possibile accedere in sola lettura ad un nodo passivo del gruppo di disponibilità
4. E' possibile eseguire le operazioni di backup su un nodo passivo  del gruppo di disponibilità

 Nonostante questa tecnologia si basi sul Failover Cluster non è necessario:

1. Avere uno storage condiviso fra i nodi del cluster
2. Fare un installazione in Cluster di SQL Server

 Quindi ogni nodo avrà un'installazione autonoma di SQL Server con i dati su uno storage locale del nodo.

Note:

•  In Windows Server 2012 la funzionalità di Failover Cluster è disponibile anche sulla versione Standard 
• Non esiste nessun problema nel creare nodi virtualizzati, se non le solite raccomandazioni relative al fare girare SQL Server su una macchina virtuale

Terminologia

Source: Microsoft Learning

  
AlwaysOn Availability Group: gruppo di  server (in cluster) che mette in alta disponibilità uno o più Database
Primary Replica:  istanza che ospita la copia in lettura e scrittura del Database

Secondary Replica: host che ospita una copia del database su cui è possibile abilitare accessi in sola lettura

Listener: consiste di un nome DNS, una porta TCP ed uno o più indirizzi IP attraverso cui i client si connettono al gruppo di disponibilità
Availability Modes:  modalità di replica dei dati fra il membro primario ed i secondari, può essere:

1. Commit-Synchronous: la replica primaria attende conferma che le modifiche siano state apportate sul membro secondario prima di chiudere la transazione. Affidabile ma pericoloso per le performances
   
2. Commit-Asynchronous: il membro primario invia le modifiche al secondario e chiude la transazione senza aspettare conferma sull'esito.Non affidabile ma performante.

Failover: processo attraverso il quale il ruolo di replica primaria viene passato ad una replica secondaria, chiamata failover target. Sono disponibili 3 tipi di failover:

1. Failover automatico (senza perdita di dati): disponibile quando sia la replica primaria che la secondaria operano in modalità sincrona (Commit-Synchronous). In questo caso la modalità di failover nel gruppo è impostata su automatica ed  il passaggio di ruoli avviene senza alcun intervento amministrativo.
2. Failover manuale pianificato (senza perdita di dati): come nel caso precedente i due membri operano in modalità sincrona, ma non essendo stato impostato il failover automatico è necessario l'intervento amministrativo  per inviare il comando di failover.
3. Failover manuale forzato (con possibile perdita di dati): i due membri di replica operano in modalità asincrona e quindi l'amministratore può forzare il failover sapendo che eventuali transazioni del membro primario non ancora sincronizzate andranno perse.

Active Secondary Replicas: per migliorare le performance e gestire in maniera più efficiente le risorse del gruppo di disponibilità è possibile indirizzare le richieste di sola lettura e backup verso repliche secondarie


Installazione

L'obiettivo di questo articolo è centrato sugli Availability Group e non sulla configurazione del Cluster di Failover, quindi non entrerò nei dettagli su questa parte.

1) Installare la funzionalità di Failover Cluster sui Server

2) Creare un Cluster di Failover che comprenda tutti i Server

nel nostro esempio abbiamo creato un cluster MIA-CLUSTER a cui partecipano i server:
MIA-CLUST1
MIA-CLUST2
MIA-CLUST3

Notare che nel Cluster non c'è Storage condiviso.  
 

3) Installare un'istanza Standalone di SQL Server 2012 su ogni Server

 

Note:

• Usate un account di dominio per il Servizio SQL
• Potete installare solo il Motore di Database, opzionalmente anche gli strumenti di gestione

4) Attaverso il Configuration Manager abilitate AlwaysOn Availability Group su ogni Server

Riavviate i servizi di SQL Server

5) Create una condivisione di rete per i file di Backup utilizzati per sincronizzare le repliche

6) Creiamo i DataBase che vogliamo proteggere sul Server che diventerà la Replica Primaria

Nel nostro caso il Server che svolgerà il ruolo di Replica Primaria è MIA-CLUST1 e mettiamo in alta disponibilità il Database Sales
Controllate che il Recovery Model sia Full

 

7) Creiamo un AlwaysOn Avalaibilty Group

Lanciamo il Wizard dal Server Primario

 

 Specifichiamo il nome del nostro AG

 

 Selezioniamo il database Sales

Aggiungiamo le Repliche Secondarie: Add Replica...

 

 

Configuriamo il Listener

 

Impostiamo il Folder per la sincronizzazione

Controlliamo l'esito

  Il warning ricevuto relativo al WSFC è trascurabile

Risultato Finale:

Il Database Sales è replicato su tutti e tre i Server   

 

 Nel cluster è stato creato un servizio il cui Onwer è il nostro Primary Replica

Configurazione

Adesso facciamo un esempio di 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 verrano 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  già possible 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 in 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 questi comandi sulla nostra Replica Primaria:

___________________ 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 connettono 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

Conclusione

Spero che questa guida passo passo possa esservi di aiuto per sfruttare appieno questa opportunità che ci viene offerta da SQL Server 2012.
Tutte le correzioni ed i commenti sono i benvenuti
Buon divertimento!