뇌를 자극하는 SQL Server 2005

1. 뇌를 자극하는 SQL Server 2005 7장. 데이터베이스 생성과 관리

2. 데이터베이스 기본 구조 (1) • 데이터베이스의 물리적 실체 • 데이터베이스는 물리적으로 파일임 • 기본적으로 ‘*.mdf’와 ‘*.ldf’ 두 파일이 생김 • 데이터 파일 • “*.mdf” 또는 “*.ndf”로 생성됨. • 이 파일에는 데이터베이스 개체(테이블, 인덱스 등)와 그 행 데이터가 저장됨 • 트랜잭션 로그파일 • “*.ldf” 로 생성됨. • 정전 등의 응급상황에서 입력된 데이터가 완전하도록 함. • 전부 되거나 전부 안되거나(All or Nothing)를 지원함.

3. 데이터베이스 기본 구조 (2) • SQL문이 동작하는 데이터베이스의 간단한 구조도 • SELECT 문 : 1  2  4 • UPDATE 문 : 1  3  2  4

4. 트랜잭션의 개념 (1) • 트랜잭션 개념 • 하나의 논리적 작업단위로 수행되는 일련의 작업 • SQL문(SELECT/INSERT/UPDATE/DELETE)의 묶음 • 한 단위의 트랜잭션은 모두 처리되거나, 모두 처리 되지 않도록 DBMS가 관리해 준다. • 구문 형식 BEGIN TRANSACTION(또는 BEGIN TRAN) SQL 문장들 COMMIT TRANSACTION(또는 COMMIT TRAN 또는 COMMIT WORK)

5. 트랜잭션의 개념 (2) • 일반적으로 사용하는 SQL문 USE sqlDB GO UPDATE userTbl SET addr = N'서울' WHERE userID = N'AJH' -- 안정환 UPDATE userTbl SET addr = N'경기' WHERE userID = N'CJC' -- 최진철 UPDATE userTbl SET addr = N'강원' WHERE userID = N'JJJ' -- 조재진 GO • 자동커밋드랜잭션 방식에 의한 내부 작동 BEGIN TRANSACTION UPDATE userTbl SET addr = N'서울' WHERE userID = N'AJH' -- 안정환 COMMIT TRANSACTION BEGIN TRAN UPDATE userTbl SET addr = N'경기' WHERE userID = N'CJC' -- 최진철 COMMIT TRAN BEGIN TRAN UPDATE userTbl SET addr = N'강원' WHERE userID = N'JJJ' -- 조재진 COMMIT WORK

6. 트랜잭션의 작동방식 (1) • 트랜잭션의 작동 방식 예제 BEGIN TRAN UPDATE 1  11 UPDATE 2  22 UPDATE 3  33 COMMIT TRAN • BEGIN TRAN 실행

7. 트랜잭션의 작동방식 (2) • UPDATE 1  11 실행

8. 트랜잭션의 작동방식 (3) • UPDATE 2  22, UPDATE 3  33 실행

9. 트랜잭션의 작동방식 (4) • COMMIT TRAN 실행

10. 사고 후의 복구 방식 (1) • “트랜잭션의 작동방식 (1)” 완료된 후에 정전 다시 전원이 들어오면..

11. 사고 후의 복구 방식 (2) • “트랜잭션의 작동방식 (2)” 완료된 후에 정전 다시 전원이 들어오면.. • “트랜잭션의 작동방식 (3)” 완료된 후에 정전 다시 전원이 들어오면.. • 위와 동일한 작업을 함

12. 사고 후의 복구 방식 (3-1) • “트랜잭션의 작동방식 (4)”에서②번의 로그 파일에 COMMIT TRAN을 기록한 직후에, 정전이 되고 다시 전원이 들어온 경우 (즉, 로그 파일에는 모두 적용이 되었으나 데이터 파일에는 적용되지 않고 정전된 경우)

13. 사고 후의 복구 방식 (3-2) • 앞 그림의 ⑧번까지 진행 한 후에 …

14. 시스템 데이터베이스와 예제 데이터베이스 (1) • master 데이터베이스 • SQL Server 의 가장 중요한 데이터베이스 • 문제 발생시 SQL Server 가 작동하지 않을 수 있음 • 디스크공간, 시스템 전체의 구성정보, 계정정보, 사용자 데이터베이스의 정보 등이 저장되어 있음 • 꼭 백업을 수행해 줘야 함. • model 데이터베이스 • 사용자데이터베이스를 생성할 때 기본틀이 됨 • 특별히 변경시에만 백업을 하면 됨 • msdb 데이터베이스 • 주로 SQL Server 에이전트가 사용하며, 예약된 작업 등이 저장. • 사용자가 특별히 신경쓰지 않아도 되지만, 변경하거나 삭제하면 안됨

15. 시스템 데이터베이스와 예제 데이터베이스 (2) • tempdb 데이터베이스 • SQL Server가 사용하는 임시 데이터베이스 • SQL Server를 가동할 때마다 새로 생성되므로, 그 내용을 모두 지워짐.  그러므로, 잠시라도 보관할 데이터를 저장하면 안됨. • 사용자 쿼리의 중간결과 등도 저장되며, 임시테이블(#,##)도 저장됨. • 시스템 성능에 영향을 미칠 수 있으므로, 잘 관리할 필요가 있음. • 복제 관련 데이터베이스 • Publication : 게시자 역할을 할 경우 생성됨 • Distribution : 배포자 역할을 할 경우 생성됨 • Subscription : 구독자 역할을 할 경우 생성됨 • 예제데이터베이스 • AdventureWorks 및 AdventureWorksDW

16. 데이터베이스 생성/관리 : 데이터베이스 생성 (1) • SSMS를 이용해서 생성 • SSMS를 이용하면 간단히 DB를 생성할 수 있다.

17. 데이터베이스 생성/관리 : 데이터베이스 생성 (2) • 항목 설명 • 데이터베이스 이름 : DB의 이름을 직접 입력 • 논리적 이름 : 데이터 파일 및 로그파일의 논리적인 이름 • 파일 유형 : 데이터 파일 또는 로그파일로 지정 • 파일 그룹 : 기본적으로 PRIMARY 파일 그룹으로 지정됨 • 처음 크기 : 처음 생성되는 파일의 크기 • 자동 증가 : 처음 크기를 넘었을 때 파일의 증가량 • 경로 : 파일이 저장되는 폴더위치 • 파일이름 : 파일의 물리적 이름 • 실습 목표 • SSMS에서 데이터베이스를 생성해 본다. • 파일의 자동증가의 의미와 크기를 제한했을 때 문제점을 파악한다. • 로그파일의 증가시에 문제가 발생되는 것을 확인해 본다. <실습3> SSMS에서 데이터베이스 생성

18. 데이터베이스 생성/관리 : 데이터베이스 생성 (3) • T-SQL을 이용해서 생성 • 간단한 구문 형식 CREATE DATABASE database_name [ ON [ PRIMARY ] [ <filespec> [ ,...n ] [ , <filegroup> [ ,...n ] ] [ LOG ON { <filespec> [ ,...n ] } ] ] [ COLLATE collation_name ] [ WITH <external_access_option> ] ] [;] • <filespec> 부분의 구문 형식 ( NAME = logical_file_name , FILENAME = 'os_file_name' [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ] ) [ ,...n ]

19. <실습4> T-SQL을 이용한 데이터베이스 생성 • 실습 목표 • SSMS를 사용한 것과 동일한 효과를 내는 T-SQL문을 사용해 본다. • 실습 결과 쿼리 CREATE DATABASE [brainDB] ON PRIMARY -- 데이터 파일 설정 ( NAME = N'brainDB', -- 논리적 이름 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\brainDB.mdf' , -- 경로 및 파일명 SIZE = 2240KB , -- 처음 크기 MAXSIZE = UNLIMITED , -- 최대 크기 FILEGROWTH = 1024KB ) – 자동 증가 크기 LOG ON -- 트랜잭션 로그 파일 설정 ( NAME = N'brainDB_log', -- 논리적 이름 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\brainDB_log.LDF' , SIZE = 560KB , -- 처음 크기 MAXSIZE = 2048GB , -- 최대 크기 FILEGROWTH = 10%) -- 자동 증가 %

20. 데이터베이스 생성/관리 : 파일 그룹 (1) • 개념 • 아무런 옵션이 없을 때의 DB 구조 • 로그파일은 파일그룹과 관련 없음 • 기본적으로 PRIMARY 라는 파일그룹이 생성되고, 그 내부에 데이터 파일이 1개 존재하게 됨 • 필요하다면 PRIMARY 그룹에 여러 개의 데이터파일을 둘 수 있음 • 또한, 여러 개의 파일그룹을 두고, 각각의 파일그룹안에 여러 개의 데이터파일을 둘 수도 있음  SQL Server의 성능향상에 도움이 많이 됨.

21. 데이터베이스 생성/관리 : 파일 그룹 (2) • 하나의 파일그룹에 여러 개의 데이터 파일을 사용(동일한 디스크에 저장 시) • PRIMARY 그룹 안에 여러 개의 파일을 둬서 데이터파일의 용량 초과시에 관리를 편리하게 사용할 수 있음. • 또한 로그파일을 별도의 디스크에 둬서 디스크 경합을 줄임. • 데이터 파일이 한 디스크(C:)에 들어 있어서, 관리는 편리하겠으나 성능에는 별 도움이 안됨.

22. 데이터베이스 생성/관리 : 파일 그룹 (3) • 여러 개의 파일그룹에 여러 개의 데이터 파일을 사용(서로 다른 디스크에 저장 시) • 파일그룹에 소속된 각 데이터파일이 별도의 디스크에 존재하게 됨으로써 디스크의 입출력 속도가 월등하게 향상됨 • 대용량 데이터베이스에서 필요한 방식이며, 소용량일 경우에는 오히려 관리에만 불편할 수 있음.

23. <실습5> SSMS 에서 파일그룹을 생성 • 실습 목표 • SSMS를 이용해서 파일 그룹을 생성하는 방법을 익힌다. • 실습 결과 화면

24. <실습6> T-SQL을 이용한 파일그룹을 생성 • 실습 목표 • T-SQL문을 이용해서 파일 그룹을 생성하는 방법을 익힌다. • 기본 파일그룹을 변경하는 방법을 익힌다. • 실습 쿼리 일부 CREATE DATABASE [brainDB] ON PRIMARY -- 지금부터 PRIMARY 그룹에 데이터파일을 생성 ( NAME = 'brainDB', -- 첫번째 데이터 파일 FILENAME = N'C:\하드디스크C\brainDB.mdf' , -- 파일경로와 파일명 SIZE = 10240KB , -- 처음 크기 MAXSIZE = 102400KB , -- 최대 파일 크기 FILEGROWTH = 1024KB ), -- 파일 증가 : 1MB 단위로 NAME = 'brainDB2', -- 두번째 데이터 파일 FILENAME = N'C:\하드디스크D\brainDB2.ndf' , SIZE = 10240KB , MAXSIZE = 102400KB , FILEGROWTH = 1024KB ),

25. 데이터베이스 크기 늘리기 및 줄이기 <실습7> 데이터베이스의 크기 변경 • 실습 목표 • 데이터베이스의 크기를 늘리거나 줄이는 방법을 익힌다. • 데이터파일의 중간에 빈 공간이 생겼을 때 데이터파일을 줄이는 방법을 확인한다. • 여러 개의 데이터파일 중에 일부 데이터 파일을 제거하는 방법을 익힌다. • 실습 요약 • 대량의 데이터 입력을 통한 데이터파일의 용량 증가 확인 • DBCC SHRINKDATABASE : 데이터베이스 축소 • DBCC SHRINKFILE : 특정 데이터파일 축소

26. 데이터베이스 수정 (1) • 구문 형식 ALTER DATABASE database_name { <add_or_modify_files> | <add_or_modify_filegroups> | <set_database_options> | MODIFY NAME = new_database_name | COLLATE collation_name } [;] • 파일 늘이기 • SSMS는 데이터베이스 속성창에서 간단히 변경 가능 • T-SQL 예제 ALTER DATABASE brainDB -- 데이터베이스 이름 MODIFY FILE ( NAME = brainDB, -- 데이터베이스 파일의 논리적 이름 SIZE = 200 -- 기본단위는 MB )

27. 데이터베이스 수정 (2) • 데이터베이스 이름 바꾸기 • SSMS는 데이터베이스 속성창 SINGLE_USER로 바꾼 후에 이름변경 • T-SQL 예제 : brainDBhanbitDB로 변경 ALTER DATABASE brainDB SET SINGLE_USER GO ALTER DATABASE brainDB MODIFY NAME = hanbitDB GO ALTER DATABASE hanbitDB SET MULTI_USER GO

28. 데이터베이스 수정 (3) • 데이터베이스 파일의 논리적/물리적 이름 바꾸기 • SSMS는 논리적 파일명은 변경가능하나, 물리적 파일명은 변경 불가 • 실습 목표 • T-SQL을 이용해서 데이터베이스 파일의 논리적/물리적이름을 변경하는 방법을 익힌다. • 실습 순서 요약 • 데이터베이스 이름 변경 • 논리적 파일명 변경 • 데이터베이스 오프라인 • 데이터 파일을 새로운 폴더로 옮기고 이름도 변경 • 물리적인 파일명 및 경로 변경 • 데이터베이스 온라인 <실습8> 데이터베이스 파일의 이름 변경

29. <실습9> tempdb의 데이터 및 로그파일을 변경 • 실습 목표 • 시스템의 성능향상을 위해서 tempdb를 빠른 디스크(RAID0)로 이동시키는 방법을 익힌다. • 실습 결과 화면

30. 데이터베이스 수정 (4) • 데이터베이스 파일 및 파일그룹의 추가 및 삭제 • SSMS • 데이터베이스 속성창의 [파일] 페이지에서 <추가> 또는 <삭제>버튼을 클릭해서 사용 • T-SQL • 데이터 파일 추가 ALTER DATABASE hanbitDB ADD FILE ( … • 로그 파일 추가 ALTER DATABASE hanbitDB ADD LOG FILE ( … • 파일 삭제 ALTER DATABASE hanbitDB REMOVE FILE hanbitDB3 • 파일 그룹 생성 ALTER DATABASE hanbitDB ADD FILEGROUP third 데이터베이스의 삭제는 간단히 DROP DATABASE DB이름; 을 사용하면 된다.

31. 데이터베이스 옵션 • 데이터베이스 주요 옵션의 일부 • AUTO_SHRINK • 데이터베이스가 자동으로 줄어들지를 결정하는 옵션 • SINGLE_USER | RESTRICTED_USER | MULTI_USER • 데이터베이스에 접근하는 사용자의 수를 결정한다 • OFFLINE | ONLINE | EMERGENCY • 데이터베이스의 가동 상태를 설정한다 • READ_ONLY | READ_WRITE • READ_ONLY 로 설정하면 데이터베이스를 수정할 수가 없다 • RECOVERY • 데이터베이스 백업/복구와 관련된 옵션 • AUTO_CREATE_STATISTICS 및 AUTO_UPDATE_STATISTICS • 통계 자동작성과 관련된 옵션으로 디폴트는 ON으로 되어 있다