Jak skonfigurować replikację w 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 wykonywanie 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 do wykonywania kopii zapasowych systemu 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 pozwala na skrócenie czasu przywrócenia działalności (RTO) i zapewnienie maksymalnej dostępnoś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 spójność i integralność danych.

Terminologia stosowana w replikacji MS SQL Server

Zanim zagłębimy się w konfigurację i ustawienia replikacji MS SQL Server, omówmy najpierw pokrótce główne terminy i modele replikacji.

Articles to podstawowe jednostki przeznaczone do replikacji, takie jak tabele, procedury, funkcje i widoki. Artykuły można skalować w pionie lub w poziomie za pomocą filtrów. Dla tego samego obiektu można utworzyć wiele artykułów.

A publication to logiczny zbiór artykułów. Jest to ostateczny zestaw elementów z bazy danych przeznaczonych do replikacji.

A filter to zestaw warunków dla artykułu. Replikacja MS SQL Server pozwala na użycie filtrów i wybór niestandardowych elementów do replikacji, co w rezultacie zmniejsza ruch, nadmiarowość oraz ilość danych przechowywanych w replice bazy danych. Na przykład można wybrać tylko najbardziej krytyczne tabele i pola za pomocą filtrów, a następnie replikować tylko te dane.

Agents to komponenty MS SQL Server, które mogą działać jako usługi w tle dla systemów zarządzania relacyjnymi bazami danych i są używane do planowania automatycznego wykonywania zadań, takich jak wykonywanie kopii zapasowej i replikacja baz danych MS SQL. Istnieje pięć typów agentów: Snapshot Agent, Log Reader Agent, Distribution Agent, Merge Agent oraz Queue Reader Agent.

Metadata to dane używane do opisania elementów bazy danych. Istnieje szeroki zakres wbudowanych funkcji metadanych, które pozwalają na zwracanie informacji o instancji MS SQL Server, instancjach baz danych oraz elementach bazy danych.

Role w replikacji bazy danych SQL

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

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

  Baza danych dystrybutora może być traktowana jednocześnie jako wydawca i dystrybutor. 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ą korzystać 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.

• A Publisher 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 mieć więcej niż jedną publikację.
• A Subscriber to baza danych, która odbiera replikowane dane z publikacji. Jeden subskrybent może odbierać dane od więcej niż jednego wydawcy i publikacji. Model z jednym subskrybentem jest używany, gdy istnieje jeden subskrybent. Model z wieloma subskrybentami jest używany, gdy wielu subskrybentów jest podłączonych do jednej publikacji.

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

  • Push subscription: Zmienione dane są przesyłane w sposób wymuszony z dystrybutora do bazy danych subskrybenta. Nie jest wymagane żądanie ze strony subskrybenta.
  • Pull subscription: Zmienione dane w wydawcy są żądane przez 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 MS SQL Server

Replikacja MS SQL Server 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 MS SQL Server może być jednokierunkowa, typu jeden do wielu, dwukierunkowa oraz typu wiele do jednego. Istnieją cztery typy replikacji MS SQL Server: replikacja migawkowa, replikacja transakcyjna, replikacja peer-to-peer oraz replikacja scalająca.

Replikacja migawkowa

Snapshot replication 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 wprowadza się dużą liczbę zmian. W replikacji migawkowej nie stosuje się śledzenia zmian.

Na przykład replikację migawkową można wykorzystać, 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

Transactional replication to okresowa, zautomatyzowana replikacja, w 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ż migawka. Replikowane są wszystkie wykonane 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ą stale 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.

Przypadki użycia replikacji transakcyjnej:

• Tworzenie serwera bazy danych z repliką bazy danych do wykorzystania w Trybie 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 peer-to-peer

Peer-to-peer replication służy do jednoczesnego replikowania danych bazy danych do wielu subskrybentów. Ten typ replikacji MS SQL Server może być stosowany, 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 peer-to-peer może pomóc w skalowaniu aplikacji korzystającej z bazy danych. Główna zasada działania opiera się na replikacji transakcyjnej.

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

Replikacja scalająca

Merge replication to rodzaj replikacji dwukierunkowej, stosowanej zazwyczaj w środowiskach typu serwer-klient do synchronizacji danych między serwerami baz danych, gdy nie ma możliwości zapewnienia ciągłego połączenia między nimi. Gdy nawiązane zostanie połączenie sieciowe między obydwoma serwerami baz danych, agenci replikacji scalającej wykrywają zmiany wprowadzone w obydwu bazach danych i modyfikują je w celu zsynchronizowania i aktualizacji ich stanu. Replikacja scalająca jest podobna do replikacji transakcyjnej, ale dane są replikowane od wydawcy do subskrybenta i w drugą stronę.

Ten rodzaj replikacji baz danych jest najbardziej złożonym ze wszystkich typów replikacji MS SQL Server i jest rzadko stosowany. Na przykład replikacja scalająca może być używana przez wiele sklepów równorzędnych, które współpracują ze wspólnym magazynem. Każdy sklep ma prawo do zmiany informacji w bazie danych magazynu, a jednocześnie wszystkie sklepy muszą posiadać zaktualizowany stan swoich baz danych po wysyłce towarów lub dostawie zapasów do magazynu. 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 MS SQL Server

Następujące porty muszą być otwarte dla ruchu przychodzącego:

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

Przed instalacją MS SQL Server 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 za pomocą nazwy hosta.

Przed skonfigurowaniem replikacji MS SQL Server należy zainstalować następujące oprogramowanie dla MS SQL Server:

• .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 z GUI (graficznym interfejsem użytkownika).

UWAGA: W tym poście do konfiguracji używamy MS SQL Server 2016. Tę samą zasadę można zastosować do konfiguracji replikacji w nowszych wersjach SQL Server.

Pamiętaj, że jeśli zainstalujesz MS SQL Server 2016 na pierwszym komputerze, na którym znajduje się źródłowa baza danych, to na drugim komputerze też musisz zainstalować MS SQL Server 2016, żeby baza działała poprawnie. 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że być skonfigurowany w wersjach 2016, 2017, 2019 i 2022, a subskrybent może być skonfigurowany 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 zainstalujesz MS SQL Server 2008 na drugim komputerze.

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ć tylko tabele z kluczem głównym.
• Zaleca się, aby nie używać planowania tworzenia migawek w przypadku dużych baz danych, aby uniknąć zużycia dużej ilości 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 poddane edycji lub usunięte, replikacja może zostać zatrzymana 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 MS SQL Server.

Host 1

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

Host 2

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

Oba komputery mają w konfiguracji dyski C: i D:.

Podczas instalacji MS SQL Server można tymczasowo wyłączyć zaporę systemu Windows, aby przećwiczyć konfigurację replikacji MS SQL Server. W tym wpisie na blogu nie omawiamy sposobu instalacji programu MS SQL Server, ponieważ niniejszy poradnik skupia się na konfiguracji replikacji w MS SQL Server. W tym przykładzie oba serwery MS SQL Server są zainstalowane bez dodatku PolyBase.

Po zakończeniu instalacji programu MS SQL Server sprawdź, czy zainstalowano funkcje wymagane do replikacji w MS SQL Server. Należy pamiętać, że podczas instalacji programu MS SQL Server należy zaznaczyć usługi silnika bazy danych, takie jak replikacja 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 MS SQL Server 2016 i SQL Server Management Studio na komputerach można przygotować serwery MS SQL do replikacji bazy danych.

Przygotowanie do replikacji MS SQL Server

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

1. Utwórz użytkownika mssql na obu serwerach i ustaw to samo hasło.
2. W tym przykładzie użytkownik mssql jest członkiem następujących grup:
   • Administratorzy (lokalni administratorzy na komputerach lokalnych, a nie administratorzy domeny)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Możesz edytować użytkowników i grupy, naciskając Win+R, otwierając CMDi 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ć użytkownika mssql na kontrolerze domeny.

Łą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 i nazwa instancji MS SQL na pierwszym serwerze.
   • MSSQL02MSSQLSERVER2 to nazwa hosta i nazwa instancji MS SQL na drugim serwerze.

   

Podobnie 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 pierwszego serwera MS SQL (MSSQL01), wprowadzając poświadczenia w programie SQL Server Management Studio. Można połączyć się z obiema instancjami serwera MS SQL (MSSQL01 i MSSQL02) w jednej instancji programu SQL Server Management Studio.

Aby to zrobić, w Eksploratorze obiektów kliknij Connect > Database engine. W tym samouczku połączymy się z MSSQLSERVER1 z MSSQL01 oraz z MSSQLSERVER2 z MSSQL02, używając programu SQL Server Management Studio do konfiguracji serwerów MS SQL.

Uruchamianie agenta

Po zalogowaniu się do instancji serwera MS SQL Server zobaczysz, że agent nie jest uruchomiony. Domyślnie agent SQL Server nie uruchamia się automatycznie. Można uruchomić tę usługę ręcznie, ale lepiej jest 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 SQL Server Agent i ustaw typ uruchamiania S na Automatic.

   

Konfigurowanie użytkowników dla MS SQL Server

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

1. Przejdź do Object Explorer i otwórz Security > Logins.
2. Kliknij prawym przyciskiem myszy Logins i wybierz New Login. Wybierz Windows authentication.
3. Wprowadź nazwę logowania mssql w sekcji General .
4. Kliknij Search, następnie naciśnij Check names w celu potwierdzenia i kliknij OK dwukrotnie, aby zapisać ustawienia.

   

5. Teraz MSSQL01mssql użytkownik systemu Windows został dodany do listy użytkowników, którzy mogą logować się do bazy danych (podobnie dodaj mssql użytkownika do logins na drugim komputerze MSSQL02 w programie SQL Server Management Studio).
6. Dodaj użytkownika mssql do ról serwera sysadmins w konfiguracji Security bazy danych w programie SQL Server Management Studio.
7. Przejdź do MSSQL01MSSQLSERVER1 > Server roles, kliknij prawym przyciskiem myszy sysadmini otwórz Properties.
8. Na stronie Members kliknij Add, wprowadź nazwę użytkownika mssql, i kliknij Check names.
9. Zaznacz pole wyboru przy nazwie użytkownika MSSQL01mssql i kliknij OK.

   

10. Wykonaj tę samą konfigurację na drugim komputerze (w tym przypadku MSSQL02).
11. Uruchom ponownie oba komputery.

    Teraz możesz zalogować się przy użyciu uwierzytelniania systemu Windows na obu serwerach.

    

Importowanie bazy danych z kopii zapasowej

Zaimportujmy przykładową bazę danych z kopii zapasowej, a następnie zreplikujmy ją z pierwszego komputera na drugi. W tym przykładzie jako przykładową bazę danych wykorzystano bazę danych AdventureWorks2016 .

1. Skopiuj plik kopii zapasowej bazy danych AdventureWorks2016.bak do katalogu kopii zapasowych MSSQL. W naszym przypadku na pierwszym serwerze jest to katalog D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Zaimportuj przykładową bazę danych. Na pierwszym komputerze w programie SQL Server Management Studio przejdź do MSSQL01MSSQLSERVER1 , kliknij prawym przyciskiem myszy Databases, i wybierz Restore Database z menu kontekstowego.

   

3. W oknie Restore Database wybierz potrzebne parametry:
   • Źródło: Device.
   • Kliknij przycisk three dots , aby przejrzeć pliki kopii zapasowej bazy danych.
     • W oknie Select Backup Devices wybierz typ nośnika kopii zapasowej: file.
     • Kliknij przycisk Add.
   • Wybierz potrzebny .bak plik – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Kliknij OK, a następnie ponownie OK .
4. Baza danych AdventureWorks2016 została pomyślnie przywrócona.

   

Można zaimportować bazę danych z kopii zapasowej na drugim komputerze, na którym będzie działać replika bazy danych. Takie podejście pozwala zmniejszyć ruch sieciowy, ponieważ replikacja rozpocznie się od skopiowania zmian, które nastąpiły od momentu wykonania kopii zapasowej, bez kopiowania wszystkich danych bazy do pustej bazy danych.

Przywróć bazę danych z kopii zapasowej na drugim serwerze i zmień nazwę bazy danych na AdventureWorks2016r , gdzie „r” oznacza „replika”.

W końcu mamy:

Po zaimportowaniu bazy danych należy przeprowadzić pewne działania optymalizacyjne w celu przygotowania serwerów MS SQL

1. Na komputerze MSSQL01 przejdź do MSSQL01MSSQLSERVER1 > Security > Loginsi wybierz MSSQL01mssql. Kliknij prawym przyciskiem myszy (lub dwukrotnie) mssql użytkownika i wybierz Properties.
2. W Rolach serwera zaznacz pole wyboru obok roli dbcreator .

   

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

   

5. Kliknij OK , aby zapisać ustawienia.

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

Konfiguracja replikacji bazy danych

Konfiguracja replikacji w trybie graficznym jest najwygodniejszą metodą. Kolejna konfiguracja jest wykonywana w programie SQL Server Management Studio. W tym przykładzie wyjaśniono transakcyjną replikację bazy danych, ponieważ jest to jeden z najczęściej używanych typów replikacji MS SQL Server.

Widok na głównym serwerze bazy danych (MSSQL01MSSQLSERVER1) oraz widok na drugim serwerze (MSSQL02MSSQLSERVER2) w programie SQL Server Management Studio przedstawiono na poniższym zrzucie ekranu.

Konfigurowanie dystrybucji

Dystrybucji można używać dla wielu wydawców i subskrybentów. W tym przykładzie dystrybucja jest skonfigurowana na głównym serwerze, na którym przechowywana jest źródłowa baza danych. Na głównym serwerze (MSSQL01MSSQLSERVER1) kliknij prawym przyciskiem myszy Replication i w menu kontekstowym wybierz Configure Distribution.

Otworzy się Kreator konfiguracji dystrybucji .

1. Distributor. Wybierz bieżącą instancję bazy danych działającą na głównym serwerze (MSSQL01MSSQLSERVER1), która w tym przykładzie będzie pełnić rolę dystrybutora. Kliknij Next za każdym razem, aby przejść do następnego kroku kreatora.
2. SQL Server Agent Start. Jeśli nie skonfigurowałeś agenta MS SQL Server do automatycznego uruchamiania, jak wyjaśniono powyżej, wyświetli się następujący komunikat. Wybierz Yes, configure the SQL Server Agent service to start automatically.

   

3. Snapshot Folder. W tym miejscu możesz pozostawić domyślną ścieżkę. Do zainicjowania replikacji potrzebna jest migawka. Upewnij się, że na dysku, na którym znajduje się katalog migawek, jest wystarczająca ilość wolnego miejsca. Ilość wolnego miejsca musi odpowiadać co najmniej rozmiarowi replikowanej bazy danych.
4. Distribution Database. Wprowadź nazwę bazy danych dystrybucyjnej. Można pozostawić domyślną nazwę ( distribution ) oraz foldery dla pliku bazy danych dystrybucyjnej i pliku dziennika.

   

5. Publishers. Zdefiniuj wydawców replikacji serwera MS SQL, którzy mogą uzyskać dostęp do dystrybutora. Zaznacz pole wyboru obok nazwy bazy danych dystrybucyjnej w głównej instancji serwera MS SQL (na której znajduje się baza danych źródłowa przeznaczona do replikacji). W tym przykładzie jest to instancja MSSQL01MSSQLSERVER1, a nazwa bazy danych dystrybucji to distribution .
6. Wizard Actions. Wybierz Configure distribution , aby skonfigurować dystrybucję podczas ostatniego kroku kreatora. W tym przykładzie nie będziemy generować pliku skryptu do późniejszego wykonania.

   

7. Complete the Wizard. Sprawdź podsumowanie konfiguracji dystrybucji i kliknij Finish , aby utworzyć dystrybutora.

   

8. Jeśli dystrybutor został pomyślnie utworzony i skonfigurowany, powinien pojawić się status Success .

   

Jeśli zauważysz, że wystąpił błąd podczas konfigurowania automatycznego uruchamiania agenta SQL Server, przejdź do konfiguracji usług i sprawdź tryb uruchamiania agenta SQL Server (zobacz, jak skonfigurować uruchamianie agenta powyżej w tym wpisie na blogu).

Możesz również otworzyć właściwości agenta SQL Server w programie SQL Server Management Studio i sprawdzić stan usługi oraz opcje ponownego uruchamiania. Kliknij prawym przyciskiem myszy SQL Server Agent na końcu listy w Object Explorer i wybierz Properties , aby wyświetlić lub edytować właściwości agenta.

Konfiguracja wydawcy

Po skonfigurowaniu dystrybucji można skonfigurować wydawcę. Wydawca powinien być skonfigurowany na serwerze głównym (MSSQL01MSSQLSERVER1), na którym przechowywana jest baza danych master przeznaczona do replikacji. Wybierz Replication, kliknij prawym przyciskiem myszy Local Publications i w menu kontekstowym wybierz New Publication.

Otworzy się Kreator nowej publikacji .

1. Publication Database. Wybierz bazę danych, którą chcesz replikować ( AdventureWorks2016 w tym przypadku). Kliknij Next na każdym kroku kreatora, aby kontynuować.

   

2. Publication Type. W tym kroku możesz wybrać typy replikacji MS SQL Server dla bazy danych. Wybierzmy publikację transakcyjną, która jest powszechnie stosowanym typem replikacji.
3. Articles. Wybierz potrzebne obiekty, takie jak tabele, procedury, widoki, widoki indeksowane i funkcje zdefiniowane przez użytkownika, które mają zostać opublikowane jako artykuły. W razie potrzeby można wybrać replikację pól niestandardowych w tabelach oraz określić właściwości artykułów. W tym przykładzie wybrano kilka tabel.

   

4. Filter Table Rows. W tym przykładzie nie dodano żadnych filtrów (jest to domyślna konfiguracja filtrów). W razie potrzeby można dodać filtry.
5. Snapshot Agent. Określ, kiedy ma być uruchamiany agent migawki. Skonfigurujmy agenta tak, aby uruchamiał się natychmiast. Wybierz Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

   

6. Agent Security. Wybierz Use the security settings from the Snapshot Agent. Kliknij przycisk Security Settings , aby wybrać konto, pod którym będzie działał agent.

   W oknie Snapshot Agent Security , które się otworzy, wprowadź poświadczenia użytkownika systemu Windows mssql , którego utworzyłeś wcześniej. Wybierz opcję połączenia z wydawcą By impersonating the process account. Kliknij OK, aby zapisać ustawienia i powrócić do kreatora.

   

   Po zdefiniowaniu wymaganego użytkownika, możesz go zobaczyć w sekcjach Migawka Agent oraz Log Reader Agent .

   

7. Wizard Actions. Zaznacz górne pole wyboru, aby utworzyć publikację podczas ostatniego kroku kreatora.
8. Complete the Wizard. Sprawdź konfigurację publikacji i kliknij Finish , aby utworzyć nową publikację.

   

W oknie Creating Publication możesz monitorować postęp tworzenia nowej publikacji. Poczekaj chwilę, a jeśli wszystko zostało wykonane poprawnie, powinien pojawić się komunikat o pomyślnym zakończeniu.

Publikacja została utworzona i można ją zobaczyć w Eksploratorze obiektów, przechodząc do Replication > Local Publications.

Konfigurowanie subskrybenta

Jak pamiętasz, replikacja w MS SQL Server może być typu pull lub push. Jeśli konfigurujesz replikację typu push, należy skonfigurować subskrybenta tak, aby uruchamiał agenty na głównym serwerze bazy danych (w tym przypadku MSSQL01). Jeśli skonfigurujesz replikację typu pull, subskrybent musi być skonfigurowany do uruchamiania agentów na drugim komputerze (MSSQL02), czyli na komputerze, na którym zostanie utworzona replika bazy danych.

Skonfigurujmy replikację typu push i utwórzmy nową subskrypcję na pierwszym serwerze MS SQL Server (MSSQL01MSSQLSERVER1), na którym znajduje się baza danych master.

W Eksploratorze obiektów przejdź do Replication, kliknij prawym przyciskiem myszy Local Subscriptions i w menu kontekstowym wybierz New Subscriptions.

Otworzy się Kreator nowej subskrypcji .

1. Publication. Wybierz publikację, dla której chcesz utworzyć nową subskrypcję. W naszym przykładzie nazwa wydawcy to MSSQL01MSSQLSERVER1, a nazwa publikacji (utworzonej wcześniej) to AdvWorks_Pub . Kliknij Dalej na każdym etapie kreatora, aby kontynuować.
2. Distribution Agent Location. Wybierz typ replikacji, wybierając subskrypcję typu „push” lub „pull”. W naszym przykładzie chcemy, aby wszystkie agenty działały po stronie serwera źródłowego, dlatego wybieramy pierwszą opcję, aby utworzyć subskrypcję typu push. Pozwala to na centralne zarządzanie replikacją MS SQL Server.

   

3. Subscribers. Domyślnie serwer, na którym uruchamiasz kreator (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 (w naszym przypadku MSSQL01MSSQLSERVER2). Kliknij Add Subscriber i w menu kontekstowym wybierz Add SQL Server Subscriber.
   • W oknie podręcznym wprowadź poświadczenia dla drugiej instancji serwera MSSQL (w naszym przypadku MSSQL01MSSQLSERVER2) i kliknij Connect.

     

   • Zaznacz pole wyboru drugiego serwera, na którym będzie przechowywana replika bazy danych (MSSQL02MSSQLSERVER2), a następnie w menu rozwijanym Subskrypcja Database wybierz nową bazę danych lub istniejącą bazę danych przywróconą z kopii zapasowej, która ma służyć jako replika bazy danych.

     W naszym przykładzie baza danych AdventureWorks2016r została utworzona na drugim serwerze poprzez przywrócenie głównej (źródłowej) bazy danych AdventureWorks2016 z kopii zapasowej w celu rozpoczęcia replikacji. Replikacja jest uruchamiana poprzez replikowanie tylko nowych danych, a nie poprzez kopiowanie całej bazy danych po rozpoczęciu procesu replikacji. W związku z tym w bieżącym przykładzie jako bazę danych subskrypcji wybrano AdventureWorks2016r .

     

4. Distribution Agent Security. Kliknij przycisk z trzema kropkami (…) i wybierz użytkownika oraz inne opcje zabezpieczeń dla agenta dystrybucji.

   W oknie Zabezpieczenia agenta dystrybucji , które się otworzy, ustaw agenta dystrybucji tak, aby działał na hoście MSSQL01 pod kontem użytkownika mssql . Wprowadź hasło dla użytkownika systemu Windows mssql . Wybierz Connect to the Distributor by impersonating the process account i wybierz Connect to the Subscriber by impersonating the process account. Kliknij OK , aby zapisać ustawienia.

   

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

   

5. Synchronization Schedule. Wybierz agenta znajdującego się na serwerze dystrybucyjnym, aby uruchomić go w trybie ciągłym dla bieżącego subskrybenta.
6. Initialize Subscriptions. Zaznacz pole wyboru Initialize i z menu rozwijanego wybierz Immediately opcję inicjalizacji subskrypcji. W razie potrzeby możesz również wybrać opcję Zoptymalizowane pod kątem pamięci .

   

7. Wizard Actions. Zaznacz górne pole wyboru, aby utworzyć subskrypcję (subskrypcje) na końcu kreatora.
8. Complete the Wizard. Możesz sprawdzić ustawienia subskrypcji i kliknąć Finish , aby utworzyć subskrypcję.

   

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

   

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

    

Finalizacja konfiguracji replikacji

Po skonfigurowaniu dystrybutora, wydawcy i subskrybenta możesz sprawdzić status replikacji MS SQL Server.

1. Na pierwszym serwerze (MSSQL01MSSQLSERVER1) uruchom monitorowanie replikacji, aby sprawdzić stan replikacji MS SQL Server. W programie SQL Server Management Studio wybierz instancję MS SQL Server (MSSQLSERVER1), przejdź do Replication, kliknij prawym przyciskiem myszy Local Publications i w menu kontekstowym wybierz Launch Replication Monitor.

   

2. W naszym przypadku występuje błąd Log Reader Agent . Aby wyświetlić szczegóły błędu, wybierz bazę danych źródłową (wydawcę) w lewym panelu, wybierz kartę Agents w prawym panelu i 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 to:
   • Proces nie mógł wykonać sp_replcmds na MSSQL01MSSQLSERVER1. Źródło: MSSQL_REPL. Numer błędu: MSSQL_REPL20011).
   • Nie można wykonać jako podmiot bazy danych, ponieważ podmiot „dbo” nie istnieje, nie można podszywać się pod ten typ podmiotu lub nie masz uprawnień. (Źródło: MSSQLServer, numer błędu: 15517).

   

   Drugi komunikat o błędzie sugeruje, że brakuje jakiegoś uprawnienia. Naprawmy ten błąd.

4. Utwórz nowe zapytanie w programie MS SQL Management Studio i wykonaj je. W głównym oknie kliknij przycisk New Query .
5. W sekcji zapytań SQL głównego okna wprowadź następujące zapytanie:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Kliknij przycisk Execute .

   

   Polecenie (polecenia) zostało pomyślnie wykonane.

6. Następnie przejdź do MSSQL01MSSQLSERVER1 > Replication > Local Publications > [AdventureWorks2016]: AdvWorks_Pub. Kliknij prawym przyciskiem myszy nazwę publikacji i w menu kontekstowym wybierz Wyświetl stan agenta migawek . Możesz kliknąć Action > Refresh , aby odświeżyć status, oraz Reinitialize All Subscriptions , aby zastosować migawkę do każdego subskrybenta.

   Teraz wszystko jest rozwiązane, nie są wyświetlane żadne błędy, a replikacja MS SQL Server powinna działać.

   

Sprawdzanie działania replikacji

Zobaczmy, jak działa replikacja MS SQL Server. Wyświetl zawartość tabeli AdventureWorks2016 bazy danych przechowywanej na pierwszym serwerze MS SQL ( MSSQL01MSQLSERVER1 ). W naszym przykładzie wybierzemy wszystkie dane z tabeli Person.AddressType . Aby to zrobić, należy wykonać zapytanie:

USE AdventureWorks2016;

GO

SELECT *

FROM Person.AddressType

;

Wynik wykonania zapytania jest wyświetlony na poniższym zrzucie ekranu:

Wykonaj podobne zapytanie na drugim serwerze, aby wyświetlić wszystkie dane z tabeli Person.AddressType bazy danych AdventureWorks2016r przechowywanej na serwerze 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 i baza docelowa, która jest repliką bazy danych 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:

DELETE FROM Person.AddressType WHERE Name='Billing';

SELECT * FROM Person.AddressType;

Jak widać, pierwszy wiersz z AddressTypeID 1 i nazwą „Billing” został usunięty z tabeli Person.AddressType w bazie danych AdventureWorks2016 na maszynie MSSQL01 .

Replikacja transakcyjna działa. Sprawdźmy zawartość tabeli Person.AddressType w bazie danych AdventureWorks2016r na serwerze MSSQL02 . Aby wyświetlić zawartość tabeli, należy ponownie wykonać zapytanie podobne do powyższego:

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, pełniącej rolę repliki ( AdventureWorks2016r ). Wyniki można zobaczyć na poniższym zrzucie ekranu.

Replikacja bazy danych w SQL Server działa poprawnie.

Wnioski

Istnieją cztery rodzaje replikacji 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 typ replikacji 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) i serwerze docelowym (replikacja typu pull).

Należy jednak rozważyć użycie zarówno replikacji, jak i wykonywanie kopii zapasowych 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 poznać wszystkie funkcje rozwiązania w zakresie ochrony danych. 15 dni za darmo. Bez żadnych ograniczeń dotyczących funkcji ani pojemności. Nie trzeba podawać danych karty kredytowej.

Wypróbuj za darmo