안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, SQL강좌: 4-4. 데이터베이스 옵션을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 데이터베이스 옵션입니다.

TL;DR

데이터베이스 설정을 확인합니다. 데이터 정렬(Collation), 복구 모델(Recovery Model), 호환성 수준(Compatibility Level) 옵션과 기타 설정에 대해 알아보고 설정합니다.

SQL서버 데이터베이스는 여러 설정 가능한 옵션들이 많습니다. 차근차근 여러 중요 옵션들을 살펴보겠습니다.
데이터베이스 옵션은 데이터베이스 단위로 설정합니다. SSMS에서 데이터베이스를 선택하고, 마우스 우클릭 후 속성을 클릭하세요.

이미지 - 데이터베이스 옵션

데이터베이스 옵션은 이렇게 여러 항목들이 있습니다. 주요 항목들을 소개해 드리겠습니다.

데이터 정렬(Database Collation)

지난 ORDER BY 강좌에서도 잠시 소개해 드렸습니다. Collation은 데이터 정렬 설정입니다. SQL Server 단위 설정, 데이터베이스 단위 설정, 테이블 컬럼 단위 설정도 가능하며, ORDER BY 정렬 구문에서도 사용할 수 있습니다. 

복구 모델(Recovery Model)

복구 모델은 트랜잭션 로그 유지 관리 방안을 결정하는 중요한 설정입니다. 복구 모델로 트랜잭션 로그가 로깅(기록)되는 방법을 설정해, 로그를 백업 할지여부를 제어할 수 있습니다. 복구 모델은 전체(Full)/대량로그(Bulk-logged)/단순(Simple) 모델이 있습니다.

이후 “백업과 복구(Backup과 Restore)” 강좌에서 상세하게 논의합니다. 기본 설정은 “전체(Full)”입니다. AdventureWorks와 같은 예제 데이터베이스는 “단순(Simple)”으로 구성되어 있습니다.

간략히, 
1) 단순 모델은 로그를 기록하지 않습니다. 전체 백업(Full backup)만 복원할 수 있습니다. 
2) 대량로그 모델은 로그가 쌓이기 때문에 주기적으로 로그를 백업해야 합니다. 대량작업(Bulk Insert 등)에 대해 최소 로그를 기록해 로그 기록 양이 비교적 적습니다.
3) 전체 모델은 대량작업도 모두 기록해 로그가 가장 많이 기록됩니다. 지정시간 복구(Restore - stop at)를 지원해 특정 시점까지 복원할 수 있습니다.

예제 데이터베이스나 개인 테스트 용도로는 단순모델도 괜찮습니다. 하지만, 팀의 개발 데이터베이스 서버나 운영 서버는 반드시 전체 복구 모델을 사용하고, 주기적으로 전체 백업과 로그 백업을 수행하세요. 이어지는 백업 강좌를 꼭 참조하시길 바랍니다.

호환성 수준(Compatibility Level)

지정된 SQL Server 엔진과 호환되도록 호환성 수준을 설정할 수 있습니다. SQL Server 2022는 호환성 수준을 SQL2008 (호환성 레벨 값 100)부터 SQL Server 2019(150)까지 설정할 수 있습니다.

호환성 수준을 낮출 경우는 신중하게 선택하세요. 최신 버전은 향상된 쿼리 최적화기와 지능형 쿼리 처리 기능을 사용할 수 있습니다. 레거시 애플리케이션 지원과 같은 특수한 상황이 아니라면 호환성 수준을 항상 최신으로 유지하세요.

현재 데이터베이스의 호환성 수준을 SQL Server 2019로 변경하려면 아래 쿼리로 변경 가능합니다.

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 150

- 호환성 수준 추가 정보: ALTER DATABASE compatibility level (Transact-SQL) - SQL Server | Microsoft Learn 

포함된 데이터베이스(Contained Database) 유형

포함된 데이터베이스는 데이터베이스를 호스팅 하는 SQL Server 인스턴스에서 격리된 데이터베이스입니다. 예를 들어, 데이터베이스에 대한 메타 정보는 모두 master 데이터베이스에 저장되고, 사용자 인증 로그인 정보 등도 master 데이터베이스에 저장됩니다. 포함된 데이터베이스는 이런 데이터베이스 메타 정보나 로그인 인증 정보를 데이터베이스에 포함시켜서 독립적으로 저장하는 기능입니다.

포함된 데이터베이스는 개발 단계에서 데이터베이스를 이동할 경우나, Always On과 같은 고가용성 기능을 구성할 때 유용합니다. 데이터베이스에 인증이나 메타 정보가 포함되어 있으므로, 옮겨도 바로 동작하기 때문에 개발 단계에서 여러 SQL Server 머신으로 옮겨 다녀도 로그인 인증이나 설정 정보가 그대로 이전됩니다. 장애가 발생할 경우에도 포함된 로그인 사용자를 생성하면, 복제본을 실행하는 SQL Server에서도 따로 인증을 구성할 필요가 없습니다. 기본 포함 유형 설정은 없음이며, 부분(Partial)으로 구성할 수 있습니다. 

위의 데이터 정렬, 복구 모델, 호환성 수준 설정은 데이터베이스 생성 시 고려해야 할 사항이며 중요한 설정이니 잘 기억해 두세요. 아래의 설정들은 비교적 적게 사용됩니다.

FILESTREAM 설정

FILESTREAM은 바이너리 파일(이하 Blob) 데이터를 SQL Server에  통합 저장하는 설정입니다.

문서 파일이나 이미지 파일 같은 Blob 파일을 데이터베이스에 저장하려면 어떻게 해야 할까요? Blob 데이터는 데이터베이스에 저장하지 않고 OS가 제공하는 파일시스템에 저장하는 것이 일반적이었습니다. 그러고, 파일의 전체 경로(path)를 데이터베이스에 저장하고, 웹서버나 애플리케이션에서 파일을 요청할 경우 데이터베이스에서 해당 파일의 위치만 쿼리로 가져와 웹서버나 애플리케이션이 실제 파일에 접근하는 형태로 개발했습니다.

편리해 보이기는 하지만, 파일과 DB가 따로 관리되다 보니,
- 파일 중복 및 경로, 파일명 변경 시 데이터베이스 데이터와의 일관성 문제
- Blob 데이터 접근 권한 불일치 문제
- 데이터베이스 백업에 포함되지 않아 Blob 데이터 무결성 추가 관리 비용 발생
- 파일서버/디스크드라이브 추가/변경이나 용량 추가 시 데이터베이스 설계에도 영향
- SQL Server의 Full Text  Search 같은 통합 검색 기능 적용이 어려움

시간이 지나, varbinary(max)나 varchar(max) 데이터형을 사용해 위의 문제들을 일부 해결했습니다. (참고로 text, ntext, image 데이터형은 차기 SQL Server에서 제거되니 사용하지 않습니다.)

SQL Server와 같은 DBMS에 varbinary(max) 데이터형이 등장하면서 테이블에 Blob 데이터를 넣을 수 있게 되었지만, 
- 대용량 데이터베이스 관리 문제
- Blob 데이터를 클라이언트 애플리케이션으로 전송할 때 SQL Server 성능 문제
- Blob에 신속한 데이터 접근이 필요할 때 속도가 저하됨

제한이 있습니다.

FILESTREAM은 파일시스템 저장과 varbinary(max) 저장 문제들을 해결합니다.
- Blob 데이터를 SQL Server가 관리하는 파일 시스템에 저장 
- SQL 구문으로 테이블형 데이터와 FILESTREAM 데이터 조회/삽입/수정/백업 가능
- OS 캐시를 이용하기 때문에, SQL Server 데이터베이스 엔진에 성능 영향이 적음

FILESTREAM을 사용하려면 SQL Server 설정을 변경해야 합니다.
- 시작 메뉴에서 "모든 프로그램" > "Microsoft SQL Server 2022" > "SQL Server 2022 Configuration Manager(구성 도구)" > "SQL Server 구성 관리자" 실행
- 서비스 목록에서 SQL Server 서비스를 마우스 오른쪽 클릭하고 "열기"를 선택
- SQL Server 구성 관리자에서 FILESTREAM을 사용할 SQL Server 인스턴스를 선택
- 해당 인스턴스를 마우스 오른쪽 클릭하고 "속성"을 선택
- 속성 대화 상자에서 "FILESTREAM" 탭을 클릭
- "Transact-SQL 액세스에 FILESTREAM 사용" 확인 선택
- FILESTREAM 데이터를 읽고 쓰기 위해 
파일 I/O 스트리밍 액세스를 사용하는 경우, "파일 I/O 스트리밍 액세스에 FILESTREAM 사용"을 선택하고 Windows 공유 이름을 입력
- 원격 클라이언트가 FILESTREAM 데이터에 액세스해야 하는 경우, "원격 클라이언트가 FILESTREAM 데이터에 대한 스트리밍 액세스를 가질 수 있도록 허용" 선택
- "적용"을 클릭
- SQL Server Management Studio에서 "새 쿼리"를 클릭하여 쿼리 편집기를 엽니다.
- 쿼리 편집기에 Transact-SQL 코드를 실행

EXEC sp_configure filestream_access_level, 2;  
RECONFIGURE;

- SQL Server 서비스를 다시 시작
하면 적용됩니다. FILESTREAM에 대해서는 이후 강좌에서 조금 더 풀어보도록 하겠습니다.

기타 설정

ANSI NULL 기본값

옵션이 ON으로 설정되면, CREATE TABLE 또는 ALTER TABLE 문에서 NOT NULL로 정의되지 않은 모든 사용자 정의 데이터 형식 또는 열에서 Null 값을 허용합니다.

ANSI NULL 설정

NULL 값과 함께 사용할 때 같음(=)과 같지 않음(<>) 비교 연산자의 동작을 지정합니다. 기본은 NULL 비교가 UNKNOWN입니다.

자동 닫기(Auto close)

Auto close 옵션은 데이터베이스에 사용자가 더 이상 없을 경우 데이터베이스를 Close 하며 사용하는 리소스를 반환합니다. 사용자가 접근하게 되면 다시 활성화됩니다.

재귀 트리거(Recursive Triggers)

자기 참조 트리거 설정이며 활성화시킬지 여부를 선택 가능합니다. 이후 트리거 강좌에서 상세히 다루게 됩니다.

데이터베이스 범위 구성(Scoped Configuration)

데이터베이스 수준으로 범위(Scope)를 지정하는 옵션들입니다.

MAX DOP(Degree Of Parallelism)

최대 병렬 처리 수준 설정입니다. 0은 가능한 모든 프로세서를 사용해 병렬 처리를 수행하며, 기본 최대 64개의 프로세서를 사용합니다.

☑️ 챗GPT 활용: DOP(Degree of Parallelism)에 대해서 알려줘

레거시 카디널리티 추정(Legacy cardinality estimate)

쿼리 최적화에 사용되는 카디널리티 설정을 조절할 수 있습니다. SQL Server 2012(데이터베이스 호환성 수준 120) 이상에서, 이전 버전보다 쿼리 수행이 늦어진다면, 카디널리티 추정값 조절을 고려할 수 있습니다. 

☑️ 챗GPT 활용: SQL Server cardinality에 대해서 설명해 줘

복구 설정

체크포인트 - 대상 복구 시간(초)

체크포인트(검사점 - Checkpoint) 실행 주기를 설정합니다. SQL Server 데이터베이스 엔진은 성능을 높이기 위해 데이터 변경을 매번 디스크에 쓰는 IO 작업을 하지 않습니다. 메모리에 버퍼캐시 영역을 생성하고, 메모리 영역에서 처리를 하다가 주기적으로 디스크에 기록합니다. 이 기록 과정이 체크포인트입니다. 체크포인트는 메모리의 수정된 페이지(SQL Server IO 처리 단위)를 디스크에 쓰고, 트렌잭션 로그에도 기록합니다. 

만약, SQL Server 비정상 종료가 발생할 경우, 메모리 버퍼의 내용은 디스크에 기록되지 않았기 때문에 최대 체크포인트 주기(기본 60초) 동안의 데이터 손실이 발생할 수 있습니다. 이 주기를 줄이면 IO가 자주 발생해 CPU 사용량(주로 스파이크)과 초당 디스크 IO가 높아질 수 있고, 주기를 늘리면, 비정상 종료 시 데이터 손실 구간이 늘어날 수 있습니다.

이후 백업과 복원 강좌에서 체크포인트를 더 상세하게 다루게 됩니다.

페이지 확인(Page Verity) - Checksum

중요한 파일을 검사할 때 Checksum을 이용하는 것과 같습니다. SQL Server에서 체크포인트가 실행되면서 버퍼의 내용을 디스크에 쓰게 됩니다. 이때, 페이지 내용을 검증하기 위한 값을 페이지 헤더에 쓰는데, Checksum 값을 같이 저장합니다. Torn page는 페이지 쓰기 작업 중 일부 비트값을 헤더에 기록하는 방식입니다. 일부 비트값만 기록해 검사하기 때문에 모든 오류를 감지하지는 못해 Checksum 방식을 더 많이 사용합니다.

☑️ 챗GPT 활용: Checksum에 대해 설명해 줘

데이터베이스 상태 설정

데이터베이스 읽기 전용(Read only) 설정

데이터베이스 데이터 수정은 불가하고 읽기만 가능해집니다. 데이터베이스를 사용 중에는 읽기 전용으로 설정이 불가능합니다. 만약 데이터베이스 복구 후 읽기 전용 상태로 표시된다면, 윈도 서버의 파일 시스템에 위치한 데이터베이스 폴더(mdf와 ldf 파일이 위치한)가 “읽기”만 설정되어 있는지 확인하세요. 해당 SQL Server 실행 계정은 해당 폴더에 모든 권한이 있어야 합니다. 만약, 데이터베이스 상태가 “대기/읽기 전용(standby/read only)”일 경우에는 복원(Restore)을 할 때 with recovery로 복원을 완료하면 됩니다. 이후 백업과 복원 강좌에서 자세하게 다룹니다.

암호화 사용

SQL Server의 투명한 데이터베이스 암호화(TDE-Transparent Data Encryption) 기능으로 데이터베이스를 암호화합니다.

☑️ 챗GPT 활용: SQL Server TDE-Transparent Data Encryption 기능에 대해서 알려줘

액세스 제한 설정

MULTI_USER는 일반적인 다중 사용자 설정이며, SINGLE_USER는 단일 사용자 설정입니다. RESTRICTED_USER 설정은 db_owner, dbcreator 또는 sysadmin 역할 사용자만 데이터베이스를 사용할 수 있습니다.

원장(Ledger)

원장 데이터베이스(Ledger database)

원장은 타 시스템과 통신 시 데이터 무결성을 보장하는 기능입니다. 금융권에서 주로 사용되는 기능으로, 데이터베이스가 변조되지 않았음을 보장하는 증거를 다른 사용자에게 암호화된 방식으로 증명하는 기능입니다.

☑️ 챗GPT 활용: SQL Server Ledger database(원장 데이터베이스)에 대해서 알려줘

자동 설정

자동 축소(Auto shrink)

데이터베이스를 주기적으로 검사 후 축소 시키는 설정입니다.

통계자동 업데이트(Auto update statistics)

SQL Server는 테이블이나 인덱싱된 뷰(Indexed View) 등에 통계정보를 가지고 있습니다.
SQL 쿼리 최적화기는 통계정보로 쿼리를 실행할 때 어떤 방식으로 쿼리를 수행할지 결정합니다. 이 통계 데이터를 자동 업데이트 할지 여부를 설정합니다. 성능에 중요한 옵션이기 때문에 기본 자동으로 사용합니다. 가끔 성능상의 문제로 꺼두고 주기적으로 스케줄을 걸어 유지관리 계획(Maintenance plan)으로 업데이트하는 게 좋은지 질문하시는 경우도 있는데, SQL Server가 성능에 거의 영향 없이 자동 조절하면서 업데이트를 수행합니다. 가급적 항상 자동으로 설정하세요.

통계 자동 생성(Auto create statistics)

테이블 등을 생성할 때 자동으로 함께 통계 데이터를 생성하는 설정입니다. 역시 디폴트인 체크 상태로 사용하세요. 설정 안 할 경우에는 수작업으로 CREATE STATISTICS 구문을 이용해 생성하고 관리합니다.

커서(Cursor) - 기본 커서 설정

커서는 결과셋을 로우 단위로 처리 가능한 프로그래밍 기능입니다. GLOBAL 커서는 연결되어 실행되는 저장 프로시저(Stored Procedure)나 배치에서 커서명을 참조해 연동할 수 있습니다. LOCAL은 지정된 범위 내에서만 유효합니다. 이후 커서 강좌에서 상세히 진행합니다.

☑️ 챗GPT 활용: 데이터베이스 커서(Cursor)에 대해서 알려줘

커밋 시 커서 닫기 설정(cursor close on commit)

기본값은 False입니다. True로 설정된 경우, 트랜잭션이 커밋되거나 롤백될 때 열려 있던 모든 커서가 종료됩니다. false로 설정된 경우, 트랜잭션이 커밋될 때 해당 커서가 열린 상태로 남게 됩니다. 트랜잭션 설정, 커서, 새로운 용어로 힘드시죠? 이후 커서와 트랜잭션 모두 강좌로 진행됩니다. 지금은 이런 설정이 있다는 정도만 기억해 두세요.

데이터베이스 설정을 쿼리로 확인하고 변경하는 방법

SSMS에서 설정 옵션을 확인/변경할 수 있으며, SQL 쿼리로도 설정을 확인하고 변경할 수 있습니다. 

데이터베이스 설정 확인

데이터베이스 설정 확인은 DATABASEPROPERTYEX 또는 시스템 카탈로그 - sys.database를 이용합니다. 
- DATABASEPROPERTYEX(Transact-SQL) - SQL Server | Microsoft Learn

-- DATABASEPROPERTYEX로 설정 확인
SELECT DATABASEPROPERTYEX('AdventureWorks', 'IsAutoShrink');
GO

--sys.databases로 설정 확인
SELECT database_id, is_read_only, collation_name, compatibility_level  
FROM sys.databases WHERE name = 'AdventureWorks';
GO

이렇게, SQL 쿼리로 데이터베이스의 설정을 확인할 수 있습니다.

데이터베이스 설정 변경

ALTER DATABASE 구문으로 설정 정보를 변경합니다.

USE master;
GO

-- recovery_model 확인
SELECT name, recovery_model_desc FROM sys.databases  
WHERE name = 'AdventureWorks';
GO  

-- recovery_model을 Full로 변경
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO

SELECT name, recovery_model_desc FROM sys.databases  
WHERE name = 'AdventureWorks';
GO  

-- recovery_model을 다시 Simple로 변경
ALTER DATABASE AdventureWorks SET RECOVERY Simple;
GO

이렇게, ALTER 구문을 이용해 데이터베이스 설정을 변경할 수 있습니다.

이번 강좌에서 SQL Server의 데이터베이스 설정 옵션을 살펴보았습니다. 위의 데이터 정렬, 복구 모델, 호환성 수준 설정은 데이터베이스 생성 시 고려해야 할 사항이며 중요한 설정이니 잘 기억해 두세요.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022