Ghid pentru sincronizarea datelor în Microsoft SQL Server

Partajarea informațiilor conexe între sisteme izolate a devenit din ce în ce mai importantă pentru organizații, deoarece le permite să îmbunătățească calitatea și disponibilitatea datelor. Există multe situații în care este util să aveți un set de date care este disponibil și consecvent în mai mult de un server de directoare. De aceea, cunoașterea metodelor obișnuite pentru efectuarea sincronizării datelor SQL Server este importantă.

Disponibilitatea și coerența datelor pot fi obținute prin procese de replicare și sincronizare a datelor. Replicarea datelor este procesul de creare a uneia sau mai multor copii redundante ale unei baze de date în scopul toleranței la erori sau îmbunătățirii accesibilității. Sincronizarea datelor este procesul de stabilire a coerenței datelor între două sau mai multe baze de date și actualizările continue ulterioare pentru a menține consecvența menționată.

În multe organizații, efectuarea sincronizării datelor pe diverse sisteme este atât de dorit, cât și provocatoare. Putem găsi multe cazuri de utilizare în care trebuie să realizăm sincronizarea datelor:

• Migrarea bazei de date
• Sincronizare regulată între sistemele informaționale
• Importarea datelor dintr-un sistem informatic în altul
• Mutarea seturi de date între diferite etape sau medii
• Importarea datelor dintr-o sursă non-bază de date

Nu există o modalitate unică sau o metodă unanim agreată pentru sincronizarea datelor. Această sarcină diferă de la caz la caz și chiar și sincronizările de date care ar trebui să fie simple la prima vedere pot fi complicate, din cauza complexității structurilor de date. În scenariile reale, sincronizarea datelor constă în multe sarcini complexe, care pot dura mult timp. Când apare o nouă cerință, specialiștii în baze de date trebuie de obicei să reimplementeze întregul proces de sincronizare. Deoarece nu există modalități standard de a face acest lucru, în afară de replicare, implementările de sincronizare a datelor sunt rareori optime. Acest lucru are ca rezultat întreținere dificilă și cheltuieli mai mari. Implementarea și întreținerea sincronizării datelor este un proces atât de consumator de timp, încât poate fi un loc de muncă cu normă întreagă în sine.

Putem implementa manual arhitectura pentru sarcinile de sincronizare a datelor, eventual folosind Microsoft Sync Framework, sau putem beneficia de soluții deja create în cadrul instrumentelor de gestionare a Microsoft SQL Server. Vom încerca să descriem cele mai comune metode și instrumente care pot fi folosite pentru a rezolva sincronizarea datelor pe bazele de date Microsoft SQL Server și vom încerca să oferim câteva recomandări.

Pe baza structurii sursei și destinației (de exemplu, baze de date, tabele) putem diferenția cazurile de utilizare când structurile sunt similare sau diferite.

Sursa și destinația au structuri foarte asemănătoare

Acesta este foarte des cazul când folosim date în diferite etape ale ciclului de viață al dezvoltării software. De exemplu, structura datelor în mediile de testare și producție este foarte asemănătoare. Cerința comună este de a compara datele dintre baza de date de testare și de producție și de a importa date din producție în baza de date de testare.

Sursa și destinația au structuri diferite

Dacă structurile sunt diferite, sincronizarea este mai complicată. Aceasta este, de asemenea, o sarcină recurentă mai frecvent. Un caz comun este importul dintr-o bază de date în alta. Cel mai frecvent caz este atunci când o bucată de software trebuie să importe date dintr-o altă bucată de software care este întreținută de o altă companie. De obicei, importurile trebuie să ruleze automat pe o bază programată.

Metoda folosită depinde de preferințele personale și de complexitatea problemei pe care trebuie să o rezolvi.

Indiferent de cât de similare sunt structurile, putem alege patru moduri diferite de rezolvare a sincronizării datelor:

• Sincronizare folosind scripturi SQL create manual
• Sincronizare folosind metoda de comparare a datelor (poate fi utilizată numai atunci când sursa și ținta au o structură similară)
• Sincronizare folosind scripturi SQL generate automat - nevoie de produs comercial

Sursa și destinația au structuri identice sau foarte asemănătoare

Utilizarea scripturilor SQL create manual

Cea mai simplă și plictisitoare soluție este să scrieți manual scripturi SQL pentru sincronizare.

Avantaje

• Poate fi realizat cu instrumente gratuite și open source (FOSS).
• Dacă tabelul are indici, este foarte rapid.
• Scriptul SQL poate fi salvat într-o procedură stocată sau rulat periodic ca job pentru SQL Server.
• Poate fi folosit ca import automat, chiar și pentru date modificate continuu.

Dezavantaje

• Crearea unui astfel de script SQL este destul de plictisitoare, deoarece de obicei sunt necesare trei scripturi pentru fiecare tabel: INSERT , UPDATE și DELETE .
• Puteți sincroniza numai datele disponibile prin interogări SQL, astfel încât să nu puteți importa din surse precum fișierele CSV și XML.
• Este greu de întreținut — atunci când structura bazei de date este schimbată, este necesar să modificați două sau trei scripturi ( INSERT , UPDATE și uneori și DELETE ).

Exemplu

Vom face sincronizarea între tabelul Source , cu coloanele ID și Value , și tabelul Target , cu aceleași coloane.

Dacă tabelele au aceeași cheie primară, iar tabelul țintă nu are o cheie primară cu incrementare automată (identitate), puteți executa următorul script de sincronizare.

 -- 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)

Folosind metoda de comparare a datelor

În această metodă, putem folosi un instrument pentru a compara datele sursă și țintă. Procesul de comparare generează scripturi SQL care aplică diferențele față de baza de date sursă în baza de date țintă.

Există o serie de programe pentru compararea și sincronizarea datelor. Aceste programe folosesc în mare parte aceeași abordare. Utilizatorul selectează baza de date sursă și țintă, dar alte alternative ar putea fi o copie de rezervă DB, un folder cu scripturi SQL sau chiar o conexiune la un sistem de control al sursei.

Mai jos sunt cele mai populare instrumente care utilizează abordarea de comparare a datelor:

• dbForge Data Compare pentru SQL Server
• Compararea datelor SQL RedGate
• Diferența datelor Apex SQL

În primul pas, datele sunt citite sau sunt citite doar sume de control ale datelor mai mari de la sursă și de la țintă. Apoi se execută procesul de comparare.

Aceste instrumente oferă și setări suplimentare pentru sincronizare.

Trebuie să setăm următoarele opțiuni de configurare necesare pentru sincronizarea datelor:

Tasta de sincronizare

În mod implicit, se utilizează cheia primară sau o constrângere UNIQUE . Dacă nu există o cheie primară, puteți alege o combinație de coloane. Tasta de sincronizare este folosită pentru a asocia rândurile sursei cu rândurile țintei.

Împerecherea mesei

În mod implicit, tabelele sunt asociate după nume. Puteți schimba acest lucru și le puteți asocia în funcție de propriile nevoi. În software-ul dbForge Data Compare, puteți alege interogarea SQL să fie sursa sau destinația.

Procesul de sincronizare

După confirmare, instrumentul compară datele sursă și țintă. Întregul proces constă în descărcarea tuturor datelor sursă și țintă și compararea acestora pe baza unor criterii specificate. În mod implicit, sunt comparate valorile din tabele și coloane cu nume egale. Toate instrumentele acceptă maparea numelor de coloane și tabele. De asemenea, există posibilitatea de a exclude coloanele IDENTITY (autoincrement) sau de a face unele transformări înainte de a compara valori (tipuri rotunde flotante, ignorarea majusculei caracterelor, tratarea NULL ca pe un șir gol etc.) Descărcarea datelor este optimizată. Dacă volumul de date este mare, sunt descărcate doar sumele de verificare. Această optimizare este utilă în majoritatea cazurilor, dar cerințele de timp pentru efectuarea operațiunilor cresc odată cu volumul de date.

În pasul următor, există un script SQL cu migrări generate. Acest script poate fi salvat sau rulat direct. Pentru a fi în siguranță, putem chiar să facem o copie de rezervă a bazei de date înainte de a rula acest script. Instrumentul ApexSQL Data Diff poate crea un program executabil care rulează scriptul pe o bază de date selectată. Acest script conține date care trebuie schimbate, nu logica cum să le schimbe. Aceasta înseamnă că scriptul nu poate fi rulat automat pentru a oferi un import recurent. Acesta este cel mai mare dezavantaj al acestei abordări.

Avantaje

• Cunoștințe avansate de SQL nu sunt necesare și se pot face prin GUI.
• Aveți posibilitatea de a verifica vizual diferențele dintre bazele de date înainte de sincronizare.

Dezavantaje

• Este o caracteristică avansată a produselor comerciale.
• Performanța scade la transferul unor volume enorme de date.
• Scriptul SQL generat conține doar diferențe și, prin urmare, nu poate fi reutilizat pentru sincronizarea automată a datelor viitoare.

Mai jos puteți vedea interfața de utilizare tipică a acestor instrumente.

Sincronizați cu SQL generat automat

Această metodă este foarte asemănătoare cu metoda de comparare a datelor. Singura diferență față de metoda anterioară este că nu există comparație de date, iar scriptul SQL generat nu conține diferențe de date, ci logica de sincronizare. Scriptul generat poate fi salvat cu ușurință într-o procedură stocată și poate fi rulat periodic (de exemplu, în fiecare noapte). Această metodă este utilă pentru importurile automate între baze de date. Performanța acestei metode este mult mai bună decât metoda de comparare a datelor.

Sincronizarea prin SQL generat automat este furnizată numai de SQL Database Studio.

SQL Database Studio oferă o interfață similară cu metoda de comparare a datelor. Trebuie să selectăm sursa și ținta (baze de date sau tabele). Apoi trebuie să setăm opțiuni (taste de sincronizare, împerechere și mapare). Există o funcție grafică de generare de interogări pentru configurarea tuturor parametrilor.

Avantaje

• Nu sunt necesare cunoștințe avansate de SQL.
• Puteți configura totul într-o interfață grafică destul de rapid.
• Scriptul SQL rezultat poate fi salvat într-o procedură stocată.
• Poate fi folosit ca import automat - ca job pentru SQL Server.

Dezavantaje

• Este o caracteristică avansată a produselor comerciale.
• Diferențele nu pot fi verificate manual înainte de sincronizare, deoarece întregul proces este executat într-un singur pas.

Benchmark-uri de performanță

Caz de testare

Două baze de date (A și B), fiecare conținând un tabel cu 2.000.000 de rânduri. Tabelele sunt în două baze de date diferite pe același server SQL. Acest test acoperă două cazuri extreme: 1) Tabelul sursă conține toate cele 2.000.000 de rânduri și tabelul țintă este gol. Sincronizarea trebuie să ofere multe INSERTS . 2) Tabelele sursă și țintă conțin 2.000.000 de rânduri. Diferența este doar într-un singur rând. Sincronizarea trebuie să ofere o singură UPDATE .

RedGate Data Compare necesită 3 pași:

• Comparaţie
• Generați script
• Rulați scriptul pe baza de date țintă

ApexSQL Data Diff are nevoie de 2 pași:

• Comparaţie
• Generați script și rulați script într-un singur pas

SQL Database Studio realizează întreaga sincronizare într-un singur pas. Mai jos sunt timpii de sincronizare, în secunde. În coloana etichetată „pași individuali” sunt duratele pașilor de sincronizare enumerați mai sus.

Mai jos este mai bine.

Același test, dar bazele de date sunt pe servere SQL diferite, care nu sunt conectate pe un server conectat.

Mai jos este mai bine.

rezumat

Din rezultate, este evident că RedGate și Apex nu le pasă dacă bazele de date sunt pe același server SQL, deoarece algoritmul de sincronizare nu este dependent de SQL Server. SQL Database Studio folosește funcții native ale SQL Server; prin urmare, rezultatul este mai bun atunci când bazele de date sunt pe același server.

Sursa și destinația au o structură diferită

Există, de asemenea, situații în care un tabel lat trebuie sincronizat cu multe tabele mici asociate.

Acest exemplu constă dintr-un tabel larg SourceData care trebuie sincronizat în tabele mici Continent , Country și City . Schema este prezentată mai jos.

Datele din SourceData ar putea fi ca cele din imaginea de mai jos.

Folosind scripturi SQL create manual

Sincronizarea scriptului Continent Table

 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;

Scriptul de sincronizare a tabelului orașului

 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;

Acest scenariu este mai complicat. Pentru că trebuie găsite înregistrări în tabelele Country și Continent . Acest script inserează înregistrările lipsă în City și completează corect ContryId .

Scripturile UPDATE și DELETE pot fi, de asemenea, scrise în același mod, dacă este necesar.

Avantaje

• Nu aveți nevoie de produse comerciale.
• Scriptul SQL poate fi salvat în procedura stocată sau rulat periodic ca un job pentru SQL Server.

Dezavantaje

• Crearea unui astfel de script SQL este dificilă și complicată (pentru fiecare tabel, sunt de obicei necesare trei scripturi - INSERT , UPDATE și DELETE ).
• Este foarte greu de întreținut.

Utilizarea instrumentelor externe

Acest tip de sincronizare (tabel larg în multe tabele înrudite) nu se poate face cu metoda de comparare a datelor, deoarece se concentrează pe diferite cazuri de utilizare. Deoarece metoda de comparare a datelor produce un script SQL cu date de inserat, nu are capacitatea simplă de a căuta referințe în tabelele asociate. Din acest motiv, aplicațiile care folosesc această metodă nu pot fi utilizate (dbForge Data Compare pentru SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Cu toate acestea, SQL Database Studio vă poate ajuta să creați automat scripturi de sincronizare. În imaginea de mai jos, există un element numit Editor pentru sincronizarea datelor în SQL Database Studio.

Editorul arată ca bine-cunoscutul constructor de interogări și funcționează într-un mod foarte similar. Fiecare tabel trebuie să aibă o cheie de sincronizare definită, dar există și relații definite între tabele. În imaginea de mai sus există mapare și pentru sincronizare. În lista de coloane (partea de jos a imaginii) se află coloanele din tabelul City (pentru alte tabele este similar).

Coloane

• Id — Această coloană nu este mapată deoarece este cheia primară (generată automat).
• CountryId — Această coloană este definită ca referință pentru tabel.
• Nume — Această coloană este completată din coloana Oraș din tabelul sursă (tabel lat).

Coloanele CountryId și Name sunt alese ca chei de sincronizare. Cheia de sincronizare este un set de coloane care identifică în mod unic un rând în tabelul sursă și țintă. Nu puteți utiliza Id -ul cheii primare ca cheie de sincronizare deoarece aceasta nu se află în tabelul sursă.

După sincronizare, așa arată tabelele:

În exemplul de mai sus, a existat un tabel larg ca sursă. Există, de asemenea, un scenariu comun când datele sursă sunt stocate în mai multe tabele asociate. Relațiile din SQL Database Studio nu sunt definite folosind chei străine, ci nume de coloane. În acest fel este posibil și importarea din fișiere CSV sau Excel (fișierul este încărcat într-un tabel temporar, iar sincronizarea se execută din acel tabel). Este o practică bună să aveți nume de coloane unice. Dacă acest lucru nu este posibil, puteți defini alias-uri pentru acele coloane.

Avantaje

• Ușor și rapid de creat
• Usor de intretinut
• Poate fi salvat într-o procedură stocată (procedura stocată este salvată cu datele necesare pentru a deschide mai târziu sincronizarea într-un editor)

Dezavantaje

• Solutie comerciala

Compararea soluțiilor

Sincronizarea datelor constă dintr-o secvență de comenzi INSERT , UPDATE sau DELETE . Există mai multe moduri de a crea secvențe ale acestor comenzi. În acest articol, am analizat trei opțiuni pentru crearea de scripturi SQL de sincronizare. Prima opțiune este să creați totul manual. Este fezabil (dar necesită prea mult timp), necesită înțelegere complexă a SQL și este dificil de creat și întreținut. A doua opțiune este utilizarea instrumentelor comerciale. Ne-am uitat la următoarele instrumente:

• dbForge Data Compare pentru SQL Server
• Compararea datelor SQL RedGate
• Diferența datelor Apex SQL
• SQL Database Studio

Primele trei instrumente funcționează foarte similar. Ele compară datele, permit utilizatorului să analizeze diferențele și pot sincroniza diferențele selectate (chiar automat sau din linia de comandă). Sunt benefice pentru aceste scenarii de utilizare:

• Bazele de date nu sunt sincronizate din cauza diverselor erori.
• Trebuie să evitați replicarea în timp ce transferați date între medii.
• Sunt necesare rapoarte de comparare a datelor în Excel sau HTML.

Fiecare instrument este iubit dintr-un motiv sau altul: dbForge are o interfață de utilizare excelentă și o mulțime de opțiuni, ApexSQL are performanțe mai bune decât restul, iar RedGate este cel mai popular.

Al patrulea instrument, SQL Database Studio, funcționează puțin diferit. Acesta generează scripturi SQL care conțin logica de sincronizare, nu modificări. Performanța este, de asemenea, grozavă, deoarece toate lucrările se desfășoară direct pe serverul bazei de date, deci nu este necesar niciun transfer de date între serverul bazei de date și instrumentul de sincronizare. Acest instrument este util pentru următoarele cazuri de utilizare:

• Migrații automate de baze de date în care bazele de date au o structură diferită
• Importați în mai multe tabele asociate
• Import din surse externe XML, CSV, MS Excel