[펌]데이터베이스 이해

1. 데이터베이스에 대한 이해

1.1 데이터베이스의 중요성

데이터베이스는 데이터를 중앙에 저장하고 관리하여 여러 사용자가 공유 할 수 있도록 하기 위해서 사용되는 데이터의 저장소이다. 공유가 필요하지 않다면 데이터를 워드 파일 또는 엑셀 파일과 같은 개별 파일로 관리해도 될 것이다. 여기서 공유해서 사용한다는 것에서 한 가지 문제점을 생각해 보아야 한다.

만일 100명의 사용자가 특정 데이터베이스를 사용한다고 가정 했을 때, 이 데이터베이스에 문제(성능저하나 데이터 손실 등)가 발생하면 100명의 사용자에게 피해가 간다. 기업의 중요 비즈니스와 연관된 데이터베이스에 문제가 발생하면 이로 인해서 심각한 업무 손실이 발생할 수 있다. 이러한 잠재적인 위험성을 완전히 제거하는 것은 거의 불가능한 일이다. 단지 우리가 할 수 있는 것은 최적의 성능과 안정성을 고려해서 데이터베이스를 만들고, 이러한 데이터베이스의 혹시 모를 손상으로부터 업무 손실을 최소화하기 위한 방안을 마련하는 것이다. 즉 서버 또는 데이터베이스를 이중화 하거나 적절한 전략으로 데이터베이스 백업을 수행하는 등의 방안을 마련해야 한다.

SQL Server를 사용하는 이유는 당연히 데이터베이스를 사용하기 위함이다. 그래서 SQL Server를 설치한 후 곧이어 수행되는 작업이 데이터베이스를 만드는 작업이다. 데이터베이스에 대한 정확한 이해를 바탕으로 하는 최선의 구축 노력은 SQL Server가 얼마나 안정적이고 빠른 성능으로 서비스를 제공할지를 결정짓는 중요한 요인이 된다.

1.2 시스템 데이터베이스에 대한 이해

[화면1] 개체탐색기에서 시스템 데이터베이스 확인

SQL Server를 설치하는 과정에서 SQL Server가 자체적으로 사용할 목적으로 자동으로 만드는 몇몇 데이터베이스를 시스템 데이터베이스라고 부른다. [화면1]에서 확인하듯 시스템 데이터베이스에는 master, model, msdb, tempdb 데이터베이스가 기본적으로 포함된다.  SQL Server는 데이터베이스 관리 시스템으로서의 여러 가지 역할을 수행하기 위해서 필요한 정보를 시스템 데이터베이스에 저장하여 사용한다.

복제(Replication)가 구성된 상황에서 배포자(Distributor) 역할을 수행하는 SQL Server라면 distribution 데이터베이스가 시스템 데이터베이스에 포함된다. 또한 숨겨져 있어서 외형적으로는 보이지 않는 resource 데이터베이스도 시스템 데이터베이스에 포함된다. resource 데이터베이스와 관련된 파일이 어느 폴더에 존재하는지는 다음 스크립트를 사용해서 확인할 수 있다.

[소스1]  resource 데이터베이스 파일 위치 확인

Use master
GO

SELECT 'ResourceDB' AS [Database Name], NAME AS [Database File],
    FILENAME AS [Database File Location]
    FROM sys.sysaltfiles WHERE DBID = 32767
GO

 

1) master 데이터베이스

SQL Server가 데이터베이스 관리 시스템으로서 수 많은 데이터베이스를 관리하기 위해서는 이들 데이터베이스에 대한 정보를 저장 할 특별한 데이터베이스가 필요하다. 즉 데이터베이스의 이름은 무엇이며, 데이터베이스와 관련된 데이터 파일과 로그 파일은 어디에 위치하고 있으며, 데이터베이스에 접근하기 위해 서버에 로그인 할 수 있는 계정은 무엇인지 등의 정보가 어딘가에 저장되어야 하는데, 이러한 역할을 담당하는 데이터베이스가 master 데이터베이스이다. master 데이터베이스에 SQL Server의 여러 가지 환경 정보와 로그인 정보, 관리되는 모든 데이터베이스에 대한 정보 들이 저장된다. SQL Server는 서비스가 시작 될 때 우선 master 데이터베이스를 찾아서 열게 된다. 이어서 자신에 대한 존재를 확인하고, 다른 데이터베이스와 관련된 파일의 위치를 확인한 후 해당 위치로 가서 그 파일들을 사용 가능하도록 열게 된다. 만일 master 데이터베이스가 손상된 상태라면 당연히 SQL Server 서비스는 시작 될 수 없다. 그러므로 master 데이터베이스가 시스템 데이터베이스 중에서도 가장 핵심이 되는 시스템 데이터베이스라고 할 수 있다.

2) model 데이터베이스

‘model’ 이라는 단어에서 그 역할을 추측 할 수 있듯이, 이 데이터베이스는 모든 데이터베이스들의 원형으로 사용된다. 그래서 새로운 데이터베이스를 만들 때 model 데이터베이스가 복사되어 만들어 진다. 이런 이유로 model 데이터베이스의 설정을 변경하면 이후에 만들어지는 새로운 데이터베이스에 그 변경 사항이 반영되는 결과를 얻게 된다. 예를 들어 앞으로 만들어질 새로운 데이터베이스에 특정 사용자 계정을 만들고 싶을 때 이 사용자 계정을 model 데이터베이스에 만들어 두면 별도의 작업이 없이도 새로 만들어지는 데이터베이스에 이 사용자 계정이 포함되게 할 수 있다. 하지만 대부분 model 데이터베이스는 처음 그대의 형태로 유지되며 관리적인 측면에서도 특별히 관심을 두지 않는 경우가 많다.

3) tempdb 데이터베이스

SQL Server가 운영될 때 만들어지는 임시 테이블들이 생성되는 곳이다. 물론 임시 테이블 말고도 여러 가지 역할을 tempdb 데이터베이스가 담당한다. tempdb 데이터베이스를 많이 사용하는 환경에서는 tempdb 데이터베이스에 대한 적절한 구성과 모니터링이 필요하다. 그렇지 않으면 SQL Server에 성능 저하가 발생할 수도 있다. tempdb는 SQL Server 서비스가 다시 시작 될 때마다 초기화 되므로 영구적으로 보관 되어야 할 사용자 테이블을 tempdb에 만들어서는 안 된다. 앞에서 model 데이터베이스는 새로운 데이터베이스를 만들 때 사용되는 템플릿 데이터베이스라고 했다. tempdb 역시 SQL Server 서비스가 다시 시작 될 때 model 데이터베이스가 복사 되면서 새롭게 만들어진다.

임시 테이블(Temporary Tables)은 테이블 이름 앞에 # 또는 ##이 붙은 테이블을 이야기한다. # 이 붙은 테이블은 지역 임시 테이블(Local Temporary Tables)이라고 하며 해당 테이블을 만든 세션에서만 사용 될 수 있다. 반면에 ## 이 붙은 테이블은 전역 임시 테이블(Global Temporary Tables)이라고 하며 해당 테이블이 존재하는 동안은 다른 세션에서도 사용이 가능하다. 이들은 자신들이 만들어진 세션이 종료될 때 자동으로 제거되며 항상 tempdb 데이터베이스에 저장된다.

4) msdb 데이터베이스

msdb 데이터베이스는 주로 자동화에 관련된 정보를 저장 하는데 사용된다. SQL Server 에이전트 서비스를 활용하면 작업(Jobs), 경고(Alerts), 운영자(Operators) 기능을 사용해 효율적인 유지관리 업무 자동화를 구현 할 수 있는데, 이들과 관련된 정보는 msdb 데이터베이스에 기록되어 관리 된다. 이런 이유로 msdb를 SQL Server 에이전트 서비스가 사용하는 데이터베이스라고 이야기 하기도 한다. 데이터 전송을 위한 SSIS(SQL Server Integration Services) 패키지를 SQL Server에 저장하면 이 역시 msdb에 저장된다. 이 외에도 백업 내역과 복원 내역을 포함한 각종 정보가 msdb 데이터베이스에 기록되어 관리 된다. msdb 데이터베이스에 저장된 정보는 시스템 카탈로그 뷰를 통해 사용자가 접근할 수 있다는 특징이 있다. 예를 들어 작업 실행 내역, 백업 내역, 복원 내역 등을 쿼리문을 사용해 확인할 수 있다.

[화면2] msdb 데이터베이스의 시스템 카탈로그 뷰

1.3 예제 데이터베이스

예제 데이터베이스는 SQL Server에 대한 기능을 익힐 때 사용하도록 제공되는 학습용 데이터베이스이다. 학습 목적 외에는 SQL Server 운영과는 전혀 연관이 없다. SQL Server 2000 버전에서는 Pubs, Northwind 데이터베이스가 예제 데이터베이스로 제공되었으나, SQL Server 2005부터는 AdventureWorks, AdventureWorksDW 데이터베이스가 대신 제공된다. AdventureWorks 데이터베이스는 OLTP용 예제 데이터베이스이며, AdventureWorksDW는 OLAP용 예제 데이터베이스이다. SQL Server 버전에 따라 예제 데이터베이스에 포함되는 항목이 조금씩 달라져서 AdventureWorks, AdventureWorks2008, AdventurteWorks2012와 같이 데이터베이스 이름에 버전이 붙은 형태의 예제 데이터베이스가 제공된다. 참고로 AdventureWorks는 가상의 자전거 제조회사이다.

2008 버전부터는 예제 데이터베이스가 설치 프로그램에 포함되지 않으므로 CodePlex 사이트에서 원하는 버전의 예제 데이터베이스를 전송 받아서 설치해야 한다.

1.4 사용자 데이터베이스

사용자가 데이터를 저장하기 위한 목적으로 만든 데이터베이스를 사용자 데이터베이스라고 한다. 어떻게 보면 업무적으로 가장 중요한 데이터베이스라고 할 수 있다. 이렇게 SQL Server를 공부 하는 것도 이 사용자 데이터베이스를 효율적으로 관리하고 활용하는 방법을 배우기 위한 것이라고 생각한다.

1.5 기타 데이터베이스

만일 개체 탐색기에서 ReportServer, ReportServerTempDB와 같은 데이터베이스가 보인다면 이는 SSRS(SQL Server Reporting Services)와 연관된 데이터베이스이다. 이들 데이터베이스에 대한 내용은 자세히 다루지 않겠다.

2. 데이터베이스를 구성하는 파일

 [그림2] 데이터베이스 구조

SQL Server에서 데이터는 데이터베이스에 저장되며, 데이터베이스는 Windows Server에 파일로 저장된다. 결국 우리가 데이터베이스를 만드는 과정은 Windows Server에 파일을 만드는 과정이다. 여러 응용 프로그램의 파일들이 각각 나름대로 확장자를 가지고 존재하듯 SQL Server 데이터베이스의 파일은 mdf, ndf, ldf 확장자를 가지고 존재한다. 물론 이러한 확장자(mdf, ndf, ldf)를 사용하도록 강요되는 것은 아니지만 되도록 그대로 사용하는 것이 바람직하다.

2.1 데이터 파일과 로그 파일

데이터베이스를 구성하는 파일은 크게 데이터 파일과 로그 파일로 분류할 수 있다. 이들의 의미와 역할을 살펴보자.

1) 데이터 파일(*.mdf, *.ndf)

모든 데이터베이스는 기본적으로 mdf 확장자를 갖는 기본 데이터 파일(Primary Data File)을 하나 가지게 된다. 보조 데이터 파일(Secondary Data File)을 필요에 따라 추가 할 수 있는데 이 파일은 ndf 확장자를 갖는다. 보조 데이터 파일은 성능을 위해 데이터를 여러 디스크로 분산할 목적으로 처음부터 만들 수도 있고, 데이터베이스의 여유 공간 확보를 위해 운영 중에 추가 할 수도 있다. 당연히 보조 데이터 파일 없이도 데이터베이스를 운영 할 수 있다.

2) 로그 파일(*.ldf)

모든 데이터베이스는 ldf 확장자를 갖는 하나 또는 여러 개의 트랜잭션 로그 파일(Transaction Log File)을 가지게 된다. 트랜잭션 로그 파일(그냥 로그 파일이라고도 부름)은 우리가 좋던 싫던 필수적으로 만들어지는 파일이며 이 파일에 트랜잭션 처리 내역, 즉 데이터에 대한 추가, 변경, 삭제 내역이 기록된다. 트랜잭션 로그를 기록하는 이유는 시스템이 정지되는 등의 예기치 않은 상황에서 데이터의 일관성을 유지하고, 데이터베이스가 손상되었을 때 문제가 발생하기 직전의 상태로 데이터를 복구 할 수 있는 가능성을 제공하기 위해서다.

갑작스럽게 시스템이 정지 되었다가 다시 가동되어 SQL Server 서비스가 재 시작 된 상황이라 가정하자. 만일 트랜잭션이 완료된 기록(커밋(Commit) 된 기록이라고 부름)이 트랜잭션 로그에는 남아 있으나 데이터 파일에는 반영되지 않았다면 트랜잭션 로그를 근거로 하여 데이터 파일에 변경 내용을 반영하는데 이러한 과정을 트랜잭션 롤 포워드(Roll Forward)이라고 부른다. 반대로 데이터 파일에는 일부 변경 내용이 반영이 되었으나 해당 트랜잭션이 완벽히 완료되지 못하고 중간에 강제 종료된 것이라면 데이터 파일에 이미 반영된 내용을 취소하는 처리를 하는데 이러한 과정을 트랜잭션 롤 백(Roll Back)이라고 부른다.

2.2 페이지와 익스텐트

데이터 파일의 내부적인 부분을 잠시 살펴보자. 내부적인 부분을 꼭 알아야 되는지 의구심을 가질 수도 있겠지만, 페이지와 익스텐트에 대한 개념을 어느 정도 가지고 있다면 분명 도움이 되는 시점이 있을 것이다. 특히 페이지 개념이 그러하다.

1) 페이지(Page)

데이터는 8KB 단위의 디스크 공간에 기록되는데 이것을 페이지라고 부른다. 결과적으로 데이터베이스는 IMB에는 128개의 페이지를 저장 할 수 있게 된다. 하나의 행(Row)은 기본적으로 여러 개의 페이지에 나뉘어 저장 될 수 없고 단 하나의 페이지에만 저장 될 수 있다. 이로 인해서 하나의 데이터 행의 최대 크기는 8,060 Byte이다. 계산해보면 8 Î 1,024 는 8,192 이지만, 8,192Byte를 행이 전부 차지하지 못하는 것은 내부적으로 몇몇 정보를 저장하기 위해 어느 정도의 공간이 사용되기 때문이다.

행 크기 제한에 대한 예외가 존재한다. 우선 varchar, nvarchar, varbinary, sql_variant 형 칼럼을 포함하는 행은 한 페이지, 즉 8KB를 넘는 사이즈를 가질 수 있다. 단, 개별 칼럼은 8KB를 넘을 수는 없다. 그리고 varchar(max)와 같이 max 한정자를 사용하면 해당 열에는 2GB 크기까지의 데이터를 저장 할 수 있다.

2) 익스텐트(Extents)

8KB 크기의 페이지 8개가 모여있는 단위를 익스텐트라고 부른다. SQL Server는 기본적으로 테이블이 만들어지고 데이터가 추가 될 때 처음에는 8KB 크기의 페이지를 하나씩 하나씩 할당하다가 나중에는 페이지 8개를 한번에 할당하여 데이터 저장을 위한 공간 할당이 효율적으로 이루어지게 한다. 처음부터 익스텐트가 할당되도록 SQL Server 시작 옵션을 추가 할 수 있지만 이를 위해서는 신중한 검토가 필요하다.

2.3 데이터 파일과 로그 파일에 대한 고민 필요

데이터베이스는 곧 파일이기에 만들기 전에 다음과 같은 사항을 고민하여 결정해야 한다.

• 데이터 파일과 로그 파일의 위치
• 데이터 파일과 로그 파일의 개수
• 데이터 파일과 로그 파일의 처음 크기
• 데이터 파일과 로그 파일의 자동 증가 크기와 최대 크기

물론 하드웨어를 구성하는 단계라면 데이터 파일과 로그 파일을 저장하게 되는 디스크에 대한 RAID 구성을 어떻게 할지, 빠른 성능을 위해 SSD를 장착할지 등도 고민을 해야 한다. 이렇게 데이터베이스를 만드는 것은 쉬운 일이 아니다.

2.4 트랜잭션 로그 파일

앞에서 트랜잭션 로그는 SQL Server에 있어서 매우 중요한 역할을 갖는다고 이야기 했다. SQL Server는 데이터 변경 시 다음과 같은 과정으로 변경 작업을 수행한다. 만일 클라이언트 응용 프로그램에서 SQL Server에 데이터 변경을 요청했다고 하면

• SQL Server는 변경이 되어야 하는 데이터 페이지를 메모리(Buffer Cache라고 부름)로 가져 온 후 메모리의 데이터를 변경한다. 만일 이미 메모리에 가져와 있는 상태라면 이것을 변경한다.
• SQL 서버는 메모리의 데이터가 변경된 내용을 로그 파일에 기록한다. 데이터 파일에 기록 하기 전에 로그부터 기록한다고 하여 로그 미리 쓰기(Write-Ahead Log)라고 부른다.
• 체크포인트(Checkpoint) 프로세스가 반복 적으로 수행되면서 데이터 파일의 내용과 메모리의 내용이 다른 데이터 페이지(Dirty 페이지라고 부름)를 데이터 파일에 기록한다.
• 로그에 기록된 내용을 근거로 시스템이 정지 된 후 다시 구동되면 자동 복구 프로세스를 통해 완료된 트랜잭션은 롤 포워드(Roll Forward) 하게 되며, 완료되지 않은 트랜잭션은 롤 백(Roll Back) 하게 된다.

성능 향상을 위해 트랜잭션 로그 파일은 데이터 파일과는 다른 디스크에 위치시키기를 권장한다. 왜냐하면 데이터 파일과 트랜잭션 로그 파일을 분리하게 되면 데이터 파일에 대한 읽기 및 쓰기 작업과 로그 파일에 대한 쓰기 작업이 각각의 디스크에 의해 처리되므로 디스크에 대한 경합을 줄일 수 있기 때문이다. 또한 트랜잭션 로그는 순차적으로 기록이 된다. 그러므로 트랜잭션 로그 파일이 별도의 디스크에 기록되면 트랜잭션 로그가 기록 된 후 그 위치에 디스크 헤드가 머무르게 되고, 이후에 추가되는 로그를 그 위치부터 이어서 기록하게 되므로 물리적으로도 빠르게 트랜잭션 로그를 기록하는 것이 가능해진다.

- 원본

http://www.yes-start.com/23337