Przewodnik po synchronizacji danych w Microsoft SQL Server

Udostępnianie powiązanych informacji pomiędzy izolowanymi systemami staje się coraz ważniejsze dla organizacji, ponieważ pozwala im poprawić jakość i dostępność danych. Istnieje wiele sytuacji, w których przydatne jest posiadanie zestawu danych, który jest dostępny i spójny na więcej niż jednym serwerze katalogowym. Dlatego ważna jest znajomość typowych metod wykonywania synchronizacji danych programu SQL Server.

Dostępność i spójność danych można osiągnąć dzięki procesom replikacji i synchronizacji danych. Replikacja danych to proces tworzenia jednej lub więcej nadmiarowych kopii bazy danych w celu zapewnienia odporności na błędy lub poprawy dostępności. Synchronizacja danych to proces ustalania spójności danych między dwiema lub większą liczbą baz danych, a następnie ciągłe aktualizacje w celu utrzymania tej spójności.

W wielu organizacjach wykonywanie synchronizacji danych w różnych systemach jest zarówno pożądane, jak i trudne. Możemy znaleźć wiele przypadków użycia, w których musimy wykonać synchronizację danych:

• Migracja bazy danych
• Regularna synchronizacja między systemami informacyjnymi
• Importowanie danych z jednego systemu informatycznego do drugiego
• Przenoszenie zbiorów danych między różnymi etapami lub środowiskami
• Importowanie danych ze źródła niebędącego bazą danych

Nie ma unikalnego sposobu ani jednogłośnie uzgodnionej metody synchronizacji danych. To zadanie różni się w zależności od przypadku, a nawet synchronizacja danych, która na pierwszy rzut oka powinna być prosta, może być skomplikowana ze względu na złożoność struktur danych. W rzeczywistych scenariuszach synchronizacja danych składa się z wielu złożonych zadań, których wykonanie może zająć dużo czasu. Gdy pojawia się nowe wymaganie, specjaliści od baz danych zwykle muszą ponownie wdrożyć cały proces synchronizacji. Ponieważ nie ma standardowych sposobów na zrobienie tego, poza replikacją, implementacje synchronizacji danych rzadko są optymalne. Powoduje to trudną konserwację i wyższe wydatki. Wdrożenie i utrzymanie synchronizacji danych jest tak czasochłonnym procesem, że samo w sobie może być pracą na pełen etat.

Architekturę do zadań synchronizacji danych możemy wdrożyć ręcznie, ewentualnie z wykorzystaniem Microsoft Sync Framework, lub możemy skorzystać z już stworzonych rozwiązań w ramach narzędzi do zarządzania Microsoft SQL Server. Postaramy się opisać najpopularniejsze metody i narzędzia, które można wykorzystać do rozwiązania problemu synchronizacji danych na bazach danych Microsoft SQL Server i postaramy się podać kilka zaleceń.

Na podstawie struktury źródła i przeznaczenia (np. bazy danych, tabele) możemy rozróżnić przypadki użycia, gdy struktury są podobne lub różne.

Źródło i miejsce docelowe mają bardzo podobną strukturę

Dzieje się tak bardzo często, gdy wykorzystujemy dane na różnych etapach cyklu życia oprogramowania. Na przykład struktura danych w środowiskach testowych i produkcyjnych jest bardzo podobna. Powszechnym wymaganiem jest porównywanie danych między testową i produkcyjną bazą danych oraz importowanie danych z produkcji do testowej bazy danych.

Źródło i miejsce docelowe mają różne struktury

Jeśli struktury są różne, synchronizacja jest bardziej skomplikowana. Jest to również częściej powtarzające się zadanie. Częstym przypadkiem jest importowanie z jednej bazy danych do drugiej. Najczęstszym przypadkiem jest to, że oprogramowanie musi importować dane z innego oprogramowania, które jest utrzymywane przez inną firmę. Zwykle importy muszą być uruchamiane automatycznie zgodnie z harmonogramem.

Zastosowana metoda zależy od osobistych preferencji i złożoności problemu, który trzeba rozwiązać.

Niezależnie od tego, jak podobne są struktury, możemy wybrać cztery różne sposoby rozwiązania synchronizacji danych:

• Synchronizacja za pomocą ręcznie tworzonych skryptów SQL
• Synchronizacja metodą porównywania danych (może być stosowana tylko wtedy, gdy źródło i cel mają podobną strukturę)
• Synchronizacja za pomocą automatycznie generowanych skryptów SQL - potrzebujesz produktu komercyjnego

Źródło i cel mają taką samą lub bardzo podobną strukturę

Korzystanie z ręcznie tworzonych skryptów SQL

Najprostszym i najbardziej żmudnym rozwiązaniem jest ręczne pisanie skryptów SQL do synchronizacji.

Zalety

• Może być wykonywany za pomocą bezpłatnych i otwartych narzędzi (FOSS).
• Jeśli tabela ma indeksy, jest bardzo szybka.
• Skrypt SQL można zapisać w procedurze składowanej lub uruchamiać okresowo jako zadanie dla programu SQL Server.
• Może być używany jako automatyczny import, nawet w przypadku stale zmieniających się danych.

Niedogodności

• Tworzenie takiego skryptu SQL jest dość żmudne, ponieważ zwykle potrzebne są trzy skrypty dla każdej tabeli: INSERT , UPDATE i DELETE .
• Możesz synchronizować tylko dane, które są dostępne za pośrednictwem zapytań SQL, więc nie możesz importować ze źródeł, takich jak pliki CSV i XML.
• Trudno to utrzymać – przy zmianie struktury bazy danych konieczne jest zmodyfikowanie dwóch lub trzech skryptów ( INSERT , UPDATE , a czasem także DELETE ).

Przykład

Przeprowadzimy synchronizację między tabelą Source , z kolumnami ID i Value , a tabelą Target , z tymi samymi kolumnami.

Jeśli tabele mają ten sam klucz podstawowy, a tabela docelowa nie ma klucza podstawowego z automatycznym przyrostem (tożsamości), możesz wykonać następujący skrypt synchronizacji.

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

Korzystanie z metody porównywania danych

W tej metodzie możemy użyć narzędzia do porównania danych źródłowych i docelowych. Proces porównania generuje skrypty SQL, które stosują różnice ze źródłowej bazy danych do docelowej bazy danych.

Istnieje wiele programów do porównywania i synchronizacji danych. Programy te w większości stosują to samo podejście. Użytkownik wybiera źródło i docelową bazę danych, ale innymi alternatywami może być kopia zapasowa bazy danych, folder ze skryptami SQL, a nawet połączenie z systemem kontroli źródła.

Poniżej znajdują się najpopularniejsze narzędzia wykorzystujące metodę porównywania danych:

• Porównanie danych dbForge dla SQL Server
• Porównanie danych SQL RedGate
• Różnica danych Apex SQL

W pierwszym kroku odczytywane są dane lub po prostu odczytywane są sumy kontrolne większych danych ze źródła i z celu. Następnie wykonywany jest proces porównania.

Te narzędzia oferują również dodatkowe ustawienia synchronizacji.

Musimy ustawić następujące opcje konfiguracyjne, które są niezbędne do synchronizacji danych:

Klucz synchronizacji

Domyślnie używany jest klucz podstawowy lub ograniczenie UNIQUE . Jeśli nie ma klucza podstawowego, możesz wybrać kombinację kolumn. Klucz Sync służy do parowania wierszy źródła z wierszami celu.

Parowanie stołu

Domyślnie tabele są parowane według nazwy. Możesz to zmienić i sparować według własnych potrzeb. W oprogramowaniu dbForge Data Compare możesz wybrać zapytanie SQL jako źródło lub miejsce docelowe.

Proces synchronizacji

Po potwierdzeniu narzędzie porównuje dane źródłowe i docelowe. Cały proces polega na pobraniu wszystkich danych źródłowych i docelowych oraz porównywaniu ich w oparciu o określone kryteria. Domyślnie porównywane są wartości z tabel i kolumn o jednakowych nazwach. Wszystkie narzędzia obsługują mapowanie nazw kolumn i tabel. Ponadto istnieje możliwość wykluczenia kolumn IDENTITY (autoinkrementacji) lub wykonania pewnych przekształceń przed porównaniem wartości (typy zmiennoprzecinkowe typu round float, ignorowanie wielkości liter, traktowanie NULL jako pusty ciąg itp.) Pobieranie danych jest zoptymalizowane. Jeśli ilość danych jest duża, pobierane są tylko sumy kontrolne. Ta optymalizacja jest pomocna w większości przypadków, ale wymagania czasowe na wykonanie operacji rosną wraz z ilością danych.

W kolejnym kroku jest skrypt SQL z wygenerowanymi migracjami. Ten skrypt można zapisać lub uruchomić bezpośrednio. Dla bezpieczeństwa możemy nawet wykonać kopię zapasową bazy danych przed uruchomieniem tego skryptu. Narzędzie ApexSQL Data Diff może stworzyć program wykonywalny, który uruchamia skrypt na wybranej bazie danych. Ten skrypt zawiera dane, które należy zmienić, a nie logikę, jak to zmienić. Oznacza to, że skryptu nie można uruchomić automatycznie, aby zapewnić cykliczny import. To jest największa wada tego podejścia.

Zalety

• Zaawansowana znajomość SQL nie jest wymagana i można to zrobić za pomocą GUI.
• Masz możliwość wizualnego sprawdzenia różnic między bazami danych przed synchronizacją.

Niedogodności

• Jest to zaawansowana funkcja produktów komercyjnych.
• Wydajność spada podczas przesyłania ogromnych ilości danych.
• Wygenerowany skrypt SQL zawiera tylko różnice i dlatego nie może być ponownie użyty do automatycznej synchronizacji przyszłych danych.

Poniżej możesz zobaczyć typowy interfejs użytkownika tych narzędzi.

Synchronizuj z automatycznie generowanym SQL

Ta metoda jest bardzo podobna do metody porównywania danych. Jedyną różnicą w porównaniu z poprzednią metodą jest brak porównania danych, a wygenerowany skrypt SQL nie zawiera różnic danych, ale logikę synchronizacji. Wygenerowany skrypt można łatwo zapisać w procedurze składowanej i uruchamiać okresowo (np. każdej nocy). Ta metoda jest przydatna do automatycznego importowania między bazami danych. Wydajność tej metody jest znacznie lepsza niż metody porównywania danych.

Synchronizacja za pomocą automatycznie generowanego kodu SQL jest dostępna tylko w programie SQL Database Studio.

SQL Database Studio zapewnia interfejs podobny do metody porównywania danych. Musimy wybrać źródło i cel (bazy danych lub tabele). Następnie musimy ustawić opcje (synchronizacja klawiszy, parowanie i mapowanie). Istnieje funkcja graficznego konstruktora zapytań do konfigurowania wszystkich parametrów.

Zalety

• Nie jest wymagana zaawansowana znajomość SQL.
• Możesz dość szybko skonfigurować wszystko w GUI.
• Wynikowy skrypt SQL można zapisać w procedurze składowanej.
• Może być używany jako automatyczny import - jako zadanie dla SQL Server.

Niedogodności

• Jest to zaawansowana funkcja produktów komercyjnych.
• Różnic nie można sprawdzić ręcznie przed synchronizacją, ponieważ cały proces odbywa się w jednym kroku.

Testy wydajności

Przypadek testowy

Dwie bazy danych (A i B), z których każda zawiera jedną tabelę z 2 000 000 wierszy. Tabele znajdują się w dwóch różnych bazach danych na tym samym serwerze SQL Server. Ten test obejmuje dwa skrajne przypadki: 1) Tabela źródłowa zawiera wszystkie 2 000 000 wierszy, a tabela docelowa jest pusta. Synchronizacja musi zapewnić wiele INSERTS . 2) Tabele źródłowa i docelowa zawierają 2 000 000 wierszy. Różnica dotyczy tylko jednego rzędu. Synchronizacja wymaga tylko jednej UPDATE .

Porównanie danych RedGate wymaga 3 kroków:

• Porównywać
• Generuj skrypt
• Uruchom skrypt na docelowej bazie danych

ApexSQL Data Diff wymaga 2 kroków:

• Porównywać
• Wygeneruj skrypt i uruchom go w jednym kroku

SQL Database Studio wykonuje całą synchronizację w jednym kroku. Poniżej znajdują się czasy synchronizacji w sekundach. W kolumnie oznaczonej „indywidualne kroki” znajdują się czasy trwania kroków synchronizacji wymienionych powyżej.

Niższy jest lepszy.

Ten sam test, ale bazy danych znajdują się na różnych serwerach SQL, które nie są połączone przez serwer połączony.

Niższy jest lepszy.

Streszczenie

Z wyników jasno wynika, że ​​RedGate i Apex nie dbają o to, czy bazy danych znajdują się na tym samym serwerze SQL, ponieważ algorytm synchronizacji nie jest zależny od SQL Server. SQL Database Studio korzysta z natywnych funkcji SQL Server; dlatego wynik jest lepszy, gdy bazy danych znajdują się na tym samym serwerze.

Źródło i miejsce docelowe mają inną strukturę

Zdarzają się również sytuacje, w których jeden szeroki stół musi być zsynchronizowany z wieloma małymi powiązanymi tabelami.

Ten przykład składa się z jednej szerokiej tabeli SourceData, która musi zostać zsynchronizowana w małych tabelach Continent , Country i City . Schemat przedstawiono poniżej.

Dane w SourceData mogą być takie jak na poniższym obrazku.

Korzystanie z ręcznie tworzonych skryptów SQL

Synchronizacja skryptów Tabela kontynentów

 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;

Synchronizacja skryptów w tabeli miast

 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;

Ten skrypt jest bardziej skomplikowany. To dlatego, że trzeba znaleźć rekordy w tabelach Country i Continent . Ten skrypt wstawia brakujące rekordy do City i poprawnie wypełnia ContryId .

W razie potrzeby skrypty UPDATE i DELETE można również napisać w ten sam sposób.

Zalety

• Nie potrzebujesz żadnych produktów komercyjnych.
• Skrypt SQL można zapisać w procedurze składowanej lub uruchamiać okresowo jako zadanie dla SQL Server.

Niedogodności

• Tworzenie takiego skryptu SQL jest trudne i skomplikowane (dla każdej tabeli zwykle potrzebne są trzy skrypty — INSERT , UPDATE i DELETE ).
• Bardzo trudno go utrzymać.

Korzystanie z narzędzi zewnętrznych

Tego rodzaju synchronizacja (szeroka tabela na wiele powiązanych tabel) nie może być wykonana za pomocą metody porównywania danych, ponieważ skupia się ona na różnych przypadkach użycia. Ponieważ metoda porównywania danych tworzy skrypt SQL z danymi do wstawienia, nie ma bezpośredniej możliwości wyszukiwania odwołań w powiązanych tabelach. Z tego powodu nie można używać aplikacji korzystających z tej metody (dbForge Data Compare dla SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Jednak SQL Database Studio może pomóc w automatycznym tworzeniu skryptów synchronizacji. Na poniższym obrazku znajduje się element o nazwie Edytor synchronizacji danych w SQL Database Studio.

Edytor wygląda jak dobrze znany kreator zapytań i działa w bardzo podobny sposób. Każda tabela musi mieć zdefiniowany klucz synchronizacji, ale istnieją również zdefiniowane relacje między tabelami. Na powyższym obrazku znajduje się również mapowanie do synchronizacji. W liście kolumn (dolna część obrazka) znajdują się kolumny tabeli City (dla pozostałych tabel jest podobnie).

Kolumny

• Id — Ta kolumna nie jest mapowana, ponieważ jest kluczem podstawowym (generowanym automatycznie).
• CountryId — ta kolumna jest zdefiniowana jako odniesienie do tabeli.
• Nazwa — ta kolumna jest wypełniana z kolumny Miasto w tabeli źródłowej (tabela szeroka).

Kolumny CountryId i Name są wybierane jako klucze synchronizacji. Klucz synchronizacji to zestaw kolumn, które jednoznacznie identyfikują wiersz w tabeli źródłowej i docelowej. Nie możesz użyć Id klucza podstawowego jako klucza synchronizacji, ponieważ nie ma go w tabeli źródłowej.

Po synchronizacji tak wyglądają tabele:

W powyższym przykładzie jako źródło była jedna szeroka tabela. Istnieje również typowy scenariusz, w którym dane źródłowe są przechowywane w kilku powiązanych tabelach. Relacje w SQL Database Studio nie są definiowane przy użyciu kluczy obcych, ale nazw kolumn. W ten sposób możliwy jest również import z plików CSV lub Excel (plik jest ładowany do tabeli tymczasowej i z tej tabeli uruchamiana jest synchronizacja). Dobrą praktyką jest posiadanie unikalnych nazw kolumn. Jeśli nie jest to możliwe, możesz zdefiniować aliasy dla tych kolumn.

Zalety

• Łatwe i szybkie tworzenie
• Łatwe w utrzymaniu
• Możliwość zapisania w procedurze składowanej (procedura składowana jest zapisywana z danymi niezbędnymi do późniejszego otwarcia synchronizacji w edytorze)

Niedogodności

• Rozwiązanie komercyjne

Porównanie rozwiązań

Synchronizacja danych składa się z sekwencji poleceń INSERT , UPDATE lub DELETE . Istnieje wiele sposobów tworzenia sekwencji tych poleceń. W tym artykule przyjrzeliśmy się trzem opcjom tworzenia skryptów synchronizacji SQL. Pierwsza opcja to tworzenie wszystkiego ręcznie. Jest to wykonalne (ale zajmuje zbyt dużo czasu), wymaga kompleksowego zrozumienia języka SQL oraz jest trudne do stworzenia i utrzymania. Drugą opcją jest wykorzystanie narzędzi komercyjnych. Przyjrzeliśmy się następującym narzędziom:

• Porównanie danych dbForge dla SQL Server
• Porównanie danych SQL RedGate
• Różnica danych Apex SQL
• Studio baz danych SQL

Pierwsze trzy narzędzia działają bardzo podobnie. Porównują dane, pozwalają użytkownikowi analizować różnice i mogą synchronizować wybrane różnice (nawet automatycznie lub z wiersza poleceń). Są korzystne w tych scenariuszach użytkowania:

• Bazy danych nie są zsynchronizowane z powodu różnych błędów.
• Należy unikać replikacji podczas przesyłania danych między środowiskami.
• Potrzebne są raporty porównujące dane w Excelu lub HTML.

Każde narzędzie jest kochane z jednego lub drugiego powodu: dbForge ma świetny interfejs użytkownika i wiele opcji, ApexSQL działa lepiej niż reszta, a RedGate jest najpopularniejszym.

Czwarte narzędzie, SQL Database Studio, działa nieco inaczej. Generuje skrypty SQL, które zawierają logikę synchronizacji, a nie zmiany. Wydajność jest również świetna, ponieważ cała praca odbywa się bezpośrednio na serwerze bazy danych, więc nie jest potrzebny transfer danych między serwerem bazy danych a narzędziem do synchronizacji. To narzędzie jest przydatne w następujących przypadkach użycia:

• Automatyczne migracje baz danych, w których bazy danych mają inną strukturę
• Importuj do wielu powiązanych tabel
• Importuj ze źródeł zewnętrznych XML, CSV, MS Excel