[MSSQL]데이터 파일(.mdf/.ndf)과 로그파일(.ldf)

다른 DBMS 백업과는 달리 MSSQL 파일을 백업할 때 두 가지의 파일로 분리되는 것을 보고 궁금증이 생김....  이유는?

 

=========================================================================== 

 

MDF, .NDF 파일은 데이터베이스의 데이터 파일 /  .LDF 파일은 데이터베이스의 로그파일을 의미한다.
데이터파일과 로그파일은 데이터베이스의 의해 개별적으로 관리된다.

MSSQL에서 관리되는 파일은 다음과 같이 3가지 타입이다.

* Primary Data File
주 데이터 파일은 데이터베이스의 시작 지점이며 데이터베이스의 나머지 파일을 가리킵니다. 모든 데이터베이스에는 하나의 주 데이터 파일이 있습니다. 권장되는 주 데이터 파일 확장명은 .mdf입니다.

* Secondary Data File
보조 데이터 파일은 주 데이터 파일이 아닌 모든 데이터 파일을 구성합니다. 데이터베이스에 따라 보조 데이터 파일이 없을 수도 있고 여러 개 있을 수도 있습니다. 권장되는 보조 데이터 파일 확장명은 .ndf입니다.

* Log File
로그 파일에는 데이터베이스를 복구하는 데 사용되는 모든 로그 정보가 들어 있습니다. 데이터베이스에 로그 파일이 두 개 이상 있을 수 있으며 데이터베이스마다 적어도 하나의 로그 파일이 있어야 합니다. 권장되는 로그 파일 확장명은 .ldf입니다.

위 그림과 같이 데이터베이스 파일은 반드시 Primary 파일그룹을 가지고 Primary 파일그룹안에는 반드시 .mdf 파일이 존재해야 한다.
그리고 .ldf 파일 역시 반드시 존재해야하고, .ndf 파일은 보조 데이터 파일이기 때문에 반드시 존재할 필요는 없다.

 

데이터베이스에 생성되는 주 데이터 파일인 .mdf 파일과 함께 보조 데이터 파일인 .ndf 파일을 생성하는 이유는 무엇일까?

데이터파일에 저장 시 파일그룹을 나눠 저장하면 하나의 파일그룹을 사용할 때 보다 좋은 효과를 낼 수 있다.
예를 들어,
어떤 데이터베이스에 테이블이 10개가 있다. 3개의 테이블을 가장 많이 사용하고 나머지 7개의 테이블은 거의 사용을 안한다고 생각해 보자.
이때 데이터베이스를 파일 그룹으로 생성하고 많이 사용하는 3개의 테이블을 프라이머리 파일그룹에 생성해 물리적인 하나의 디스크에 구축한다. 
아울러 자주 사용하지 않는 7개의 테이블을 보조 파일 그룹에 생성해 또 다른 물리적인 디스크에 구축하는 것이다.
그렇게 되면 물리적으로 다른 디스크를 사용하게 되니 하나의 디스크를 사용 할 때 보다 데이터파일 사용의 경쟁이 줄 것이다.

이렇듯 보조 데이터 파일을 생성하여 데이터 관리를 효율적으로 하게되면 데이터 베이스의 성능을 높힐 수 있을 것이다.

데이터파일과 달리 데이터가 순차적으로 저장되는 로그파일이 있다.

앞서 언급한 바와 같이 데이터파일에 쓰고 난 후 로그파일에 까지 쓰면 데이터베이스의 성능이 낮아질 수 있다.
그런데도 불구하고 로그파일을 쓰는 이유는 무엇인가?

 

그것은 바로 완전한데이터, 즉 데이터베이스의 무결성 때문이다.

가장 흔한 예로 "게임의 아이템 거래"를 들 수 있다.
A가 B에게 "+10 일본도"를 거래창을 통해 건네준다고 가정해 보면, 기본적인 처리 흐름은 아래와 같을 것이다.
B의 인벤에 "+10 일본도를 추가할 수 있는지 예외 처리

1. B의 인벤에 "+10 일본도"를 삽입
2. A의 인벤에서 "+10 일본도"를 제거

그런데, 만약 2번 과정까지만 정상적으로 처리되고, 정전이나 하드웨어 문제가 발생한다면 어떻게 될까?

B의 인벤에도 "+10 일본도"가 생기고, A의 인벤에서도 "+10 일본도"가 남아 있다면?

위 그림과 같이 이런 일을 제대로 된 트랜잭션 처리 + 트랜잭션 로그 파일을 통해 막을 수 있다.

SQLServer는 트랜잭션 로그 파일을 사용함으로써, 다시 전기가 들어왔을 때

• B의 인벤에서 "+10 일본도"를 다시 제거하거나,
• A의 인벤에서 "+10 일본도"를 제대로 제거해 주어야 한다.

물론 B의 인벤에서 "+10 일본도"가 제거된다면, A와 B는 다시 거래 과정을 거쳐야겠지만, 

고가의 아이템이 복사되는 대형 사고는 발생하지 않을 것이다.

이것이 데이터베이스의 무결성이며 , 로그파일을 사용하는 이유이다.

그리고 로그파일은 모든 행위에 대하여 기록을 남기는 것이 아니라, insert나 update등 데이터의 변화가 생길 시 기록을 남긴다.
즉 select와 같이 데이터의 변화없이 조회를 하는 행위는 로그파일에 기록되지 않는다.

출처 : http://www.tiptech.net/lecture/sql/sql2000/dbase2.html