SQL Server Always On automatic seeding. Jak włączyć kompresję?

W wersjach MSSQL starszych niż 2016 do inicjalizacji secondary replica należało użyć metody backup, copy, restore. Opcja automatic seeding w availability group jest dostępna od wersji Microsoft SQL Server 2016. Automatic seeding, z grubsza, polega na automatycznym i transparentnym wykonaniu backupu za pomocą VDI oraz odtworzeniu go na secondary replica.

Automatic seeding

Proces automatycznej inicjalizacji uruchomi się automatycznie przy tworzeniu nowej grupy availability wraz z dołączeniem secondary replica w trybie Seeding Mode = Automatic. Również gdy dodamy nową bazę do istniejącej konfiguracji silnik rozpocznie automatyczną inicjalizację. Automatic Seeding Mode możemy ustawić za pomocą SSMS lub za pomocą T-SQL:

--Listing 1.
USE [master]
GO
ALTER AVAILABILITY GROUP [ag_distr_src]
MODIFY REPLICA ON N'AGN2' WITH (SEEDING_MODE = AUTOMATIC)
GO

Zapytanie Listing 1. uruchamiamy na primary replica powodując włączenie automating seeding dla wybranej repliki.

Dodatkowo jeśli nie korzystaliśmy z wizarda proponuję sprawdzić czy grupa na secondary replika ma uprawnienia do tworzenia nowych baz danych. W przeciwnym wypadku dostaniemy błąd:

Error: 911, Severity: 16, State: 1. Local availability replica for availability group ‘ag_distr_src’ has not been granted permission to create databases, but has a SEEDING_MODE of AUTOMATIC. Use the ALTER AVAILABILITY GROUP … GRANT CREATE ANY DATABASE command to allow the creation of databases seeded by the primary availability replica.

--Listing 2.
ALTER AVAILABILITY GROUP [ag_distr_src]
GRANT CREATE ANY DATABASE

Za pomocą polecenia Listing 2. uruchomionego na secondary replika możemy nadać brakujące uprawnienia.

Initial Data Synchronization

Dodajmy więc nową bazę do grupy za pomocą wizarda. W czwartym kroku możemy wybrać (Figure 2.) w jaki sposób ma się odbyć inicjalizacja podczas dołączenia do AG wybranej bazy. I tu ważna uwaga!. Wybranie opcji ma wpływ na globalne ustawienie automatic seeding dla repliki. Jeśli wybierzemy Automatic seeding a replika miała ustawioną opcję Seeding Mode (Figure 1.) = Manual to oczywiście po zakończeniu wizarda rozpocznie się proces automatycznej inicjalizacji ale również zmieni się globalne ustawienie na “Automatic”! Podobnie jest z opcją Skip initial data synchronization. Baza nie zostanie dodana automatycznie do repliki ale zmieni się też ustawienie Seeding Mode = Manual. Można się tym trochę zdziwić w przyszłości… Aktualny status Seeding Mode możemy sprawdizć za pomocą T-SQL:

--Listing 3.
select
       seeding_mode,
       seeding_mode_desc
from
       sys.availability_replicas ar
where
       ar.replica_server_name='AGN2' --replica name

Włączenie kompresji

Tyle tytułem wstępu. Zainteresował mnie temat kompresji podczas automatycznego procesu. Temat urodził się przy okazji Distributed Availability Group, musiałem uruchomić replikację danych pomiędzy serwerami MSSQL w różnych lokalizacjach w zupełnie innych subnetach. Opóźnienia sieciowe przy inicjalizacji były zauważalnie duże. Gdy miałem zrezygnować na rzecz tradycyjnego “restora”, wynalazłem flagę, która pomaga w sytuacji dużych latency między hostami. Mowa o fladze 9567. Flaga włącza kompresję danych przy automatic seeding. Oznacza to, że mniej danych może zostać przesłanych przez sieć. Należy mieć na uwadze, że nie ma nic za darmo. Dane muszą być wcześniej poddane kompresji co jest procesem wpływającym na utylizację procesora. Przy mocno obciążonych serwerach, wpływ kompresji może być widoczny. Jednak przy zbalansowanych środowiskach z zapasem mocy traktowałbym kompresję tego typu jak kompresja dla backupu, która jest dla mnie standardem. Przejdźmy więc do uruchomienia trace flagi w Sql Server Configuration Manager. Po dodaniu, konieczny jest restart. Stan flagi sprawdzamy za pomocą DBCC TRACESTATUS.

SQL Server Properties

Ale zaraz, zaraz. Na której instancji należy, dodać flagę 9567 ? Primary czy secondary replica…? Flagę dodajemy na primary replica. Właczenie flagi na secondary replica nic nie daje. No chyba, że w wyniku failover, replika zostanie promowana jako primary. Istnieje DMV dm_hadr_physical_seeding_stats, za pomocą którego możemy śledzić progres operacji. Napotkałem tam na jeden istotny problem, wiele mówiąca kolumna is_compression_enabled zawsze pokazuje wartość *NULL**.

--Listing 4.
select
       local_database_name,
       internal_state_desc,
       transferred_size_bytes/1024/1024 'transfered_mb',
       database_size_bytes/1024/1024 'db_size_mb',
       start_time_utc,
       end_time_utc,
       total_disk_io_wait_time_ms,
       total_network_wait_time_ms,
       failure_code,
       failure_message,
       failure_time_utc,
       is_compression_enabled,
       datediff(second,start_time_utc,end_time_utc) 'elapsed_sec'
from
       sys.dm_hadr_physical_seeding_stats

Test działania

Sprawdzimy w rzeczywistości czy SQL nas nie oszukuje. Może faktycznie wartość NULL w is_compression_enabled oznacza, że flaga nie działa? Testowa baza około 1500MB danych. Dwie tabele, jedna tabela jest kopią drugiej, w tabelach dane z sys.dm_os_wait_stats zatem dane stosunkowo dobrze “kompresujące się”.

Wynik z dm_hadr_physical_seeding_stats. Kompresja włączona i wyłączona.

Wyniki potwierdzają, że włączenie trace flag 9567 ma istotny wpływ na ilość przesłanych danych. Seeding testowej bazy danych trwał połowę krócej, przez sieć przesłano 5 razy mniej danych bo zaledwie niecałe 300 MB, czyli 20% rozmiaru bazy danych.

*Sprawdzone na SQL Server 2019 RTM i 2022 CU11.