transaction log 파일

728x90

트랜잭션 로그 파일

1) 트랜잭션 로그는 정말 SQL 서버에서 매우 중요한 역할을 한다.

[ 트랜잭션 로그]

1. SQL Server 는 변경할 데이터 페이지를 메모리(Buffer cache) 로 가져온 후 메모리의 데이터를 변경한다. 이미 메모리에 있다면 이것을 변경한다.

---- 이 과정에서 하드 파싱과 소프트 파싱이 제대로 나뉘는 것 같다.

2. 메모리의 데이터를 변경한 파일을 이제 로그 파일에 기록한다. 데이터 파일에 기록하기 전에 로그부터 기록한다 하여 미리쓰기 (Write-ahead log) 라 한다.

- 왜 write ahead log 일까???

3. 체크포인트 프로세스가 반복적으로 수행되면서 데이터파일과 메모리 내용이 다른 데이터 페이지를 데이터 파일에 반영한다.

4. 만일 시스템이 재시작되면, 로그 파일에 기록된 내용을 근거로 하여 완료된 트랜잭션은 롤 포워드 부분에 대해서 이제 확인할 수 잇도록 한다.

1. 데이터베이스 백업 및 복구 전략

백업

정기적인 백업 수행: 데이터 손실을 방지하기 위해 데이터베이스와 트랜잭션 로그를 주기적으로 백업해야 합니다.
- 전체 백업(Full Backup): 모든 데이터베이스 데이터를 백업.
- 차등 백업(Differential Backup): 마지막 전체 백업 이후 변경된 데이터만 백업.
- 트랜잭션 로그 백업(Transaction Log Backup): 트랜잭션 로그의 데이터 백업으로 복구 지점 제어 가능.
백업 보관 위치: 백업 파일을 로컬, 네트워크 공유, 클라우드 등에 분산하여 저장.

복구

복구 테스트를 주기적으로 수행해 데이터 손실 상황에서도 복구 가능성을 보장합니다.
데이터베이스 복구 모델을 비즈니스 요구에 맞게 설정합니다(예: FULL, SIMPLE, BULK_LOGGED).

2. 성능 최적화

쿼리 및 인덱스 최적화

쿼리 성능 튜닝: 느린 쿼리나 비효율적인 쿼리를 분석하고 최적화.
- Execution Plan으로 쿼리 성능 병목을 파악.
- 잘못된 인덱스 사용 또는 없는 인덱스를 보완.
인덱스 관리:
- 정기적인 인덱스 재구성(Rebuild) 또는 재조정(Reorganize).
- 중복된 인덱스를 제거하고, 필요한 인덱스를 추가.

리소스 모니터링

CPU, 메모리, 디스크 I/O 사용량을 지속적으로 모니터링.
쿼리 캐시 관리: 쿼리 계획 캐시를 분석하여 불필요한 캐시가 축적되지 않도록 관리.
데드락 방지:
- 데드락 발생 쿼리를 분석하고 트랜잭션 잠금 전략을 개선.

데이터 파일 및 로그 파일 관리

데이터 및 트랜잭션 로그 파일의 크기를 적절히 설정하고 자동 성장(Autogrowth) 옵션을 최적화.
VLF(가상 로그 파일)의 수를 최소화하여 로그 파일 성능을 최적화.

3. 모니터링 및 경고 시스템

실시간 모니터링

SQL Server Management Studio(SSMS) Activity Monitor 또는 **Dynamic Management Views(DMV)**를 사용해 시스템 상태를 모니터링.
주요 DMV:

sql

복사편집

-- 실행 중인 요청 확인 SELECT * FROM sys.dm_exec_requests; -- 대기 통계 확인 SELECT * FROM sys.dm_os_wait_stats;

경고 설정

SQL Server Agent를 통해 이벤트 경고 및 알림을 구성하여 문제를 사전에 감지.
예: 디스크 공간 부족, CPU 사용률 초과, 장시간 실행되는 쿼리 경고.

4. 보안 강화

인증 및 권한

원격 액세스 제한: 필요하지 않은 경우 SQL Server 원격 연결을 비활성화.
권한 최소화: 원칙적으로 최소 권한을 부여하여 데이터베이스 객체를 보호.
사용자 인증: Windows 인증 모드를 사용하는 것이 SQL Server 인증보다 더 안전함.

데이터 암호화

TDE(Transparent Data Encryption): 데이터 파일과 로그 파일을 암호화하여 저장.
TLS를 사용하여 네트워크 통신 보안을 강화.

감사 및 로깅

데이터 액세스와 변경 사항을 SQL Server Audit로 기록하여 보안 이벤트를 추적.
로그인 실패와 같은 보안 이벤트를 적극적으로 모니터링.

5. 유지보수 및 정기 점검

통계 및 인덱스 업데이트

통계 업데이트:
- 데이터 변경이 많은 테이블의 통계는 최신 상태로 유지해야 쿼리 최적화가 가능.
sql

복사편집

UPDATE STATISTICS TableName;

데이터 정리 및 아카이빙

오래된 데이터는 주기적으로 아카이빙하거나 제거하여 데이터베이스 크기를 관리.

6. 데이터베이스 설계 최적화

정규화 및 비정규화

데이터 무결성을 유지하면서 과도한 조인을 피하기 위해 설계를 최적화.
적절한 비정규화로 성능 향상을 고려.

파티셔닝

대용량 데이터 테이블은 파티셔닝하여 관리 성능을 향상.
- 테이블 파티셔닝: 데이터 액세스를 분산하고 I/O 병목을 줄임.

7. 장애 대비

고가용성(HADR) 구현

Always On 가용성 그룹: 데이터베이스의 고가용성과 무중단 복구를 보장.
로그 전달(Log Shipping) 또는 **미러링(Database Mirroring)**으로 장애 복구 준비.