Izmenjava povezanih informacij med izoliranimi sistemi postaja za organizacije vse pomembnejša, saj jim omogoča izboljšanje kakovosti in razpoložljivosti podatkov. V številnih situacijah je koristno imeti nabor podatkov, ki je na voljo in skladen v več kot enem imeniškem strežniku. Zato poznavanje običajnih metod za izvedbo SQL Server sinhronizacija podatkov je pomembna.
Dostopnost in skladnost podatkov je mogoče doseči s postopki kopiranja in sinhronizacije podatkov. Podvajanje podatkov je postopek ustvarjanja ene ali več odvečnih kopij zbirke podatkov zaradi odpornosti na napake ali izboljšanja dostopnosti. Sinhronizacija podatkov je postopek ugotavljanja skladnosti podatkov med dvema ali več bazami podatkov in nadaljnje nenehne posodobitve za ohranitev omenjene skladnosti.
V mnogih organizacijah je sinhronizacija podatkov v različnih sistemih zaželena in zahtevna. Najdemo veliko primerov uporabe, ko moramo opraviti sinhronizacijo podatkov:
Za sinhronizacijo podatkov ni edinstvenega načina ali soglasno dogovorjene metode. Ta naloga se razlikuje od primera do primera in celo sinhronizacija podatkov, ki bi morala biti na prvi pogled preprosta, je zaradi zapletenosti podatkovnih struktur lahko zapletena. V resničnih scenarijih je sinhronizacija podatkov sestavljena iz številnih zapletenih nalog, ki jih lahko traja dolgo časa. Ko se pojavi nova zahteva, morajo strokovnjaki za baze podatkov navadno izvesti celoten postopek sinhronizacije. Ker za to ne obstajajo običajni načini, so poleg kopiranja izvedbe sinhronizacije podatkov redko optimalne. Posledica tega je težko vzdrževanje in višji stroški. Izvajanje in vzdrževanje sinhronizacije podatkov je tako dolgotrajen postopek, lahko je samostojno delo s polnim delovnim časom.
Arhitekturo za naloge sinhronizacije podatkov lahko implementiramo ročno, po možnosti z uporabo Microsoft Sync Framework ali pa izkoristimo že ustvarjene rešitve v orodjih za upravljanje strežnika Microsoft SQL Server. Poskusili bomo opisati najpogostejše metode in orodja, ki jih lahko uporabimo za reševanje sinhronizacije podatkov v zbirkah podatkov Microsoft SQL Server, in poskušali podati nekaj priporočil.
Glede na strukturo vira in cilja (npr. Baze podatkov, tabele) lahko ločimo primere uporabe, ko so strukture podobne ali različne.
To se zelo pogosto zgodi, kadar podatke uporabljamo v različnih fazah življenjskega cikla razvoja programske opreme. Na primer, struktura podatkov v preskusnem in proizvodnem okolju je zelo podobna. Skupna zahteva je primerjava podatkov med testno in proizvodno bazo podatkov ter uvoz podatkov iz produkcije v testno bazo podatkov.
Če so strukture drugačne, je sinhronizacija bolj zapletena. To je tudi pogostejša naloga. Pogost primer je uvoz iz ene baze podatkov v drugo. Najpogostejši primer je, ko mora del programske opreme uvoziti podatke iz drugega dela programske opreme, ki ga vzdržuje drugo podjetje. Običajno mora uvoz teči samodejno po načrtovanih pogojih.
Uporabljena metoda je odvisna od osebnih želja in zapletenosti problema, ki ga morate rešiti.
Ne glede na to, kako podobne so strukture, lahko za reševanje sinhronizacije podatkov izberemo štiri različne načine:
Najbolj neposredna in dolgočasna rešitev je ročno pisanje skriptov SQL za sinhronizacijo.
INSERT
, UPDATE
in DELETE
.INSERT
, UPDATE
, včasih pa tudi DELETE
).Izvedli bomo sinhronizacijo med tabelo Source
, s stolpci ID
in Value
in tabela Target
z istimi stolpci.
Če imajo tabele isti primarni ključ in ciljna tabela nima primarnega ključa s samodejnim povečevanjem (identiteta), lahko izvedete naslednji sinhronizacijski skript.
-- 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)
Pri tej metodi lahko z orodjem primerjamo izvorne in ciljne podatke. Postopek primerjave generira skripte SQL, ki uporabljajo razlike iz izvorne baze podatkov v ciljno bazo podatkov.
Obstajajo številni programi za primerjavo in sinhronizacijo podatkov. Ti programi večinoma uporabljajo enak pristop. Uporabnik izbere izvorno in ciljno bazo podatkov, druge možnosti pa so lahko varnostna kopija DB, mapa s skripti SQL ali celo povezava z nadzornim sistemom vira.
Spodaj so navedena najbolj priljubljena orodja, ki uporabljajo pristop primerjave podatkov:
V prvem koraku se preberejo podatki ali se preberejo samo kontrolne vsote večjih podatkov iz vira in cilja. Nato se izvede postopek primerjave.
Ta orodja ponujajo tudi dodatne nastavitve za sinhronizacijo.
Nastaviti moramo naslednje konfiguracijske možnosti, ki so potrebne za sinhronizacijo podatkov:
Privzeto je primarni ključ ali UNIQUE
omejitev. Če primarnega ključa ni, lahko izberete kombinacijo stolpcev. Tipka Sync se uporablja za seznanjanje vrstic vira z vrsticami cilja.
Tabele so privzeto seznanjene po imenu. To lahko spremenite in jih seznanite glede na svoje potrebe. V programski opremi dbForge Data Compare lahko poizvedbo SQL izberete kot vir ali cilj.
Po potrditvi orodje primerja izvorne in ciljne podatke. Celoten postopek vključuje prenos vseh izvornih in ciljnih podatkov ter njihovo primerjavo na podlagi določenih meril. Privzeto se primerjajo vrednosti iz enako imenovanih tabel in stolpcev. Vsa orodja podpirajo preslikavo imen stolpcev in tabel. Obstaja tudi možnost izključitve IDENTITY
(autoincrement) stolpcev ali narediti nekaj transformacij pred primerjavo vrednosti (okrogle vrste float-a, prezreti velike črke, obravnavati NULL
kot prazen niz itd.) Prenos podatkov je optimiziran. Če je obseg podatkov velik, se prenesejo samo kontrolne vsote. Ta optimizacija je v večini primerov koristna, vendar se časovne zahteve za izvajanje operacij povečujejo s količino podatkov.
V naslednjem koraku je skript SQL z generiranimi selitvami. Ta skript lahko shranite ali zaženete neposredno. Da bi bili varni, lahko pred izdelavo tega skripta izdelamo celo varnostno kopijo baze podatkov. Orodje ApexSQL Data Diff lahko ustvari izvedljiv program, ki zažene skript v izbrani bazi podatkov. Ta skript vsebuje podatke, ki jih je treba spremeniti, in ne logike, kako jih spremeniti. To pomeni, da skripta ni mogoče samodejno zagnati, da bi zagotovil ponavljajoč se uvoz. To je največja slabost tega pristopa.
Spodaj si lahko ogledate tipičen uporabniški vmesnik teh orodij.
Ta metoda je zelo podobna metodi primerjave podatkov. Edina razlika v primerjavi s prejšnjo metodo je ta, da ni primerjave podatkov in ustvarjeni skript SQL ne vsebuje podatkovnih razlik, temveč sinhronizacijsko logiko. Ustvarjeni skript lahko enostavno shranite v shranjeni postopek in ga lahko redno izvajate (npr. Vsako noč). Ta metoda je uporabna za samodejni uvoz med bazami podatkov. Učinkovitost te metode je veliko boljša od metode primerjave podatkov.
Sinhronizacijo s samodejno generiranim SQL zagotavlja samo SQL Database Studio .
SQL Database Studio ponuja podoben vmesnik kot metoda primerjave podatkov. Izbrati moramo vir in cilj (zbirke podatkov ali tabele). Nato moramo nastaviti možnosti (tipke za sinhronizacijo, seznanjanje in preslikava). Za nastavitev vseh parametrov je na voljo funkcija za ustvarjanje grafičnih poizvedb.
Dve bazi podatkov (A in B), vsaka vsebuje eno tabelo z 2.000.000 vrstic. Tabele so v dveh različnih zbirkah podatkov na istem strežniku SQL Server. Ta test zajema dva skrajna primera: 1) Izvorna tabela vsebuje vseh 2.000.000 vrstic in ciljna tabela je prazna. Sinhronizacija mora zagotoviti veliko INSERTS
. 2) Izvorna in ciljna tabela vsebujeta 2.000.000 vrstic. Razlika je le v eni vrstici. Sinhronizacija mora zagotoviti samo eno UPDATE
.
RedGate Data Compare potrebuje 3 korake:
ApexSQL Data Diff potrebuje dva koraka:
SQL Database Studio izvede celotno sinhronizacijo v enem koraku. Spodaj so sinhronizacijski časi v sekundah. V stolpcu z oznako »posamezni koraki« so trajanja zgoraj naštetih korakov sinhronizacije.
Primer A. veliko INSERT-ov | Primer A. veliko INSERT-ov (posamezni koraki) | Primer B. POSODOBI eno vrstico | Primer B. POSODOBITEV ena vrstica (posamezni koraki) | |
---|---|---|---|---|
SQL Database Studio | 47 | 5. | ||
Primerjava podatkov RedGate | 317 | 13 + 92 + 212 | 2. 3 | 22 + 0 + 1 |
ApexSQL Data Diff | 188 | 18 + 170 | 26. | 25+ |
Nižje je boljše.
Isti test, vendar so baze podatkov na različnih strežnikih SQL, ki niso povezani prek povezanega strežnika.
Primer A. veliko INSERT-ov | Primer A. veliko INSERT-ov (posamezni koraki) | Primer B. POSODOBI eno vrstico | Primer B. POSODOBITEV ena vrstica (posamezni koraki) | |
---|---|---|---|---|
SQL Database Studio | 78 | 44 | ||
Primerjava podatkov RedGate | 288 | 17 + 82 + 179 | 25. | 24 + 0 + 1 |
ApexSQL Data Diff | 203 | 18 + 185 | 25. | 24 + 1 |
dbForge primerjava podatkov | 326 | 11 + 315 | 16. | 16 + 0 |
Nižje je boljše.
Iz rezultatov je razvidno, da RedGateu in Apexu ni vseeno, ali sta zbirki podatkov na istem strežniku SQL, ker sinhronizacijski algoritem ni odvisen od strežnika SQL. SQL Database Studio uporablja izvorne funkcije SQL Server; zato je rezultat boljši, če so baze podatkov na istem strežniku.
Obstajajo tudi situacije, ko je treba eno široko tabelo sinhronizirati v veliko majhnih sorodnih tabel.
Ta primer je sestavljen iz ene široke tabele SourceData, ki jo je treba sinhronizirati v majhne tabele Continent
, Country
in City
. Shema je podana spodaj.
Podatki v SourceData so lahko podobni tistim na spodnji sliki.
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;
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;
Ta skript je bolj zapleten. To je zato, ker zapisi v tabelah Country
in Continent
je treba najti. Ta skript vstavi manjkajoče zapise v City
in polnila ContryId
pravilno.
The UPDATE
in DELETE
skripte bi lahko po potrebi napisali tudi na enak način.
INSERT
, UPDATE
in DELETE
).Tovrstne sinhronizacije (široka tabela v številne sorodne tabele) ni mogoče izvesti z metodo primerjave podatkov, ker je osredotočena na različne primere uporabe. Ker metoda primerjave podatkov ustvari skript SQL s podatki, ki jih je treba vstaviti, nima neposredne možnosti iskanja referenc v sorodnih tabelah. Iz tega razloga aplikacij, ki uporabljajo to metodo, ni mogoče uporabljati (dbForge Data Compare za SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).
Vendar vam lahko SQL Database Studio pomaga pri samodejnem ustvarjanju sinhronizacijskih skriptov. Na spodnji sliki je element, imenovan Urejevalnik za sinhronizacijo podatkov v SQL Database Studio.
Urejevalnik izgleda kot dobro znani graditelj poizvedb in deluje na zelo podoben način. Vsaka tabela mora imeti določen sinhronizacijski ključ, obstajajo pa tudi definirane relacije med tabelami. Na zgornji sliki je tudi preslikava za sinhronizacijo. Na seznamu stolpcev (spodnji del slike) so stolpci tabele City
(za druge tabele je podobno).
Stolpci CountryId
in Name
izbrani kot sinhronizacijske tipke. Sinhronizacijski ključ je niz stolpcev, ki enolično identificirajo vrstico v izvorni in ciljni tabeli. Primarnega ključa ne morete uporabiti Id
kot sinhronizacijski ključ, ker tega ni v izvorni tabeli.
Po sinhronizaciji so tabele videti tako:
V zgornjem primeru je bila kot vir ena široka tabela. Obstaja tudi pogost scenarij, ko so izvorni podatki shranjeni v več sorodnih tabelah. Odnosi v SQL Database Studio niso definirani s tujimi ključi, temveč z imeni stolpcev. Na ta način je mogoče tudi uvoziti iz datotek CSV ali Excel (datoteka se naloži v začasno tabelo in sinhronizacija se izvede iz te tabele). Dobra praksa je, da imate edinstvena imena stolpcev. Če to ni mogoče, lahko za te stolpce določite vzdevke.
Sinhronizacija podatkov je sestavljena iz zaporedja INSERT
, UPDATE
ali DELETE
ukazi. Obstaja več načinov za ustvarjanje zaporedij teh ukazov. V tem članku smo preučili tri možnosti za ustvarjanje sinhronizacijskih skriptov SQL. Prva možnost je, da vse ustvarite ročno. Je izvedljivo (vendar traja preveč časa), zahteva kompleksno razumevanje SQL-a in ga je težko ustvariti in vzdrževati. Druga možnost je uporaba komercialnih orodij. Ogledali smo si naslednja orodja:
Prva tri orodja delujejo zelo podobno. Primerjajo podatke, uporabniku omogočajo analizo razlik in lahko sinhronizirajo izbrane razlike (tudi samodejno ali iz ukazne vrstice). Koristijo za te scenarije uporabe:
Vsako orodje je iz enega ali drugega razloga všeč: dbForge ima odličen uporabniški vmesnik in veliko možnosti, ApexSQL deluje bolje kot ostali, RedGate pa je najbolj priljubljen.
Četrto orodje, SQL Database Studio, deluje nekoliko drugače. Ustvari skripte SQL, ki vsebujejo sinhronizacijsko logiko in ne sprememb. Tudi zmogljivost je odlična, saj se vsa dela izvajajo neposredno na strežniku baze podatkov, zato ni potreben prenos podatkov med strežnikom baz podatkov in orodjem za sinhronizacijo. To orodje je uporabno za naslednje primere uporabe:
Uporabite lahko orodja, kot so RedGate Data Compare, ApexSQL Data Diff in dbForge Data Compare, ki uporabljajo metodo primerjave podatkov. Uporabite lahko SQL Database Studio, ki samodejno ustvari skripte SQL za večkratno uporabo.
Primerjava na podlagi podatkov se lahko izvede samodejno z uporabo drugih orodij, ki imajo svojo ceno. Lahko pa napišete primerjalne skripte SQL, ki prikazujejo razlike, vendar je to veliko bolj zamudno in manj uporabno.
Podatkovne baze SQL lahko ročno sinhronizirate s pisanjem skriptov SQL (INSERT, DELETE, UPDATE) ali pa uporabite orodja drugih proizvajalcev, ki imajo svojo ceno. Orodja drugih proizvajalcev delujejo tako, da primerjajo podatke in generirajo sinhronizacijske skripte SQL ali pa lahko nastavite sinhronizacijsko logiko in na njeni podlagi ustvarite skripte SQL.