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:

• Selitev zbirke podatkov
• Redna sinhronizacija med informacijskimi sistemi
• Uvoz podatkov iz enega informacijskega sistema v drugega
• Premikanje naborov podatkov med različnimi stopnjami ali okolji
• Uvoz podatkov iz vira, ki ni iz baze 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.

Vir in cilj imata zelo podobne strukture

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.

Vir in cilj imata različno strukturo

Č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:

• Sinhronizacija z ročno ustvarjenimi skripti SQL
• Sinhronizacija z uporabo metode primerjave podatkov (lahko se uporablja samo, če imata izvor in cilj podobno strukturo)
• Sinhronizacija z uporabo samodejno ustvarjenih skriptov SQL - potrebujete komercialni izdelek

Vir in cilj imata enake ali zelo podobne strukture

Uporaba ročno ustvarjenih skriptov SQL

Najbolj neposredna in dolgočasna rešitev je ročno pisanje skriptov SQL za sinhronizacijo.

Prednosti

• Lahko se izvaja z brezplačnimi in odprtokodnimi orodji (FOSS).
• Če ima tabela indekse, je zelo hiter.
• Skript SQL lahko shranite v shranjeni postopek ali ga občasno zaženete kot opravilo za SQL Server.
• Lahko se uporablja kot samodejni uvoz, tudi pri nenehno spreminjanih podatkih.

Slabosti

• Ustvarjanje takšnega skripta SQL je precej dolgočasno, ker so za vsako tabelo običajno potrebni trije skripti: INSERT, UPDATE in DELETE.
• Sinhronizirate lahko samo podatke, ki so na voljo prek poizvedb SQL, zato ne morete uvoziti iz virov, kot so datoteke CSV in XML.
• Težko ga je vzdrževati - ob spremembi strukture baze podatkov je treba spremeniti dva ali tri skripte (INSERT, UPDATE, včasih pa tudi DELETE).

Primer

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)

Uporaba metode primerjave podatkov

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:

• dbForge Data Compare za SQL Server
• RedGate SQL Primerjava podatkov
• Apex SQL Data Diff

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:

Tipka za sinhronizacijo

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.

Tabela Seznanjanje

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.

Postopek sinhronizacije

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.

Prednosti

• Napredno znanje SQL ni potrebno in ga je mogoče narediti prek GUI.
• Pred sinhronizacijo lahko vizualno preverite razlike med bazami podatkov.

Slabosti

• To je napredna značilnost komercialnih izdelkov.
• Zmogljivost se zmanjša pri prenosu ogromnih količin podatkov.
• Ustvarjeni skript SQL vsebuje samo razlike in ga zato ni mogoče ponovno uporabiti za samodejno sinhronizacijo prihodnjih podatkov.

Spodaj si lahko ogledate tipičen uporabniški vmesnik teh orodij.

ApexSQL Data Diff

RedGate SQL Primerjava

Spremeni seznam v dbForge Data Compare

Sinhronizirajte s samodejno ustvarjenim SQL

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.

Prednosti

• Napredno znanje SQL ni potrebno.
• V GUI lahko nastavite vse kar hitro.
• Nastali skript SQL lahko shranite v shranjeni postopek.
• Lahko se uporablja kot samodejni uvoz - kot opravilo za SQL Server.

Slabosti

• To je napredna značilnost komercialnih izdelkov.
• Razlik pred sinhronizacijo ni mogoče preveriti ročno, ker se celoten postopek izvede v enem koraku.

Merila uspešnosti

Testni primer

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:

• Primerjaj
• Ustvari skript
• Zaženite skript v ciljni bazi podatkov

ApexSQL Data Diff potrebuje dva koraka:

• Primerjaj
• Ustvari skript in zaženi skript v enem koraku

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.

Povzetek

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.

Vir in cilj imata drugačno strukturo

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.

Uporaba ročno ustvarjenih skriptov SQL

Tabela sinhronizacije skriptov

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;

Tabela mest za sinhronizacijo skriptov

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.

Prednosti

• Ne potrebujete nobenih komercialnih izdelkov.
• Skript SQL lahko shranite v shranjeni postopek ali ga občasno zaženete kot opravilo za SQL Server.

Slabosti

• Ustvarjanje takega skripta SQL je težko in zapleteno (za vsako tabelo so običajno potrebni trije skripti - INSERT, UPDATE in DELETE).
• Zelo težko ga je vzdrževati.

Uporaba zunanjih orodij

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

• Id - Ta stolpec ni preslikan, ker je primarni ključ (samodejno ustvarjen).
• ID države - Ta stolpec je opredeljen kot referenca za tabelo.
• Ime - Ta stolpec se zapolni iz stolpca Mesto v izvorni tabeli (široka tabela).

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.

Prednosti

• Preprosto in hitro ustvarjanje
• Enostavno vzdrževanje
• Se lahko shrani v shranjeni postopek (shranjeni postopek se shrani s podatki, potrebnimi za poznejše odpiranje sinhronizacije v urejevalniku)

Slabosti

• Komercialna rešitev

Primerjava rešitev

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:

• dbForge Data Compare za SQL Server
• RedGate SQL Primerjava podatkov
• Apex SQL Data Diff
• SQL Database Studio

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:

• Zbirke podatkov niso usklajene zaradi različnih napak.
• Med prenosom podatkov med okolji se morate izogibati kopiranju.
• Potrebna so poročila o primerjanju podatkov v Excelu ali HTML-ju.

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:

• Samodejne selitve baz podatkov, kjer imajo baze podatkov drugačno strukturo
• Uvozi v več sorodnih tabel
• Uvoz iz zunanjih virov XML, CSV, MS Excel

---

Sorodno: Oracle za SQL Server in SQL Server za Oracle Migration Guide

Razumevanje osnov

Katera orodja so na voljo za sinhronizacijo baze podatkov?

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.

Kako primerjati podatke iz baze podatkov?

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.

Kako sinhronizirati zbirke podatkov v strežniku SQL Server?

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.