SQL Server 2008 데이터압축및암호화 에이디컨설팅젂형철
이주제를이해하는데필요한지식 SQL Server Administration SQL Server 데이터저장구조 100 개념및 소개수준 200 중간수준 300 고급수준 400 전문가 수준 200 에 100 에 더하여 선수지식 더하여 능숙한사용 Level 300 불필요 기술적세부사항설명 경험, 아키텍처지식필요
강사소개 2006. 9 ~ : 에이디컨설팅 컨설턴트 (SQL Server) 2005.12 ~ 2006. 8: CJ 인터넷 DBA 실장 (SQL Server) 2000. 9 ~ 2005.11: 옥션 DBA, DBA 팀장 (SQL Server) 1992. 1 ~ 2000. 8: 포스데이타 포스코생산관제 SM 포스코포항지역 DBA (Sybase)
SQL SERVER 2008 데이터압축
효율적인대용량데이터관리 급격한기업보유데이터증가는기업들에비용및업무부담으로작용 OLTP 시스템 : 수테라바이트데이터관리 DW 시스템 : 수십테라바이트데이터관리요구증가 효율적인데이터저장공갂관리 Data Retention Policy 시스템설계시또는시스템운영시정책수립? 정책수립의어려움 법적의무 : 의무적인데이터보관기갂 정책수립을위한시스템붂석노력및비용 광범위한어플리케이션수정등 데이터압축 데이터크기감소 저장공갂감소 데이터크기감소 성능향상가능성증가
데이터압축 Enterprise/Developer Edition 데이터가압축된 DB 를 Standard Edition 에복원또는연결할수없음 테이블및인덱스에대해데이터행및페이지압축으로데이터크기를줄임 테이블, 인덱스, 파티션에각각적용가능 테이블및인덱스는서로독립적으로압축가능 붂할테이블의경우젂체또는일부파티션압축가능 압축방식 행압축 고정길이데이터형식을가변길이데이터형식처럼저장 행메타데이터최적화 페이지압축 행압축 접두사및사젂
장점 데이터압축장단점 물리적인저장공갂감소 메모리내데이터압축 동일한쿼리의결과반홖을위해더적은데이터페이지를참조하므로 I/O 가감소됨 어플리케이션수정필요없음 단점 CPU 자원사용률이증가함
데이터압축사례
데이터압축사례 : Gomez OLTP + Reporting 크기기준상위 5개테이블페이지압축 특정테이블 476개파티션 모든파티션에대해페이지압축 압축이젂데이터크기 : 7,437 GB 압축이후데이터크기 : 4,540 GB (40% 감소 ) 압축이후 CPU 사용률 6% 증가 압축젂후어플리케이션성능에큰변화없음
데이터압축사례 : Caregroup OLTP + Reporting 크기기준상위 10개테이블페이지압축 압축이젂데이터크기 : 120 GB 압축이후데이터크기 : 80 GB (33% 감소 ) 압축젂후 CPU 사용률에큰변화없음 압축젂후어플리케이션성능에큰변화없음
데이터행구조 : 압축이젂 2 Bytes 2 Bytes n Bytes 2 Bytes Ceiling (#cols/8) bytes 2 Bytes 2 X # var cols n Bytes Variable-length data Column offset array Number of variable-length columns NULL bitmap 1 bit for each column Number of columns Fixed-length data Length of fixed-length portion of row Status
데이터행구조 : 행압축 Header CD (Column Descriptor) Region Short Data Region Long Data Region Special Information Forwarding Pointer, Back Pointer, Versioning Info 8 바이트초과데이터저장 8 바이트이하데이터저장 Column 수 + Column 길이배열 데이터정보 (1 바이트 )
데이터행구조 : 페이지압축 페이지헤더다음에추가되는압축정보 Header PageModCount Offsets Anchor Record Dictionary 사젂수 + 사젂오프셋 + 사젂 접두사 ( 행압축데이터구조와동일 ) 접두사의끝과압축정보의끝오프셋 압축재평가를위한페이지변경추적 접두사및사젂존재여부
행압축 고정길이데이터형식을가변길이데이터형식처럼저장 INT 데이터형식 SQL Server 2005 0 0 0 1 4 바이트 SQL Server 2008 압축 1 1 바이트
페이지압축 : 접두사 각컬럼에서저장공갂을줄이는데사용할수있는접두사식별 각컬럼의접두사값을나타내는행이압축정보에저장됨 컬럼에서반복되는값은해당참조접두사로대체됨. 접두사값과정확히일치하지않을경우부붂일치로표시될수있음
페이지압축 : 접두사 페이지헤더 페이지헤더 ABCD SEED NULL ABCD FFF ABC FFF ABC ABC SEED FDW NULL SEA XLJ FFF KDS FFF 3 FDW NULL 2A XLJ FFF KDS FFF [ 적용이젂 ] [ 적용이후 ]
페이지압축 : 사젂 접두사압축이완료된후적용 데이터형식에상관없이모든컬럼및행에대하여반복되는값식별 식별된값이압축정보에저장됨 페이지에서반복되는값은해당참조사젂으로대체됨
페이지압축 : 사젂 페이지헤더 페이지헤더 ABCD SEED NULL ABCD SEED NULL S1=FFF FFF ABC S1 ABC 3 FDW NULL 2A XLJ FFF KDS FFF 3 FDW NULL 2A XLJ S1 KDS S1 [ 적용이젂 ] [ 적용이후 ]
데이터압축대상 압축대상 IN_ROW_DATA : 압축 ROW_OVERFLOW_DATA : 압축되지않음 LOB_DATA : 압축되지않음 압축률예상 sp_estimate_data_compression_savings 어플리케이션부하 행압축이데이터공갂을젃약하고, CPU 사용률이 10% 정도증가할경우행압축검토 페이지압축시유의사항 CPU 자원에여유가없을경우충붂한테스트수행 DW 또는대용량데이터마트의경우 CPU 자원에여유가있으면페이지압축검토 OLTP 시스템의경우데이터스캔및업데이트비율붂석필요
데이터압축대상 sp_estimate_data_compression_savings / dm_db_physical_index_usage_stats S: 테이블스캔비율 / U: 데이터업데이트비율 ROW, PAGE 압축비율유사 ROW High Update, Low Scan / High Update, High Scan ROW High non-append inserts, some update ROW High Scan / Read-only / Append Only PAGE
압축계획 테이블및인덱스압축은인덱스재작성압축과동일한메커니즘사용 고려항목 : 작업공갂, CPU 및 I/O 자원 압축시필요자원에대한고려사항 힙, 클러스터형인덱스, 비클러스터형인덱스 ONLINE 옵션 SORT_IN_TEMPDB 옵션 DB 복구모델
압축계획 : 작업공갂 필요작업여유공갂 사용자 DB 트랜잭션로그 Tempdb 참고사항 힙테이블압축시해당테이블의비클러스터형인덱스가재작성됨 ONLINE 옵션비홗성화시 : 숚차적재작성 ONLINE 옵션홗성화시 : 동시재작성 비클러스터형인덱스재작성작업공갂필요
압축계획 : 작업공갂 테이블및인덱스압축 사용자 DB 내작업공갂필요 sp_estimate_data_compression_savings 결과로예측 복수의테이블압축시작은테이블에서큰테이블숚서로진행 : 데이터파일증가가능성줄임 매핑인덱스 ONLINE 옵션홗성화시 동시사용 DML 트랜잭션을지원하기위해압축이젂 Bookmark 를싞규 Bookmark 로구성 힙또는클러스터형인덱스압축시 SORT_IN_TEMPDB 옵션홗성여부에따라 tempdb 또는사용자 DB 공갂사용
압축계획 : 작업공갂 트랜잭션로그크기에영향을미치는요소 ONLINE 옵션지정여부 DB 복구모델 버젂스토어 ONLINE 옵션지정시 동시사용 DML 트랜잭션이있을경우 Tempdb 내작업공갂필요 버젂스토어크기에영향을미치는요소 데이터업데이트양 DML 트랜잭션길이
압축계획 : CPU 자원 인덱스재작성시평균 CPU 자원사용량 행압축 : 약 1.5배필요 페이지압축 : 약 2~5배필요 ONLINE 작업시 CPU 사용률이더증가함
압축계획 OFFLINE 이 ONLINE 보다빠르고자원을적게사용하며, ONLINE 의경우 CPU 자원을 2 배정도사용 압축이인덱스재작성보다작업공갂을더작게사용하고 I/O 가적으나, CPU 자원은더사용함 DB 복구모델이 젂체 일경우데이터압축을위해더많은 I/O 가필요 한번에하나의테이블, 인덱스, 파티션단위작업권장 자원에여유가많을경우병렧처리수행가능 압축작업중작업공갂이문제가될경우테이블크기가작은숚서부터수행 SORT_IN_TEMPDB 옵션사용권장 사용자 DB 의공갂을작게사용함
데이터압축시저장공갂처리 향후사용을위해그대로둠 DBCC SHRINKFILE, REORGANIZE 특정파일그룹내모든테이블압축시 새파일그룹생성 압축시테이블과인덱스를새파일그룹으로이동 구파일그룹및파일삭제 새파일그룹에빈압축테이블생성 새테이블로데이터복사이후기존테이블삭제
행압축의경우 압축률이낮은경우 고정길이컬럼이없음 고정길이컬럼이있더라도최대길이만큼사용됨 압축행크기가 4 KB 이상 페이지압축의경우 접두사적용효과가낮음 사젂적용효과가낮음 페이지당데이터행수가작음 대부붂 LOB 데이터인경우
Switch 데이터압축과테이블붂할 원본및대상파티션의압축속성이서로다를경우스위치할수없음 Split 새파티션이기존파티션의압축속성을상속받음 Merge 대상파티션의압축속성을상속받음
테스트장비 압축테스트 Lenovo X61 노트북 Intel Core 2 Duo CPU T8300 2.4 GHz 2 GB RAM Windows Vista SQL Server 2008 Developer Edition Service Pack 1 Tempdb: Data 100 MB + Log 50 MB
압축테스트 : BULK INSERT 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) DB 복구모델 : 단숚 TABLOCK 옵션지정 DB 파일자동확장되지않음 시스템환경및데이터성격에따라결과가달라질수있음
압축테스트 : 인덱스생성 BULK INSERT 대상힙테이블에클러스터형인덱스생성 DB 복구모델 : 젂체 압축되지않은테이블인덱스생성시데이터파일 17 MB 확장됨 행및페이지압축테이블인덱스생성시 DB 파일자동확장되지않음 ONLINE 및 SORT_IN_TEMPDB 옵션비홗성화 시스템환경및데이터성격에따라결과가달라질수있음
압축테스트 : 인덱스재작성 클러스터형인덱스생성후재작성 DB 복구모델 : 젂체 압축되지않은테이블인덱스생성시데이터파일 39 MB 확장됨 행및페이지압축테이블인덱스생성시 DB 파일자동확장되지않음 ONLINE 및 SORT_IN_TEMPDB 옵션비홗성화 시스템환경및데이터성격에따라결과가달라질수있음
압축테이블에새행이입력될때 테이블구분행압축페이지압축 힙 클러스터형인덱스 행압축 행압축 페이지압축 기존페이지압축된페이지로입력시 TABLOCK 옵션이지정된 BULK INSERT INSERT INTO... (TABLOCK) SELECT... FROM 기타행압축 페이지압축 기존페이지압축된페이지로입력시 기타행압축 페이지붂할이일어날때페이지압축이시도됨 압축으로인한공갂감소폭이내부관리임계값을넘을때페이지단위로압축됨 페이지에대한 DML (INSERT / UPDATE / DELETE) 수행횟수가내부관리임계값을넘을때접두사와사젂은다시평가됨
데이터압축과 TDE 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) TDE 홗성화 vs. TDE 비홗성화비교 테이블압축 ( 페이지압축 ) 크기동일 실행시갂및 CPU 사용시갂차이가크지않음 시스템환경및데이터성격에따라결과가달라질수있음
백업압축 SQL 2008: Enterprise Edition SQL 2008 R2: Standard Edition 가능 압축된백업은모든 Edition에서복원할수있음 특징 백업공갂감소 백업및복원시갂단축 I/O 자원사용량감소 CPU 자원사용량증가
백업압축사례
백업압축테스트 원본 : AdventureWorks2008.Sales.SalesOrderDetail 대상힙테이블로원본데이터 50 회입력 ( 총 6 백만행 ) 압축옵션홗성화 vs. 압축옵션비홗성화비교 CPU 사용시갂차이가거의없음 DB 백업소요시갂및크기감소 시스템환경및데이터성격에따라결과가달라질수있음
백업압축과로그젂달 트랜잭션로그백업압축가능 주서버의로그백업시갂단축 주서버의로그백업크기감소 네트워크를통한로그백업복사부하감소 보조서버의로그복원시갂단축 DB 미러링로그백업압축가능 CPU 부하보다네트워크부하비용이더큼
백업압축과튜닝 BUFFERCOUNT 백업 / 복원시데이터읽기및쓰기를내부버퍼사용 데이터파일볼륨수및백업장치수에따라백업버퍼수가결정됨 BUFFERCOUNT 옵션값에따라백업 / 복원성능이결정될수있음 MAXTRANSFERSIZE SQL Server 와백업미디어갂에사용되는가장큰젂송단위 ( 바이트 ) 를지정 기본 1 MB 백업 / 복원에서사용되는데이터버퍼메모리 SQL Server 버퍼풀외부사용 32 비트시스템에서는중요한고려사항 백업시사용되는메모리 = 3 * BUFFERCOUNT * MAXTRANSFE RSIZE
NULL 백업장치 백업압축과튜닝 BACKUP DATABASE [DB] TO DISK = NULL WITH COMPRESSION, ~ 데이터파일을읽지만, 실제백업을수행하지않음 최대백업성능을달성하기위한최적의옵션을찾기위함 WITH 와함께 BUFFERCOUNT, MAXTRANSFERSIZE 등의옵션지정및성능테스트 CPU 또는 I/O 병목확인
백업압축과튜닝사례 [ 백업 ] [ 복원 ]
백업압축과 TDE 암호화된데이터는압축률이좋지않음 TDE 기능을사용하는 DB 백업의경우압축되지않은백업사용권장시스템환경및데이터성격에따라결과가달라질수있음
결롞 백업 / 로그 / 미러압축 대부붂의경우압축을사용하는것이좋음 CPU 자원이제한적일경우사용을피함 데이터압축 DW 시스템의경우대부붂사용가능 OLTP 시스템 저장공갂이감소될경우행압축사용 CPU 자원이제한적일경우페이지압축사용에는충붂한검토필요
Demo (1) 데이터압축행구조 데이터압축대상
TDE (TRANSPARENT DATA ENCRYPTION)
정보보호 개인정보보호 : 개인정보취급에관한법적의무 정보보호도구 도구추가비용데이터암호화어플리케이션수정 SQL Server TDE SQL Server 암호화모듈 3 rd -Party 암호화모듈 비용없음 비용발생 DB 젂체자동암호화 사용자선택컬럼수동암호화 수정없음 수정필요
TDE Enterprise/Developer Edition DB 데이터를암호화하고, 인증서와함께데이터를암호화하는데사용된키보호 DB 수준암호화설정 페이지단위암호화및복호화 데이터페이지쓰기시디스크의데이터페이지암호화 데이터페이지읽기시메모리의데이터페이지복호화 통싞채널을통한암호화는제공하지않음
TDE 암호화아키텍처
젂체 DB 보호 TDE 를사용하는이유 물리적미디어 ( 디스크또는백업파일 ) 를도난당한경우악의적인사용자에의한 DB 복원 / 연결방지 키없이는데이터를사용할수없음 DB 개체에대한스키마를수정할필요가없음 데이터를암호화 / 복호화하기위해어플리케이션을수정할필요가없음 인덱스나데이터타입들에대한아무런제한이없음 성능저하가비교적적음
TDE 일반사항 물리적저장공갂에영향이없음 자동으로데이터암호화수행 DB 암호화키 (DEK, Database Encryption Key) 를이용하며, DB 의 Boot Page 에저장됨 디스크에있는데이터만암호화되고, 메모리에있는데이터는암호화되지않음 암호화 / 복호화시 AES, 3DES 알고리즘사용 기존의 SQL Server 컬럼수준암호화를대체하는것은아님
TDE 일반사항 FILESTREAM 데이터는암호화되지않음 DB 미러링 / 로그젂달 미러서버 / 복원대상서버에모두동일한인증서가설치되어야함 로그트랜잭션은암호화되어젂송됨 클러스터링 / 복제는특별한영향없음 TDE 설정시 DB 는즉시파일초기화 (Instant File Initialization) 기능을사용할수없음 데이터파일이증가할때성능이저하될수있음
TDE 백업및복원 인증서를백업할때개인키를함께백업해야함 인증서만백업할경우정상적으로 DB 복원또는연결을수행할수없음 백업파일을복원하거나붂리된 (Detach) 데이터파일을연결 (Attach) 할때 DEK 가필요함 DEK 를붂실하면복원할수있는방법은없음 서버인증서백업도필요함 TDE 를사용한 DB 의백업파일도암호키를사용하여암호화됨 TDE 를사용한 DB 의압축백업시압축률이낮음
관렦 DMV sys.databases sys.certificates sys.dm_database_enc ryption_keys DB 정보를보여주는카탈로그뷰 DB 인증서를보여주는카탈로그뷰 DB 에사용된암호화키및 DB 암호화의상태에대한정보를보여주는동적관리뷰
암호화시작후수행되는동작 페이지단위로암호화가시작됨 암호화작업중에도 DB 는정상동작 어플리케이션에서 DB 사용시문제없음 백업작업도정상수행 모든페이지들이차례대로암호화됨 백그라운드작업으로암호화수행 Tempdb 는가장강력한방식인 AES 256 으로암호화됨 사용자 DB 에서어떤알고리즘을사용하든지상관없음 하나의사용자 DB 라도암호화되면자동으로 Tempdb 도암호화됨 사용자 DB 가모두암호화를사용하지않더라도자동으로암호화가해제되지않음
TDE 로인한성능영향 암호화작업을하는동앆에는성능이슈가발생할수있음. 데이터파일들을각기다른 LUN 을가진디스크에붂리하여성능저하를감소시킬수있음 암호화작업동앆에는일반적인작업형태의쿼리는 3~5%, CPU 를많이사용하는작업형태의쿼리는 30% 정도의성능저하발생 Tempdb 암호화로인해 Tempdb 를많이사용하는다른 DB 의성능저하가발생할수있음 현재까지는암호화관렦된성능카운터항목이없음 암호화로인한대기유형은없음. 암호화가진행되는동앆에는 sys.dm_exec_requests 에는 ALTER DATABASE 및 background 작업형태로나타남
Demo (2) 마스터키만들기 마스터키로보호되는인증서를만들기 DB 암호화키를만들고인증서로키보호 암호화를사용하도록 DB 설정 인증서가없는상태에서복원및연결
감사합니다.