MSSQL Always On 가용성 그룹 — SQL Server HA 이중화 구성

# 장애 조치 클러스터링 기능 설치
Install-WindowsFeature Failover-Clustering -IncludeManagementTools

# 사전 검사
Test-Cluster -Node SQL1, SQL2, SQL3

# 클러스터 생성 (SQL1에서)
New-Cluster -Name "SQLCluster" -Node SQL1,SQL2,SQL3 -StaticAddress 192.168.1.30

Enable-SqlAlwaysOn -ServerInstance "SQL1" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL2" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL3" -Force

-- SQL1, SQL2, SQL3 각각 실행
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

-- 서비스 계정에 CONNECT 권한 부여
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\sql_service];

-- 복구 모드 FULL 필수
ALTER DATABASE [SalesDB] SET RECOVERY FULL;

-- 전체 백업 (Secondary로 복원에 사용)
BACKUP DATABASE [SalesDB]
  TO DISK = '\\fileserver\backup\SalesDB.bak'
  WITH FORMAT, INIT;

BACKUP LOG [SalesDB]
  TO DISK = '\\fileserver\backup\SalesDB_log.bak';

-- SQL2, SQL3에서 실행 (NORECOVERY 필수)
RESTORE DATABASE [SalesDB]
  FROM DISK = '\\fileserver\backup\SalesDB.bak'
  WITH NORECOVERY, MOVE 'SalesDB' TO 'C:\Data\SalesDB.mdf',
       MOVE 'SalesDB_log' TO 'C:\Data\SalesDB_log.ldf';

RESTORE LOG [SalesDB]
  FROM DISK = '\\fileserver\backup\SalesDB_log.bak'
  WITH NORECOVERY;

CREATE AVAILABILITY GROUP [AG_Sales]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000
)
FOR DATABASE [SalesDB]
REPLICA ON
  'SQL1' WITH (
    ENDPOINT_URL = 'TCP://SQL1.domain.local:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
  ),
  'SQL2' WITH (
    ENDPOINT_URL = 'TCP://SQL2.domain.local:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)   -- 읽기 오프로드
  ),
  'SQL3' WITH (
    ENDPOINT_URL = 'TCP://SQL3.domain.local:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,         -- DR 사이트
    BACKUP_PRIORITY = 60,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
  );

-- SQL2에서
ALTER AVAILABILITY GROUP [AG_Sales] JOIN;
ALTER DATABASE [SalesDB] SET HADR AVAILABILITY GROUP = [AG_Sales];

-- SQL3에서 동일하게 실행
ALTER AVAILABILITY GROUP [AG_Sales] JOIN;
ALTER DATABASE [SalesDB] SET HADR AVAILABILITY GROUP = [AG_Sales];

-- Primary에서
ALTER AVAILABILITY GROUP [AG_Sales]
ADD LISTENER 'AG_Sales_Listener' (
    WITH IP ((N'192.168.1.50', N'255.255.255.0')),
    PORT = 1433
);

-- 복제 상태
SELECT ag.name, ars.role_desc, ard.synchronization_state_desc,
       ard.synchronization_health_desc, ard.log_send_queue_size,
       ard.redo_queue_size
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 ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states ard ON ard.replica_id = ars.replica_id;

-- 페일오버 준비 상태
SELECT * FROM sys.dm_hadr_availability_group_states;

-- 새 Primary로 지정할 Secondary에서 실행
ALTER AVAILABILITY GROUP [AG_Sales] FAILOVER;

-- Primary에서 라우팅 URL 설정
ALTER AVAILABILITY GROUP [AG_Sales]
MODIFY REPLICA ON 'SQL1' WITH (
    PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SQL2','SQL3'))
);

ALTER AVAILABILITY GROUP [AG_Sales]
MODIFY REPLICA ON 'SQL2' WITH (
    SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL2.domain.local:1433')
);