Replikacja polega na kopiowaniu i przesyłaniu danych lub obiektów bazodanowych pomiędzy wieloma serwerami oraz synchronizowaniu danych znajdujących się na biorących udział w replikacji serwerach bazodanowych.

Dzięki replikacji możemy: poprawić niezawodność systemu bazodanowego, umiejscowić informacje zapisane w bazach danych jak najbliżej użytkowników końcowych, a w rezultacie poprawić wydajność systemu bazodanowego.

Replikując dane:

• Umożliwiamy lokalny dostęp do danych użytkownikom z oddalonych oddziałów firmy (zamiast nawiązywać połączenia poprzez wolne, zawodne i niebezpieczne sieci WAN, użytkownicy będą łączyć się z lokalnym serwerem bazodanowym).
• Pozwalamy na częściową niezależność serwerów bazodanowych, które mogą cały dzień przechowywać kopie danych, a jedynie nocą, podczas niskiego obciążenia systemu, połączą się z dystrybutorem i zsynchronizują informacje.
• Możemy podzielić dane w sposób odpowiadający strukturze firmy.
• Możemy fizycznie rozdzielić serwery bazodanowe realizujące różne zadania w oparciu o te same dane (np. przenieść serwer OLAP na inny fizycznie SQL Server).

Wstęp do replikacji

Model wydawca — dystrybutor — subskrybent

Logiczny model replikacji firmy Microsoft opisuje trzy role, które mogą zostać przypisane serwerom bazodanowym: serwer może zostać skonfigurowany jako dystrybutor, wydawca oraz subskrybent.

1. Zadaniami serwera pełniącego rolę wydawcy są:
   • Utrzymywanie wzorcowej wersji danych.
   • Udostępnianie danych źródłowych innym serwerom.
   • Monitorowanie zmian w replikowanych danych i informowanie o nich serwer pełniący role dystrybutora.
2. Dane udostępnione przez wydawcę zostają przesłane na zarządzający replikacją serwer pełniący rolę dystrybutora. Zadaniami tego serwera są:
   • Utrzymywanie systemowej bazy danych distribution (baza zostanie automatycznie utworzona podczas przypisywania serwerowi tej roli).
   • Przechowywanie i zarządzanie metainformacjami związanymi z replikacją danych, takimi jak historia zmian danych, transakcje przeprowadzone na poszczególnych serwerach, konfiguracja serwerów biorących udział w replikacji itd.
3. Subskrybent jest serwerem bazodanowym, który:
   • Odbiera zmiany replikowanych danych przeprowadzone na innych serwerach.
   • Przechowuje lokalną kopię tych danych. Dane mogą zostać udostępnione użytkownikom tylko do odczytu lub do odczytu i modyfikacji.

Publikacje

Dane replikowane pomiędzy serwerami bazodanowymi składają się z publikacji. Publikacją jest dowolny zbiór artykułów pobranych z tej samej bazy danych (zakresem publikacji jest pojedyncza baza danych).

Artykuły mogą zawierać część danych przechowywanych w tabeli (w przypadku replikacji transakcyjnej niemożliwa jest projekcja danych) lub mogą składać się z takich obiektów jak: tabela, widok, procedura składowana czy funkcja.

Dowolna liczba artykułów, po przypisaniu ich do określonej publikacji, może zostać replikowana pomiędzy serwerami bazodanowym. Natomiast niemożliwe jest niezależne skonfigurowanie (np. określenie innego harmonogramu replikacji) pojedynczych artykułów.

Model replikacji zaimplementowany przez firmę Microsoft nie umożliwia replikowania obiektów lub danych powiązanych z innymi obiektami czy danymi bez jednoczesnego replikowania obiektów źródłowych — jeżeli dodaliśmy do publikacji procedurę składowaną odwołującą się do tabeli, tabela ta również musi zostać dodana do publikacji.

Synchronizowanie danych

Zarówno dystrybutor, jak i subskrybent mogą zainicjować proces synchronizacji replikowanych danych. W pierwszym wypadku mówimy o replikacji wypychanej (ang. push), a w przypadku zainicjowania synchronizacji przez subskrybenta — o replikacji ściąganej (ang. pull).

Replikacja wypychana jest zalecana w przypadku synchronizowania poufnych danych pomiędzy niewielką liczbą serwerów i wymaga wydajnego komputera, na którym zainstalowano serwer pełniący rolę dystrybutora. Obciążająca subskrybentów replikacja ściągana zalecana jest w przypadku synchronizowania danych pomiędzy dużą liczbą serwerów.

Dystrybutor może zezwolić na pobranie replikowanych danych anonimowym serwerom (w takim wypadku SQL Server musi jedynie nawiązać połączenie z dystrybutorem, czyli wystarczy, że podamy login i hasło dystrybutora) lub ograniczyć listę subskrybentów do serwerów określonych podczas tworzenia publikacji.

Typy replikacji

Dane wchodzące w skład publikacji mogą być synchronizowane poprzez wysyłanie migawki bazy danych (replikację migawkową), kopię danych zmodyfikowanych od czasu ostatniej synchronizacji (replikację scalaną) lub poprzez przesyłanie aktywnej części dziennika transakcyjnego (replikację transakcyjną). Każdy typ replikacji cechuje się różnym stopniem niezależności serwerów (ich autonomią) oraz czasem, po którym dane na wszystkich serwerach są spójne (opóźnieniem).

1. Replikacja transakcyjna (ang. Transactional replication) — dane zmodyfikowane w bazie wydawcy są przesyłane do subskrybentów w takiej kolejności, w jakiej zostały wprowadzone. W rezultacie serwery subskrybentów cechuje bardzo mała autonomia i niewielki czas opóźnienia. Ponieważ zmiany są zatwierdzane w kolejności ich wprowadzania, w tym typie replikacji nie występują konflikty (sytuacje, w których te same dane zostały zmodyfikowane na różnych serwerach) i podczas synchronizacji tylko jedna wartość będzie musiała zostać wprowadzona do wszystkich baz danych. Replikacja transakcyjna wymaga szybkiego i niezawodnego połączenia wszystkich serwerów bazodanowych i w praktyce nie jest stosowana poza sieciami lokalnymi (brak połączenia z jednym serwerem spowoduje niemożność obcięcia dziennika transakcyjnego bazy distribution i w krótkim czasie doprowadzi do zatrzymania pracy całego systemu).
2. Replikacja scalana (ang. Merge replication) — dane zmodyfikowane na jednym serwerze bazodanowym po określonym czasie są przesyłane do dystrybutora, który rozwiązuje konflikty i wysyła spójny obraz danych do wszystkich serwerów bazodanowych. Konflikty rozwiązywane są na podstawie priorytetów przyznanych poszczególnym serwerom. Ten typ replikacji cechuje pewna autonomia i związane z nią średnie opóźnienie.
3. Replikacja migawkowa (ang. Snapshot replication) — obraz bazy danych wydawcy poprzez dystrybutora systematycznie przesyłany jest subskrybentom. Ponieważ poszczególni subskrybenci przechowują te same dane, konflikty nie występują. Ten typ replikacji charakteryzuje najwyższa autonomia i największe opóźnienie.

Fizyczne modele replikacji

Replikacja dowolnego typu może zostać zaimplementowana w jednym z trzech fizycznych modeli replikacji: centralnego wydawcy (dystrybutora), centralnego subskrybenta lub w modelu równorzędnym.

Domyślny model centralny wydawca (dystrybutor) zakłada, że jeden serwer pełni rolę wydawcy (dystrybutora) replikującego dane pomiędzy dowolną liczbą subskrybentów (rysunek 21.1).

Rysunek 21.1. Model centralnego wydawcy (dystrybutora)

Role wydawcy i dystrybutora mogą zostać przypisane temu samemu lub odrębnym serwerom bazodanowym. W tym drugim przypadku większość operacji związanych z replikacją będzie wykonywanych przez dystrybutora, co odciąży serwer pełniący rolę wydawcy.

Typowym przykładem tego modelu jest replikacja danych w centralnie zarządzanej firmie, w której decyzje (zmiany) podejmowane są w centrali, a następnie udostępniane oddziałom — subskrybentom.

Model centralnego subskrybenta zakłada, że dowolna liczba wydawców (dystrybutorów) przesyła dane do jednego subskrybenta (rysunek 21.2).

Rysunek 21.2. Model centralnego subskrybenta

W tym przypadku dane modyfikowane jednocześnie na wielu serwerach (oddziałach) zostają przesłane do centrali (subskrybenta), gdzie są analizowane.

Model równorzędny zakłada, że w replikacji bierze udział wielu dystrybutorów (wydawców) i wielu subskrybentów (rysunek 21.3).

Rysunek 21.3. Model równorzędny

Implementacja tego modelu jest najtrudniejsza i wymaga dokładnego zaplanowania struktury replikowanej bazy danych i zawartości poszczególnych publikacji — w innym wypadku utrzymanie spójnego obrazu danych będzie odbywało się kosztem albo ograniczenia funkcjonalności poszczególnych serwerów, albo wycofywania podczas synchronizacji sporej części transakcji użytkowników.

Konfiguracja replikacji pomiędzy serwerami bazodanowymi

Replikacja danych pomiędzy serwerami bazodanowym wymaga:

1. Uruchomienia usług SQL Server i SQLServerAgent w kontekście zabezpieczeń konta użytkownika domenowego (do replikacji nie jest wymagane nadanie tym użytkownikom uprawnień administratora komputera lub domeny).
2. Nadania uprawnień użytkownikowi, w kontekście którego działa usługa SQLServerAgent, do folderu, w którym zapisywane są dane publikacji.
3. Umożliwienia usłudze SQLServerAgent nawiązania połączenia z pozostałymi serwerami bazodanowymi replikującymi dane.
   
   Chociaż replikacja realizowana jest przez agentów replikacji, ich działanie wymaga uruchomienia usługi SQLServerAgent na wszystkich komputerach replikujących dane.
4. Umożliwienia obu głównym usługom SQL Servera zapis i odczyt kluczy rejestru związanych z SQL Serverem.
5. Ujednolicenia zestawu znaków wszystkich baz danych biorących udział w replikacji.

Wybór dystrybutora

Jako pierwszy należy wybrać serwer bazodanowy, który będzie pełnił rolę dystrybutora. To on będzie koordynował synchronizację danych i przechowywał (w bazie distribution oraz na lokalnych dyskach twardych) replikowane dane. Dystrybutorem może być ten sam serwer, który będzie pełnił rolę wydawcy lub osobny serwer bazodanowy. Zaletami tego drugiego rozwiązania są:

• Wzrost wydajności systemu bazodanowego osiągnięty dzięki przydzieleniu dystrybutorowi i wydawcy własnych zasobów sprzętowych.
• Zrównoważenie obciążenia serwerów.

Rozwiązanie to ma również wady:

• Wzrost kosztów.
• Utworzenie dodatkowego punktu awarii systemu.
• Znaczne zwiększenie obciążenia sieci w segmencie, w którym znajdują się wydawca i dystrybutor.

Wybór wydawcy i subskrybenta

Po określeniu, który serwer będzie pełnił rolę dystrybutora, wybór wydawcy i subskrybenta zależy wyłącznie od wybranego modelu replikacji i od lokalizacji istniejących serwerów bazodanowych. W przypadku replikacji transakcyjnych wydawcą nie może być SQL Server zainstalowany w środowisku systemów Windows 9x.

Agenci replikacji

Za replikację danych (tworzenie kopii obiektów bazodanowych i danych, wykrywanie modyfikacji, przesyłanie publikacji i wcielanie ich do baz subskrybentów) odpowiedzialne są usługi replikacji nazywane agentami.

Agent migawki

Agent migawki (ang. Snapshot Agent) — używany w replikacjach każdego typu, odpowiada za wykonywanie pełnego obrazu publikacji, włącznie z definicją wszystkich replikowanych obiektów bazodanowych. Tak utworzony obraz (migawka) jest podstawą wszystkich replikacji i służy do wstępnego zsynchronizowania baz danych. Jeden agent migawki odpowiada za jedną publikację.

Zdiagnozować ewentualne problemy w pracy agenta możemy poprzez wywołanie go z wiersza polecenia oraz analizę zwracanych komunikatów (rysunek 21.4). Plik wykonywalny agenta, snapshot.exe, znajduje się w folderze ProgramFiles%\Microsoft SQL Server\80\COM:

Rysunek 21.4. Wynik uruchomienia agenta migawki z wiersza polecenia

 D:\Program Files\Microsoft SQL Server\80\COM\snapshot -Publisher LOLEK –PublisherDB 􀂪pubs -Publication pubs –DistributorSecurityMode 1 –DistributionLogin E

Agent transakcji

Agent transakcji (ang. Log Reader Agent) — używany wyłącznie w replikacji transakcyjnej, odpowiada za przesyłanie transakcji oznaczonych do replikacji z dystrybutora do subskrybentów. Jeden agent transakcji odpowiada za replikowanie jednej bazy danych.

Ewentualne problemy w pracy agenta możemy zdiagnozować, wywołując go z wiersza polecenia i analizując zwracane komunikaty (rysunek 21.5). Plik wykonywalny agenta, logread.exe, znajduje się w folderze %ProgramFiles%\Microsoft SQL Server\80\COM:

 D:\Program Files\Microsoft SQL Server\80\COM\logread -Publisher lolek -PublisherDB 􀂪pubs -DistributorSecurityMode 1 

Rysunek 21.5.Ponieważ poprawnie skonfigurowany agent transakcji jest automatycznie uruchamiany, kolejna próba jego uruchomienia, bez zatrzymania działającego dla bazy danych agenta, zakończy się wyświetleniem komunikatu błędu 18 752 i wszystkich błędów będących jego konsekwencją

Informacje wyświetlone na rysunku 21.5 świadczą o poprawnej pracy agenta — w bazie wydawcy nie ma żadnych transakcji, które należałby wysłać do subskrybentów.

Agent scalania

Agent scalania (ang. Merge Agent) — używany wyłącznie w replikacji scalanej, odpowiada za wcielenie początkowej migawki do bazy subskrybenta oraz za przesyłanie i wcielanie danych zmodyfikowanych w replikowanych bazach. Jeden agent migawki odpowiada za jedną publikację scalaną. W przypadku replikacji wypychanej agent działa po stronie dystrybutora, w przypadku replikacji ściąganej — po stronie subskrybenta.

Ewentualne problemy w pracy agenta możemy zdiagnozować, wywołując go z wiersza polecenia i analizując zwracane komunikaty (rysunek 21.6). Plik wykonywalny agenta, replmerg.exe, znajduje się w folderze %ProgramFiles%\Microsoft SQL Server\80\COM:

Rysunek 21.6. Wynik uruchomienia agenta scalania z wiersza polecenia

 D:\Program Files\Microsoft SQL Server\80\COM\replmerg -Publisher lolek -PublisherDB 􀂪pubs -Publication pubs2 -Subscriber lolek\kopia -SubscriberDB p_3 -SubscriptionType 􀂪0 -SubscriberSecurityMode 1 -Distributor lolek -DistributorLogin sa 

Agent dystrybucji

Agent dystrybucji (ang. Distribution Agent) — używany w replikacjach migawkowej i transakcyjnej, odpowiada za przesyłanie pliku migawki i transakcji z dystrybutora do subskrybentów. W przypadku replikacji wypychanej agent działa po stronie dystrybutora, w przypadku replikacji ściąganej — po stronie subskrybenta.

Ewentualne problemy w pracy agenta możemy zdiagnozować, wywołując go z wiersza polecenia i analizując zwracane komunikaty (rysunek 21.7). Plik wykonywalny agenta, distrib.exe, znajduje się w folderze %ProgramFiles%\Microsoft SQL Server\80\COM:

 D:\Program Files\Microsoft SQL Server\80\COM\distrib -Subscriber lolek\kopia 􀂪-SubscriberDB pubs -Publisher lolek -Distributor lolek -PublisherDB pubs 􀂪-DistributorSecurityMode 1 -distributorlogin E 

Agent kolejkowania

Agent kolejkowania (ang. Queue Reader Agent) — używany w replikacjach migawkowej i transakcyjnej, odpowiada za realizowanie określonych harmonogramów synchronizowania danych.

Ewentualne problemy w pracy agenta możemy zdiagnozować, wywołując go z wiersza polecenia i analizując zwracane komunikaty (rysunek 21.8). Plik wykonywalny agenta, qrdrsvc.exe, znajduje się w folderze %ProgramFiles%\Microsoft SQL Server\80\COM:

Rysunek 21.7. Wynik uruchomienia agenta dystrybucji z wiersza polecenia

Rysunek 21.8. Wynik uruchomienia agenta kolejkowania z wiersza polecenia

 D:\Program Files\Microsoft SQL Server\80\COM>"D:\Program Files\Microsoft SQL Server\ 􀂪80\COM\qrdrsvc.exe 

Replikacja migawkowa

Replikacja migawkowa polega na wykonywaniu kopii danych i obiektów wydawcy i przesyłaniu ich do subskrybentów. Za przebieg replikacji odpowiadają uruchomiony na serwerze pełniącym rolę dystrybutora agent migawki oraz agent dystrybucji.

Synchronizując dane, agent migawki kolejno:

1. Nawiązuje połączenie z wydawcą i zakłada współdzieloną blokadę na wszystkich tabelach wchodzących w skład publikacji.
2. Odczytuje strukturę wybranych tabel i zapisuje je do plików .sch. Dodatkowo, jeżeli publikacja zawiera obiekty tego typu, zostaną utworzone pliki .idx zawierające definicję indeksów, .trg — wyzwalaczy i .dri — zawężeń.
   
   Domyślnie pliki migawki zapisywane są w podfolderze folderu \ProgramFiles\ Microsoft SQL Server\MSSQL\REPLDATA, ale ze względów bezpieczeństwa i w celu poprawy wydajności powinny zostać zapisane na odrębnym dysku, chyba że w replikacji biorą udział serwery w wersji 7.0 lub wcześniejszej.
3. Odczytuje wybrane dane i zapisuje je do plików .bcp.
4. Zapisuje historię wykonanych operacji do tabeli distribution..MSrepl_ transactions oraz distribution..MSrepl_commands i zwalnia założone przez siebie blokady.

Agent dystrybucji, aby przesłać dane migawki subskrybentom, kolejno:

1. Nawiązuje połączenie z dystrybutorem i odczytuje zawartość tabel. distribution..MSrepl_transactions oraz distribution..MSrepl_commands.
2. Pobiera pliki migawki i przesyła je do komputera, na którym został uruchomiony serwer pełniący rolę subskrybenta.
3. Jeżeli zachodzi taka potrzeba (subskrybentem jest inny serwer bazodanowy lub SQL Server w innej wersji), konwertuje instrukcje języka Transact-SQL tworzące obiekty bazodanowe.
4. Tworzy obiekty bazodanowe.
5. Wypełnia obiekty bazodanowe danymi.
6. Zapisuje historię swoich operacji w bazie distribution.

Replikowane dane są zapisywane do plików, a następnie importowane do docelowych baz danych za pomocą narzędzia bcp (ang. Bulk Copy Program). Jeżeli wszystkie bazy danych replikujące dane pracują w środowisku SQL Servera 2000, dane zapisywane są w charakterystycznym dla tego serwera formacie, w przeciwnym wypadku dane zostaną zapisane w postaci pliku tekstowego, co wiąże się z obniżeniem wydajności programu.

Poprawić wydajność replikacji możemy poprzez modyfikację wartości poniższych opcji agenta migawki:

1. MaxBcpThreads — domyślnie import i eksport danych realizowany jest przez pojedynczy wątek. W systemach wieloprocesorowych, w których dane zapisane są na kilku dyskach twardych, zwiększenie liczby wątków może skrócić czas potrzebny na kopiowanie danych.
2. BcpBatchSize — domyślna wartość (100 000 wierszy) powinna zostać zwiększona w systemach, w których replikowana jest duża liczba danych, a połączenie pomiędzy serwerami cechuje się dużą niezawodnością. W przypadku awaryjnych połączeń zmniejszenie domyślnej wartości spowoduje ponowienie wysyłania mniejszych pakietów.

Replikacja transakcyjna

Replikacja transakcyjna polega na kopiowaniu transakcji przeprowadzonych w bazie danych wydawcy do baz subskrybentów i przebiega w trzech etapach:

1. Początkowa migawka bazy wydawcy zostaje wcielona do bazy subskrybentów w celu zsynchronizowania baz danych.
2. Agent transakcji systematycznie sprawdza, czy w dzienniku transakcyjnym bazy wydawcy nie zostały zapisane transakcje, które należy skopiować do subskrybentów i jeżeli je znajdzie — kopiuje je do dystrybutora. Agent transakcji kolejno:
   • Zapisuje do tabeli distribution.dbo.MSlogreader_history numer LSN ostatnio przesłanej transakcji.
   • Łączy się z wydawcą i odczytuje dziennik transakcyjny replikowanej bazy, sprawdzając, czy zostały w nim zapisane transakcje o wyższym numerze LSN (aby odczytać dziennik, agent zdalnie wywołuje procedurę sp_replcmds),
   • Kopiuje transakcje tworzące publikacje do dystrybutora.
   • Wykonuje procedurę systemową sp_repldone oznaczającą transakcje jako odczytane.
   • Podczas najbliższego obcinania dziennika transakcyjnego usuwa transakcje o numerach mniejszych niż LSN.
3. Agent dystrybucji przesyła i wciela transakcje do baz danych subskrybentów.

Ponieważ replikacja transakcyjna polega na kopiowaniu instrukcji języka Transact-SQL, a nie danych, niemożliwe jest dołączenie do publikacji tabel, które nie mają zdefiniowanego klucza głównego. Spójność replikowanych danych administratorzy z reguły wymuszają, dodając do definicji zawężeń i wyzwalaczy klauzulę NOT FOR REPLICATION — w takim wypadku zawężenie zostaje sprawdzone, a wyzwalacz uruchomiony, o ile dane zostały zmodyfikowane w lokalnym serwerze, a nie w wyniku replikacji.

Aktywni subskrybenci

W replikacji transakcyjnej (tak jak w migawkowej, ale w praktyce nie stosuje się replikacji migawkowej z aktywnymi subskrybentami) można zezwolić subskrybentom na zmianę replikowanych danych. Aby jednak było to możliwe, należy spełnić poniższe warunki:

1. Dane nie mogą być dodatkowo replikowane przez scalanie.
2. Niemożliwe jest modyfikowanie po stronie subskrybentów danych typu text, ntext oraz image.
3. Bazy subskrybentów nie mogą zawierać innych danych niż dane wcielone poprzez migawkę początkową.
4. Niemożliwe będzie takie pionowe filtrowanie danych, w wyniku którego artykuły nie będą zawierały wartości automatycznie dodanej kolumny MSrepl_tran_version.
5. Dodatkowym ograniczeniem jest wymóg określania kolumn instrukcji INSERT podczas wstawiania wierszy po stronie subskrybentów.

Jeżeli dane zostały zmodyfikowane po stronie subskrybenta, uruchomiona zostaje usługa MS DTC i transakcja zostaje, w dwóch fazach, zatwierdzona zarówno na serwerze pełniącym rolę wydawcy, jak i na serwerze pełniącym rolę subskrybenta. Od tego momentu modyfikacja jest traktowana tak, jakby została przeprowadzona po stronie wydawcy i zgodnie z harmonogramem replikacji zostanie wysłana do pozostałych subskrybentów.

Monitorować replikację transakcyjna możemy z poziomu języka Transact-SQL za pomocą poniższych procedur systemowych:

Procedura sp_browsereplcmds

Procedura zwraca listę instrukcji wykonanych w związku z replikacją danych.

Składnia:

 sp_browsereplcmds [[@xact_seqno_start =] 'początkowa_instrukcja'] [,[@xact_seqno_end =] 'końcowa_instrukcja'] [,[@originator_id =] 'id_programu'] [,[@publisher_database_id =] 'id_wydawcy'] [,[@article_id =] 'id_artykułu'] [,[@command_id =] 'id_instrukcji'] 

Przykład:

 USE distribution EXEC sp_browsereplcmds @article_id =13 GO 

Procedura sp_replshowcmds

Procedura zwraca listę instrukcji oczekujących na wykonanie, odpowiadającą zmianom, które wystąpiły po ostatnim wywołaniu procedury sp_repldone. Przed jej wywołaniem należy zatrzymać agenta transakcji.

Składnia:

sp_replshowcmds [@maxtrans =] największa_liczba_transakcji

Przykład:

 USE pubs EXEC sp_replshowcmds GO xact_seqno originator_id publisher_database_id article_id type command ------------------------ ------------- --------------------- ----------- --- 0x00000007000000DF0008 0 0 14 30 {CALL 􀂪sp_MSdel_authors ('998-72-3563')} (1 row(s) affected) 

Procedura sp_repltrans

Procedura zwraca listę transakcji przeznaczonych do replikacji, ale niedostarczonych do dystrybutora. Duża liczba takich transakcji może wymagać skrócenia czasu, po upływie którego następuje synchronizacja danych.

Składnia:

sp_repltrans

Przykład:

 USE pubs EXEC sp_repltrans GO xdesid xact_seqno ---------------------- ---------------------- 0x00000007000000DF0001 0x00000007000000DF0008 0x00000007000000E10001 0x00000007000000E10005 (2 row(s) affected) 

Procedura sp_replflush

Wykonanie procedury usuwa artykuły czekające na skopiowanie do subskrybentów oraz wyrejestrowuje uruchomionego dla bazy danych agenta transakcji.

Składnia:

sp_replflush

Przykład:

 USE pubs EXEC sp_replflush GO The command(s) completed successfully. 

W przypadku replikacji transakcyjnej duży wpływ na wydajność całego systemu ma serwer pełniący rolę subskrybenta — silnie obciążająca system replikacja transakcyjna nakłada dodatkowe wymagania co do zasobów tego komputera.

Poprawić wydajność replikacji możemy poprzez modyfikację wartości poniższych opcji agenta transakcji:

1. PollingInterval — zmieniając domyślną wartość (10 sekund) określimy, jak często agent ma sprawdzać, czy w dzienniku transakcyjnym bazy wydawcy nie pojawiły się nowe transakcje (opcja dostępna jest również dla agenta dystrybucji).
2. ReadBatchSize — ponieważ agent odczytuje jednokrotnie określoną liczbę transakcji, zwiększenie tego parametru poprawi wydajność w sytuacji, gdy niewielki procent transakcji był przeznaczony do replikacji.
3. MaxCmdsInTran — transakcje składające się z wielu instrukcji mogą zostać przez agenta podzielone na kilka mniejszych, co np. w przypadku problemów z połączeniem sieciowym może poprawić wydajność replikacji. Wartość parametru określa maksymalną liczbę instrukcji składających się na pojedynczą transakcję.

Replikacja scalana

Replikacja scalana polega na systematycznym wprowadzaniu zmian dokonanych na jednym z serwerów (na wydawcy lub którymś z subskrybentów) do pozostałych serwerów biorących udział w replikacji. Ponieważ dane mogą być jednocześnie modyfikowane na różnych serwerach, będą występowały rozwiązywane przez dystrybutora konflikty. Za replikację tego typu odpowiadają agent migawki i agent scalania.

W ramach każdej publikacji replikacji scalanej agent migawki kolejno:

1. Tworzy migawkę zawierającą definicje obiektów i dane oraz zapisuje ją na dystrybutorze.
2. Tworzy cyklicznie uruchamiane zdanie, w ramach którego synchronizowane będą dane i rozwiązywane ewentualne konflikty.
3. Dodaje do każdej tabeli wchodzącej w skład publikacji kolumnę typu uniqueidentifier o nazwie rowguid.
4. Tworzy niegrupujący, unikalny indeks powiązany z kolumną rowguid o nazwie typu index_liczba.
5. Dodaje dwie tabele systemowe. Tabela MSmerge_contents umożliwia replikację zmienionych i dodanych wierszy, tabela MSmerge_tombstone — replikację usuniętych wierszy.
6. Dla każdej tabeli wchodzącej w skład publikacji tworzy wyzwalacze wywoływane wykonaniem instrukcji INSERT, UPDATE i DELETE o nazwach typu ins_losowyGUID, del_losowyGUID, upd_losowyGUID. Wyzwalacze te zapisują modyfikowane dane w uprzednio utworzonych tabelach systemowych.
7. Zapisuje historię swoich operacji w bazie distribution.

Zadaniem agenta scalania jest:

1. Odtworzenie tabeli MSmerge_contents we wszystkich serwerach pełniących rolę subskrybenta.
2. Odczyt zawartości tej tabeli we wszystkich serwerach biorących udział w replikacji.
3. Odzwierciedlenie zmian wprowadzonych po stronie subskrybenta na serwerze pełniącym rolę wydawcy.
4. Po skopiowaniu wszystkich wierszy do wydawcy odzwierciedlenie zmian wprowadzonych po stronie wydawcy na serwerach pełniących rolę subskrybenta.
5. Rozwiązanie konfliktów, jeżeli wiersz o tej samej wartości rowguid znajdował się w kilku bazach danych.

W przeciwieństwie do dwóch poprzednich typów replikacji, które do wymiany danych wykorzystywały zasoby komputera, na którym uruchomiono serwer pełniący rolę dystrybutora i samą bazę distribution, replikacja scalana dodaje niezbędne tabele systemowe do replikowanych baz danych. W tym przypadku rola dystrybutora sprowadza się do:

• Identyfikowania serwerów biorących udział w replikacji i ich ról.
• Przechowywania informacji o lokalizacji plików migawki.
• Przechowywaniu informacji o czasie ostatniej synchronizacji.

Domyślnie zmiany danych przechowywane są przez 14 dni, co oznacza, że jeżeli któryś z subskrybentów przez tak długi okres czasu będzie niedostępny i nie scali zmian, będzie to wymagać ponownej synchronizacji wykonanej za pomocą migawki.

Rozwiązywanie konfliktów

Konflikty w replikacji scalanej mogą być wykrywane i rozwiązywane na poziomie całych wierszy lub poszczególnych kolumn tabeli. W pierwszym przypadku jakakolwiek modyfikacja wiersza przeprowadzona w kilku bazach danych powoduje powstanie konfliktu, w drugim — jedynie zmiana danych przechowywanych w tych samych kolumnach. Domyślnie SQL Server wykrywa konflikty na poziomie kolumn. Zaletą wykrywania konfliktów na poziome kolumn jest zminimalizowanie ich liczby i ograniczenie do minimum danych, które muszą być przesyłane podczas synchronizacji baz danych. Wadą tego rozwiązania jest wysoki koszt związany z koniecznością śledzenia zmian zachodzących w poszczególnych kolumnach.

Niezależnie od poziomu blokowania konflikty rozwiązywane są na podstawie priorytetów nadanych poszczególnym serwerom. Serwerem z najwyższym priorytetem jest zawsze wydawca — zmiany wprowadzone na tym serwerze zastąpią zmiany wprowadzone na jakimkolwiek serwerze pełniącym rolę subskrybenta. Konflikty zawsze mogą zostać rozwiązane, ponieważ zmodyfikowane dane są przechowywane w poszczególnych bazach danych w automatycznie utworzonych tabelach o nazwach typu conflict_Publikacja_ Artykuł. Na przykład, po utworzeniu publikacji pubs2 obejmującej wszystkie tabele bazy pubs, w bazie zostaną utworzone poniższe tabele:

 USE pubs SELECT LEFT(name,30) FROM dbo.sysobjects WHERE name LIKE 'conflict%' GO ------------------------------ conflict_pubs2_authors conflict_pubs2_discounts conflict_pubs2_employee conflict_pubs2_jobs conflict_pubs2_pub_info conflict_pubs2_publishers conflict_pubs2_roysched conflict_pubs2_sales conflict_pubs2_stores conflict_pubs2_titleauthor conflict_pubs2_titles (11 row(s) affected) 

Należy także poświecić uwagę automatycznie generowanym wartościom kluczy głównych. Ponieważ będą one generowane w kilku serwerach, aby zapewnić im niepowtarzalność, należy przypisać zakresy wartości nadawanych przez poszczególne serwery. Z poziomu języka Transact-SQL możemy zrobić to, wywołując procedurę sp_adjustpublisheridentityrange.

Zarządzanie replikacjami

Implementacja wybranego typu i modelu replikacji wpływa na wykonywanie typowych zadań administracyjnych, takich jak zmiana definicji tabeli. Należy też pamiętać o wprowadzonych automatycznie przez SQL Server zmianach do definicji niektórych obiektów.

Replikowanie definicji tabel

Dodając do publikacji dane przechowywane w tabeli, SQL Server modyfikuje jej strukturę poprzez dodanie kolumn umożliwiających jednoznaczne określenie wiersza w skali wszystkich baz danych uruchomionych na serwerach biorących udział w replikacji.

O ile zmiana nazwy tabeli i poszczególnych kolumn oraz zmiana typu danych mogą zostać przeprowadzone bez żadnych dodatkowych ograniczeń, o tyle usunięcie lub dodanie kolumny należy wykonać, wywołując procedury systemowe sp_repladdcolumn lub sp_repldropcolumn, a nie wykonując instrukcję ALTER TABLE. Tak więc:

 USE pubs EXEC sp_repladdcolumn @source_object = 'dbo.authors' ,@column = 'sex' ,@typetext = 'char (1) NOT NULL DEFAULT ''M''' ,@publication_to_add = 'all' GO 

będzie funkcjonalnym odpowiednikiem instrukcji:

 USE pubs ALTER TABLE dbo.authors ADD sex char(1) NOT NULL DEFAULT 'M' GO 

Procedura sp_repldropcolumn umożliwia usunięcie kolumny, o ile nie przechowuje ona unikalnych identyfikatorów wiersza, do których zalicza się: klucz główny, kolumny o zawężeniu UNIQUE jednoznacznie identyfikujące wiersz w ramach publikacji oraz kolumny typu UNIQUEIDENTIFIER.

Replikowanie definicji widoków, funkcji i procedur

Replikując definicje obiektów bazodanowych, należy pamiętać, że:

1. Definicje obiektów nie powinny wchodzić w skład tej samej publikacji co dane.
2. Replikując widoki, procedury lub funkcje należy dołączyć do replikacji tabele, do których odwołują się te obiekty — SQL Server automatycznie dołączy do replikacji jedynie strukturę tabel źródłowych, bez przechowywanych w nich danych.
3. Uprawnienia użytkowników nie są replikowane wraz z obiektami — aby umożliwić użytkownikom korzystanie z replikowanych obiektów na wszystkich serwerach. należy nadać do nich odpowiednie uprawnienia.

Sprawdzanie replikacji danych

Podstawowym narzędziem do monitorowania replikacji jest konsola Enterprise Manager — dzięki monitorowi replikacji (ang. Replication Monitor) możemy poznać dane o bieżącej konfiguracji wszystkich agentów oraz historie wykonywanych przez nich zadań.

Jednak sprawdzić konfigurację replikacji oraz jej wynik, a więc poprawność danych wchodzących w skład publikacji i artykułów, możemy z poziomu języka Transact-SQL.

Procedura sp_helpsubscription

Zwraca informacje o subskrypcjach istniejących publikacji.

Składnia:

 sp_helpsubscription [[@publication =] 'publikacja'] [,[@article =] 'artykuł'] [,[@subscriber =] 'subskrybent'] [,[@destination_db =] 'docelowa_baz_danych'] 

Przykład:

 USE pubs EXEC sp_helpsubscription GO 

Procedura sp_helparticle

Procedura zwraca szczegółowe informacje o określonym artykule.

Składnia:

 sp_helparticle [@publication =] 'publikacja' [,[@article =] 'artykuł'] [,[@returnfilter =] filtr] 

gdzie:

filtr określa, czy procedura ma zwrócić informacje o filtrowaniu poziomym danych przeprowadzonym za pomocą klauzuli FOR REPLICATION (domyślna wartość 1), czy dane te zostaną pominięte (wartość 0).

Przykład:

 USE pubs EXEC sp_helparticle @publication = 'pubs' ,@article = 'authors' ,@returnfilter = 0 GO 

Procedura sp_helppublication

Procedura zwraca informacje o publikacji.

Składnia:

sp_helppublication [[@publication =] 'publikacja']

Przykład:

 USE pubs EXEC sp_helppublication @publication = 'pubs' GO 

Procedura sp_table_validation

Procedura zwraca liczbę wierszy oraz sumę kontrolną danych przechowywanych w tabeli lub widoku powiązanym z indeksem. Procedura może zostać wykorzystana w replikacji każdego typu.

Składnia:

 sp_table_validation [@table =] 'tabela' [, [@expected_rowcount =] spodziewana_liczba_wierszy] [, [@expected_checksum =] spodziewana_suma_kontrolna] [, [@rowcount_only =] tylko_zliczanie_wierszy] [, [@owner =] 'właściciel'] [, [@full_or_fast =] metoda] [, [@shutdown_agent =] agent] [, [@table_name =] tabela_wyjściowa] 

gdzie:

tylko_zliczanie_wierszy określa, czy ma zostać policzona jedynie liczba wierszy (domyślna wartość 1), czy również suma kontrolna danych (wartość 0),
metoda określa, czy liczba wierszy ma zostać zliczona na podstawie wyniku funkcji COUNT(*) (wartość 0), na podstawie zawartości tabeli sysindexes.rows (wartość 1). czy SQL Server ma najpierw spróbować oszacować liczbę wierszy na podstawie wpisów w tabeli systemowej i w razie niepowodzenia wykonać pełne skanowanie tabeli (domyślna wartość 2),
agent określa, czy agent dystrybucji ma zostać zatrzymany po zakończeniu sprawdzania (wartość 1), czy nie (domyślna wartość 0).

Przykład:

 USE pubs EXEC sp_table_validation @table = 'authors' ,@rowcount_only =0 GO Conditional Fast Rowcount method requested without specifying an expected count. 􀂪Fast method will be used. Generated expected rowcount value of 23 and expected checksum value of 10644740958 􀂪for authors. 

Procedura sp_article_validation

Procedura sprawdza poprawność danych wchodzących w skład określonego artykułu. Procedura może być wykorzystywana w replikacjach transakcyjnych i migawkowych.

Składnia:

 sp_article_validation [@publication =] 'publikacja' [, [@article =] 'artykuł'] [, [@rowcount_only =] tylko_zliczanie_wierszy] [, [@full_or_fast =] metoda] [, [@shutdown_agent =] agent] 

Przykład:

 USE pubs EXEC sp_article_validation @publication = 'pubs' ,@article = 'authors' GO Server: Msg 20523, Level 16, State 1, Procedure sp_article_validation, Line 104 Could not validate the article 'authors'. It is not activated. 

Procedura sp_publication_validation

Procedura sprawdza poprawność wszystkich artykułów wchodzących w skład publikacji. Procedura może być wykorzystywana w replikacjach transakcyjnych i migawkowych.

Składnia:

 sp_publication_validation [@publication =] 'publikacja' [, [@rowcount_only =] tylko_zliczanie_wierszy] [, [@full_or_fast =] metoda] [, [@shutdown_agent =] agent] 

Przykład:

 USE pubs EXEC sp_publication_validation @publication = 'pubs' GO The command(s) completed successfully. 

Autor: Marcin Szeliga

Spis treści

• Wstęp do replikacji
• Konfiguracja replikacji pomiędzy serwerami bazodanowymi
• Agenci replikacji
• Replikacja migawkowa
• Replikacja transakcyjna
• Replikacja scalana
• Zarządzanie replikacjami

Podstawą kursu jest wydana przez Helion książka Transact-SQL. Czarna księga.

Wszystkie ćwiczenia przygotowane są dla SQL Server 2000 i korzystają z przykładowych baz danych Northwind lub Pubs.