운영과 배포: 백업·복구, 고가용성, 스키마 마이그레이션

애플리케이션 코드와 쿼리가 아무리 정교하게 최적화되어 있어도, 운영 환경에서 데이터가 유실되거나 서비스가 중단되는 순간 그 모든 노력은 빛을 잃습니다. SQL Server는 강력한 복구 모델과 고가용성 아키텍처를 제공하지만, 이를 올바르게 설계하고 자동화하지 않으면 장애 상황에서 속수무책이 됩니다.

이 레슨은 심화 과정의 마지막을 장식하는 '운영/배포' 편입니다. 입문 과정에서 다룬 트랜잭션·잠금의 개념 위에서, 트랜잭션 로그가 백업과 복구에 어떻게 연결되는지, 고가용성 솔루션은 어떤 원리로 동작하는지, 그리고 운영 중인 서비스에 스키마 변경을 안전하게 배포하는 패턴까지 체계적으로 살펴봅니다.

학습 목표

• **복구 모델(Recovery Model)**의 세 가지 유형과 트랜잭션 로그 관리 방식의 차이를 설명할 수 있다.
• 전체·차등·로그 백업을 조합한 복구 전략을 설계하고, **Point-in-Time Recovery(PITR)**를 실행할 수 있다.
• **Always On 가용성 그룹(Availability Groups)**의 동기/비동기 복제 방식과 자동 페일오버 메커니즘을 이해한다.
• 버전 관리 기반 스키마 마이그레이션과 무중단 배포 패턴(확장-수축)을 적용할 수 있다.
• SQL Agent 잡으로 유지보수 작업을 자동화하고, DMV와 알림을 활용한 운영 모니터링 체계를 구축한다.

복구 모델과 트랜잭션 로그 동작

SQL Server의 복구 모델은 트랜잭션 로그를 어떻게 관리하느냐를 결정하며, 이것이 백업 전략 전체의 출발점입니다.

세 가지 복구 모델 비교

복구 모델	로그 잘라내기(Truncation)	로그 백업 가능	시점 복구 가능	적합한 환경
Simple	체크포인트마다 자동	불가	불가	개발·테스트, 재생성 가능한 데이터
Full	로그 백업 시에만	가능	가능	운영 환경, 무손실 복구 필요 시
Bulk-logged	로그 백업 시에만	가능	제한적	대량 데이터 로드 기간 일시 적용

-- 현재 데이터베이스의 복구 모델 확인
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = DB_NAME();

-- 복구 모델 변경 (운영 DB는 반드시 Full 권장)
ALTER DATABASE MyAppDB SET RECOVERY FULL;

-- 로그 파일 현황 확인 (log_reuse_wait_desc 주목)
DBCC SQLPERF(LOGSPACE);

-- 파일 크기(size)는 sys.databases에 없고 sys.master_files에 있으므로 조인해서 조회
SELECT
    mf.name,
    mf.size * 8.0 / 1024  AS log_size_mb,   -- size는 8KB 페이지 단위
    d.log_reuse_wait_desc
FROM sys.master_files mf
JOIN sys.databases d ON mf.database_id = d.database_id
WHERE mf.type_desc = 'LOG'
  AND d.name = DB_NAME();

⚠️ 주의 Simple 복구 모델에서 Full로 전환한 직후에는 즉시 전체 백업(Full Backup)을 수행해야 합니다. 전체 백업이 없으면 로그 백업 체인이 성립하지 않아 시점 복구가 불가능합니다.

트랜잭션 로그가 꽉 차는 이유

Full 복구 모델에서 로그 백업을 정기적으로 실행하지 않으면 트랜잭션 로그가 계속 증가합니다. log_reuse_wait_desc 값이 LOG_BACKUP이면 로그 백업이 밀려 있다는 신호입니다.

-- 로그 백업이 밀린 경우 즉시 로그 백업으로 공간 확보
BACKUP LOG MyAppDB
TO DISK = 'D:\Backups\MyAppDB_log_emergency.bak'
WITH COMPRESSION, STATS = 10;

-- 이후 로그 공간 재확인
DBCC SQLPERF(LOGSPACE);

백업 전략과 시점 복구(PITR)

전체·차등·로그 백업의 역할

[전체 백업] → [차등 백업] → [로그 백업1] → [로그 백업2] → [장애 발생]
    일요일        수요일         매 15분          매 15분

• 전체 백업(Full Backup): 데이터베이스 전체를 복사. 복구의 기준점(베이스라인).
• 차등 백업(Differential Backup): 마지막 전체 백업 이후 변경된 익스텐트만 저장. 복구 시 전체 + 최신 차등 1개만 필요.
• 로그 백업(Log Backup): Full/Bulk-logged 모델에서만 가능. 시점 복구의 핵심.

-- 전체 백업
BACKUP DATABASE MyAppDB
TO DISK = 'D:\Backups\MyAppDB_full_20260608.bak'
WITH COMPRESSION,           -- 백업 파일 압축
     CHECKSUM,              -- 백업 무결성 검증
     STATS = 10;            -- 10% 단위로 진행률 출력

-- 차등 백업
BACKUP DATABASE MyAppDB
TO DISK = 'D:\Backups\MyAppDB_diff_20260608_1800.bak'
WITH DIFFERENTIAL,
     COMPRESSION,
     CHECKSUM,
     STATS = 10;

-- 트랜잭션 로그 백업
BACKUP LOG MyAppDB
TO DISK = 'D:\Backups\MyAppDB_log_20260608_183000.bak'
WITH COMPRESSION,
     CHECKSUM,
     STATS = 10;

💡 TIP WITH COPY_ONLY 옵션을 붙이면 백업 체인에 영향을 주지 않는 독립 백업을 만들 수 있습니다. 운영 스냅샷을 개발 환경에 복사할 때 유용합니다.

시점 복구(Point-in-Time Recovery)

특정 시각(예: 실수로 대량 DELETE가 발생하기 1분 전)으로 데이터베이스를 되돌리는 절차입니다.

-- 1단계: 현재의 비상 로그 백업 (tail-log backup) — 장애 직후 실행
BACKUP LOG MyAppDB
TO DISK = 'D:\Backups\MyAppDB_log_taillog.bak'
WITH NORECOVERY,   -- 복원 체인 유지를 위해 필수
     COMPRESSION,
     CHECKSUM;

-- 2단계: 전체 백업 복원 (WITH NORECOVERY 유지)
RESTORE DATABASE MyAppDB
FROM DISK = 'D:\Backups\MyAppDB_full_20260608.bak'
WITH NORECOVERY,
     MOVE 'MyAppDB' TO 'D:\Data\MyAppDB.mdf',
     MOVE 'MyAppDB_log' TO 'D:\Data\MyAppDB_log.ldf',
     STATS = 10;

-- 3단계: 차등 백업 복원 (있는 경우)
RESTORE DATABASE MyAppDB
FROM DISK = 'D:\Backups\MyAppDB_diff_20260608_1800.bak'
WITH NORECOVERY,
     STATS = 10;

-- 4단계: 로그 백업들을 순서대로 적용, 마지막에 STOPAT으로 시점 지정
RESTORE LOG MyAppDB
FROM DISK = 'D:\Backups\MyAppDB_log_20260608_183000.bak'
WITH NORECOVERY,
     STOPAT = '2026-06-08T18:44:00';  -- 문제 발생 직전 시각

-- 5단계: 복구 완료 (데이터베이스를 온라인 상태로 전환)
RESTORE DATABASE MyAppDB WITH RECOVERY;

백업 유효성 검증

백업 파일이 존재해도 복원이 실패하면 의미가 없습니다. 정기적으로 복원 테스트를 자동화해야 합니다.

-- 백업 파일 헤더 정보 확인 (실제 복원 없이 메타데이터 검증)
RESTORE HEADERONLY
FROM DISK = 'D:\Backups\MyAppDB_full_20260608.bak';

-- 백업 파일 무결성 검증 (CHECKSUM 옵션으로 생성된 경우)
RESTORE VERIFYONLY
FROM DISK = 'D:\Backups\MyAppDB_full_20260608.bak'
WITH CHECKSUM;

Always On 가용성 그룹과 고가용성

SQL Server의 대표적인 고가용성(HA) 솔루션을 비교한 뒤, 현재 표준인 Always On 가용성 그룹(AG)을 중심으로 살펴봅니다.

고가용성 솔루션 비교

솔루션	RPO	RTO	읽기 오프로드	적합한 규모
Always On AG	0~수 초	수십 초	가능 (보조 복제본)	엔터프라이즈
로그 전달(Log Shipping)	수 분~수십 분	수 분 이상	제한적(STANDBY)	중소형, 저예산
데이터베이스 미러링	~0	자동(주 모드)	불가	레거시(deprecated, 향후 제거 예정)
장애 조치 클러스터(FCI)	0	수십 초	불가	인스턴스 수준 HA

• RPO(Recovery Point Objective): 장애 발생 시 허용 가능한 최대 데이터 손실 시간
• RTO(Recovery Time Objective): 서비스 복구까지 허용 가능한 최대 시간

ℹ️ 참고 데이터베이스 미러링은 SQL Server 2012부터 사용 중단(deprecated)으로 예고된 기능이지만, SQL Server 2022에서도 여전히 존재하며 정상 동작합니다(아직 제거되지 않음). 다만 신규 구축에는 Always On 가용성 그룹(AG)을 사용하는 것이 권장됩니다.

Always On 가용성 그룹 동작 원리

AG는 하나의 주 복제본(Primary Replica)과 최대 8개의 보조 복제본(Secondary Replica)으로 구성됩니다. 트랜잭션은 주 복제본에서만 커밋되며, 로그 레코드가 보조 복제본으로 전송됩니다.

애플리케이션
    │
    ▼ (AG Listener 접속)
┌─────────────┐    로그 스트림    ┌─────────────────┐
│  Primary    │ ───────────────► │  Secondary (동기) │ (자동 페일오버)
│  Replica    │                  └─────────────────┘
│             │ ───────────────► ┌─────────────────┐
└─────────────┘    로그 스트림    │  Secondary (비동기)│ (수동 페일오버)
                                 └─────────────────┘

동기 커밋(Synchronous Commit): 보조 복제본이 로그를 수신하고 경화(Harden)한 뒤에야 주 복제본이 커밋 확인. RPO=0이지만 네트워크 지연 영향을 받음.

비동기 커밋(Asynchronous Commit): 주 복제본이 즉시 커밋 확인 후 로그를 비동기 전송. 성능 우선이지만 최소한의 데이터 손실 가능성 존재.

-- 가용성 그룹 상태 확인
SELECT
    ag.name                             AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.synchronization_health_desc,
    ars.connected_state_desc,
    ars.operational_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
    ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars
    ON ar.replica_id = ars.replica_id;

-- 데이터베이스별 동기화 지연(로그 전송 지연) 확인
SELECT
    db_name(drs.database_id)            AS db_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size   / 1024.0  AS log_send_queue_mb,
    drs.log_send_rate         / 1024.0  AS log_send_rate_mb_sec,
    drs.redo_queue_size       / 1024.0  AS redo_queue_mb,
    drs.redo_rate             / 1024.0  AS redo_rate_mb_sec,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
WHERE drs.is_primary_replica = 0;

⚠️ 주의 AG Listener에 연결하지 않고 직접 서버 이름으로 연결하면 페일오버 시 애플리케이션이 자동으로 새 Primary를 찾지 못합니다. 연결 문자열에는 반드시 Server=<ListenerName> 과 MultiSubnetFailover=True를 사용하세요.

로그 전달(Log Shipping) — 저비용 DR 구성

AG를 구성할 수 없는 환경에서 로그 전달은 간단하고 검증된 대안입니다.

-- 로그 전달 상태 모니터링 (주 서버에서 실행)
SELECT
    primary_database,
    last_backup_date,
    last_backup_file,
    backup_threshold,       -- 백업 간격 임계값 (분)
    DATEDIFF(MINUTE, last_backup_date, GETDATE()) AS minutes_since_backup
FROM msdb.dbo.log_shipping_monitor_primary;

-- 보조 서버에서 복원 지연 확인
SELECT
    secondary_database,
    last_restored_date,
    last_restored_file,
    restore_threshold,
    DATEDIFF(MINUTE, last_restored_date, GETDATE()) AS minutes_since_restore
FROM msdb.dbo.log_shipping_monitor_secondary;

버전 관리 기반 스키마 마이그레이션과 무중단 배포

운영 중인 데이터베이스에 스키마 변경을 적용하는 것은 위험한 작업입니다. 잘못된 순서의 마이그레이션은 서비스 중단, 데이터 손실, 롤백 불가 상황을 유발합니다.

스키마 마이그레이션 도구와 버전 관리

버전 번호나 타임스탬프로 각 마이그레이션 스크립트를 식별하고, 적용 이력을 별도 테이블에 관리합니다. Flyway, Liquibase, DbUp 등의 도구가 이 원칙을 자동화합니다.

-- 마이그레이션 이력 테이블 예시 (DbUp/Flyway 유사 구조)
CREATE TABLE dbo.schema_versions (
    id              INT IDENTITY PRIMARY KEY,
    version         VARCHAR(50)  NOT NULL UNIQUE,   -- 예: 'V20260608_001'
    description     NVARCHAR(200) NOT NULL,
    script_name     NVARCHAR(300) NOT NULL,
    applied_on      DATETIME2    NOT NULL DEFAULT SYSUTCDATETIME(),
    applied_by      NVARCHAR(100) NOT NULL DEFAULT SUSER_SNAME(),
    checksum        VARBINARY(32) NOT NULL          -- 스크립트 해시
);

-- 특정 버전 이후로만 실행되는 멱등(idempotent) 마이그레이션 패턴
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.Orders')
      AND name = 'ShippingRegion'
)
BEGIN
    ALTER TABLE dbo.Orders ADD ShippingRegion NVARCHAR(50) NULL;
    PRINT 'Column ShippingRegion added.';
END
ELSE
    PRINT 'Column ShippingRegion already exists, skipping.';

무중단 배포: 확장-수축(Expand-Contract) 패턴

컬럼 이름 변경이나 타입 변경 같은 파괴적(breaking) 변경을 무중단으로 처리하는 표준 패턴입니다.

Phase 1 (Expand):   기존 컬럼 유지 + 새 컬럼 추가, 애플리케이션이 양쪽 모두 쓰기
Phase 2 (Migrate):  기존 데이터를 새 컬럼으로 복사
Phase 3 (Contract): 기존 컬럼 제거, 애플리케이션이 새 컬럼만 사용

-- Phase 1: 새 컬럼 추가 (기존 컬럼 CustomerName은 그대로 유지)
ALTER TABLE dbo.Customers
ADD FullName NVARCHAR(200) NULL;
GO

-- Phase 2: 기존 데이터 마이그레이션 (대용량이면 배치 처리)
DECLARE @BatchSize INT = 5000;
DECLARE @Updated INT = 1;

WHILE @Updated > 0
BEGIN
    UPDATE TOP (@BatchSize) dbo.Customers
    SET FullName = CustomerName
    WHERE FullName IS NULL;

    SET @Updated = @@ROWCOUNT;
    WAITFOR DELAY '00:00:00.1';   -- 부하 분산
END
GO

-- NOT NULL 제약 추가 (데이터가 모두 채워진 후)
ALTER TABLE dbo.Customers
ALTER COLUMN FullName NVARCHAR(200) NOT NULL;
GO

-- Phase 3: 기존 컬럼 제거 (애플리케이션 배포 완료 후)
ALTER TABLE dbo.Customers
DROP COLUMN CustomerName;
GO

💡 TIP 인덱스 추가처럼 오래 걸리는 DDL에는 WITH (ONLINE = ON) 옵션을 사용하면 테이블 잠금 없이 인덱스를 생성할 수 있습니다(Enterprise Edition).

-- ✅ 온라인 인덱스 생성 (Enterprise Edition)
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID)
WITH (ONLINE = ON, MAXDOP = 4);

-- ❌ 기본 생성은 테이블에 Sch-M 잠금 발생 (운영 중 위험)
CREATE INDEX IX_Orders_CustomerID
ON dbo.Orders (CustomerID);

보안: 최소 권한·역할·행 수준 보안과 암호화

운영 환경에서 보안은 사후 대책이 아닌 설계 단계부터 내재화해야 합니다.

최소 권한 원칙과 역할 설계

-- 애플리케이션 전용 로그인 생성 (sa나 sysadmin 절대 사용 금지)
CREATE LOGIN AppUser_Prod
WITH PASSWORD = N'복잡한_패스워드_여기';
GO

USE MyAppDB;
GO

CREATE USER AppUser FOR LOGIN AppUser_Prod;
GO

-- ✅ 최소 권한: 필요한 테이블/뷰에만 명시적 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Orders     TO AppUser;
GRANT SELECT                          ON dbo.Products   TO AppUser;
GRANT EXECUTE                         ON dbo.usp_CreateOrder TO AppUser;
GO

-- ❌ 과도한 권한 (운영 계정에 db_owner 부여)
-- EXEC sp_addrolemember 'db_owner', 'AppUser';

행 수준 보안(Row-Level Security)

테넌트별 데이터 격리나 담당자별 데이터 접근 제한에 사용합니다.

-- 보안 조건자 함수 생성
CREATE FUNCTION dbo.fn_SecurityPredicate(@TenantID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS result
    WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT)
       OR IS_MEMBER('db_owner') = 1;   -- 관리자는 모든 행 접근 허용
GO

-- 보안 정책 생성 및 적용
CREATE SECURITY POLICY TenantIsolationPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(TenantID) ON dbo.Orders,
ADD BLOCK  PREDICATE dbo.fn_SecurityPredicate(TenantID) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
GO

-- 애플리케이션 연결 시 세션 컨텍스트 설정
EXEC sp_set_session_context N'TenantID', 42;
-- 이후 SELECT * FROM dbo.Orders 는 TenantID=42인 행만 반환

투명한 데이터 암호화(TDE)와 Always Encrypted 개요

-- TDE 활성화 여부 확인
SELECT name, is_encrypted
FROM sys.databases
WHERE is_encrypted = 1;

-- TDE는 데이터 파일·로그 파일·백업 파일을 자동 암호화
-- (활성화 절차: 마스터 키 → 인증서 → 데이터베이스 암호화 키 순서)

-- 민감 컬럼 암호화 현황 확인 (Always Encrypted)
SELECT
    c.name           AS column_name,
    c.encryption_type_desc,
    c.encryption_algorithm_name,
    c.column_encryption_key_id
FROM sys.columns c
WHERE c.encryption_type IS NOT NULL;

⚠️ 주의 TDE는 저장소 수준 암호화로 SQL Server 내에서의 쿼리는 평문으로 처리됩니다. 메모리 내 데이터나 네트워크 구간 암호화가 필요하다면 Always Encrypted나 TLS 설정을 별도로 고려하세요.

유지보수 자동화(SQL Agent)와 운영 모니터링

SQL Agent 잡으로 정기 작업 자동화

수동 유지보수는 휴먼 에러와 누락의 온상입니다. SQL Server Agent를 사용해 백업, 통계 업데이트, 인덱스 재구성을 자동화합니다.

USE msdb;
GO

-- 야간 전체 백업 잡 생성
EXEC sp_add_job
    @job_name = N'Daily_Full_Backup_MyAppDB';

EXEC sp_add_jobstep
    @job_name   = N'Daily_Full_Backup_MyAppDB',
    @step_name  = N'Run Full Backup',
    @subsystem  = N'TSQL',
    @command    = N'
DECLARE @Path NVARCHAR(500) =
    N''D:\Backups\MyAppDB_full_'' +
    FORMAT(GETDATE(), ''yyyyMMdd'') + N''.bak'';
BACKUP DATABASE MyAppDB
TO DISK = @Path
WITH COMPRESSION, CHECKSUM, STATS = 10;',
    @database_name = N'MyAppDB',
    @on_success_action = 1,   -- 다음 스텝으로
    @on_fail_action    = 2;   -- 잡 중단

-- 매일 새벽 1시 스케줄 등록
EXEC sp_add_schedule
    @schedule_name      = N'Daily_1AM',
    @freq_type          = 4,      -- 매일
    @freq_interval      = 1,
    @active_start_time  = 010000; -- 01:00:00

EXEC sp_attach_schedule
    @job_name      = N'Daily_Full_Backup_MyAppDB',
    @schedule_name = N'Daily_1AM';

EXEC sp_add_jobserver
    @job_name = N'Daily_Full_Backup_MyAppDB';
GO

인덱스 단편화 관리 자동화

-- 단편화 수준에 따라 REBUILD/REORGANIZE 자동 선택
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CASE
    WHEN avg_fragmentation_in_percent >= 30
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) +
             N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) +
             N'.' + QUOTENAME(o.name) +
             N' REBUILD WITH (ONLINE = ON, MAXDOP = 4);' + CHAR(13)
    WHEN avg_fragmentation_in_percent >= 10
        THEN N'ALTER INDEX ' + QUOTENAME(i.name) +
             N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) +
             N'.' + QUOTENAME(o.name) +
             N' REORGANIZE;' + CHAR(13)
    ELSE N''
END
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes      i ON ips.object_id = i.object_id
                        AND ips.index_id  = i.index_id
JOIN sys.objects      o ON i.object_id   = o.object_id
WHERE ips.avg_fragmentation_in_percent >= 10
  AND ips.page_count > 1000     -- 소규모 인덱스 제외
  AND i.type > 0;               -- 힙 제외

IF LEN(@sql) > 0
    EXEC sp_executesql @sql;

운영 모니터링: 핵심 DMV 대시보드

-- 1. 장기 실행 쿼리 감지 (10초 이상)
SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.wait_time        / 1000.0 AS wait_sec,
    r.total_elapsed_time / 1000.0 AS elapsed_sec,
    r.cpu_time         / 1000.0 AS cpu_sec,
    r.logical_reads,
    DB_NAME(r.database_id)      AS db_name,
    SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(t.text)
            ELSE r.statement_end_offset END
          - r.statement_start_offset) / 2) + 1) AS current_sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50             -- 시스템 세션 제외
  AND r.total_elapsed_time > 10000  -- 10초 이상
ORDER BY r.total_elapsed_time DESC;

-- 2. 블로킹 체인 확인
SELECT
    blocking_session_id AS blocker,
    session_id          AS blocked,
    wait_type,
    wait_time / 1000.0  AS wait_sec,
    DB_NAME(database_id) AS db_name
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
ORDER BY wait_time DESC;

-- 3. 디스크 여유 공간 경고 (SQL Agent 잡으로 주기적 실행)
-- 데이터/로그 등 모든 파일이 위치한 볼륨을 점검 (백업 드라이브가 다른 경우도 포함)
SELECT DISTINCT
    vs.volume_mount_point,
    vs.total_bytes    / 1073741824.0 AS total_gb,
    vs.available_bytes / 1073741824.0 AS free_gb,
    CAST(vs.available_bytes * 100.0 / vs.total_bytes AS DECIMAL(5,1)) AS free_pct
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
WHERE vs.available_bytes * 100.0 / vs.total_bytes < 20;  -- 여유 공간 20% 미만

Database Mail로 알림 발송

-- Database Mail로 임계값 초과 시 알림 (SQL Agent 잡 스텝에 포함)
IF EXISTS (
    SELECT 1
    FROM sys.master_files mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
    WHERE vs.available_bytes * 100.0 / vs.total_bytes < 20
)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name  = N'OpsMailProfile',
        @recipients    = N'dba-team@company.com',
        @subject       = N'[경고] SQL Server 디스크 여유 공간 부족',
        @body          = N'데이터 볼륨의 여유 공간이 20% 미만으로 떨어졌습니다. 즉시 확인하세요.',
        @importance    = N'High';
END

요약

• 복구 모델은 트랜잭션 로그 관리 방식을 결정한다. 운영 환경은 Full 모델을 사용하고, 로그 백업을 정기적으로 수행해야 로그 비대화를 막을 수 있다.
• 백업 체인(전체 → 차등 → 로그)을 올바르게 구성해야 시점 복구(PITR)가 가능하다. 복원 테스트를 자동화해 백업의 유효성을 주기적으로 검증해야 한다.
• Always On 가용성 그룹은 동기/비동기 복제와 자동 페일오버로 RPO=0에 가까운 HA를 제공한다. 애플리케이션은 서버 이름이 아닌 AG Listener에 MultiSubnetFailover=True로 연결해야 한다.
• 확장-수축(Expand-Contract) 패턴은 파괴적 스키마 변경을 무중단으로 처리하는 핵심 패턴이다. ONLINE = ON 인덱스 옵션과 함께 활용한다.
• **행 수준 보안(RLS)**과 최소 권한 원칙으로 애플리케이션 계정의 접근 범위를 엄격히 제한해야 한다.
• SQL Agent 잡으로 백업·인덱스 관리·통계 업데이트를 자동화하고, DMV와 Database Mail 알림으로 장애를 선제적으로 감지하는 운영 체계를 갖춰야 한다.

연습문제

1. 현재 SalesDB 데이터베이스는 Simple 복구 모델로 운영 중입니다. Full 복구 모델로 전환하고, 이후 즉시 수행해야 할 작업과 그 이유를 SQL과 함께 설명하세요.

2. dbo.Employees 테이블의 Email 컬럼을 ContactEmail로 이름을 변경해야 합니다. 서비스 중단 없이 배포하는 확장-수축 패턴의 3단계 SQL 스크립트를 작성하세요.

3. 다음 쿼리는 현재 실행 중인 세션 중 30초 이상 경과한 쿼리와 그 쿼리가 블로킹하는 세션 ID를 함께 출력해야 합니다. 빈칸을 채워 완성하세요.

SELECT
    r.session_id,
    r.total_elapsed_time / 1000.0 AS elapsed_sec,
    r.blocking_session_id,
    -- (A): 현재 실행 중인 SQL 텍스트를 추출하는 표현식
    ___________
FROM sys.dm_exec_requests r
-- (B): SQL 텍스트를 가져오는 CROSS APPLY
___________
WHERE r.session_id > 50
  AND r.total_elapsed_time > __________;   -- (C): 30초 조건

4. SQL Agent 잡 스텝에서 매시간 AppDB의 로그 백업을 E:\Backups\ 경로에 AppDB_log_YYYYMMDD_HH.bak 형식으로 저장하는 T-SQL을 작성하세요.

힌트 FORMAT(GETDATE(), 'yyyyMMdd_HH')로 날짜·시간 포맷 문자열을 생성할 수 있습니다.