Jak skonfigurować replikację MS SQL Server

Microsoft SQL Server to oprogramowanie do zarządzania bazami danych, które można zainstalować w systemach operacyjnych Windows Server. Z baz danych korzystają firmy ze wszystkich branż, a wiele rozwiązań programowych wykorzystuje bazy danych – zarówno scentralizowane, jak i rozproszone. Dostępność baz danych i spójność danych mają kluczowe znaczenie dla przedsiębiorstw, dlatego tworzenie kopii zapasowych i replikacja baz danych są absolutną koniecznością.
Dowiedz się więcej o typach replikacji w SQL Serverze, o tym, jak działa replikacja w SQL Serverze oraz jak przeprowadzić replikację w SQL Serverze.

NAKIVO – tworzenie kopii zapasowej w systemie Windows

Szybkie wykonanie kopii zapasowej serwerów i stacji roboczych z systemem Windows na miejscu, poza siedzibą firmy oraz w chmurze. Odzyskiwanie całych maszyn i obiektów w ciągu kilku minut, co zapewnia krótki czas przywrócenia (RTO) i maksymalny czas sprawności.

ODKRYJ ROZWIĄZANIE

Czym jest replikacja w SQL Serverze?

Replikacja w MS SQL Serverze to proces kopiowania danych z jednej bazy danych do drugiej, w tym określonych obiektów bazy danych, oraz utrzymywania zsynchronizowanej kopii tych danych w bazie źródłowej i docelowej. Dzięki replikacji w SQL Serverze można utworzyć identyczną kopię głównej bazy danych i synchronizować zmiany między obiema bazami, zachowując jednocześnie spójność i integralność danych.

Terminologia stosowana w replikacji serwera MS SQL Server

Zanim przejdziemy do omówienia sposobu konfiguracji i uruchamiania replikacji serwera MS SQL Server, przyjrzyjmy się najpierw pokrótce głównym terminom i modelom replikacji.
Artykuły to podstawowe jednostki podlegające replikacji, takie jak tabele, procedury, funkcje i widoki. Artykuły można skalować pionowo lub poziomo za pomocą filtrów. Dla tego samego obiektu można utworzyć wiele artykułów.
Publikacja to logiczny zbiór artykułów. Jest to ostateczny zestaw elementów z bazy danych przeznaczonych do replikacji.
Filtr to zestaw warunków dla artykułu. Replikacja w MS SQL Server pozwala na stosowanie filtrów i wybieranie niestandardowych elementów do replikacji, co w rezultacie zmniejsza ruch sieciowy, nadmiarowość oraz ilość danych przechowywanych w replice bazy danych. Na przykład za pomocą filtrów można wybrać tylko najważniejsze tabele i pola, a następnie replikować wyłącznie te dane.
Agenci to komponenty serwera MS SQL Server, które mogą pełnić rolę usług działających w tle w systemach zarządzania relacyjnymi bazami danych i służą do planowania automatycznego wykonywania zadań, takich jak tworzenie kopii zapasowej i replikacja baz danych MS SQL. Istnieje pięć typów agentów: Migawka Agent, Log Reader Agent, Distribution Agent, Merge Agent oraz Queue Reader Agent.
Metadane to dane służące do opisu elementów bazy danych. Dostępna jest szeroka gama wbudowanych funkcji metadanych, które pozwalają na uzyskanie informacji o instancji serwera MS SQL, instancjach baz danych oraz elementach bazy danych.

Role w replikacji baz danych SQL

W replikacji baz danych MS SQL występują trzy główne role: dystrybutor, wydawca i subskrybent.

• Dystrybutor to instancja bazy danych MS SQL skonfigurowana do zbierania transakcji z publikacji i dystrybuowania ich do subskrybentów. Dystrybutor pełni rolę bazy danych służącej do przechowywania replikowanych transakcji.

  Baza danych dystrybutora może pełnić jednocześnie rolę wydawcy i dystrybutora. W modelu lokalnego dystrybutora pojedyncza instancja serwera MS SQL Server obsługuje zarówno wydawcę, jak i dystrybutora. Model zdalnego dystrybutora można zastosować, gdy subskrybenci mają być skonfigurowani tak, aby korzystali z jednej instancji serwera MS SQL Server w celu pobierania różnych publikacji (dystrybucja scentralizowana). W tym modelu wydawca i dystrybutor działają na różnych serwerach.

• Wydawca to główna kopia bazy danych, w której skonfigurowano publikację, udostępniająca dane innym serwerom MS SQL Server skonfigurowanym do udziału w procesie replikacji. Wydawca może posiadać więcej niż jedną publikację.
• Subskrybent to baza danych, która odbiera zreplikowane dane z publikacji. Jeden subskrybent może odbierać dane od więcej niż jednego wydawcy i z więcej niż jednej publikacji. Model z jednym subskrybentem stosuje się, gdy istnieje tylko jeden subskrybent. Model z wieloma subskrybentami jest stosowany, gdy do jednej publikacji podłączonych jest wielu subskrybentów.

  Subskrypcja to żądanie kopii publikacji, która musi zostać dostarczona do subskrybenta. Subskrypcja służy do zdefiniowania danych publikacji, które muszą zostać odebrane, oraz miejsca i czasu ich odbioru. Istnieją dwa rodzaje subskrypcji:

  • Subskrypcja typu „push” : Zmienione dane są przymusowo przesyłane z dystrybutora do bazy danych subskrybenta. Nie jest wymagane żadne żądanie ze strony subskrybenta.
  • Subskrypcja typu „pull” : Zmodyfikowane dane w wydawcy są pobierane na żądanie subskrybenta. Agent działa po stronie subskrybenta.

  Baza danych subskrypcji jest bazą docelową w modelu replikacji MS SQL.

  

W modelu wielu wydawców – wielu subskrybentów wydawca może pełnić rolę subskrybenta na jednym z serwerów MS SQL. Należy unikać potencjalnych konfliktów aktualizacji podczas korzystania z tego modelu replikacji MS SQL Server.

Rodzaje replikacji serwera MS SQL

Replikacja serwera MS SQL to technologia służąca do kopiowania i synchronizacji danych między bazami danych w sposób ciągły lub regularny w zaplanowanych odstępach czasu. Jeśli chodzi o kierunek replikacji, replikacja serwera MS SQL może być jednokierunkowa, typu „jeden do wielu”, dwukierunkowa oraz typu „wiele do jednego”. Istnieją cztery rodzaje replikacji serwera MS SQL: replikacja migawkowa, replikacja transakcyjna, replikacja peer-to-peer oraz replikacja scalająca.

Replikacja migawkowa

Replikacja migawkowa służy do replikowania danych dokładnie w stanie, w jakim występują w momencie utworzenia migawki bazy danych. Ten rodzaj replikacji nadaje się do danych, które nie ulegają częstym zmianom, gdy posiadanie repliki bazy danych starszej od bazy głównej nie stanowi istotnego problemu lub gdy w krótkim czasie wprowadzana jest duża liczba zmian. W migawce nie stosuje się śledzenia zmian.
Na przykład migawkę można zastosować, gdy kursy walut lub cenniki są aktualizowane raz dziennie i muszą być dystrybuowane z serwera głównego do serwerów w oddziałach.

Replikacja transakcyjna

Replikacja transakcyjna to okresowa, zautomatyzowana replikacja, w ramach której dane są dystrybuowane z bazy danych głównej do repliki bazy danych w czasie rzeczywistym (lub prawie rzeczywistym). Replikacja transakcyjna jest bardziej złożona niż replikacja migawkowa. Replikowane są wszystkie przeprowadzone transakcje, a także końcowy stan bazy danych, co umożliwia monitorowanie całej historii transakcji na replice.
Na początku procesu replikacji transakcyjnej do subskrybenta stosowana jest migawka, a następnie dane są w sposób ciągły przesyłane z bazy danych głównej do repliki bazy danych w miarę wprowadzania zmian w tych danych. Replikacja transakcyjna jest szeroko stosowana jako replikacja jednokierunkowa.

Przykłady przypadków użycia replikacji transakcyjnej:

• Tworzenie serwera bazy danych z repliką bazy danych, która posłuży do Trybu failover w przypadku awarii głównego serwera bazy danych.
• Otrzymywanie raportów dotyczących operacji wykonywanych w oddziałach przy użyciu wielu wydawców w oddziałach i jednego subskrybenta w siedzibie głównej.
• Replikowanie zmian natychmiast po ich wystąpieniu.
• Dane w bazie źródłowej ulegają częstym zmianom.

Replikacja typu peer-to-peer

Replikacja typu peer-to-peer służy do jednoczesnego replikowania danych bazy danych do wielu subskrybentów. Ten typ replikacji MS SQL Server można stosować, gdy serwery baz danych są rozmieszczone na całym świecie. Zmiany można wprowadzać na dowolnym serwerze bazy danych. Zmiany są propagowane do wszystkich serwerów baz danych. Replikacja typu peer-to-peer może pomóc w horyzontalnym skalowaniu aplikacji korzystającej z bazy danych. Główna zasada działania opiera się na replikacji transakcyjnej.

Poniżej można zobaczyć, w jaki sposób replikacja typu peer-to-peer w MS SQL Server może być wykorzystywana między serwerami baz danych rozmieszczonymi na całym świecie.

Replikacja scalająca

Replikacja scalająca to rodzaj replikacji dwukierunkowej, stosowanej zazwyczaj w środowiskach typu serwer-klient do synchronizacji danych między serwerami baz danych, gdy nie można zapewnić ciągłego połączenia między nimi. Gdy między obydwoma serwerami baz danych zostanie nawiązane połączenie sieciowe, agenci replikacji scalającej wykrywają zmiany wprowadzone w obydwu bazach danych i modyfikują je w celu zsynchronizowania oraz zaktualizowania ich stanu. Replikacja scalająca jest podobna do replikacji transakcyjnej, jednak dane są replikowane zarówno z wydawcy do subskrybenta, jak i w drugą stronę.

Ten rodzaj replikacji baz danych jest najbardziej złożonym spośród wszystkich typów replikacji w MS SQL Server i jest rzadko stosowany. Na przykład replikacja scalająca może być wykorzystywana przez wiele sklepów równorzędnych, które współpracują ze wspólną hurtownią. Każdy sklep ma prawo do zmiany informacji w bazie danych hurtowni, a jednocześnie wszystkie sklepy muszą posiadać zaktualizowany stan swoich baz danych po wysyłce towarów lub dostawie zapasów do hurtowni. Replikacja scalająca może być stosowana w przypadkach, gdy zaktualizowane informacje muszą być dostępne jednocześnie dla głównej (lub centralnej) bazy danych oraz baz danych oddziałów.

Wymagania dotyczące replikacji serwera MS SQL

Należy otworzyć następujące porty dla ruchu przychodzącego:

• TCP 1433, 1434, 2383, 2382, 135, 80, 443
• UDP 1434

Przed zainstalowaniem serwera MS SQL należy skonfigurować zaporę systemu Windows i włączyć odpowiednie porty dla ruchu przychodzącego na każdym hoście. Hosty uczestniczące w replikacji MS SQL muszą rozpoznawać się nawzajem na podstawie nazwy hosta.
Przed skonfigurowaniem replikacji serwera MS SQL należy zainstalować następujące oprogramowanie dla serwera MS SQL:

• .NET Framework – zestaw bibliotek
• MS SQL Server – oprogramowanie serwera baz danych
• MS SQL Server Management Studio (SSMS) – oprogramowanie do zarządzania bazami danych MS SQL za pomocą GUI (graficznego interfejsu użytkownika).

UWAGA: W niniejszym wpisie do konfiguracji wykorzystano MS SQL Server 2016. Tę samą zasadę można zastosować do konfiguracji replikacji w nowszych wersjach SQL Server.
Należy pamiętać, że jeśli na pierwszym komputerze, na którym znajduje się źródłowa baza danych, zainstalowano MS SQL Server 2016, to aby baza danych działała poprawnie, na drugim komputerze również musi być zainstalowany MS SQL Server 2016. Na przykład, jeśli chcesz skonfigurować replikację transakcyjną MS SQL, możesz użyć drugiego serwera bazy danych (na którym skonfigurowano subskrybenta) w wersji nie różniącej się o więcej niż dwie wersje od serwera bazy danych źródłowej, na którym skonfigurowano wydawcę. Jeśli wersja wydawcy na serwerze MS SQL Server to 2016, dystrybutor można skonfigurować w wersjach 2016, 2017, 2019 i 2022, a subskrybenta — na serwerach MS SQL Server 2012, 2014, 2016, 2017 i 2019. Wersja dystrybutora nie może być niższa niż wersja wydawcy. Replikacja nie będzie działać, jeśli na przykład na drugim komputerze zainstalujesz MS SQL Server 2008.

Podstawowe zalecenia dotyczące replikacji baz danych MS SQL

Przed skonfigurowaniem środowiska dla MS SQL Server należy wziąć pod uwagę kilka czynników:

• Istnieją ograniczenia dotyczące pól tożsamości i wyzwalaczy.
• Publikacje mogą zawierać wyłącznie tabele z kluczem głównym.
• Zaleca się, aby w przypadku dużych baz danych nie stosować harmonogramowania tworzenia migawek, co pozwoli uniknąć zużycia znacznych zasobów obliczeniowych.
• Należy zachować ostrożność podczas zmiany danych w replice bazy danych znajdującej się u subskrybenta. Gdy nadchodzi transakcja modyfikująca dane, a dane te zostały już poddane edycji lub usunięte, replikacja może zostać wstrzymana do czasu rozwiązania tego problemu.

Konfiguracja środowiska

Podczas pierwszej konfiguracji replikacji MS SQL zaleca się wykonanie jej najpierw w środowisku testowym. Na przykład konfigurujemy replikację na serwerach SQL działających na maszynach wirtualnych. W tym samouczku do wyjaśnienia replikacji MS SQL Server wykorzystano dwa hosty z systemem Windows Server 2016 i MS SQL Server 2016.
Przyjrzyjmy się konfiguracji środowiska testowego wykorzystanego do napisania tego wpisu na blogu, aby lepiej zrozumieć konfigurację replikacji serwera MS SQL.
Host 1

• Adres IP: 192.168.101.101
• Nazwa hosta: MSSQL01
• Identyfikator instancji serwera MS SQL: MSSQLSERVER1

Host 2

• Adres IP: 192.168.101.102
• Nazwa hosta: MSSQL02
• Identyfikator instancji serwera MS SQL: MSSQLSERVER2

Oba komputery mają w konfiguracji dyski C: i D:.
Podczas instalacji serwera MS SQL można tymczasowo wyłączyć zaporę systemu Windows, aby przećwiczyć konfigurację replikacji serwera MS SQL. W tym wpisie na blogu nie omówiono sposobu instalacji serwera MS SQL Server, ponieważ niniejszy poradnik skupia się na konfiguracji replikacji serwera MS SQL Server. W tym przykładzie oba serwery MS SQL Server są zainstalowane bez dodatku PolyBase.
Po zakończeniu instalacji serwera MS SQL Server sprawdź, czy zainstalowano funkcje spełniające wymagania dotyczące replikacji serwera MS SQL Server. Należy pamiętać, że podczas instalacji serwera MS SQL Server należy wybrać usługi silnika bazy danych, takie jak replikacja serwera SQL Server i R-Services. W tym przykładzie używana jest domyślna ścieżka instalacji (C:Program FilesMicrosoft SQL Server).

Inne ustawienia:

• Tryb uwierzytelniania mieszanego (uwierzytelnianie systemu Windows i uwierzytelnianie serwera MS SQL Server)
• Katalog główny danych: D:MSSQL_Server
• Katalog bazy danych systemowej: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Katalog bazy danych użytkownika: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Katalog dziennika bazy danych użytkownika: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Katalog kopii zapasowych: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Po zainstalowaniu programu MS SQL Server 2016 i programu SQL Server Management Studio na komputerach można przygotować serwery MS SQL do replikacji baz danych.

Przygotowanie do replikacji serwera MS SQL

Przed rozpoczęciem replikacji baz danych należy skonfigurować serwery. W naszym przykładzie do obsługi agentów replikacji serwera MS SQL Server zostanie używane jedno konto systemu Windows.

1. Utwórz mssql użytkownika na obu serwerach i ustaw dla niego to samo hasło.
2. W tym przykładzie użytkownik mssql należy do następujących grup:
   • Administratorzy (administratorzy lokalni na komputerach lokalnych, a nie administratorzy domeny)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Można edytować użytkowników i grupy, naciskając Win+R , otwierając CMD i uruchamiając polecenie lusrmgr.msc .

Dwa komputery z systemem Windows Server użyte w tym przykładzie nie znajdują się w usłudze Active Directory. Jeśli korzystasz z usługi Active Directory, możesz utworzyć na kontrolerze domeny użytkownika mssql .

Łączenie się z serwerem MS SQL Server

1. Uruchom program SQL Server Management Studio.
2. Zaloguj się (patrz zrzut ekranu) jako sa przy użyciu uwierzytelniania serwera SQL Server.
   • MSSQL01MSSQLSERVER1 to nazwa hosta oraz nazwa instancji MS SQL na pierwszym serwerze.
   • MSSQL02MSSQLSERVER2 to nazwa hosta oraz nazwa instancji MS SQL na drugim serwerze.

   

W podobny sposób można połączyć się na drugim serwerze (MSSQL02) z drugą instancją serwera MS SQL (MSSQLSERVER2). Można również połączyć się z drugą instancją serwera MS SQL (MSSQLSERVER2) z poziomu pierwszego serwera MS SQL (MSSQL01), wprowadzając poświadczenia w programie SQL Server Management Studio. W jednej instancji programu SQL Server Management Studio można połączyć się z obiema instancjami serwera MS SQL (MSSQL01 i MSSQL02).
Aby to zrobić, w Eksploratorze obiektów kliknij Połącz > Silnik bazy danych . W tym samouczku połączymy się z serwerem MSSQLSERVER1 z serwera MSSQL01 oraz z serwerem MSSQLSERVER2 z serwera MSSQL02, korzystając z programu SQL Server Management Studio w celu skonfigurowania serwerów MS SQL.

Uruchamianie agenta

Po zalogowaniu się do instancji serwera MS SQL Server zauważysz, że agent nie jest uruchomiony. Domyślnie agent SQL Server nie uruchamia się automatycznie. Można uruchomić tę usługę ręcznie, ale lepiej skonfigurować ją tak, aby uruchamiała się automatycznie po uruchomieniu systemu Windows.

Aby skonfigurować usługę agenta tak, aby uruchamiała się automatycznie:

1. Naciśnij Win+R , uruchom cmd, i uruchom polecenie services.msc .
2. Otwórz właściwości usługi agenta serwera SQL i ustaw typ uruchamiania na automatyczny .

   

Konfiguracja użytkowników dla MS SQL Server

Po połączeniu się z instancją MSSQLSERVER1 w programie SQL Server Management Studio należy skonfigurować użytkowników:

1. Przejdź do Eksploratora obiektów i otwórz Zabezpieczenia > Loginy .
2. Kliknij prawym przyciskiem myszy Loginy i wybierz Nowy login . Wybierz Uwierzytelnianie systemu Windows .
3. W sekcji Ogólne wprowadź nazwę logowania mssql .
4. Kliknij Wyszukaj , następnie kliknij Sprawdź nazwy w celu potwierdzenia, a następnie kliknij OK dwukrotnie, aby zapisać ustawienia.

   

5. Teraz użytkownik MSSQL01mssql Windows został dodany do listy użytkowników, którzy mogą logować się do bazy danych (podobnie, dodaj użytkownika mssql do logins na drugiej maszynie MSSQL02 w SQL Server Management Studio).
6. Dodaj użytkownika mssql do ról serwera sysadmins w konfiguracji bezpieczeństwa Security bazy danych w SQL Server Management Studio.
7. Przejdź do MSSQL01MSSQLSERVER1 > Ról serwera , kliknij prawym przyciskiem myszy sysadmin i otwórz Właściwości .
8. Na stronie Członkowie kliknij Dodaj , wprowadź nazwę użytkownika mssql, i kliknij Sprawdź nazwy .
9. Zaznacz pole wyboru nazwy użytkownika MSSQL01mssql i kliknij OK .

   

10. Skonfiguruj to samo na swojej drugiej maszynie (w tym przypadku MSSQL02).
11. Uruchom ponownie obie maszyny.

    Teraz możesz logować się za pomocą uwierzytelniania Windows na obu serwerach.

    

Import bazy danych z kopii zapasowej

Importujmy przykładową bazę danych z kopii zapasowej, a następnie zreplikujmy bazę danych z pierwszej maszyny na drugą maszynę. Baza danych AdventureWorks2016 jest używana jako przykład w tym przypadku.

1. Skopiuj plik kopii zapasowej AdventureWorks2016.bak do katalogu kopii zapasowych MSSQL. W naszym przypadku ten katalog na pierwszym serwerze to D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Importuj przykładową bazę danych. Na pierwszej maszynie w SQL Server Management Studio, przejdź do MSSQL01MSSQLSERVER1 , kliknij prawym przyciskiem myszy Bazy danych, i wybierz Przywróć bazę danych z menu kontekstowego.

   

3. W oknie Przywróć bazę danych , wybierz potrzebne parametry:
   • Źródło: Urządzenie .
   • Kliknij na trzy kropki , aby przeglądać plik kopii zapasowej bazy danych.
     • W oknie Wybierz urządzenia kopii zapasowej , wybierz typ nośnika kopii zapasowej: plik .
     • Kliknij Dodaj .
   • Wybierz potrzebny plik .bak – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Kliknij OK , a następnie ponownie kliknij OK .
4. Baza danych AdventureWorks2016 została pomyślnie przywrócona.

   

Możesz zaimportować bazę danych z kopii zapasowej na drugiej maszynie, gdzie będzie działać replika bazy danych. To podejście pozwala zmniejszyć ruch sieciowy, ponieważ replikacja rozpocznie się od kopiowania zmian od momentu utworzenia kopii zapasowej, bez kopiowania całych danych bazy danych do pustej bazy danych.
Przywróć bazę danych z kopii zapasowej na drugim serwerze i zmień nazwę bazy danych na AdventureWorks2016r , gdzie „r” oznacza „replika”.
Ostatecznie, mamy:

Po zaimportowaniu bazy danych musisz przeprowadzić pewne dostrojenia, aby przygotować serwery MS SQL.

1. Na maszynie MSSQL01 , przejdź do MSSQL01MSSQLSERVER1 > Bezpieczeństwo > Loginy , wybierz MSSQL01mssql . Kliknij prawym przyciskiem (lub dwukrotnie kliknij) na użytkownika mssql i wybierz Właściwości .
2. W Rolach Serwera , zaznacz pole wyboru na roli dbcreator .

   

3. Na stronie Mapowanie Użytkownika , wybierz użytkowników przypisanych do tego loginu i zaznacz pole wyboru dla bazy danych AdventureWorks2016 (wybierz odpowiednio AdventureWorks2016r na drugim serwerze).
4. W sekcji członkostwa w roli bazy danych zaznacz pole wyboru db_owner .
   

   

Kliknij

5. OK , aby zapisać ustawienia.

Zrób tę samą konfigurację na maszynie MSSQL02. Następnie możesz skonfigurować komponenty MS SQL Server potrzebne do replikacji bazy danych.

Znajdź plan dostosowany do Twojego środowiska

Zapoznaj się z elastycznymi edycjami oprogramowania NAKIVO i modelami licencjonowania, które zapewniają ochronę danych na poziomie Enterprise w cenie dostosowanej do Twojego budżetu.

ZOBACZ CENY

Konfigurowanie replikacji bazy danych

Konfigurowanie replikacji w trybie graficznym to najwygodniejsza metoda. Następna konfiguracja jest wykonywana w Zarządzaniu SQL Server. W tym przykładzie omówiono replikację transakcyjną bazy danych, ponieważ jest to jeden z najczęściej używanych typów replikacji w MS SQL Server.
Widok na głównym serwerze baz danych (MSSQL01MSSQLSERVER1) oraz widok na drugim serwerze (MSSQL02MSSQLSERVER2) w Zarządzaniu SQL Server przedstawiono na poniższym zrzucie ekranu.

Konfigurowanie dystrybucji

Dystrybucja może być używana dla wielu wydawców i subskrybentów. W tym przykładzie dystrybucja jest skonfigurowana na głównym serwerze, na którym przechowywana jest baza źródłowa. Na głównym serwerze (MSSQL01MSSQLSERVER1) kliknij prawym przyciskiem Replikacja i w menu kontekstowym wybierz Konfiguruj Dystrybucję .

Otworzy się Kreator konfiguracji dystrybucji .

1. Dystrybutor . Wybierz bieżącą instancję bazy danych działającą na głównym serwerze (MSSQL01MSSQLSERVER1), aby pełniła rolę dystrybutora w tym przykładzie. Klikaj Dalej za każdym razem, aby przejść do kolejnego kroku w kreatorze.
2. Uruchamianie agenta SQL Server . Jeśli nie skonfigurowałeś agenta MS SQL Server do automatycznego startu, jak wyjaśniono wcześniej, pojawi się następujący komunikat. Wybierz Tak, skonfiguruj usługę agenta SQL Server do automatycznego uruchamiania .

   

3. Folder migawki . Możesz pozostawić domyślną ścieżkę tutaj. Migawka jest potrzebna do inicjalizacji replikacji. Upewnij się, że na dysku, gdzie znajduje się katalog migawki, jest wystarczająca ilość wolnego miejsca. Ilość wolnego miejsca musi być równoważna co najmniej rozmiarowi replikowanej bazy danych.
4. Baza danych dystrybucji . Wprowadź nazwę bazy danych dystrybucji. Możesz pozostawić domyślną nazwę ( dystrybucja ) i foldery dla plików bazy danych i pliku dziennika.

   

5. Wydawcy . Zdefiniuj wydawców replikacji MS SQL Server, którzy mają dostęp do dystrybutora. Zaznacz pole wyboru obok nazwy bazy danych dystrybucji na głównym serwerze MS SQL Server (który hostuje bazę źródłową, która będzie replikowana). W tym przykładzie jest to instancja MSSQL01MSSQLSERVER1, a nazwa bazy danych dystrybucji to dystrybucja .
6. Działania kreatora . Wybierz Konfiguracja dystrybucji aby skonfigurować dystrybucję podczas ostatniego kroku kreatora. W tym przykładzie nie wygenerujemy pliku skryptu do wykonania później.

   

7. Zakończ kreatora . Sprawdź Podsumowanie konfiguracji dystrybucji i kliknij Zakończ aby utworzyć Dystrybutora.

   

8. Status Powodzenie powinien się pojawić, jeśli Dystrybutor został utworzony i skonfigurowany pomyślnie.

   

Jeśli zobaczysz, że wystąpił błąd podczas konfigurowania automatycznego uruchamiania SQL Server Agent, przejdź do konfiguracji usług i sprawdź tryb uruchamiania SQL Server Agent (zobacz, jak skonfigurować Agent Start powyżej w tym wpisie na blogu).
Możesz także otworzyć właściwości SQL Server Agent w SQL Server Management Studio i sprawdzić stan usługi oraz opcje ponownego uruchamiania. Kliknij prawym przyciskiem SQL Server Agent na końcu listy w Eksplorator Obiektów i wybierz Właściwości aby wyświetlić lub edytować właściwości agenta.

Konfiguracja Wydawcy

Po skonfigurowaniu Dystrybucji można skonfigurować Wydawcę. Wydawca powinien być skonfigurowany na głównym serwerze (MSSQL01MSSQLSERVER1), gdzie przechowywana jest główna baza danych do replikacji. Wybierz Replikacja , kliknij prawym przyciskiem Lokalne Publikacje i w menu kontekstowym wybierz Nowa Publikacja .

Otwiera się Kreator Nowej Publikacji .

1. Baza danych publikacji . Wybierz bazę danych, którą chcesz replikować ( AdventureWorks2016 w tym przypadku). Kliknij Dalej na każdym etapie kreatora, aby kontynuować.

   

2. Typ publikacji . Dla tego kroku możesz wybrać typy replikacji MS SQL Server dla bazy danych. Wybierzmy publikację transakcyjną, która jest powszechnie używanym typem replikacji.
3. Artykuły . Wybierz potrzebne obiekty, takie jak tabele, procedury, widoki, widoki z indeksami i funkcje zdefiniowane przez użytkownika do publikowania jako artykuły. Możliwe jest wybranie replikacji pola niestandardowego w tabelach i wybór właściwości artykułów, jeśli to konieczne. W tym przykładzie niektóre tabele są wybrane.

   

4. Filtrowanie wierszy tabeli . W tym przykładzie nie dodano filtrów (jest to domyślna konfiguracja filtrów). Możesz dodać filtry, jeśli to konieczne.
5. Agent Migawki . Określ, kiedy uruchomić Agenta Migawki. Skonfigurujmy Agenta, aby działał natychmiast. Wybierz Utwórz migawkę natychmiast i zachowaj migawkę dostępne do inicjowania subskrypcji .

   

6. Bezpieczeństwo Agenta . Wybierz Użyj ustawień bezpieczeństwa z Agenta Migawki . Kliknij przycisk Ustawienia bezpieczeństwa , aby wybrać konto, na którym Agent będzie działał.

   W oknie Bezpieczeństwo Agenta Migawki , które się otworzy, wprowadź poświadczenia użytkownika Windows mssql , który został stworzony wcześniej. Wybierz połączenie z Wydawcą Podszywając się pod konto procesowe . Kliknij OK, aby zapisać ustawienia i wrócić do kreatora.

   

   Po zdefiniowaniu potrzebnego użytkownika, zobaczysz tego użytkownika w sekcjach Agent Migawki oraz Agent Odczytu Dziennika .

   

7. Działania kreatora . Wybierz górne pole wyboru, aby utworzyć publikację podczas ostatniego kroku kreatora.
8. Ukończ kreatora . Sprawdź konfigurację publikacji i kliknij Zakończ , aby utworzyć nową publikację.

   

W oknie Tworzenie Publikacji , możesz monitorować postęp tworzenia nowej publikacji. Poczekaj chwilę, a jeśli wszystko zostanie wykonane poprawnie, powinien pojawić się status sukcesu.

Publikacja została teraz utworzona i możesz zobaczyć publikację w Eksploratorze Obiektów, przechodząc do Replikacji > Publikacje Lokalne .

Konfiguracja Subskrybenta

Jak pamiętasz, replika serwera MS SQL może być albo repliką pobierającą, albo wysyłającą. Jeśli konfigurujesz replikację wysyłającą, powinieneś skonfigurować Subskrybenta do uruchamiania agentów na głównym serwerze bazy danych (MSSQL01 w tym przypadku). Jeśli konfigurujesz replikację pobierającą, Subskrybent musi być skonfigurowany do uruchamiania agentów na drugiej maszynie (MSSQL02), to jest na maszynie, na której zostanie utworzona replika bazy danych.
Skonfigurujmy replikację wysyłającą i utwórzmy nową subskrypcję na pierwszym serwerze MS SQL (MSSQL01MSSQLSERVER1), gdzie znajduje się baza danych główna.
W Eksploratorze Obiektów przejdź do Replikacji , kliknij prawym przyciskiem Subskrypcje Lokalne i w menu kontekstowym wybierz Nowe Subskrypcje .

Otwiera się Kreator Nowej Subskrypcji .

1. Publikacja . Wybierz publikację, dla której chcesz utworzyć nową subskrypcję. W naszym przykładzie nazwa Wydawcy to MSSQL01MSSQLSERVER1, a nazwa publikacji (utworzona wcześniej) to AdvWorks_Pub . Kliknij Dalej na każdym kroku kreatora, aby kontynuować.
2. Lokalizacja Agenta Dystrybucji . Wybierz typ replikacji, zaznaczając subskrypcję push lub pull. W naszym przykładzie chcemy, aby wszystkie agenty działały po stronie serwera źródłowego, dlatego też wybrano pierwszą opcję, aby utworzyć subskrypcję push. Dzięki temu można centralnie zarządzać replikacją MS SQL Server.

   

3. Subskrybenci . Domyślnie, serwer, na którym uruchamiasz kreatora (w tym przypadku MSSQL01MSSQLSERVER1), jest wyświetlany jako Subskrybent, a baza danych subskrypcji nie jest zdefiniowana. Dodajmy nowego subskrybenta i wybierzmy bazę danych subskrypcji znajdującą się na drugim serwerze baz danych (MSSQL01MSSQLSERVER2). Kliknij Dodaj Subskrybenta i w menu kontekstowym wybierz Dodaj Subskrybenta SQL Server .
   • W oknie, które się pojawi, wprowadź dane uwierzytelniające dla drugiej instancji serwera MSSQL (w naszym przypadku MSSQL01MSSQLSERVER2) i kliknij Połącz .

     

   • Zaznacz pole wyboru swojego drugiego serwera, na którym zostanie przechowywana replika bazy danych (MSSQL02MSSQLSERVER2), a następnie w rozwijanym menu Baza Danych Subskrypcji wybierz nową lub istniejącą bazę danych odtworzoną z kopii zapasowej do użycia jako replika bazy danych.

     W naszym przykładzie AdventureWorks2016r została utworzona na drugim serwerze poprzez przywrócenie z kopii zapasowej głównej (źródłowej) bazy danych AdventureWorks2016 , aby rozpocząć replikację. Replikacja zostaje rozpoczęta poprzez replikowanie tylko nowych danych, bez kopiowania całej bazy danych po rozpoczęciu procesu replikacji. Zatem AdventureWorks2016r jest wybrana jako baza danych subskrypcji w obecnym przykładzie.

     

4. Bezpieczeństwo Agenta Dystrybucji . Kliknij przycisk z trzema kropkami (…) i wybierz użytkownika oraz inne opcje bezpieczeństwa dla Agenta Dystrybucji.

   W otwartym oknie Bezpieczeństwo Agenta Dystrybucji , ustaw Agenta Dystrybucji do uruchamiania się na hoście MSSQL01 pod kontem użytkownika mssql . Wprowadź hasło do konta Windows mssql . Wybierz Połącz się z dystrybutorem, podszywając się pod konto procesowe i wybierz Połącz się z subskrybentem, podszywając się pod konto procesowe . Kliknij OK , aby zapisać ustawienia.

   

   Teraz właściwości subskrypcji są skonfigurowane.

   

5. Harmonogram synchronizacji . Wybierz agenta znajdującego się na dystrybutorze, aby działał w trybie ciągłym dla bieżącego subskrybenta.
6. Inicjalizacja subskrypcji . Zaznacz pole wyboru Zainicjuj i w menu rozwijanym wybierz opcję Natychmiast jako moment zainicjowania subskrypcji. W razie potrzeby możesz również wybrać opcję Zoptymalizowana pod kątem pamięci .

   

7. Czynności kreatora . Zaznacz górne pole wyboru, aby utworzyć subskrypcję (lub subskrypcje) na końcu kreatora.
8. Zakończ kreatora . Możesz sprawdzić ustawienia subskrypcji i kliknąć Zakończ , aby utworzyć subskrypcję.

   

9. Poczekaj, aż subskrypcja zostanie utworzona. Jeśli zobaczysz status Powodzenie , oznacza to, że subskrypcja została pomyślnie utworzona.

   

10. Po skonfigurowaniu replikacji w programie SQL Server w Eksploratorze obiektów wyświetlane są trzy zadania, które można wyświetlić, przechodząc do Agent programu SQL Server > Zadania .

    

Finalizacja konfiguracji replikacji

Po skonfigurowaniu dystrybutora, wydawcy i subskrybenta można sprawdzić stan replikacji serwera MS SQL Server.

1. Na pierwszym serwerze (MSSQL01MSSQLSERVER1) uruchom monitor replikacji, aby sprawdzić stan replikacji serwera MS SQL Server. W programie SQL Server Management Studio wybierz instancję serwera MS SQL Server (MSSQLSERVER1), przejdź do Replikacja , kliknij prawym przyciskiem myszy Publikacje lokalne i w menu kontekstowym wybierz Uruchom monitor replikacji .

   

2. W naszym przypadku występuje błąd Agenta odczytu dziennika . Aby wyświetlić szczegóły błędu, wybierz bazę danych źródłową (wydawcę) w lewym panelu, wybierz kartę Agenci w prawym panelu, a następnie kliknij dwukrotnie nazwę błędu.

   

3. W oknie, które się otworzy, możesz zobaczyć historię agenta i komunikaty o błędach. Komunikaty o błędach są następujące:
   • Nie udało się wykonać procedury sp_replcmds na serwerze MSSQL01MSSQLSERVER1. Źródło: MSSQL_REPL. Numer błędu: MSSQL_REPL20011).
   • Nie można wykonać jako główny użytkownik bazy danych, ponieważ główny użytkownik “dbo” nie istnieje, tego typu główny użytkownik nie może być maskowany lub nie masz uprawnień. (Źródło: MSSQLServer, numer błędu: 15517).

   

   Druga wiadomość o błędzie sugeruje, że brakuje jakiegoś rodzaju uprawnień. Naprawmy ten błąd.

4. Utwórz nowe zapytanie w MS SQL Studio Zarządzania i wykonaj to zapytanie. W głównym oknie kliknij przycisk Nowe Zapytanie .
5. W sekcji zapytań SQL głównego okna wprowadź następujące zapytanie:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Kliknij przycisk Wykonaj .

   

   Polecenie zakończono pomyślnie.

6. Następnie przejdź do MSSQL01MSSQLSERVER1 > Replikacja > Lokalne Publikacje > [AdventureWorks2016]: AdvWorks_Pub . Kliknij prawym przyciskiem myszy nazwę publikacji i w menu kontekstowym wybierz Zobacz Status Agenta Migawki . Możesz kliknąć Działanie > Odśwież aby odświeżyć status i Ponownie Zainicjuj Wszystkie Subskrypcje aby zastosować migawkę do każdego Subskrybenta.

   Teraz wszystko jest rozwiązane, brak jest wyświetlanych błędów, a replikacja MS SQL Server powinna działać.

   

Sprawdzenie Jak Działa Replikacja

Zobaczmy replikację serwera MS SQL w akcji. Zobacz zawartość tabeli bazy danych AdventureWorks2016 przechowywanej na pierwszym serwerze MS SQL ( MSSQL01MSQLSERVER1 ). W naszym przykładzie wybierzemy wszystkie dane z tabeli Person.AddressType . Aby to zrobić, wykonaj zapytanie:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
Wynik wykonania zapytania jest wyświetlony na zrzucie ekranu poniżej:

Wykonaj podobne zapytanie na drugim serwerze aby wyświetlić wszystkie dane tabeli Person.AddressType bazy danych AdventureWorks2016r przechowywanej na MSSQL02MSSQLSERVER2.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Jeśli porównasz zrzuty ekranu powyżej i poniżej, zawartość Person.AddressType jest identyczna w obu bazach danych (baza źródłowa na pierwszym serwerze oraz docelowa baza danych, która jest repliką na drugim serwerze).

Usuńmy jeden wiersz z tabeli PersonAddressType z bazy danych AdventureWorks2016 (źródło) na pierwszym serwerze (MSSQL01MSSQLSERVER1). Uruchom zapytanie, aby usunąć wiersz zawierający „Billing” w nazwie, a następnie wyświetlić zawartość tabeli po tej operacji:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Jak widać, pierwszy wiersz z AddressTypeID równym 1 i nazwą „Billing” został usunięty z tabeli Person.AddressType w bazie danych AdventureWorks2016 na komputerze MSSQL01 .
Replikacja transakcyjna działa. Sprawdźmy zawartość tabeli Person.AddressType w bazie danych AdventureWorks2016r na serwerze MSSQL02 . Wykonaj ponownie zapytanie podobne do powyższego, aby wyświetlić zawartość tabeli:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
W wyniku replikacji pierwszy wiersz został również usunięty z tabeli Person.AddressType w bazie danych pomocniczej, która pełni rolę repliki bazy danych ( AdventureWorks2016r ). Wyniki można zobaczyć na poniższym zrzucie ekranu.

Replikacja bazy danych w programie SQL Server działa poprawnie.

Wnioski

Istnieją cztery rodzaje replikacji w programie MS SQL Server — migawka, transakcyjna, peer-to-peer oraz scalająca. Ponieważ replikacja transakcyjna jest powszechnie stosowana, w tym wpisie na blogu skonfigurowaliśmy właśnie ten rodzaj replikacji w programie MS SQL Server. Aby replikacja bazy danych działała, należy skonfigurować dystrybutora, wydawcę i subskrybenta. Subskrybenta można skonfigurować na serwerze źródłowym (replikacja typu push) oraz na serwerze docelowym (replikacja typu pull).
Należy jednak rozważyć zastosowanie zarówno replikacji, jak i kopia zapasowa baz danych MS SQL w celu zwiększenia szans na pomyślne Odzyskiwanie danych z bazy danych.

Wypróbuj NAKIVO Backup & Replication

Skorzystaj z bezpłatnej wersji próbnej, aby zapoznać się ze wszystkimi funkcjami rozwiązania w zakresie ochrony danych. 15 dni za darmo. Bez żadnych ograniczeń dotyczących funkcji ani pojemności. Nie jest wymagana karta kredytowa.

WYPRÓBUJ ZA DARMO