Konfigurowanie grupy dostępności programu SQL Server na potrzeby skalowania odczytu w systemie Linux

Dotyczy:programu SQL Server — Linux

W tym artykule wyjaśniono, jak utworzyć grupę dostępności Always On programu SQL Server na systemie Linux bez menedżera klastra. Ta architektura zapewnia skalowanie wyłącznie dla odczytu. nie zapewnia wysokiej dostępności.

Istnieją dwa typy architektur dla AG. Architektura wysokiej dostępności wykorzystuje menedżera klastra w celu zapewnienia lepszej ciągłości działania. Aby utworzyć architekturę wysokiej dostępności, zobacz Konfigurowanie zawsze włączonej grupy dostępności programu SQL Server w celu zapewnienia wysokiej dostępności w systemie Linux.

Grupa dostępności z CLUSTER_TYPE = NONE może zawierać repliki hostowane na różnych systemach operacyjnych. Nie zapewnia wysokiej dostępności.

Warunki wstępne

Przed utworzeniem grupy dostępności należy:

• Ustaw środowisko tak, aby wszystkie serwery, które będą obsługiwać repliki dostępności, mogły się komunikować.
• Zainstaluj program SQL Server.

W systemie Linux należy utworzyć grupę dostępności przed dodaniem jej jako zasobu klastra do zarządzania przez klaster. Ten dokument zawiera przykład, który tworzy grupę dostępności.

1. Zaktualizuj nazwę komputera dla każdego hosta.

   Każda nazwa wystąpienia programu SQL Server musi być:

   • 15 znaków lub mniej.
   • Unikatowe w sieci.

   Aby ustawić nazwę komputera, edytuj /etc/hostname. Poniższy skrypt umożliwia edytowanie /etc/hostname przy użyciu vi:

   sudo vi /etc/hostname
   

2. Skonfiguruj plik hosts.

   Notatka

   Jeśli nazwy hostów są zarejestrowane przy użyciu ich adresu IP na serwerze DNS, nie musisz wykonywać poniższych kroków. Sprawdź, czy wszystkie węzły, które mają być częścią konfiguracji grupy dostępności, mogą komunikować się ze sobą. (Polecenie ping do nazwy hosta powinno wskazywać odpowiedni adres IP). Upewnij się również, że plik /etc/hosts nie zawiera rekordu mapowania adresu IP hosta lokalnego 127.0.0.1 z nazwą hosta węzła.

   Plik hosts na każdym serwerze zawiera adresy IP i nazwy wszystkich serwerów, które będą uczestniczyć w grupie dostępności.

   Następujące polecenie zwraca adres IP bieżącego serwera:

   sudo ip addr show
   

   Zaktualizuj /etc/hosts. Poniższy skrypt umożliwia edytowanie /etc/hosts przy użyciu vi:

   sudo vi /etc/hosts
   

   Poniższy przykład przedstawia /etc/hosts na node1 z dodatkami dla node1, node2i node3. W tym przykładzie node1 odnosi się do serwera, który hostuje replikę podstawową, a node2 i node3 odnoszą się do serwerów hostujących repliki pomocnicze.

   127.0.0.1    localhost localhost4 localhost4.localdomain4
   ::1          localhost localhost6 localhost6.localdomain6
   10.128.18.12 node1
   10.128.16.77 node2
   10.128.15.33 node3
   

Instalowanie programu SQL Server

Zainstaluj program SQL Server. Poniższe linki wskazują instrukcje instalacji programu SQL Server dla różnych dystrybucji:

• Szybki start: instalowanie programu SQL Server i tworzenie bazy danych w usłudze Red Hat
• Szybki start: instalowanie programu SQL Server i tworzenie bazy danych w systemie SUSE Linux Enterprise Server
• Szybki start: instalowanie programu SQL Server i tworzenie bazy danych w systemie Ubuntu

Włączanie zawsze włączonych grup dostępności

Włącz Always On availability groups dla każdego węzła hostującego wystąpienie programu SQL Server, a następnie uruchom ponownie mssql-server. Uruchom następujący skrypt:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Włącz sesję zdarzeń AlwaysOn_health

Możesz opcjonalnie włączyć zdarzenia rozszerzone (XE), aby ułatwić identyfikację źródłowej przyczyny podczas rozwiązywania problemów z grupą dostępności. Uruchom następujące polecenie w każdym wystąpieniu programu SQL Server:

ALTER EVENT SESSION AlwaysOn_health ON SERVER
WITH
(
        STARTUP_STATE = ON
);
GO

Aby uzyskać więcej informacji na temat tej sesji XE, zobacz Konfigurowanie zdarzeń rozszerzonych dla grup dostępności.

Tworzenie certyfikatu

Usługa SQL Server w systemie Linux używa certyfikatów do uwierzytelniania komunikacji między punktami końcowymi mirroringu.

Poniższy skrypt Transact-SQL tworzy klucz główny i certyfikat. Następnie tworzy kopię zapasową certyfikatu i zabezpiecza plik przy użyciu klucza prywatnego. Zaktualizuj skrypt przy użyciu silnych haseł. Połącz się z podstawową instancją programu SQL Server. Aby utworzyć certyfikat, uruchom następujący skrypt Transact-SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<private-key-password>'
);

W tym momencie podstawowa replika programu SQL Server ma certyfikat w /var/opt/mssql/data/dbm_certificate.cer i klucz prywatny w var/opt/mssql/data/dbm_certificate.pvk. Skopiuj te dwa pliki do tej samej lokalizacji na wszystkich serwerach, które będą hostować repliki dostępności. Użyj użytkownika mssql lub przyznaj użytkownikowi mssql uprawnienia dostępu do tych plików.

Na przykład na serwerze źródłowym następujące polecenie kopiuje pliki na maszynę docelową. Zastąp wartości <node2> nazwami wystąpień programu SQL Server, które będą hostować repliki.

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

Na każdym serwerze docelowym przyznaj użytkownikowi mssql uprawnienia dostępu do certyfikatu.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

Tworzenie certyfikatu na serwerach pomocniczych

Poniższy skrypt Transact-SQL tworzy klucz główny i certyfikat z kopii zapasowej utworzonej w podstawowej repliki programu SQL Server. Zaktualizuj skrypt przy użyciu silnych haseł. Hasło odszyfrowywania jest tym samym hasłem, które zostało użyte do utworzenia pliku .pvk w poprzednim kroku. Aby utworzyć certyfikat, uruchom następujący skrypt na wszystkich serwerach pomocniczych:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<private-key-password>'
);

W poprzednim przykładzie zastąp <private-key-password> tym samym hasłem użytym podczas tworzenia certyfikatu w repliki podstawowej.

Tworzenie punktów końcowych dublowania bazy danych na wszystkich replikach

Punkty końcowe dublowania bazy danych używają protokołu TCP (Transmission Control Protocol) do wysyłania i odbierania komunikatów między wystąpieniami serwera uczestniczącymi w sesjach dublowania bazy danych lub replikami dostępności hosta. Punkt końcowy dublowania bazy danych nasłuchuje unikalny numer portu TCP.

Poniższy skrypt Transact-SQL tworzy punkt końcowy nasłuchiwania o nazwie Hadr_endpoint dla grupy dostępności. Uruchamia punkt końcowy i daje uprawnienie połączenia do utworzonego certyfikatu. Przed uruchomieniem skryptu zastąp wartości umieszczone pomiędzy < ... >. Opcjonalnie możesz dołączyć adres IP LISTENER_IP = (0.0.0.0). Adres IP odbiornika musi być adresem IPv4. Możesz również użyć 0.0.0.0.

Zaktualizuj skrypt o nazwie Transact-SQL dla środowiska we wszystkich instancjach SQL Server.

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP
(
            LISTENER_PORT = 5022
)
    FOR DATABASE_MIRRORING
(
            ROLE = ALL,
            AUTHENTICATION = CERTIFICATE dbm_certificate,
            ENCRYPTION = REQUIRED ALGORITHM AES
);

ALTER ENDPOINT [Hadr_endpoint]
    STATE = STARTED;

Notatka

Jeśli używasz wersji SQL Server Express w jednym węźle do hostowania repliki skonfigurowanej wyłącznie, jedyną prawidłową wartością dla ROLE jest WITNESS. Uruchom następujący skrypt w wersji SQL Server Express:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP
(
            LISTENER_PORT = 5022
)
    FOR DATABASE_MIRRORING
(
            ROLE = WITNESS,
            AUTHENTICATION = CERTIFICATE dbm_certificate,
            ENCRYPTION = REQUIRED ALGORITHM AES
);

ALTER ENDPOINT [Hadr_endpoint]
    STATE = STARTED;

Port TCP zapory sieciowej musi być otwarty dla portu nasłuchu.

Ważny

W przypadku programu SQL Server 2017 (14.x) jedyną metodą uwierzytelniania obsługiwaną dla punktu końcowego dublowania bazy danych jest CERTIFICATE. Opcja WINDOWS jest niedostępna.

Aby uzyskać więcej informacji, zobacz punkt końcowy mirroringu bazy danych (SQL Server).

Tworzenie grupy dostępności

Utwórz AG. Ustaw CLUSTER_TYPE = NONE. Ponadto skonfiguruj każdą replikę przy użyciu FAILOVER_MODE = MANUAL. Aplikacje klienckie z uruchomionymi obciążeniami analitycznymi lub raportowaniem mogą łączyć się bezpośrednio z pomocniczymi bazami danych. Możesz również utworzyć listę routingu tylko do odczytu. Połączenia z repliką podstawową przekazują żądania połączenia odczytu do każdej repliki pomocniczej z listy routingu w sposób okrężny.

Poniższy skrypt Transact-SQL tworzy AG o nazwie ag1. Skrypt konfiguruje repliki AG, czyli grupy dostępności, przy użyciu SEEDING_MODE = AUTOMATIC. To ustawienie powoduje, że program SQL Server automatycznie utworzy bazę danych na każdym serwerze pomocniczym po jej dodaniu do grupy dostępności. Zaktualizuj następujący skrypt dla swojego środowiska. Zastąp wartości <node1> i <node2> nazwami wystąpień programu SQL Server hostujących repliki. Zastąp wartość <5022> portem ustawionym dla punktu końcowego. Uruchom następujący skrypt Transact-SQL w podstawowej repliki programu SQL Server:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        ),
        N'<node2>' WITH (
            ENDPOINT_URL = N'tcp://<node2>:<5022>',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
        );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Dołączanie pomocniczych wystąpień programu SQL Server do grupy dostępności

Poniższy skrypt Transact-SQL dołącza serwer do grupy dostępności nazwanej ag1. Zaktualizuj skrypt dla środowiska. Na każdej pomocniczej replice programu SQL Server uruchom następujący skrypt Transact-SQL, aby dołączyć do AG:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Dodawanie bazy danych do grupy dostępności

Upewnij się, że baza danych dodana do grupy dostępności znajduje się w pełnym modelu odzyskiwania i ma prawidłową kopię zapasową dziennika. Jeśli baza danych jest testową bazą danych lub nowo utworzoną bazą danych, utwórz kopię zapasową bazy danych. W podstawowym programie SQL Server uruchom następujący skrypt Transact-SQL (T-SQL), aby utworzyć i utworzyć kopię zapasową bazy danych o nazwie db1:

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
    SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
    TO DISK = N'/var/opt/mssql/data/db1.bak';

W podstawowej repliki programu SQL Server uruchom następujący skrypt języka T-SQL, aby dodać bazę danych o nazwie db1 do grupy dostępności o nazwie ag1:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Sprawdź, czy baza danych została utworzona na serwerach pomocniczych

W każdej pomocniczej repliki programu SQL Server uruchom następujące zapytanie, aby sprawdzić, czy baza danych db1 została utworzona i jest zsynchronizowana:

SELECT *
FROM sys.databases
WHERE name = 'db1';
GO

SELECT DB_NAME(database_id) AS 'database',
       synchronization_state_desc
FROM sys.dm_hadr_database_replica_states;
GO

Ta grupa dostępności (AG) nie jest konfiguracją o wysokiej dostępności. Jeśli potrzebujesz wysokiej dostępności, postępuj zgodnie z instrukcjami w Konfigurowanie zawsze włączonej grupy dostępności programu SQL Server pod kątem wysokiej dostępności w systemie Linux. W szczególności, utwórz AG z CLUSTER_TYPE=WSFC (w systemie Windows) lub CLUSTER_TYPE=EXTERNAL (w systemie Linux). Następnie możesz zintegrować się z menedżerem klastra, używając klastrowania awaryjnego Windows Server na systemie Windows lub Pacemaker na systemie Linux.

Nawiązywanie połączenia z replikami pomocniczymi tylko do odczytu

Istnieją dwa sposoby dostępu do replik wtórnych tylko do odczytu. Aplikacje mogą łączyć się bezpośrednio z wystąpieniem programu SQL Server, które hostuje replikę pomocniczą i wysyłać zapytania do baz danych. Mogą również używać routingu tylko do odczytu, który wymaga odbiornika.

• Przeniesienie obciążenia tylko do odczytu na pomocniczą replikę Always On grupy dostępności
• Trasowanie tylko do odczytu

Przełącz replikę podstawową na tryb awaryjny w grupie dostępności skonfigurowanej dla skalowania odczytu.

Każda grupa dostępności ma tylko jedną replikę podstawową. Replika podstawowa umożliwia odczyty i zapisy. Aby zmienić replikę podstawową, możesz przejść w tryb failover. W typowej grupie dostępności menedżer klastra automatyzuje proces przełączenia awaryjnego. W grupie dostępności z typem klastra NONE proces failover jest wykonywany ręcznie.

Istnieją dwa sposoby przełączania repliki podstawowej w tryb failover w grupie dostępności z typem klastra NONE:

• Ręczny failover bez utraty danych
• Wymuszone ręczne przejście w tryb failover z utratą danych

Ręczne przełączenie awaryjne bez utraty danych

Użyj tej metody, gdy jest dostępna replika podstawowa, ale musisz tymczasowo lub trwale zmienić wystąpienie, które hostuje replikę podstawową. Aby uniknąć potencjalnej utraty danych, przed przełączeniem ręcznym na tryb failover upewnij się, że docelowa replika pomocnicza jest zaktualizowana.

Aby ręcznie przejść w tryb failover bez utraty danych:

1. Przekształć bieżącą replikę podstawową w pomocniczą i docelową w replikę wtórną SYNCHRONOUS_COMMIT.

   ALTER AVAILABILITY GROUP [AGRScale] 
        MODIFY REPLICA ON N'<node2>' 
        WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

2. Aby zidentyfikować, że aktywne transakcje są zatwierdzane w głównej i co najmniej jednej synchronicznej repliki pomocniczej, uruchom następujące zapytanie:

   SELECT ag.name, 
      drs.database_id, 
      drs.group_id, 
      drs.replica_id, 
      drs.synchronization_state_desc, 
      ag.sequence_number
   FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
   WHERE drs.group_id = ag.group_id; 
   

   Replika pomocnicza jest synchronizowana, gdy synchronization_state_desc jest SYNCHRONIZED.

3. Zaktualizuj REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT na 1.

   Poniższy skrypt ustawia REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT na 1 w grupie dostępności o nazwie ag1. Przed uruchomieniem następującego skryptu zastąp ag1 nazwą grupy dostępności:

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
   

   To ustawienie zapewnia, że każda aktywna transakcja jest zatwierdzana w repliki podstawowej i co najmniej jednej synchronicznej repliki pomocniczej.

   Notatka

   To ustawienie nie jest specyficzne dla trybu failover i powinno być ustawione na podstawie wymagań środowiska.

4. Ustaw replikę podstawową i repliki pomocnicze, które nie uczestniczą w trybie failover w trybie offline, aby przygotować się do zmiany roli:

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. Awansuj docelową replikę pomocniczą na podstawową.

   ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
   

6. Zaktualizuj rolę starej repliki podstawowej i innych replik pomocniczych na SECONDARY, a następnie uruchom następujące polecenie na instancji programu SQL Server, która hostuje starą replikę podstawową:

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (ROLE = SECONDARY); 
   

   Notatka

   Aby usunąć grupę dostępności, użyj DROP AVAILABILITY GROUP. W przypadku grupy dostępności, która została utworzona z typem klastra NONE lub EXTERNAL, należy wykonać polecenie na wszystkich replikach należących do grupy dostępności.

7. Aby wznowić przenoszenie danych, uruchom następujące polecenie dla każdej bazy danych w grupie dostępności na instancji SQL Server, która hostuje replikę podstawową.

   ALTER DATABASE [db1]
        SET HADR RESUME
   

8. Utwórz ponownie każdy odbiornik, który utworzyłeś na potrzeby skalowania do odczytu i który nie jest zarządzany przez menedżera klastra. Jeśli oryginalny odbiornik wskazuje stary element podstawowy, usuń go i utwórz go ponownie, aby wskazać nowy element podstawowy.

Wymuszone ręczne przejście w tryb failover z utratą danych

Jeśli replika podstawowa jest niedostępna i nie można jej od razu odzyskać, musisz wymusić przejście w tryb failover do repliki pomocniczej z utratą danych. Jeśli jednak oryginalna replika podstawowa odzyska sprawność po przejściu w tryb failover, przyjmie rolę podstawową. Aby uniknąć sytuacji, w której każda replika znajduje się w innym stanie, usuń oryginalną główną replikę z grupy dostępności po wymuszonym przełączeniu z utratą danych. Gdy oryginalny serwer główny wróci online, usuń z niego całkowicie grupę dostępności.

Aby wymusić ręczne przejście w tryb failover z powodu utraty danych z repliki podstawowej N1 do repliki pomocniczej N2, wykonaj następujące kroki:

1. W repliki pomocniczej (N2) zainicjuj wymuszony tryb failover:

   ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

2. W nowej repliki podstawowej (N2) usuń oryginalną replikę podstawową (N1):

   ALTER AVAILABILITY GROUP [AGRScale]
   REMOVE REPLICA ON N'N1';
   

3. Sprawdź, czy cały ruch aplikacji jest kierowany na nasłuchiwacz i/lub nową replikę podstawową.

4. Jeśli oryginalny serwer główny (N1) przełączy się na tryb online, natychmiast wyłącz grupę dostępności AGRScale na oryginalnym serwerze głównym (N1).

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. Jeśli istnieją dane lub zmiany niezsynchronizowane, zachowaj te dane za pomocą kopii zapasowych lub innych opcji replikowania danych odpowiadających potrzebom biznesowym.

6. Następnie usuń grupę dostępności z oryginalnego podstawowego (N1):

   DROP AVAILABILITY GROUP [AGRScale];
   

7. Usuń bazę danych z grupy dostępności na oryginalnej replice podstawowej (N1).

   USE [master]
   GO
   DROP DATABASE [AGDBRScale]
   GO
   

8. (Opcjonalnie) W razie potrzeby można teraz dodać N1 z powrotem jako nową replikę pomocniczą do grupy dostępności AGRScale.

Powiązana zawartość

• rozproszone grupy dostępności
• Co to jest grupa dostępności Always On?
• Wykonaj wymuszone ręczne przełączenie w tryb failover dla grupy dostępności Always On (SQL Server)