SQL Server Always On — wysoka dostępność bazy danych krok po kroku

2. Architektura Always On Availability Groups

Zrozumienie architektury AG jest fundamentem prawidłowej konfiguracji. Oto kluczowe komponenty:

2.1. Replika główna (Primary Replica)

Serwer SQL Server, który obsługuje wszystkie operacje odczytu i zapisu. W danym momencie tylko jedna replika może być primary. Hostuje ona bazy danych primary — jedyne kopie, do których aplikacje mogą zapisywać dane.

2.2. Repliki wtórne (Secondary Replicas)

Serwery utrzymujące kopie baz danych zsynchronizowane z primary. Mogą być skonfigurowane jako:

• Synchroniczne — dane są potwierdzone na replice wtórnej przed zatwierdzeniem transakcji na primary
• Asynchroniczne — primary nie czeka na potwierdzenie z repliki wtórnej

2.3. Availability Group Listener

Wirtualna nazwa sieciowa (VNN) z własnym adresem IP, która działa jako single point of contact dla aplikacji klienckich. Listener automatycznie przekierowuje połączenia do aktualnego primary po failoverze — aplikacje nie wymagają zmiany connection stringów.

2.4. Windows Server Failover Cluster (WSFC)

Klaster Windows, który zarządza kworum i koordynuje failover. Każdy węzeł SQL Server uczestniczący w AG musi być członkiem tego samego klastra WSFC.

2.5. Database Mirroring Endpoint

Każda instancja SQL Server w AG komunikuje się przez dedykowany endpoint TCP (domyślnie port 5022). Ten endpoint obsługuje cały ruch replikacji między replikami.

2.6. Schemat przepływu danych

3. Always On AG vs Failover Cluster Instance (FCI) — różnice

4. Wymagania systemowe i licencyjne

4.1. Wymagania sprzętowe i systemowe

• System operacyjny: Windows Server 2016 lub nowszy (zalecany Windows Server 2022 Standard lub Windows Server 2022 Datacenter)
• WSFC: Windows Server Failover Clustering zainstalowane na wszystkich węzłach
• Sieć: Dedykowana podsieć dla heartbeat, stabilne połączenie o niskiej latencji
• DNS: Prawidłowa rozdzielczość nazw dla węzłów klastra i listenera
• Active Directory: Wszystkie węzły w tej samej domenie AD
• Konto usługi SQL Server: To samo konto domenowe na wszystkich węzłach lub gMSA

4.2. Wymagania edycji SQL Server

5. Konfiguracja WSFC krok po kroku

Windows Server Failover Clustering to fundament Always On AG.

Krok 1: Instalacja roli Failover Clustering

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Krok 2: Walidacja klastra

Test-Cluster -Node "SQL-NODE1","SQL-NODE2","SQL-NODE3" -Include "Inventory","Network","Storage","System Configuration"

Krok 3: Tworzenie klastra

New-Cluster -Name "SQL-CLUSTER" -Node "SQL-NODE1","SQL-NODE2","SQL-NODE3" -StaticAddress 10.0.1.100

Krok 4: Konfiguracja kworum

# Cloud Witness (Azure) — rekomendowany od Windows Server 2016
Set-ClusterQuorum -CloudWitness -AccountName "mystorageaccount" -AccessKey "storage-access-key"

Krok 5: Włączenie Always On

Enable-SqlAlwaysOn -ServerInstance "SQL-NODE1" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL-NODE2" -Force
# Wymaga restartu usługi SQL Server!
Restart-Service -Name "MSSQLSERVER" -Force

6. Tworzenie Availability Group — GUI i T-SQL

6.1. Metoda T-SQL (rekomendowana dla automatyzacji)

Krok 1: Utwórz endpoint na każdym węźle

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\SQLServiceAccount];

Krok 2: Utwórz Availability Group na primary

CREATE AVAILABILITY GROUP [AG_Production]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON)
FOR DATABASE [MyDatabase1], [MyDatabase2]
REPLICA ON
    N'SQL-NODE1' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE1.domain.local:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC),
    N'SQL-NODE2' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE2.domain.local:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC),
    N'SQL-NODE3' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE3.domain.local:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        FAILOVER_MODE = MANUAL,
        SEEDING_MODE = AUTOMATIC);

ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'AG-LISTENER' (
    WITH IP ((N'10.0.1.110', N'255.255.255.0')), PORT = 1433);

Krok 3: Dołącz repliki wtórne

-- Na SQL-NODE2
ALTER AVAILABILITY GROUP [AG_Production] JOIN;
ALTER AVAILABILITY GROUP [AG_Production] GRANT CREATE ANY DATABASE;

-- Na SQL-NODE3
ALTER AVAILABILITY GROUP [AG_Production] JOIN;
ALTER AVAILABILITY GROUP [AG_Production] GRANT CREATE ANY DATABASE;

6.2. Metoda GUI (SSMS Wizard)

1. W Object Explorer: Always On High Availability → New Availability Group Wizard
2. Podaj nazwę grupy (np. AG_Production)
3. Wybierz bazy danych (muszą być w trybie Full Recovery)
4. Dodaj repliki — serwery, tryb dostępności, tryb failover
5. Skonfiguruj synchronizację — Automatic Seeding (rekomendowane)
6. Utwórz Listener
7. Walidacja i potwierdzenie

7. Repliki synchroniczne vs asynchroniczne

7.1. Replikacja synchroniczna (SYNCHRONOUS_COMMIT)

7.2. Replikacja asynchroniczna (ASYNCHRONOUS_COMMIT)

8. Tryby failover

8.1. Automatyczny failover

Wymaga replikacji synchronicznej + FAILOVER_MODE = AUTOMATIC. WSFC wykrywa awarię i promuje secondary. Brak utraty danych, czas: 10-30 sekund.

8.2. Planowany ręczny failover

ALTER AVAILABILITY GROUP [AG_Production] FAILOVER;

Używany podczas konserwacji. Brak utraty danych — wymaga synchronicznej repliki.

8.3. Wymuszony failover

ALTER AVAILABILITY GROUP [AG_Production] FORCE_FAILOVER_ALLOW_DATA_LOSS;

9. Listener — konfiguracja i DNS

Listener to wirtualna nazwa sieciowa (VNN) — aplikacje łączą się z listenerem, nie z konkretnymi serwerami. Po failoverze połączenia automatycznie kierowane do nowego primary.

Connection String

Server=AG-LISTENER;Database=MyDatabase;
MultiSubnetFailover=True;ApplicationIntent=ReadWrite;

Read-Only Routing

ALTER AVAILABILITY GROUP [AG_Production]
MODIFY REPLICA ON N'SQL-NODE1' WITH (
    PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (N'SQL-NODE2', N'SQL-NODE3')));

Aplikacje z ApplicationIntent=ReadOnly automatycznie kierowane do secondary.

10. Readable Secondary Replicas — odciążenie raportów

• Odciążenie primary — raporty i analizy BI na secondary
• Izolacja obciążeń — długie zapytania nie blokują transakcji produkcyjnych
• Skalowanie odczytu — do 2 readable secondaries (Enterprise)

ALTER AVAILABILITY GROUP [AG_Production]
MODIFY REPLICA ON N'SQL-NODE2' WITH (
    SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Zapytania na readable secondary automatycznie używają snapshot isolation — brak blokad, redo nie jest blokowane.

11. Monitorowanie AG

11.1. Always On Dashboard (SSMS)

Prawy klik na AG → Show Dashboard: stan synchronizacji, opóźnienie replikacji, failover readiness.

11.2. Kluczowe DMV

-- Stan replik
SELECT ag.name, ar.replica_server_name, ars.role_desc,
    ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id;

-- Opóźnienie replikacji
SELECT d.name, drs.log_send_queue_size, drs.redo_queue_size,
    drs.log_send_rate, drs.redo_rate
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.databases d ON drs.database_id = d.database_id
WHERE drs.is_local = 1;

11.3. Alerty SQL Agent

• Alert 1480 — zmiana roli AG (failover)
• Alert 35264 — AG data movement suspended
• Alert 41404 — AG offline
• Alert 41405 — AG not ready for automatic failover

12. Distributed Availability Groups

Distributed AG (SQL Server 2016+) to grupa złożona z dwóch oddzielnych AG w różnych klastrach WSFC:

• DR między data center — dwa klastry w różnych lokalizacjach
• Migracja między klastrami bez downtime
• Migracja między wersjami SQL Server

CREATE AVAILABILITY GROUP [DistributedAG]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
    N'AG1' WITH (LISTENER_URL = N'TCP://AG1-Listener:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL,
        SEEDING_MODE = AUTOMATIC),
    N'AG2' WITH (LISTENER_URL = N'TCP://AG2-Listener:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL,
        SEEDING_MODE = AUTOMATIC);

13. Contained Availability Groups (SQL Server 2022)

SQL Server 2022 wprowadza Contained AG — rozwiązanie problemu synchronizacji obiektów systemowych.

W standardowych AG replikowane są tylko bazy użytkownika. Loginy, SQL Agent jobs, linked servers muszą być ręcznie synchronizowane. Contained AG tworzy własne kopie baz systemowych (master, msdb):

• Loginy — replikowane automatycznie
• SQL Agent Jobs — synchronizowane jako część AG
• Linked Servers — replikowane

CREATE AVAILABILITY GROUP [ContainedAG]
WITH (CONTAINED, DB_FAILOVER = ON, CLUSTER_TYPE = WSFC)
FOR DATABASE [MyDatabase]
REPLICA ON ...;

14. Backup na replikach wtórnych

ALTER AVAILABILITY GROUP [AG_Production]
SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY);

ALTER AVAILABILITY GROUP [AG_Production]
MODIFY REPLICA ON N'SQL-NODE2' WITH (BACKUP_PRIORITY = 70);

IF (SELECT sys.fn_hadr_backup_is_preferred_replica('MyDatabase')) = 1
BEGIN
    BACKUP DATABASE [MyDatabase]
    TO DISK = N'D:\Backups\MyDatabase_Full.bak'
    WITH COPY_ONLY, COMPRESSION, CHECKSUM;
END

15. Najczęstsze problemy i troubleshooting

Problem 1: Baza danych "Not Synchronizing"

Zawieszony data movement, przepełniony log, problem z siecią.

ALTER DATABASE [MyDatabase] SET HADR RESUME;
EXEC sp_readerrorlog 0, 1, N'availability';

Problem 2: Wysoki redo lag

Wolne dyski na secondary, duże transakcje. Rozwiązanie: SSD/NVMe, trace flag 3459 (parallel redo).

Problem 3: Listener nie odpowiada po failoverze

Cache DNS, brak MultiSubnetFailover=True. Ustaw TTL DNS listenera na 300s.

Problem 4: Automatic failover nie działa

Checklist: obie repliki SYNCHRONOUS_COMMIT? FAILOVER_MODE = AUTOMATIC? Stan = SYNCHRONIZED? WSFC quorum healthy? DB_FAILOVER = ON?

16. FAQ

Ile kosztuje licencja SQL Server z Always On?

Basic AG dostępne w SQL Server 2022 Standard. Pełne AG wymaga Enterprise. Repliki pasywne mogą być licencjonowane ze zniżką (Software Assurance). Sprawdź licencje SQL Server w naszym sklepie.

Czy Always On AG wymaga SAN?

Nie. AG używa lokalnych dysków — dane replikowane przez sieć via transaction log shipping.

Jaki jest czas failover przy automatic failover?

Typowo 10-30 sekund. Można zoptymalizować zmniejszając HealthCheckTimeout i LeaseTimeout.

Czy mogę łączyć replikację synchroniczną i asynchroniczną?

Tak — rekomendowana konfiguracja: 2-3 synchroniczne (HA) + 1-2 asynchroniczne (DR).

Jak AG radzi sobie z bazami >1 TB?

Automatic seeding dla dużych baz może trwać godziny. Alternatywa: backup/restore z NORECOVERY + join. Bieżąca replikacja na poziomie logów — opóźnienie minimalne.

Powiązane artykuły:

• Failover Clustering Windows Server — konfiguracja
• SQL Server 2019 vs 2022 — porównanie
• Instalacja SQL Server krok po kroku
• Bezpieczeństwo SQL Server — best practices
• Licencjonowanie SQL Server — przewodnik