MSSQL 로그 전달(Log Shipping) — 재해 복구 이중화 구성

# 폴더 생성
New-Item -Path "C:\LogShipBackup" -ItemType Directory

# 공유 (Secondary 서버 컴퓨터 계정 허용)
New-SmbShare -Name "LogShipBackup" -Path "C:\LogShipBackup" `
  -FullAccess "DOMAIN\SQL-Secondary$", "DOMAIN\sql_agent"

-- Primary에서
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'SalesDB';
-- FULL 이어야 함

-- SIMPLE이면 변경
ALTER DATABASE SalesDB SET RECOVERY FULL;

-- 전체 백업 (Log Shipping 초기화 전 필수)
BACKUP DATABASE SalesDB
  TO DISK = '\\SQL-Primary\LogShipBackup\SalesDB_full.bak'
  WITH FORMAT, COMPRESSION;

-- 로그 전달 Primary 등록
EXEC master.dbo.sp_add_log_shipping_primary_database
    @database = N'SalesDB',
    @backup_directory = N'C:\LogShipBackup',
    @backup_share = N'\\SQL-Primary\LogShipBackup',
    @backup_job_name = N'LSBackup_SalesDB',
    @backup_retention_period = 4320,      -- 72시간 (분 단위)
    @backup_threshold = 60,               -- 60분 초과 시 경고
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @backup_job_id = @LS_BackupJobID OUTPUT,
    @primary_id = @LS_PrimaryId OUTPUT;

-- SQL Agent 백업 작업 스케줄 (15분마다)
EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'LSBackup_SalesDB',
    @schedule_name = N'LS_15min';

EXEC master.dbo.sp_add_log_shipping_secondary_primary
    @primary_server = N'SQL-Primary',
    @primary_database = N'SalesDB',
    @backup_source_directory = N'\\SQL-Primary\LogShipBackup',
    @backup_destination_directory = N'C:\LogShipRestore',
    @copy_job_name = N'LSCopy_SQL-Primary_SalesDB',
    @restore_job_name = N'LSRestore_SQL-Primary_SalesDB',
    @file_retention_period = 4320,
    @copy_job_id = @LS_CopyJobID OUTPUT,
    @restore_job_id = @LS_RestoreJobID OUTPUT,
    @secondary_id = @LS_SecondaryId OUTPUT;

EXEC master.dbo.sp_add_log_shipping_secondary_database
    @secondary_database = N'SalesDB',
    @primary_server = N'SQL-Primary',
    @primary_database = N'SalesDB',
    @restore_delay = 0,
    @restore_mode = 1,         -- 1: STANDBY (읽기 가능), 0: NORECOVERY
    @disconnect_users = 0,
    @block_size = 512,
    @buffer_count = 15,
    @max_transfer_size = 1048576,
    @restore_threshold = 45,   -- 45분 초과 시 경고
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @secondary_id = @LS_SecondaryId;

-- Primary에서 Log Shipping 상태 확인
SELECT
    primary_database,
    last_backup_file,
    last_backup_date,
    DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS minutes_since_backup
FROM msdb.dbo.log_shipping_monitor_primary;

-- Secondary에서 복원 상태 확인
SELECT
    secondary_database,
    last_restored_file,
    last_restored_date,
    last_restored_latency,   -- 분 단위 지연
    restore_threshold
FROM msdb.dbo.log_shipping_monitor_secondary;

-- Secondary에서: 마지막 로그까지 복원 후 RECOVERY
RESTORE DATABASE SalesDB WITH RECOVERY;
-- 이후 DB 상태: ONLINE (쓰기 가능)

-- 애플리케이션 연결 문자열을 SQL-Secondary IP로 변경