Microsoft SQL Server è un database davvero ricco di caratteristiche sul fronte della replica, del mirroring e del failover clustering. Sono tutte strategie e configurazioni che hanno come scopo, con modalità diverse, quello di rendere i dati sempre disponibili alle varie applicazioni in caso di crash o disservizi, oppure semplicemente di replicare i dati in più posizioni geografiche per incrementare le prestazioni nella loro accessibilità.
In questo tutorial non parleremo di Failover Clustering e e gruppi di disponibilità Always On, che sono modalità di mirroring dei database con lo scopo principale di assicurare la continuità dei servizi in modo immediato nel caso si verifichino dei disservizi o crash di server. Ci concentreremo invece sulle modalità di replica dei database, ovvero quelle configurazioni che ci consentono di avere tutti i dati replicati su più Server SQL in modo del tutto automatizzato e in tempo reale.
SQL Server offre 4 tipi di replica:
- Transazionale
- Merge
- Snapshot
- Peer to Peer
La replica peer-to-peer è una soluzione per la scalabilità orizzontale ad elevata disponibilità in quanto consente di gestire copie dei dati in più istanze del server, definite “nodi”. Basata sulla replica transazionale, la replica peer-to-peer propaga a tutti i nodi e quasi in tempo reale le modifiche ai dati. Grazie a questa ridondanza, le applicazioni che richiedono la scalabilità orizzontale delle operazioni di lettura possono distribuire le operazioni di lettura dei client in più nodi, e accedere ai dati in modo molto più veloce. Questo tipo di replica è certamente una delle più performanti e a prova di crash, dal momento che la mancata disponibilità di uno o più nodi non inficia il funzionamento e la disponibilità del sistema.
Tuttavia, la modalità di replica peer-to-peer è disponibile solo sulla versione Enterprise di SQL Server, quindi con costi di licenza certamente molto elevati. Una buona alternativa, sicuramente più alla portata di piccole e medie imprese, e certamente stabile e flessibile nella configurazione, è la replica di tipo Merge, di cui ora parleremo.
Passiamo quindi subito alla parte pratica. Di cosa abbiamo bisogno per configurare una replica di tipo Merge e come metterla in piedi in pochi semplici passaggi.
Partiamo da un esempio di 3 SQL Server 2014, che consideriamo geograficamente distanti e quindi su reti differenti. Lo schema che andremo a ottenere è quello mostrato nella figura sottostante:
Ci dovrà quindi essere un server principale definito Publisher, e server secondari definiti Subscribers. I subscribers sincronizzeranno i loro dati con il Publisher (invio e ricezione) e di conseguenza, anche i dati dei subscribers saranno sincronizzati tra loro. Quindi, la prima cosa da fare è decidere quale dei tre server dovrà essere il Publisher e procedere alla sua configurazione, come illustrato di seguito.
Come prima cosa, accertiamoci che il servizio SQL Server Agent sia avviato e in modalità automatica, altrimenti avviamolo.
PASSO 1: Creare la Pubblicazione sul server Publisher
Cominciamo quindi a configurare le varie opzioni nel wizard di creazione della pubblicazione. Andiamo subito avanti nella prima schermata:
Ora selezioniamo il database che vogliamo sincronizzare:
Quindi scegliamo il tipo di replica che vogliamo effettuare, ovvero la “Merge publication“:
Nella schermata successiva, lasciamo le opzioni di compatibilità in default e procediamo cliccando “Avanti”:
Ora c’è invece una parte più importante, ovvero selezionare quali tabelle del database vogliamo sincronizzare. Selezionarle è molto semplice:
Procedendo alla schermata successiva, il wizard ci dice che verrà aggiunto un nuovo campo ID alle tabelle selezionate per identificare i record in modo univoco.
Procediamo quindi alla schermata successiva dove ci verrà chiesto se vogliamo configurare anche dei filtri per i dati da sincronizzare. Nel nostro caso non utilizzeremo filtri, quindi procediamo cliccando su “Avanti”:
Nella schermata successiva, lasciamo selezionate le due opzioni, come in default, e procediamo:
Ora invece arriva una parte importante, ovvero l’impostazione degli account per eseguire il servizio di replica (agent) e per collegarsi al Publisher, quindi, in questo caso, a se stesso.
Nel primo riquadro, mettiamo il nome utente amministratore del server, con davanti il nome del server o del dominio. Nel nostro caso, consideriamo che il server si chiami PUBLISHER.
Nel riquadro sotto invece mettiamo l’account di SQL Server, con in quale è possibile connettersi al database con i massimi privilegi. Nel nostro caso abbiamo usato l’utente di default “sa”.
NOTA BENE: la cosa migliore è avere lo stesso utente di SQL Server e la stessa password su tutti i server database (sia sul publisher che sui subscribers).
Confermiamo cliccando OK e procediamo alla schermata successiva.
Nella schermata successiva, lasciamo selezionata l’opzione per creare la pubblicazione e procediamo:
Vediamo infine il riepilogo delle nostre impostazioni e possiamo quindi creare la pubblicazione cliccando su Finish:
Verrà aperta una finestra di progress dello snapshot iniziale e le due operazioni dovranno essere completate correttamente.
PASSO 2: Configurare la modalità con cui il server Publisher consentirà ai subscribers di accedere ai dati da lui pubblicati: FTP Server
Una delle configurazioni fondamentali della replica è la modalità con cui i subscribers possono accedere ai dati “pubblicati” dal publisher. Fisicamente, il publisher crea uno snapshot periodico dei dati in una sua cartella locale, e aggiorna questo snapshot con le modifiche ai dati. Bisogna quindi fare in modo che i subscribers possano accedere a questi snapshot, per sincronizzare i propri dati con il publisher. Dato che nel nostro esempio parliamo di server remoti (quindi non nella stessa LAN), la modalità che abbiamo scelto per rendere disponibili i dati ai subscribers è quella FTP (altrimenti sarebbe stato possibile usare una semplice cartella condivisa in rete).
Dobbiamo quindi configurare sul server publisher un server FTP che renda accessibile da remoto ai subscribers la cartella dove risiedono gli snapshot dei dati. Possiamo usare sia il server FTP di IIS, quindi quello di default di Windows, sia un software di terze parti, come ad esempio FileZilla Server. Per la configurazione FTP di IIS rimandiamo alla guida ufficiale Microsoft https://technet.microsoft.com/it-it/library/hh831655(v=ws.11).aspx.
Prima però dobbiamo effettuare una modifica alla configurazione della pubblicazione appena creata, per dirgli appunto di usare l’FTP:
Configuriamo quindi i dati di connessione (queste informazioni dovranno riflettere la configurazione del server FTP). Mettiamo il nome del server FTP (uguale al nome del server), la porta (21 è quella di default, ma per sicurezza si potrebbe scegliere una porta diversa), il nome utente e la password FTP (se usiamo IIS, sceglieremo un nome utente di Windows, avendo cura che sia un amministratore, se invece usiamo un altro server FTP, mettiamo qui l’utente che abbiamo configurato su questo). Infine, specifichiamo il percorso relativo della cartella contenente gli snapshot, a partire dalla root del server FTP. Quando si configura questa modalità, SQL Server crea (di default) una cartella che chiama “ftp” (con dentro lo snapshot dei dati) nel percorso “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\repldata”. Quest’ultima va configurata come root del server FTP, e quindi, il percorso relativo da specificare in questa finestra sarà proprio “/ftp”, come mostrato in figura.
NOTA BENE: sarà necessario aprire la porta FTP mediante una apposita regola sul firewall di Windows del server Publisher. Inoltre, nel caso dovessero verificarsi errori di connessione nonostante la corretta configurazione firewall sul Publisher, la cosa potrebbe essere dovuta anche al firewall del Subscriber. Per verificarlo, provare a disattivare completamente il firewall del Subscriber e quindi ritentare la sincronizzazione. Successivamente, potremo riabilitare il firewall ed eventualmente aggiungere le apposite regole.
NOTA BENE 2: un errore nella sincronizzazione (sul subscriber) potrebbe indicare che questa configurazione FTP non è stata effettuata correttamente. Il subscriber al momento dell’accesso ai dati potrebbe ricevere un errore generico, che si tradurrebbe in una interruzione anomala dell’agent/job di sincronizzazione (errori del tipo “the agent is not running”, oppure crash con relativi dump di memoria). In questi casi, non facciamoci fuorviare dai messaggi di errore, che potrebbero suggerire problematiche completamente diverse, e ricontrolliamo questa configurazione. Un primo test potrebbe essere quello di collegarsi dal server remoto (un subscriber) con un qualsiasi altro client FTP, come ad esempio FileZilla, per assicurarsi che la connessione sia ok e le cartelle accessibili.
NOTA BENE 3: la cartella “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\repldata” e la relativa sottocartella “/ftp” potrebbero non essere accessibili per problemi di permessi. In caso di problemi che sembrano non facilmente identificabili, facciamo subito un test mettendo i permessi di questa cartella a “Everyone” -> “Controllo completo”.
A questo punto tutte le configurazioni sono state effettuate. Controlliamo con “View snapshot agent status” che gli snapshot vengano eseguiti correttamente e con “Launch replication monitor” che i vari subscribers stiano effettuando correttamente la sincronizzazione (finché non configuriamo un subscriber, rimarrà vuoto).
Se tutto sul Publisher è ok, possiamo passare a configurare un subscriber, e quindi effettuare la prima sincronizzazione dei dati.
PASSO 3: Creare la sottoscrizione sui Subscribers
Prerequisito: anche sui subscribers accertiamoci che sia avviato il servizio SQL Server Agent.
Andiamo ora sul server SQL che dovrà collegarsi al server principale come sottoscrittore e quindi sincronizzare i dati. Sempre nel menu “Replication” clicchiamo col destro su “Local Subscriptions” e quindi su “New Subscriptions”.
Come per la pubblicazione, partirà subito un wizard:
Nella schermata successiva dovremo collegarci al server Publisher per visualizzare e selezionare la sua pubblicazione:
Qui ci sono alcune configurazioni da fare che sono fondamentali per il corretto collegamento. Infatti, quando cerchiamo un Publisher, possiamo collegarci ad esso solo specificando il nome del server, e non l’IP (con o senza una porta specifica). Questo significa che dobbiamo in qualche modo “mappare” su questo sistema l’indirizzo IP del server publisher con il suo nome. Possiamo farlo creando un Alias nella configurazione dei servizi di SQL Server, come mostrato nella figura sottostante:
In entrambi i rami “Aliases” mettiamo un alias con il nome del server publisher remoto, il suo indirizzo IP e la porta di SQL Server.
Questo ci permetterà di collegarci ai server SQL remoti semplicemente usando il nome e non l’IP. Ricordiamoci comunque che è fondamentale aprire la porta di SQL Server (qui vediamo quella di default) sui server. Quando facciamo “Find SQL Server Publisher”, ci verrà richiesto di collegarci al server SQL publisher, e quindi ci apparirà la classica maschera di autenticazione di SQL Server:
Una volta autenticati, potremo vedere la pubblicazione che abbiamo creato sul publisher e quindi selezionarle e proseguire:
Proseguiamo al passaggio successivo e lasciamo l’opzione di default:
Nella schermata successiva, selezioniamo il database da sincronizzare. Questo database possiamo averlo creato sul subscriber semplicemente facendo un restore del database principale che abbiamo sul publisher.
Come vediamo nell’immagine, c’è il nome del server subscriber (lo abbiamo chiamato direttamente “SUBSCRIBER” nel nostro esempio) e il nome del database.
Nella schermata successiva, dovremo impostare le credenziali per l’autenticazione. Sceglieremo l’account amministratore di Windows nel primo pannello, e l’account di SQL Server nel secondo (esattamente come abbiamo fatto sul publisher).
Procediamo quindi al prossimo passaggio, dove dovremo dire quando eseguire la sincronizzazione dei dati. Si può fare continuamente e in realtime oppure definire una pianificazione. Si tratta di una normale pianificazione di Windows. Se abbiamo bisogno che i dati siano costantemente sincronizzati, possiamo anche scegliere un intervallo di 10 secondi. Altrimenti anche ore o giorni.
Nella finestra successiva diciamo di inizializzare la sincronizzazione immediatamente:
Infine, diciamo al subscriber di agire anche in modalità server, ovvero non solo di prendere i dati dal publisher ma anche di inviare i suoi a quest’ultimo, quindi una sincronizzazione bidirezionale. Lasciamo l’impostazione per la gestione dei conflitti in default:
Arriviamo quindi all’ultima schermata e clicchiamo su Finish per creare la sottoscrizione e far partire la sincronizzazione:
Potremo subito vedere se la sincronizzazione sta funzionando correttamente visualizzando il monitor della sincronizzazione:
Sincronizzazione attiva!
Seguendo correttamente tutti i passaggi di questo tutorial si riuscirà certamente ad eseguire una sincronizzazione di dati tra due o più database SQL Server remoti senza alcun intoppo.
Chiaramente possono emergere delle problematiche dovute alla configurazione molto articolata, ma con alcuni piccoli accorgimenti si può riuscire a rendere il tutto molto stabile ed efficiente.
Sul sito Microsoft possiamo trovare molta documentazione su come ottimizzare e configurare al meglio la replica di database:
PLEASE NOTE: if you need technical support or have any sales or technical question, don't use comments. Instead open a TICKET here: https://www.iperiusbackup.com/contact.aspx
**********************************************************************************
PLEASE NOTE: if you need technical support or have any sales or technical question, don't use comments. Instead open a TICKET here: https://www.iperiusbackup.com/contact.aspx
*****************************************