SQL Server Always On Availability Group 구성

증상

목표: Always On Availability Group(AG)으로 SQL Server의 자동 페일오버와 읽기 가능 secondary replica를 구성한다.

1. Windows Server Failover Cluster(WSFC)를 구성한다

Always On은 기반 WSFC에 의존한다. 모든 노드에 Failover Clustering 기능을 설치하고 네트워크와 witness를 구성한다. PowerShell로 클러스터를 생성한다.

SQL Server

-- PowerShell (the Failover-Clustering feature must be installed on every node):
New-Cluster -Name sqlclu -Node node1,node2 -StaticAddress 10.0.0.50
-- Witness:
Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\quorum

2. 각 인스턴스에서 Always On을 활성화한다

SQL Server Configuration Manager 또는 PowerShell로 Always On Availability Groups 기능을 활성화한다. 그 후 SQL Server 서비스를 재시작한다. 이로써 클러스터 멤버십이 인스턴스에 연결된다.

SQL Server

-- PowerShell:
Enable-SqlAlwaysOn -ServerInstance node1\SQL01 -Force
Enable-SqlAlwaysOn -ServerInstance node2\SQL01 -Force

3. 데이터베이스를 백업하고 secondary에서 WITH NORECOVERY로 복원한다

AG에 추가할 데이터베이스는 FULL 복구 모델이어야 한다. primary에서 전체 백업과 로그 백업을 수행하고, secondary에서 WITH NORECOVERY로 복원한다(redo를 계속 받을 수 있도록). 이것이 manual seeding이며, 대안은 automatic seeding이다.

SQL Server

-- On the primary:
BACKUP DATABASE app TO DISK='\\fs01\bak\app.bak';
BACKUP LOG app TO DISK='\\fs01\bak\app.trn';
-- On the secondary:
RESTORE DATABASE app FROM DISK='\\fs01\bak\app.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK='\\fs01\bak\app.trn' WITH NORECOVERY;

4. endpoint를 생성하고 Availability Group을 정의한다

노드 간 redo 트래픽을 위해 포트 5022에 HADR endpoint를 생성한다. 동기 모드(SYNCHRONOUS_COMMIT)는 데이터 손실 없는 자동 페일오버를 제공하고, 비동기(ASYNCHRONOUS_COMMIT)는 지연이 있지만 원격 DR에서 성능이 좋다.

SQL Server

CREATE ENDPOINT hadr_endpoint STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION=REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP appag
  FOR DATABASE app
  REPLICA ON
    'NODE1\SQL01' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL)),
    'NODE2\SQL01' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL));

5. secondary를 AG에 참여시키고 데이터베이스를 조인한다

secondary 인스턴스에서 AG에 JOIN한 후 복원한 데이터베이스를 AG에 추가한다. manual seeding을 사용한 경우 이 단계가 필요하며, automatic seeding에서는 primary에서 SET(SEEDING_MODE)로 처리된다.

SQL Server

-- On the secondary:
ALTER AVAILABILITY GROUP appag JOIN;
ALTER DATABASE app SET HADR AVAILABILITY GROUP = appag;

6. Availability Group Listener를 생성한다

listener는 애플리케이션이 연결하는 가상 이름과 IP다. 페일오버 후 연결은 새 primary로 자동 리디렉션된다. 애플리케이션 연결 문자열에 이 이름과 MultiSubnetFailover=True를 사용한다.

SQL Server

ALTER AVAILABILITY GROUP appag
  ADD LISTENER 'appag-listener' (WITH IP ((N'10.0.0.60', N'255.255.255.0')), PORT=1433);

7. 페일오버를 테스트하고 동기화 상태를 확인한다

동기화가 'HEALTHY'이면 계획된 페일오버로 역할 전환을 테스트한다. 자동 페일오버에는 동기 모드와 FAILOVER_MODE=AUTOMATIC이 필요하다. sys.dm_hadr_availability_replica_states로 상태를 모니터링한다.

SQL Server

SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ag.group_id=ars.group_id;
-- Planned failover (on the node that will become the new primary):
ALTER AVAILABILITY GROUP appag FAILOVER;

DBAtlas에서 열기 →