인스턴스 파일 초기화 활성

인스턴스 파일 초기화 활성

 

• 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를 초기화 할 때 성능상의 이점을 취할 것이냐 보안을 고려할 것이냐는 각자의 환경에 따라 선택해야 한다.

 

[참고자료]

• http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx
• SetFileValidData function : http://msdn.microsoft.com/en-us/library/aa365544(VS.85).aspx
• Physical Database Storage Design : http://technet.microsoft.com/en-us/library/cc966414.aspx

 

 

 

2013-10-22 / 강성욱 / http://sqlmvp.kr