none
SQL Server 2012 AlwaysOn- la guida passo passo di Ruggiero Lauria

    Discussione generale

  • Ciao a tutti,

    Visto che la tecnologia AlwaysOn è una delle novità importante di SQL Server 2012, Ruggiero Lauria si è dedicato a realizzare una guida passo passo che ha voluto condividere anche con voi, appassionati di SQL che volete approfondire il contenuto.

    Eccola qui sotto, tutto il contenuto lo trovate anche sul suo Tech Blog. Condividere ha il suo costo, ma alla fine finisce per arricchire tutti ;-)

    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 

    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

    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) Attraverso 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) Facciamo un Backup Full nello Share di cui al punto 5

    8) 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 Owner è 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'));
    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:


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

    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!  

    Anca Popa Follow ForumTechNetIt on Twitter

    Microsoft offre questo servizio gratuitamente, per aiutare gli utenti e aumentare il database dei prodotti e delle tecnologie. Il contenuto viene fornito “così come è” e non comporta alcuna responsabilità da parte dell'azienda. 


    martedì 12 marzo 2013 16:51
    Proprietario

Tutte le risposte

  • Ottimo!

    Grazie.


    Luigi Bruno
    MCP, MCTS, MOS, MTA

    venerdì 28 febbraio 2014 09:36
  • Figurati, il merito e' tutto di Ruggiero (che ha dedicato tempo, fatica e impegno per realizzare la guida)- grazie, Ruggiero :-)

    Anca Popa Follow ForumTechNetIt on Twitter

    Microsoft offre questo servizio gratuitamente, per aiutare gli utenti e aumentare il database dei prodotti e delle tecnologie. Il contenuto viene fornito “così come è” e non comporta alcuna responsabilità da parte dell'azienda.

    venerdì 28 febbraio 2014 10:08
    Proprietario
  • Così mi fate arrossire ;-)

    Ciao e grazie


    Ruggiero Lauria
    MCT-MCITP-MCSA-MCSE-MS SQL DBA

    venerdì 28 febbraio 2014 11:33
  • Ciao,

     grazie per aver descritto la procedura di configurazione. Domando è necessario un Active Directory oppure i nodi SQL posso essere in WORKGROUP?

    Saluti

    Alpe

    venerdì 3 marzo 2017 13:47
  • Allora è la ncecessità  del Cluster Microsoft che ti obbliga al dominio.

    Direi che se non hai Windows Server 2016 non ne vieni fuori, altrimenti puoi provare questa strada:

    https://www.sqlpassion.at/archive/2016/01/11/how-to-create-a-sql-server-availability-group-without-an-active-directory-domain/

    Ciao e buon lavoro


    Ruggiero Lauria
    MCT-MCITP-MCSA-MCSE-MS SQL DBA

    venerdì 3 marzo 2017 14:01
  • Grazie per la veloce risposta,

     quindi evitando la soluzione linkata, con due Microsoft Server 2016 ed SQL 2012 (o superiore) è possibile configurare una coppia di server SQL con un database Always On?

    Saluti

    Alpe

    venerdì 3 marzo 2017 14:47
  • Non ti seguo più.

    Perché dovresti evitare la soluzione che ti ho linkato visto che è esattamente la risposta a quanto mi hai chiesto?

    Ti chiederei di essere un pò più preciso se possibile


    Ruggiero Lauria
    MCT-MCITP-MCSA-MCSE-MS SQL DBA

    venerdì 3 marzo 2017 15:23
  • Perdon,

     avevo inteso che la soluzione linkata fosse una altrnativa non "ufficiale". Ma effettivamente capisco che utilizzando W2016 nella configurazione suggerita nel link ottengo appunto una coppia di SQL in Alway On.

    Saluti

    alpe

    venerdì 3 marzo 2017 15:39