데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

인스턴스 파일 초기화 활성

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server를 운영할 때 데이터베이스를 생성하거나 수정할 때 디스크 파일을 할당하는데 오랜 시간이 걸릴 수 있다. 좀더 빠르게 파일을 할당 할 수는 없을까?

 

볼륨 관리 작업 권한을 이용하는 방법과 이를 확인하는 추적플래그에 대해 알아 보자.

 

이 포스트는 SQL Server Premier Field Engineer Blog에 게시된 내용으로 필자가 읽고 이해한 내용을 바탕으로 정리 하였으며 번역의 오류나 기술적 오류가 있음을 미리 알려둔다. 자세한 내용은 원문을 참고하길 바란다.

 

SQL Server 시작시 서비스 계정에 대한 즉시 파일 초기화(SE_MANAGE_VOLUME_NAME, 볼륨 관리 작업) 사용을 고려 할 수 있다. 이 옵션은 훨씬 빠르게 데이터 파일을 할당(CREATE AND ALTER FILE) 하지만 로그 파일 할당 작업을 하지 않는다.

 

볼륨 관리 작업은 로컬 보안 정책을 통해 각 인스턴스에 대해 사용할 수 있다. 클러스터 경우에는 모든 노드에 권한을 부여 해야 한다. 서버 또는 클러스터에 여러 인스턴스가 있는 경우 각 인스턴스의 보안 그룹에 권한이 부여되어야 한다.

 

이 권한을 사용하면 SQL Server의 데이터 파일 생성이나 확장 시 새로운 공간을 "Zeroing Out" 한다. (로그파일에는 적용되지 않는다.) 이는 데이터베이스의 생성, 수정, 복원, 자동 증가 등 확장하는데 걸리는 시간에서는 큰 이득이 있으나 보안 위험이 있다.

그 이유는 파일을 삭제 하였을 때 삭제된 파일의 공간에 0으로 채우는 것이 아닌 공간 할당만 해제하기 때문에 다음 파일 할당 시 재사용 되면서 다른 사람이 삭제 된 데이터를 읽을 가능성이 있기 때이다.

 

[SQL Server의 각 인스턴스에 권한을 할당하는 방법]

  1. SQL Server의 각 인스턴스에 대한 적절한 그룹 이름을 찾기 위해 서버에서 lusmgr.msc를 실행 한다. (사용자의 버전 그리고 인스턴스에 따라 이름이 조금씩 다르다.)

 

  1. 서버에서 secpol.msc를 실행 한다. 왼쪽 탭의 보안 설정에서 [사용자 권한 할당]으로 이동하여 오른쪽의 정책 창에서 [볼륨 유지 관리 작업 수행]을 더블클릭 한다.

 

  1. [볼륨 유지 관리 작업 수행 속성] 창에서 [사용자 또는 그룹 추가] 버튼을 클릭하여 사용자 계정을 등록 한다.

 

  1. SQL Server를 재시작 한다. SQL Server 서비스 계정에 직접 권한을 부여한 경우 SQL Server를 시작 계정을 변경 할 때마다 권한을 다시 부여해야 한다.

 

 

SQL Server 인스턴스가 권한을 가지고 있는 경우 해당 파일을 Zero Out 하지 않아도 활용 가능 하다. 하지만 몇 가지 제약 사항이 있다.

  • 로그파일에는 적용되지 않는다.
  • OS는 SetFileValidData 함수를 호출한다. (http://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx )
  • 계정이 필요한 권한을 가지고 있어야 한다
  • 스파스 파일이 아니다. (일명 스냅숏이 아니다.)
  • 투명한 데이터 암호화(TDE)를 사용할 수 없다.
  • 추적 플래그 1806을 지원하지 않는다.

 

 

파일을 비우는 작업을 확인하기 위해서는 문서화 되지 않은 추적 플래그 3004를 통하여 확인할 수 있다. 다음 스크립트를 통하여 확인하여 보자.

DBCC TRACEON(3004,3605,-1)

GO

 

CREATE DATABASE TestFileZero

GO

 

EXEC sp_readerrorlog

GO

 

DROP DATABASE TestFileZero

GO

 

DBCC TRACEOFF(3004,3605,-1)

 

 

 

SQL Server를 초기화 할 때 성능상의 이점을 취할 것이냐 보안을 고려할 것이냐는 각자의 환경에 따라 선택해야 한다.

 

[참고자료]

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1447
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2263
» 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1702
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2835
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4003
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1347
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1285
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1461
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2636
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2337
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 1986
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2018
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1838
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3430
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2253





XE Login