Guida alla sincronizzazione dei dati in Microsoft SQL Server

La condivisione delle informazioni correlate tra sistemi isolati è diventata sempre più importante per le organizzazioni, poiché consente loro di migliorare la qualità e la disponibilità dei dati. Esistono molte situazioni in cui è utile disporre di un set di dati disponibile e coerente in più di un server di directory. Ecco perché è importante conoscere i metodi comuni per eseguire la sincronizzazione dei dati di SQL Server.

La disponibilità e la coerenza dei dati possono essere ottenute tramite la replica dei dati e i processi di sincronizzazione dei dati. La replica dei dati è il processo di creazione di una o più copie ridondanti di un database ai fini della tolleranza agli errori o del miglioramento dell'accessibilità. La sincronizzazione dei dati è il processo per stabilire la coerenza dei dati tra due o più database e i successivi aggiornamenti continui per mantenere tale coerenza.

In molte organizzazioni, eseguire la sincronizzazione dei dati su diversi sistemi è sia desiderabile che impegnativo. Possiamo trovare molti casi d'uso in cui è necessario eseguire la sincronizzazione dei dati:

• Migrazione database
• Sincronizzazione regolare tra i sistemi informativi
• Importazione di dati da un sistema informativo a un altro
• Spostamento di set di dati tra fasi o ambienti diversi
• Importazione di dati da un'origine non database

Non esiste un modo univoco o un metodo concordato all'unanimità per la sincronizzazione dei dati. Questo compito varia da caso a caso e anche le sincronizzazioni dei dati che a prima vista dovrebbero essere semplici possono essere complicate, a causa della complessità delle strutture dei dati. In scenari reali, la sincronizzazione dei dati consiste in molte attività complesse, che possono richiedere molto tempo per essere eseguite. Quando si presenta un nuovo requisito, gli specialisti di database di solito devono re-implementare l'intero processo di sincronizzazione. Poiché non esistono metodi standard per eseguire questa operazione, oltre alla replica, le implementazioni della sincronizzazione dei dati sono raramente ottimali. Ciò si traduce in una manutenzione difficile e spese più elevate. L'implementazione e la manutenzione della sincronizzazione dei dati è un processo così dispendioso in termini di tempo che può essere di per sé un lavoro a tempo pieno.

Possiamo implementare l'architettura per le attività di sincronizzazione dei dati manualmente, possibilmente utilizzando Microsoft Sync Framework, oppure possiamo beneficiare di soluzioni già create all'interno degli strumenti per la gestione di Microsoft SQL Server. Cercheremo di descrivere i metodi e gli strumenti più comuni che possono essere utilizzati per risolvere la sincronizzazione dei dati sui database di Microsoft SQL Server e cercheremo di dare alcuni consigli.

In base alla struttura dell'origine e della destinazione (ad es. database, tabelle) possiamo differenziare i casi d'uso quando le strutture sono simili o diverse.

Sorgente e destinazione hanno strutture molto simili

Questo è molto spesso il caso quando utilizziamo i dati in varie fasi del ciclo di vita dello sviluppo del software. Ad esempio, la struttura dei dati negli ambienti di test e produzione è molto simile. Il requisito comune è confrontare i dati tra il database di test e produzione e importare i dati dalla produzione nel database di test.

Origine e destinazione hanno strutture diverse

Se le strutture sono diverse, la sincronizzazione è più complicata. Anche questa è un'attività ricorrente. Un caso comune è l'importazione da un database in un altro. Il caso più comune è quando un software deve importare dati da un altro software gestito da un'altra società. Di solito, le importazioni devono essere eseguite automaticamente in base a una pianificazione.

Il metodo utilizzato dipende dalle preferenze personali e dalla complessità del problema che è necessario risolvere.

Indipendentemente da quanto siano simili le strutture, possiamo scegliere quattro modi diversi per risolvere la sincronizzazione dei dati:

• Sincronizzazione tramite script SQL creati manualmente
• Sincronizzazione utilizzando il metodo di confronto dei dati (può essere utilizzato solo quando sorgente e destinazione hanno una struttura simile)
• Sincronizzazione mediante script SQL generati automaticamente: è necessario un prodotto commerciale

Origine e destinazione hanno strutture uguali o molto simili

Utilizzo di script SQL creati manualmente

La soluzione più semplice e noiosa è scrivere manualmente gli script SQL per la sincronizzazione.

Vantaggi

• Può essere eseguito da strumenti gratuiti e open source (FOSS).
• Se la tabella ha indici, è molto veloce.
• Lo script SQL può essere salvato in una stored procedure o eseguito periodicamente come processo per SQL Server.
• Può essere utilizzato come importazione automatica, anche su dati continuamente modificati.

Svantaggi

• La creazione di uno script SQL di questo tipo è piuttosto noiosa, perché di solito sono necessari tre script per ogni tabella: INSERT , UPDATE e DELETE .
• Puoi sincronizzare solo i dati disponibili tramite query SQL, quindi non puoi importare da origini come file CSV e XML.
• È difficile da mantenere: quando la struttura del database viene modificata, è necessario modificare due o tre script ( INSERT , UPDATE e talvolta anche DELETE ).

Esempio

Effettueremo la sincronizzazione tra la tabella Source , con le colonne ID e Value , e la tabella Target , con le stesse colonne.

Se le tabelle hanno la stessa chiave primaria e la tabella di destinazione non ha una chiave primaria a incremento automatico (identità), puoi eseguire il seguente script di sincronizzazione.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Utilizzo del metodo di confronto dei dati

In questo metodo, possiamo utilizzare uno strumento per confrontare i dati di origine e di destinazione. Il processo di confronto genera script SQL che applicano le differenze dal database di origine al database di destinazione.

Esistono numerosi programmi per il confronto e la sincronizzazione dei dati. Questi programmi utilizzano principalmente lo stesso approccio. L'utente seleziona l'origine e il database di destinazione, ma altre alternative potrebbero essere un backup del database, una cartella con script SQL o anche una connessione a un sistema di controllo del codice sorgente.

Di seguito sono riportati gli strumenti più popolari che utilizzano l'approccio di confronto dei dati:

• Confronto dati dbForge per SQL Server
• Confronto dati SQL RedGate
• Apex SQL Data Diff

Nella prima fase, i dati vengono letti o vengono letti solo i checksum di dati più grandi dall'origine e dalla destinazione. Quindi viene eseguito il processo di confronto.

Questi strumenti offrono anche impostazioni aggiuntive per la sincronizzazione.

È necessario impostare le seguenti opzioni di configurazione necessarie per la sincronizzazione dei dati:

Chiave di sincronizzazione

Per impostazione predefinita, viene utilizzata la chiave primaria o un vincolo UNIQUE . Se non esiste una chiave primaria, puoi scegliere una combinazione di colonne. La chiave Sync viene utilizzata per accoppiare le righe dell'origine con le righe della destinazione.

Abbinamento al tavolo

Per impostazione predefinita, le tabelle sono accoppiate per nome. Puoi cambiarlo e accoppiarli in base alle tue esigenze. Nel software dbForge Data Compare, puoi scegliere la query SQL come origine o destinazione.

Processo di sincronizzazione

Dopo la conferma, lo strumento confronta i dati di origine e di destinazione. L'intero processo consiste nel scaricare tutti i dati di origine e di destinazione e nel confrontarli in base a criteri specificati. Per impostazione predefinita, vengono confrontati i valori di tabelle e colonne con nomi uguali. Tutti gli strumenti supportano la mappatura dei nomi di colonne e tabelle. Inoltre, c'è la possibilità di escludere le colonne IDENTITY (autoincremento) o di eseguire alcune trasformazioni prima di confrontare i valori (tipi float arrotondati, ignorare maiuscole e minuscole, trattare NULL come una stringa vuota, ecc.) Il download dei dati è ottimizzato. Se il volume di dati è elevato, vengono scaricati solo i checksum. Questa ottimizzazione è utile nella maggior parte dei casi, ma i requisiti di tempo per l'esecuzione delle operazioni aumentano con il volume dei dati.

Nel passaggio successivo è presente uno script SQL con le migrazioni generate. Questo script può essere salvato o eseguito direttamente. Per sicurezza, possiamo anche fare un backup del database prima di eseguire questo script. Lo strumento ApexSQL Data Diff può creare un programma eseguibile che esegue lo script su un database selezionato. Questo script contiene dati che devono essere modificati, non la logica su come cambiarli. Ciò significa che lo script non può essere eseguito automaticamente per fornire un'importazione ricorrente. Questo è il più grande svantaggio di questo approccio.

Vantaggi

• Non è richiesta una conoscenza avanzata di SQL e può essere eseguita tramite la GUI.
• Hai la possibilità di controllare visivamente le differenze tra i database prima della sincronizzazione.

Svantaggi

• È una funzionalità avanzata dei prodotti commerciali.
• Le prestazioni diminuiscono durante il trasferimento di enormi volumi di dati.
• Lo script SQL generato contiene solo differenze e quindi non può essere riutilizzato per sincronizzare automaticamente i dati futuri.

Di seguito puoi vedere l'interfaccia utente tipica di questi strumenti.

Sincronizza con SQL generato automaticamente

Questo metodo è molto simile al metodo di confronto dei dati. L'unica differenza rispetto al metodo precedente è che non esiste un confronto dei dati e lo script SQL generato non contiene differenze di dati, ma logica di sincronizzazione. Lo script generato può essere facilmente salvato in una procedura memorizzata e può essere eseguito periodicamente (ad esempio, ogni notte). Questo metodo è utile per le importazioni automatiche tra database. Le prestazioni di questo metodo sono molto migliori rispetto al metodo di confronto dei dati.

La sincronizzazione tramite SQL generato automaticamente è fornita solo da SQL Database Studio.

SQL Database Studio fornisce un'interfaccia simile al metodo di confronto dei dati. Dobbiamo selezionare l'origine e la destinazione (database o tabelle). Quindi dobbiamo impostare le opzioni (sincronizzazione delle chiavi, abbinamento e mappatura). C'è una funzione di creazione di query grafica per l'impostazione di tutti i parametri.

Vantaggi

• Non è richiesta una conoscenza avanzata di SQL.
• Puoi configurare tutto in una GUI abbastanza rapidamente.
• Lo script SQL risultante può essere salvato in una stored procedure.
• Può essere utilizzato come importazione automatica - come lavoro per SQL Server.

Svantaggi

• È una funzionalità avanzata dei prodotti commerciali.
• Le differenze non possono essere verificate manualmente prima della sincronizzazione, poiché l'intero processo viene eseguito in un unico passaggio.

Benchmark delle prestazioni

Caso di prova

Due database (A e B), ciascuno contenente una tabella con 2.000.000 di righe. Le tabelle si trovano in due database diversi sullo stesso SQL Server. Questo test copre due casi estremi: 1) La tabella di origine contiene tutte le 2.000.000 di righe e la tabella di destinazione è vuota. La sincronizzazione deve fornire molti INSERTS . 2) Le tabelle di origine e di destinazione contengono 2.000.000 di righe. La differenza è solo in una riga. La sincronizzazione deve fornire un solo UPDATE .

RedGate Data Compare richiede 3 passaggi:

• Confrontare
• Genera script
• Esegui lo script sul database di destinazione

ApexSQL Data Diff richiede 2 passaggi:

• Confrontare
• Genera script ed esegui script in un solo passaggio

SQL Database Studio esegue l'intera sincronizzazione in un solo passaggio. Di seguito sono riportati i tempi di sincronizzazione, in secondi. Nella colonna denominata "passaggi individuali" ci sono le durate dei passaggi di sincronizzazione sopra elencati.

È meglio più basso.

Lo stesso test, ma i database si trovano su server SQL diversi, che non sono collegati su un server collegato.

È meglio più basso.

Sommario

Dai risultati, è ovvio che RedGate e Apex non si preoccupano se i database si trovano sullo stesso server SQL, perché l'algoritmo di sincronizzazione non dipende da SQL Server. SQL Database Studio utilizza le funzioni native di SQL Server; pertanto, il risultato è migliore quando i database si trovano sullo stesso server.

Sorgente e destinazione hanno una struttura diversa

Ci sono anche situazioni in cui un'ampia tabella deve essere sincronizzata in molte piccole tabelle correlate.

Questo esempio è costituito da un'ampia tabella SourceData che deve essere sincronizzata in piccole tabelle Continent , Country e City . Lo schema è riportato di seguito.

I dati in SourceData potrebbero essere come quelli nell'immagine qui sotto.

Utilizzo di script SQL creati manualmente

Script di sincronizzazione della tabella del continente

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Script di sincronizzazione della tabella della città

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Questo script è più complicato. È perché è necessario trovare i record nelle tabelle Country e Continent . Questo script inserisce i record mancanti in City e riempie correttamente ContryId .

Gli script UPDATE e DELETE possono anche essere scritti allo stesso modo, se necessario.

Vantaggi

• Non hai bisogno di alcun prodotto commerciale.
• Lo script SQL può essere salvato nella stored procedure o eseguito periodicamente come processo per SQL Server.

Svantaggi

• La creazione di uno script SQL di questo tipo è difficile e complicata (per ogni tabella sono generalmente necessari tre script: INSERT , UPDATE e DELETE ).
• È molto difficile da mantenere.

Utilizzo di strumenti esterni

Questo tipo di sincronizzazione (tabella ampia in molte tabelle correlate) non può essere eseguita con il metodo di confronto dei dati, poiché si concentra su diversi casi d'uso. Poiché il metodo di confronto dei dati produce uno script SQL con i dati da inserire, non ha una capacità diretta di cercare i riferimenti nelle tabelle correlate. Per questo motivo, le applicazioni che utilizzano questo metodo non possono essere utilizzate (dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Tuttavia, SQL Database Studio può aiutarti a creare automaticamente script di sincronizzazione. Nell'immagine seguente è presente un elemento denominato Editor per la sincronizzazione dei dati in SQL Database Studio.

Editor assomiglia al noto Query builder e funziona in modo molto simile. Ogni tabella deve avere una chiave di sincronizzazione definita, ma esistono anche relazioni definite tra le tabelle. Nell'immagine sopra c'è anche la mappatura per la sincronizzazione. Nell'elenco delle colonne (parte inferiore dell'immagine) ci sono le colonne della tabella City (per le altre tabelle è simile).

Colonne

• Id : questa colonna non è mappata perché è la chiave primaria (generata automaticamente).
• CountryId — Questa colonna è definita come riferimento per la tabella.
• Nome : questa colonna è compilata dalla colonna Città nella tabella di origine (tabella ampia).

Le colonne CountryId e Name vengono scelte come chiavi di sincronizzazione. La chiave di sincronizzazione è un insieme di colonne che identificano in modo univoco una riga nella tabella di origine e di destinazione. Non è possibile utilizzare l' Id della chiave primaria come chiave di sincronizzazione perché non è nella tabella di origine.

Dopo la sincronizzazione, ecco come appaiono le tabelle:

Nell'esempio sopra, c'era un'ampia tabella come origine. Esiste anche uno scenario comune in cui i dati di origine vengono archiviati in diverse tabelle correlate. Le relazioni in SQL Database Studio non vengono definite utilizzando chiavi esterne, ma nomi di colonna. In questo modo è anche possibile importare da file CSV o Excel (il file viene caricato in una tabella temporanea e la sincronizzazione viene eseguita da quella tabella). È buona norma avere nomi di colonna univoci. Se ciò non è possibile, puoi definire alias per quelle colonne.

Vantaggi

• Facile e veloce da creare
• Facile da mantenere
• Può essere salvato in una stored procedure (la stored procedure viene salvata con i dati necessari per aprire la sincronizzazione in un editor in un secondo momento)

Svantaggi

• Soluzione commerciale

Confrontando le soluzioni

La sincronizzazione dei dati consiste in una sequenza di comandi INSERT , UPDATE o DELETE . Esistono diversi modi per creare sequenze di questi comandi. In questo articolo, abbiamo esaminato tre opzioni per la creazione di script SQL di sincronizzazione. La prima opzione è creare tutto manualmente. È fattibile (ma richiede troppo tempo), richiede una comprensione complessa di SQL ed è difficile da creare e mantenere. La seconda opzione è utilizzare strumenti commerciali. Abbiamo esaminato i seguenti strumenti:

• Confronto dati dbForge per SQL Server
• Confronto dati SQL RedGate
• Apex SQL Data Diff
• Studio di database SQL

I primi tre strumenti funzionano in modo molto simile. Confrontano i dati, consentono all'utente di analizzare le differenze e possono sincronizzare le differenze selezionate (anche automaticamente o dalla riga di comando). Sono utili per questi scenari di utilizzo:

• I database non sono sincronizzati a causa di vari errori.
• È necessario evitare la replica durante il trasferimento dei dati tra ambienti.
• Sono necessari rapporti di confronto dei dati in Excel o HTML.

Ogni strumento è amato per un motivo o per l'altro: dbForge ha un'ottima interfaccia utente e molte opzioni, ApexSQL funziona meglio degli altri e RedGate è il più popolare.

Il quarto strumento, SQL Database Studio, funziona in modo leggermente diverso. Genera script SQL che contengono la logica di sincronizzazione, non le modifiche. Anche le prestazioni sono ottime, perché tutto il lavoro viene svolto direttamente sul server del database, quindi non è necessario alcun trasferimento di dati tra il server del database e lo strumento di sincronizzazione. Questo strumento è utile per i seguenti casi d'uso:

• Migrazioni automatiche di database in cui i database hanno una struttura diversa
• Importa in più tabelle correlate
• Importazione da fonti esterne XML, CSV, MS Excel