이인석_오늘과내일 운영관리팀 대리
EM과 쿼리아날라이저로 데이터베이스 작성
이제 셋업을 했으니 데이터베이스를 다뤄야 할 것이다. 지금부터는 EM과 쿼리아날라이저를 동시에 열어 놓고 작업을 해 보는 것도 추천한다. 같이 열어놓고 작업할 때 쿼리에서 실행돼 결과가 정상적으로 출력됐는데 EM에서는 적용이 안됐다고 나타나는 경우가 있는데, 이럴 때는 EM을 F5키로 새로고침한다. EM에는 결과값이 적용됐지만 보여지지 않기 때문이다. 같이 열어놓고 작업을 하되 주로 작업은 쿼리아날라이저에서 작업하는 것을 권장한다. EM에서도 작업해도 되지만, 쿼리로 하는 것이 여러모로 공부하는 데 많은 도움이 된다.
이제 데이터베이스를 만들어 보자. 매우 간단하다. 쿼리아날라이저에서 다음과 같이 구문을 넣는다.
CREATE DATABASE DATABASE_NAME
물론 이와 같은 쿼리문이 전부가 아니다. 너무 간단하다면 의심해야 한다. 마이크로소프트 SQL을 이야기하면 약방의 감초처럼 등장하는 온라인 설명서(마이크로 소프트 SQL을 설치를 하고 나면 SQL 프로그램 그룹에서 볼 수 있다)를 열어서 CREATE DATABASE를 검색해 보자. 그럼 다음과 같은 문법을 찾을 수 있다.
CREATE DATABASE database_name
[ ON
[ < filespec > [ ,...n ] ]
[ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]
< filespec > ::=
[ PRIMARY ]
( [ NAME = logical_file_name , ]
FILENAME = 'os_file_name'
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ ,...n ]
< filegroup > ::=
FILEGROUP filegroup_name < filespec > [ ,...n ]
CREATE DATABASE에 보면 인수라고 돼 있고 각각의 인수에 대해 설명돼 있다. 기본적으로 DATABASE_NAME이라는 이름으로 데이터베이스가 만들어지기는 한다. 그러나 좀더 세부적으로 하려면 이와 같은 인수사항이 들어가야 한다. 논리적인 파일명을 지정할 수도 있고, 물리적인 파일명을 파일 경로와 함께 지정할 수 있다. 그리고 해당 파일의 크기를 지정할 수도 있고 파일의 자동 증가도 설정할 수 있는 여러가지 옵션이 있다. 이런 것들을 지정하지 않으면 새로 생성하려는 데이터베이스를 어떤 기준으로 만들까. 마이크로소프트 SQL을 설치하면 MASTER, MODEL, MSDB, TEMPDB과 같은 4개의 시스템 데이터베이스가 생성된다.
이와 같이 새로 생성된 데이터베이스는 MODEL을 기준으로 생성된다. 4개의 데이터베이스는 각각 어떤 역할을 할까. MASTER는 SQL 시스템의 전반적인 정보(시스템 테이블, 스토어드 프로시저)가 저장되는 아주 중요한 데이터베이스로, MASTER가 깨지면 SQL이 더 이상 작동하지 않는다. 아울러 로그인도 관리해야하고 서버 역할 등이 존재한다. MASTER는 SQL이 변경되는 사항이 있다면 백업을 받아야 한다. MODEL은 설명했듯이 새로 생성하는 데이터베이스의 모델이 돼 MODEL을 참조로 해서 데이터베이스를 만든다.
MSDB는 자동화, 복제의 일부, DTS와 관련된 사항이 MSDB에 저장된다. EM을 이용해 백업 작업을 자동화했다고 하자. 그렇게 되면 이같은 설정 사항은 MSDB에 저장된다. TEMPDB는 임시 작업 공간으로, 임시 작업에는 임시 테이블을 만들거나 정렬 작업을 할 때 사용된다. 중요한 것은 TEMPDB의 특성은 SQL 서버가 시작될 때마다 자동적으로 지워지고 다시 만들어지는 특성이 있다. 따라서 테스트 형식으로 임시로 작업을 한다고 여기에 테이블을 두면 곤란한 경우에 처할 수도 있다. MASTER, MSDB, MODEL은 반드시 백업을 받아야 한다. 만약 백업받지 않게 되면 SQL 로그인 계정도 다시 생성해야 하고 자동화 작업도 다시 만들어야 하는 작업을 반복해야 한다. 백업은 아무리 강조해도 지나치지 않는다.
이렇게 MODEL을 참고로 데이터베이스를 만들었다. 이제 데이터베이스 안에 테이블을 만들어 본다. 테이블도 역시 이와 같이 CREATE DATABASE 구문처럼 찾을 수 있고 마찬가지로 여러가지 인수들로 테이블을 만들 수 있다. 사람들의 눈에 보이는 테이블의 구조는 일상 생활에서 보는 표를 생각하면 된다.
테이블로 구성되는 데이터베이스
이제 데이터베이스도 만들었고 데이터베이스 안에 들어 있어야 할 테이블도 만들었다. 그럼 여기서 간단하게 데이터베이스의 특징과 4대 구문의 예제를 들어보자. 이 테이블에 데이터를 삽입해야 한다. 예를 들어 이제 막 회사가 설립됐는데, 회사의 자재나 사원들의 개인 정보 등을 입력(insert)했다고 하자. 이걸 어떻게 사용해야 할까. 원활하게 사용하기 위해서는 데이터베이스를 구축해야 한다. 괜히 비싼 돈 들여 비싼 서버에 라이선스 비용에 프로그래머와 DBA를 고용한 것이 아니다. 보다 신속하고 정확하게 업무를 처리하려고 데이터베이스를 구축한 것이다. 만일 이 회사가 데이터베이스를 구축하지 않고 엑셀을 이용해서 자재 관리, 사원 관리를 하고 있다면, 관리되지 않는 것은 아니지만 여러 가지 비효율적인 사항이 존재한다.
윈도우 파일 서버를 놓고 이 엑셀 파일을 권한이 있는 사람들만이 공유한다고 가정해보자. 사원 관리의 A팀의 강감찬 사원이 이순신 사원의 파일을 업데이트(update)했다. 업데이트 내용은 이순신 사원 전남 진해로 전보 발령이다. 강감찬 사원이 업데이트를 하고 파일을 다시 올려 놓았다. 자재 관리팀에서 이순신 사원이 필요하다는 판단 아래 사원에 대한 이력을 보려고 사원 관리 엑셀 파일에 접근(select)했다. 이는 강감찬 사원이 사원 관리 엑셀 파일을 다운 받은 후 5초 이후의 일이다. 둘은 같은 파일을 다운받지만 파악하려는 내용은 다르다. 이순신 사원은 진해로 전보 발령 조치가 돼 짐을 싸고 있는데 자재관리팀에서는 아직 본사에 있는 줄 알고 이순신 사원을 찾을 것이다. 혹시 자재관리팀의 담당자가 해당 엑셀 파일을 받고 이것보다 우선 순위가 높은 일을 갑자기 생겨서 그 일을 처리하고 엑셀 파일을 열어볼 참이었지만 일이 좀 밀리게 돼 다음날이나 파일을 열어 보게 됐다. 그러나 이순신은 벌써 짐싸서 진해로 가 버린 이후다.
그 이외의 다른 여러가지 특성으로 데이터베이스를 이용하고 있다. 필자는 처음 데이터베이스를 접한 것이 사이베이스라는 제품이다. 코볼로 프로그램을 제작했고 사이베이스를 데이터베이스로 이용했다. 데이터베이스를 사용하면서 가장 만족했던 부분은 강력한 검색 기능이다. 그러나 검색은 데이터베이스가 구현할 수 있는 하나의 단순한 기능이다. 데이터베이스는 데이터를 무궁무진하게 표현하고 검색, 삭제, 변경을 할 수 있다. 이런 것들이 select, insert, update, delete라는 쿼리 4대 구문으로 이뤄진다.
논리적으로 데이터가 어떻게 저장되는지 표를 예로 들어 설명했다. 정확하게 하기 위해서 (화면 4)를 보자.
(화면 4) 데이터 저장 예
(화면 4)를 보면 이제 데이터가 어떻게 테이블에 저장되는지 감이 잡힐 것이다. 그렇다면 이같은 테이블이 어떤 구조로 마이크로소프트 SQL을 이용해 컴퓨터에 저장될까.
데이터베이스를 만들게 되면 기본적으로 두 개의 파일이 생긴다. 확장자가 .mdf라는 데이터파일이 저장되는 파일과 .ldf라는 로그가 저장되는 파일 두개가 생긴다. 이 두개로 데이터베이스를 구성할 수 있다. mdf와 ldf는 데이터베이스의 특성에 따라 더 만들 수도 있고, 서로 물리적으로 다른 디스크에도 생성할 수 있다. 마이크로소프트 SQL의 저장소 기본 단위는 페이지라고 하며, 이 페이지의 크기는 8KB이다. 데이터 파일은 이 페이지로 구성된다. 각 페이지의 시작 부분에는 96바이트가 있고 이 헤더 정보에는 페이지의 유형, 그 페이지를 소유한 오브젝트 아이디, 페이지의 여유공간, 이전 페이지와 다음 페이지의 연결된 리스트를 가지고 있다. 이런 페이지는 익스텐트를 구성한다. 익스텐트는 8개의 연속적인 페이지의 단위이고 크기는 64KB이며, 테이블과 인덱스 공간을 지원할 때 익스텐트 단위로 설정된다. 이같은 구조로 데이터베이스의 저장소가 이뤄진다.
DDL(Data Definition Language)은 데이터와 데이터 간의 관계를 정의하는 데 사용되는 언어로, 데이터베이스 내에서 데이터 구조를 만드는 데 사용된다. 주요 데이터베이스 관리 시스템은 모두 SQL 데이터 정의 언어를 사용한다. DML(Data Manipulation Language)은 데이터베이스 내의 데이터를 검색, 저장, 수정과 삭제하는데 사용되는 일련의 명령어다. 여기에는 두 가지 형태의 DML이 있는데, 하나는 절차적 DML로서 사용자가 어떤 데이터가 필요하며, 어떻게 그것을 얻을 수 있는지를 사용자가 일일이 기술하는 것이며, 다른 하나인 비절차적 DML은 단지 어떤 데이터가 필요한지만을 사용자가 기술하는 것이다. 많은 정보를 제공하지 않았지만 DDL과 DML의 정의를 보면 명확하게 프로그래밍 부분과 관리 부분이 나타날 것이다.
DBA, 프로그래밍과 문서화에 중점
이번 강좌는 마이크로소프트의 사이트에 나오는 SQL의 장점을 나열하면서 설명할 수도 있지만 그런 것은 쉽게 찾아 볼 수 있어 몇가지 주의사항에 대해 알아봤다. 한가지 덧붙이고 싶은 말은 DBA를 하려면 처음부터 방향을 잘 잡아야 한다는 것이다. 필자는 역방향으로 배웠다. 쿼리문을 배우지 않고 DBA를 해보겠다고 하는 얼토당토 않는 꿈을 꾼 것이다. 우선 프로그래밍(쿼리문)을 먼저 해 보라는 것을 권한다. SELECT를 알고 검색 조건을 알게 되고 데이터를 조작하면서 데이터베이스 모델링을 꼭 배우라고 추천하고 싶다. 잘 짜여진 모델링은 데이터베이스가 그렇게 이쁘게 보일 수가 없다. 모델링을 하면 그 모델링을 바탕으로 쿼리문을 작성하면 된다. 모델링을 잘하려면 그 회사의 업무 파악을 그 회사의 담당 직원만큼이나 잘 알아야 한다.
이제 회사의 업무 흐름도를 아주 잘 파악했고 이에 맞춰 산뜻하게 모델링을 했고 프로그램을 작성했다면 완성됐다. 이제부터는 본격적으로 DBA가 활동한다. SQL 서비스가 동작하면서 예기치 못한 상황이 발생할 수 있다. 믿을만한 서버를 구입했는데 셋업부터 오동작을 일으키거나 어렵사리 셋업을 하고 데이터베이스를 만들어 올려놓고 서비스를 시작했는데 예상보다 성능이 안 나오고 시스템이 부하를 일으킨다면, 서버 컴퓨터를 한대 더 사서 데이터베이스를 분산해야 할지 셋업할 때 기계가 이상하더니 기계가 오동작을 일으키는 것은 아닐지, 윈도우 2000이 잘못됐는지, 나름대로 테스트할 때는 이상이 없었는데 프로그램 잘못인지를 분석해야 한다. 쉽지는 않지만 분석해 그 문제를 고쳐야 한다. 제대로 분석을 다 했는데 문제가 없다면 확장(scale up)을 고려해야 한다.
마지막으로 강조할 것은 문서화다. 문서화는 해당 작업에 대한 히스토리 뿐만이 아니라 서버에 대한 정보(물론 윈도우 2000에서 충분히 볼 수 있다. 그러나 윈도우 2000이 부팅이 안되면) 등 많은 부분을 문서화하기를 바란다. 문서화, 정말 귀찮은 작업이지만 해 놓고 나면 피가 되고 살이 되는 것이다. 하지만 문서는 한번 만들었다고 끝나는 것이 아니라, 변경 사항이 있으면 반드시 업데이트를 해야 한다.
이 글을 쓰고 있는 중에 마이크로소프트 SQL 서비스팩 4가 나왔다는 전자우편을 받았다. 서비스팩은 마이크로소프트의 핫픽스 패치 모음 버전이라고 보면 된다. 잘못된 버그나 수정사항이 있거나 보안적으로 문제가 되면 발표되는 것이다. 핫픽스는 단일 사항이다. 서비스팩 4가 나왔으니 이제 마이크로소프트 SQL 2000도 생명을 다하는 것 같다. 어쩌면 이제 막 입문을 하는 분들이 SQL 2000을 배우다가 SQL 2005를 배워야 할지 모르는 혼선을 가질지 모르지만, 이같은 점은 누구나 다 겪는 갈등이기도 하다.
출처 : on the NET