MSSQL Server 설정

처음에 SQL Server를 설치 한 후에 설정해야 하는 값들을 정리해봤습니다.설정 이외에 왜 설정을 하는지 간단한 이유를 기입하였습니다.

버전은 SQL Server STD 2008R2 위주로 작성되었으며,(제가 가장 많이써서...) 혹시 문제가 있거나 틀린 부분은 댓글을 남겨주세요. (_ _)

설정하는 방법이 아닌 왜 설정해야 하는가를 위주로 포스트를 작성 하였습니다.

 

 

 

목차 

Ⅰ. SYSTEM 설정

1. 하이퍼스레딩

2. 전원 계획 사용옵션 

3. 프로세서 사용계획

4. Lock Pages In Memory

5. RAID 구성

6. 바이러스 백신 프로그램 관리

 

Ⅱ. SQL Server 설정

1. 포트설정

2. 시작 계정 관리

3. Flag 설정

4. 구성관리자 설정

5. Memory 설정

6. 계정 설정

7. TempDB 설정

8. SQL Error Logs 설정

9. 데이터베이스 파일 설정

 

 

본론으로 들어가보겠습니다. 여기저기서 그림을 퍼온게 많습니다. 어디서 많이 보셨던 그림 일 수도 있습니다.

 

 

 

 

Ⅰ. SYSTEM 설정

 

 

 

1. 하이퍼스레딩

Hyper Threading은 가장 흔히 알듯이 CPU가 2배로 보입니다.

아래의 그림은 Windows 작업 관리자에서 보이는 CPU입니다. 하이퍼스레딩을 켤 경우 이 CPU의 코어 개수가 2배로 보입니다. 

아래의 초록색 꼬불꼬불 선이 나타나 있는 것들이 각각의 코어입니다. 현재 이 PC에는 8개의 코어가 있습니다. (하이퍼스레딩 안켰으므로)

 

 

하이퍼스레딩을 가장쉽게 이해 할수 있는 것이 아래의 그림입니다. 아래의 그림 중에 보면 우측에 그림에 그 설명이 매우 잘 나옵니다.

우측 그림에서 빨간색 선을 기준으로 첫번째 부터 싱글코어,듀얼코어,하이퍼스레딩 입니다. 

(출처 : http://smsinfo.tistory.com/m/post/514)

 

하이퍼 스레딩은 CPU가 마치 2개 인것 처럼 놀고 있는 부분을 1개의 코어가 처리하는 것으로써 배타적 작업이 많을 때에 유리 합니다.

위의 그림에서 1개의 작은 네모칸이 1개의 연산을 할 수 있는 곳이라고 할때 여러개의 연산이 가능 한 부분중에 항상 모든 곳이 작업을 

하고 있는 것은 아니므로, 놀고 있는 곳을 사용 함으로써 마치 2개의 코어가 일하는 것 처럼 보이는 것 입니다.

 

보통 (작은)OLTP 환경 즉, 게임 같은 서버에서는 하이퍼스레딩을 켜주면 성능 향상에 효과를 볼 수 있습니다.

하지만 큰 대용량 작업이 많은 OLAP 같은 환경에서는 CPU의 코어를 나눠서 파워를 분산함으로써 더욱 처리가 늦어 질 수 있습니다.

하여, 결론적으로 작은 OLTP환경에서는 사용을 하며, OLAP등 (DW,로그)등의 환경에서는 사용을 하지 않습니다.

 

 

 

 

2. 전원계획 사용옵션

설정법 : 실행 -> 전원 옵션

 

전원계획은 설정에 따라서 그 파워의 차이로 인하여, 성능에 영행을 줄 수 있습니다.

총 3가지 형태가 있는데, 균형조정/절전/고성능이 있습니다. 무조건 SQL Server가 있는 서버에서는 고성능을 선택해야지 100%의 성능을

발휘 합니다. 균형조정과 절전모드는 최소 프로세스 상태이므로, 5%의 파워를 고성능은 100%의 파워를 냅니다. 

결론적으로 고성능 옵션을 사용하는 것이 바람직 합니다.

 

물론 전기세가 증가하지만.. 성능을 포기하고 전기료를 아낄분은 없을것이라 생각 됩니다.

 

참조 사이트 

㉠전원 계획 사용옵션에 따른 성능 차이 테스트 링크

https://sqlserverperformance.wordpress.com/2010/09/28/windows-power-plans-and-cpu-performance/

 

 

 

3. 프로세서 사용계획

설정법 : 제어판 -> 성능 정보 및 도구 -> 시각효과 조정 -> 고급탭 

 

 

프로세서 사용계획은 2가지로 성능이 가능 합니다. 프로그램/백그라운드 서비스로 설정이 가능한데, 보통 SQL Server만 사용하는 서버에서는 

백그라운드 서비스를 사용합니다. 그 이유를 대부분이 SQL Server는 Service에 등록되어 백단에서 움직이므로, 그렇게 설정한다고 알고 있는데

잘못된 내용입니다.

 

백그라운드 서비스로 설정하는 것은 맞으나, 이렇게 설정하는 이유는 Context Switch 를 최소화 시키기 위해서 입니다. 결론적으로 백그라운드 

서비스로 설정하는 것이 바람직 합니다. Context Swith의 내용 및 자세한 사항은 아래의 링크를 참조하시면 좋을 듯 합니다.

 

참조 사이트

㉠ 백그라운드 서비스 관련 링크

http://blogs.technet.com/b/sankim/archive/2009/06/10/windows-vs.aspx

 

 

 

 

4. Lock Pages In Memory 설정

설정법 : 실행 -> 

gpedit.msc -> Windows 설정 -> 보안설정 -> 로컬정책 -> 메모리에 페이지 잠금

 

Lock Pages In Memory 는 매우 중요한 부분 입니다. 이것은 꼭 설정이 필요 합니다.

 

메모리에 페이지 잠금 속성 더블 클릭

 

사용자 또는 그룹추가 클릭하여, SQL Server 시작 계정 등록

 

이것을 설정하는 가장 큰 이유는 가상메모리 사용을 방지하기 위함인데, 가상 메모리란 메모리가 부족하여 디스크를 메모리 처럼 사용 하는 것을 말 합니다. 

디스크를 메모리 처럼 사용하다 보니, 그 성능이 매우 느려져서 성능 저하를 가져 오는 것 입니다. 메모리를 디스크 처럼 사용 하는 것을 Hard faults라고 하며,

Perfmon항목에서 Pages/sec를 확인하여, 이 수치가 높다면 Memory 항목에 Page faults/sec(Hard faults)를 유심히 봐야 합니다. 

 

 

Lock Pages In Memory 를 설정하지 않으면 다음과 같은 문제가 발생 할 수 있습니다.

1. SQL Server 성능이 갑자기 저하됨

2. SQL Server 실행하는 컴퓨터가 잠시 응답하지 않음

3. SQL Server 응용 프로그램에 대한 Time out 발생

4. 시스템에서 응용 프로그램을 사용 할 때 문제가 발생 함

 

참조 사이트

㉠ Lock Pages in Memory 설정 방법 및 설정하지 않을시 문제점

http://cclassic2.cafe24.com/?mid=SQL_Tip&search_target=tag&search_keyword=%ED%8E%98%EC%9D%B4%EC%A7%80&page=1&document_srl=8324

㉡ 가상 메모리 설명

http://egloos.zum.com/sweeper/v/2988689

㉢ 메모리 관리 및 Perfmon 보는 방법

http://blog.daum.net/question0921/1031

 

 

5. RIAD 

레이드는 SE 분들이 설정을 하시며 DBA는 DB서버에서 최적의 성능을 발휘할 레이드를 고려해야 합니다. 주로 게임환경에서 사용하는 레이드는 1+0 입니다. 게임에서 가장 많이 사용하며, 안정성과 성능을 모두 확보할 수 있습니다. 우선 1+0을 이해하기 위해서는 레이드 0과 1을 알아야 합니다.
㉠

레이드 0 

레이드 0의 경우 빠른 속도가 강점입니다. 레이드 0의 경우 데이터를 스트라이핑을 통해 DISK 4개에 나누어 뿌리는 방식으로 데이터를 저장 하므로, 그 처리 속도가

매우 빠릅니다. 하지만 DISK 중에 1개라도 깨진다면 데이터가 모두 손실됩니다. (중간에 이빨이 빠진것으로 이해하면 좋을 듯 합니다.)

(출처 : http://www.acnc.com/raidedu/0_1)

 

㉡ 레이드 1레이드 1의 경우 0과 다르게 안정성이 뛰어납니다. 레이드 1은 데이터를 미러링하여, 동시에 저장하기 때문에 1개의 DISK가 깨져도 복구가 가능합니다. 하지만 250GB씩 

4장의 DISK를 꽂아서 1TB로 사용할 경우 미러링을 위해 500GB밖에 사용 할 수 없습니다.

 또한 레이드 1을 구성하기 위해서는 최소 2장의 DISK가 필요 합니다. 

(출처 : http://www.acnc.com/raidedu/0_1)

 

㉢ 레이드 1+0 레이드 1+0의 경우 레이드 0으로 묶여진 레이드를 다시 레이드 1로 묶는 것을 말 합니다. 이렇게 함으로써 스트라이핑으로 데이터를 저장 함과 동시에미러링을 하므로, 그 안정성과 성능의 이점을 모두 얻을 수 있습니다. 

 

(출처 : http://cappleblog.co.kr/133)

 

㉣ 레이드 0+1레이드 0+1의 경우 위의 1+0과 반대로 생각하면 됩니다. 레이드 1로 묶여진 것을 다시 레이드 0으로 묶은 것 입니다. 위와 마찬가지로 속도 및 안정성을모두 확보 가능 하지만 이것이 깨졌을때 그 문제점이 나타므로, 사용하지 않습니다.

(출처 : http://cappleblog.co.kr/133)

 

 

* 레이드 1 + 0을 사용하는 이유 

1. 0 + 1로 묶은 레이드에서 디스크가 깨졌다고 가정 할때 위의 그림에서 첫번째 레이드의 A1 데이터가 손상 된다면, 전 데이터를 사용 불가능 합니다. 

하지만 반대로 1 + 0의 경우 A1의 데이터가 있는 디스크가 깨져도 옆에 그대로 미러링 된 데이터가 있으므로, 지장이 없습니다.

 

2. 복구시에도 그 차이가 분명히 드러납니다. 만약에 위와 마찬가지로 첫번쨰 레이드가 깨졌다고 가정 할 경우 0 + 1의 경우 데이터를 정렬 및 재 구성을

위해서 전부 복원 해야 하지만, 1 + 0 의 경우 아직 살아있는 옆의 미러링된 디스크에서 데이터를 복사하여 복원 하므로, 처리 시간 및 리소스에 비용이

현저하게 줄어 듭니다.

 

이밖에 레이드는 레이드5 등도 자주 사용하지만 게임회사인 저는 1 + 0을 거이 대부분 사용하였기에 나머지 자료는 링크를 참조 부탁드립니다.

 

참조 사이트

㉠ 레이드 동작 플래쉬

http://www.acnc.com/raidedu/0_1

㉡ 레이드 1 + 0과 0 + 1의 차이점

http://lab.gamecodi.com/board/zboard.php?id=GAMECODILAB_Lecture&page=1&sn1=&divpage=1&sn=off&ss=on&sc=on&select_arrange=headnum&desc=asc&no=103

㉢ 레이드 설명 

http://cappleblog.co.kr/133

 

 

 

6. 바이러스 백신 프로그램 관리

바이러스 및 백신 프로그램은 주로 일 단위, 주단위로 검사를 수행 합니다. 하지만 데이터베이스의 데이터파일 및 백업 파일등은 그 검사 대상에서 제외되어야 합니다.

만약 MDF , NDF , LDF 파일등이 있는 폴더를 검사하게 된다면, 검사 시 대기현상이 발생하거나 성능에 영향을 줄 수 있습니다. 
버전별로 정리된 바이러스 백신 관련 설정은 MSDN에 설정을 참조하시길 바랍니다.

 

참조 사이트

㉠ 백신 프로그램 관리

https://support.microsoft.com/en-us/kb/309422/ko

 

 

 

 

 

 

 

Ⅱ. SQL Server 설정

 

 

1. 포트 설정

설정 법 : 실행 -> SQL Server 구성 관리자 -> SQL Server 네트워크 구성 -> (인스턴스명)에 대한 프로토콜 -> TCP/IP -> IP주소 탭 -> TCP포트 부분 변경

 

포트를 설정하는 가장 큰 이유는 보안의 문제가 나타나기 때문입니다. 보통 SQL Server를 처음 설정 했을 경우 기본 포트는 1433입니다. 하지만 이 1433포트를 그대로

사용 하는 경우는 극히 드문 경우 입니다. 그 이유는 1433 포트는 알려진 포트이므로, 공격의 대상이 됩니다. 이러한 공격의 한 종유로 브루트 포스(Brute Force Attack)공격이 있습니다.

이것은 무차별 적으로 암호를 변경해가며, sa 계정으로 1433 포트로 접근을 계속해서 시도하는 공격 입니다. 이로 인해 차후에 나오겠지만 sa 계정도 사용하지 않습니다.

 

이러한 공격이 초단위로 수백 ~ 수천번씩 공격하기 때문에 부하가 심할 수 밖에 없습니다. SQL Server의 포트는 기본 1433이 아닌 다른 포트로 변경 하며, 변경 후 에 SQL Server를 

재시작 해야 합니다.

 

참고 자료

㉠ 포트 관련 공격

http://ozit.tistory.com/35

 

 

 

 

2. 시작 계정 관리

설정 법(계정 생성) : 실행 -> 컴퓨터 관리 -> 로컬 사용자 및 그룹 -> 사용자 폴더 우클릭 -> 새 사용자

 

컴퓨터 관리로 접속하여, 로컬 사용자 및 그룹을 탭으로 갑니다.

 

 

로컬 사용자 및 그룹 탭에 사용자 폴더를 우클릭하여, 새 사용자를 추가합니다.

 

새 사용자는 SQL Server 시작 계정으로만 사용하므로, 네이밍을 SQL 또는 SQL Server등 분별하기 쉽게 주는게 좋습니다.

 

생성된 계정을 우클릭하여 속성을 누르고, 속성에서 소슥그룹탭에 있는 모든 소슥그룹을 제거합니다. 기본적으로 Users로 소속그룹이 되어 있습니다.

소속 그룹을 제거하는 이유는 Users 소속그룹에 속해 있을시 응용프로그램 실행, 로컬 및 네티워크 프린터 사용 , 컴퓨터 잠금과 같은 일반적인 작업을 수행 할 수 있기

떄문 입니다. 하여, SQL Server 만 수행하기에 아무런 소속그룹도 필요하지 않습니다. 

 

소속스룹에 따른 권한 관련 링크

https://technet.microsoft.com/ko-kr/library/cc771990.aspx

 

 

설정 법(시작 계정 설정) 

SQL Server 구성관리자 -> SQL Server 서비스탭 클릭 -> SQL Server(인스턴스명) 우클릭 -> 속성 클릭 -> 로그온탭에서 계정 변경 (생성한 계정으로)

 

SQL Server의 구성관리자를 실행하여 SQL Server 서비스 탭을 클릭하면 다음과 같이 서비스들이 나옵니다. 여기서 SQL Server (인스턴스명) 을 우클릭하여 속성을 들어갑니다.

 

계정을 변경하고, 적용 후 SQL Server를 재시작 합니다.

결론적으로 생성된 아무 권한이 없는 오직 SQL Server만 실행하는 계정을 이용해 SQL Server를 구동해야 합니다.

 

 

 

 

3. Flag 설정

설정법 : SQL Server 구성관리자 접속 -> SQL Server 서비스 -> SQL Server (인스턴스) -> 우클릭하여, 고급탭 클릭 -> 시작매개 변수 부분에 Flag 기입

 

시작 매개변수에 설정해주면 SQL Server가 시작되면서 플래그가 적용됩니다.

 

아래의 표에 설정이 필요한 플래그를 정리하였습니다.

T845 , T1118 을 제외한 플래그는 상황에 맞게 설정하는 것이 좋을 것으로 보입니다.

 

 

 

 

4. 구성 관리자 설정 

 

구성관리자는 상황 및 사용에 따라서, 중지,사용안함,자동으로 만들어야 합니다.

자동으로 설정 할 경우 SQL Server시작시 같이 올라 옵니다. 하지만 자동으로 설정하였다고해서 무조건 올라오는 것이 아니므로 (가끔 안올라옴) 점검 및 서버 재기동시

DBA가 함께 확인을 해줘야 합니다.

 

아래의 표는 현재 Live 중인 게임DB 서버에서 실행 운영중인 내용입니다. 

 

서버의 사용 용도 및 사용에 맞게 서비스를 변경해 주면 좋을 듯 합니다.

 

 

 

5.  Memory 설정 

설정법(UI) : SSMS 접속 -> 최상위 SQL Server 로 되어 있는 부분 우클릭 -> 속성 -> 메모리 부분 클릭 -> 최소,최대 서버 메모리 부분 변경

 

 

최소 서버메모리는 SQL Server가 최소 유지할 메모리를 뜻하여, 최대 서버 메모리는 최대 SQL Server가 사용 할 수 있는 메모리를 뜻 합니다.

 

 

설정법 (쿼리) : 

sp_configure 'max server memory (MB)', 14000    --최대메모리지정 sp_configure 'min server memory (MB)', 12000      --최소메모리지정

 

SQL Server는 메모리를 동적으로 관리합니다. 필요 할때마다 메모리를 점유하며, OS에서 필요하다고 하기 전까지 그 메모리를 반환하지 않습니다. 그 이유는 재사용성 때문입니다.

SQL Server가 프로시저 및 파라미터 쿼리에 속도가 빠른 이유는 컴파일 이후에 메모리에 상주시켜놨다가 바로 실행 시키기 때문입니다. 

 

SQL Server에서 최소/최대 메모리를 꼭 설정해야 하는 이유는 설정하지 않을 시 SQL Server는 기본적으로 최소/최대에 대한 제한이 없습니다. 

이렇게 제한이 없으므로, 부하가 많은 시스템에서 I/O가 지속적으로 발생하면 SQL Server는 OS에 메모리를 계속 요구하며 할당받아 사용하게 됩니다. 하지만 이는 OS 및 다른 응용

프로그램에 동작에 영향을 줄 수 있습니다. 반대로 OS에서 다른 응용프로그램에서 많은 공간의 메모리를 사용하고 있다면 SQL Server가 할당 받아 사용할 메모리가 줄어들게 됩니다.

SQL Server의 버퍼풀 메모리가 부족 할 경우 잦은 I/O를 발생시키고 쿼리를 실행하는데 메모리를 얻지 못하여, 대기가 발생 할 수 있습니다.

 

주로 SQL Serve가 설치된 서버라면 SQL Server 이외에 다른 프로그램을 돌리지 않는 것이 일반적입니다. 하여, SQL Server 전용 서버라면 80~90%까지 (전체 메모리에) SQL Server가 점유하게 합니다. 

 

참고 자료

㉠ 메모리 관련 

http://kajin7.tistory.com/entry/MS-SQL-Server-2008-%EB%A9%94%EB%AA%A8%EB%A6%AC%EA%B4%80%EB%A6%AC

 

 

 

6. 계정 설정

설정 법 : SSMS 실행 -> 보안 -> 로그인 

 

위에서 언급하였듯이 포트를 변경하는 이유에서 처럼 계정을 관리해야 하는 이유도 보안상 이유입니다. 기본적으로 변경해야 할 것은 다음과 같습니다.

 

## 시작 및 ##으로 끝나는 계정 관련한 MSDN 링크 

https://msdn.microsoft.com/ko-kr/library/ms181127.aspx

 

 

 

 

7. Temp DB 설정

설정 법 

아래의 설정은 TempDB를 8개로 나누는 것이며, 사이즈 및 경로를 서버의 상황 및 구성에 맞게 변경 해야 합니다.

use master go   alter database tempdb modify file  (name=tempdev, filename='D:\MSSQL_TempDB\tempdb.mdf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb modify file (name=templog, filename='D:\MSSQL_TempDB\templog.ldf', SIZE = 1024MB, FILEGROWTH = 128MB) Go   alter database tempdb add  file (name=tempdev01, filename='D:\MSSQL_TempDB\tempdev01_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev02, filename='D:\MSSQL_TempDB\tempdev02_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev03, filename='D:\MSSQL_TempDB\tempdev03_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev04, filename='D:\MSSQL_TempDB\tempdev04_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev05, filename='D:\MSSQL_TempDB\tempdev05_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev06, filename='D:\MSSQL_TempDB\tempdev06_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go alter database tempdb add file (name=tempdev07, filename='D:\MSSQL_TempDB\tempdev07_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go  alter database tempdb add  file (name=tempdev08, filename='D:\MSSQL_TempDB\tempdev08_Data.ndf', SIZE = 1024MB, FILEGROWTH = 128MB) go

 

TempDB는 SQL Server에서 아주 중요한 역할을 하며, 몇가지 역할을 살펴보면 다음과 같습니다.

1. 정렬 시 중간 결과값 저장

2. 임시 테이블 생성 시 저장

3. Hash join 등에서 사용

 

일부의 역할이며, 더욱 많은 역할을 담당합니다.

 

하지만 TempDB는 SQL Server 설치 초기에 아무런 정보 없이 설정하기 매우 어렵습니다. MSDN에서는 코어의 갯수데로 TempDB를 쪼개라고 권장하고 있습니다.

TempDB의 경우 그간의 시스템을 모니터링 하여, 적절하게 쪼개주면 좋습니다. 용량의 증가율 및 i/O의 경합률등을 고려하여 개수를 쪼개는 것이 바람직 합니다.

 

TempDB의 경우 너무 많이도, 너무 적게도 쪼개는 것이 좋지 않으므로, 초기에 4~8개 정도 적절한 용량을 세팅한 뒤에 차후 모니터링을 통해서 그 개수를 측정

하는 것이 좋습니다.

 

 

참조 자료

㉠ TEMP DB성능 최적화 MSDN

https://technet.microsoft.com/ko-kr/library/ms345368(v=sql.105).aspx

㉡ SQL TAG 2

TempDB 부분

 

 

 

8. SQL Error Logs 설정

설정 법 : SSMS -> 관리 -> SQL Server 로그 우클릭 하여 구성 클릭

 

 

SQL Server 오류로그 구성에서 개수를 조절 합니다.

 

 

SQL Server 오류로그의 수는 아래에 보이는 로그의 개수를 뜻 합니다.

이 개수는 99개 까지 조절이 가능 합니다. 이 개수를 너무 적게 설정하면, 그 히스토리가 적게 남으므로 많이 설정하는 것을 권장 합니다.

예전에 제가 실제로 오류 내용을 봐야하는데, 개수를 6개만 저장하여, 히스토리를 확인하지 못한 경우가 있었습니다. 

 

 

 

 

 

9. 데이터베이스 파일 성정

설정 법 : SSMS -> 설정 할 DB 우클릭 -> 속성 -> 파일

 

데이버테이스에서 용량 산정 및 증가값을 설정하는 것은 매우 중요합니다. 딱 정해진 값이 없고, 이것도 TempDB와 마찬가지로 기존에 사용률 및 증가값을

모니터링 하여, 설정해야 합니다. 처음 세팅시에 기본적인 값을 설정(예상하여) 해야 합니다.

 

여기서 중요한것은 증가값을 설정할때 기본적으로 LDF 파일의 경우 퍼센트(%) 단위로 지정되어 있는데 이것은 MB 단위로 변경해야 합니다.

MB 단위로 지정해야지 일정 단위로 증가되며 퍼센트로 지정 할 경우 차후에 그 용량이 커지면 기하 급수적으로 용량이 증가 할 수 있습니다.

 

또한 MDF,NDF와 LDF는 각각 다른 디스크에 구성하여, 그 성능을 높여야 합니다. 그리고 백업 파일등도 다른 디스크에 보관하여, 혹시 모를 MDF,NDF의 깨짐이

발생하면, 백업 파일을 통해 복원을 해줘야 합니다.