Sql server 2005
Sponsored Links
This presentation is the property of its rightful owner.
1 / 31

뇌를 자극하는 SQL Server 2005 PowerPoint PPT Presentation


  • 124 Views
  • Uploaded on
  • Presentation posted in: General

뇌를 자극하는 SQL Server 2005. 7 장 . 데이터베이스 생성과 관리. 데이터베이스 기본 구조 (1). 데이터베이스의 물리적 실체 데이터베이스는 물리적으로 파일임 기본적으로 ‘*.mdf’ 와 ‘*.ldf’ 두 파일이 생김 데이터 파일 “*.mdf” 또는 “*.ndf” 로 생성됨 . 이 파일에는 데이터베이스 개체 ( 테이블 , 인덱스 등 ) 와 그 행 데이터가 저장됨 트랜잭션 로그파일 “*.ldf” 로 생성됨 . 정전 등의 응급상황에서 입력된 데이터가 완전하도록 함 .

Download Presentation

뇌를 자극하는 SQL Server 2005

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


뇌를 자극하는 SQL Server 2005

7장.

데이터베이스 생성과 관리


데이터베이스 기본 구조 (1)

  • 데이터베이스의 물리적 실체

    • 데이터베이스는 물리적으로 파일임

    • 기본적으로 ‘*.mdf’와 ‘*.ldf’ 두 파일이 생김

  • 데이터 파일

    • “*.mdf” 또는 “*.ndf”로 생성됨.

    • 이 파일에는 데이터베이스 개체(테이블, 인덱스 등)와 그 행 데이터가 저장됨

  • 트랜잭션 로그파일

    • “*.ldf” 로 생성됨.

    • 정전 등의 응급상황에서 입력된 데이터가 완전하도록 함.

    • 전부 되거나 전부 안되거나(All or Nothing)를 지원함.


데이터베이스 기본 구조 (2)

  • SQL문이 동작하는 데이터베이스의 간단한 구조도

    • SELECT 문 : 1  2  4

    • UPDATE 문 : 1  3  2  4


트랜잭션의 개념 (1)

  • 트랜잭션 개념

    • 하나의 논리적 작업단위로 수행되는 일련의 작업

    • SQL문(SELECT/INSERT/UPDATE/DELETE)의 묶음

    • 한 단위의 트랜잭션은 모두 처리되거나, 모두 처리 되지 않도록 DBMS가 관리해 준다.

  • 구문 형식

    BEGIN TRANSACTION(또는 BEGIN TRAN)

    SQL 문장들

    COMMIT TRANSACTION(또는 COMMIT TRAN 또는 COMMIT WORK)


트랜잭션의 개념 (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


트랜잭션의 작동방식 (1)

  • 트랜잭션의 작동 방식 예제

    BEGIN TRAN

    UPDATE 1  11

    UPDATE 2  22

    UPDATE 3  33

    COMMIT TRAN

  • BEGIN TRAN 실행


트랜잭션의 작동방식 (2)

  • UPDATE 1  11 실행


트랜잭션의 작동방식 (3)

  • UPDATE 2  22, UPDATE 3  33 실행


트랜잭션의 작동방식 (4)

  • COMMIT TRAN 실행


사고 후의 복구 방식 (1)

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


사고 후의 복구 방식 (2)

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

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

    • 위와 동일한 작업을 함


사고 후의 복구 방식 (3-1)

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


사고 후의 복구 방식 (3-2)

  • 앞 그림의 ⑧번까지 진행 한 후에 …


시스템 데이터베이스와 예제 데이터베이스 (1)

  • master 데이터베이스

    • SQL Server 의 가장 중요한 데이터베이스

    • 문제 발생시 SQL Server 가 작동하지 않을 수 있음

    • 디스크공간, 시스템 전체의 구성정보, 계정정보, 사용자 데이터베이스의 정보 등이 저장되어 있음

    • 꼭 백업을 수행해 줘야 함.

  • model 데이터베이스

    • 사용자데이터베이스를 생성할 때 기본틀이 됨

    • 특별히 변경시에만 백업을 하면 됨

  • msdb 데이터베이스

    • 주로 SQL Server 에이전트가 사용하며, 예약된 작업 등이 저장.

    • 사용자가 특별히 신경쓰지 않아도 되지만, 변경하거나 삭제하면 안됨


시스템 데이터베이스와 예제 데이터베이스 (2)

  • tempdb 데이터베이스

    • SQL Server가 사용하는 임시 데이터베이스

    • SQL Server를 가동할 때마다 새로 생성되므로, 그 내용을 모두 지워짐.  그러므로, 잠시라도 보관할 데이터를 저장하면 안됨.

    • 사용자 쿼리의 중간결과 등도 저장되며, 임시테이블(#,##)도 저장됨.

    • 시스템 성능에 영향을 미칠 수 있으므로, 잘 관리할 필요가 있음.

  • 복제 관련 데이터베이스

    • Publication : 게시자 역할을 할 경우 생성됨

    • Distribution : 배포자 역할을 할 경우 생성됨

    • Subscription : 구독자 역할을 할 경우 생성됨

  • 예제데이터베이스

    • AdventureWorks 및 AdventureWorksDW


데이터베이스 생성/관리 : 데이터베이스 생성 (1)

  • SSMS를 이용해서 생성

    • SSMS를 이용하면 간단히 DB를 생성할 수 있다.


데이터베이스 생성/관리 : 데이터베이스 생성 (2)

  • 항목 설명

    • 데이터베이스 이름 : DB의 이름을 직접 입력

    • 논리적 이름 : 데이터 파일 및 로그파일의 논리적인 이름

    • 파일 유형 : 데이터 파일 또는 로그파일로 지정

    • 파일 그룹 : 기본적으로 PRIMARY 파일 그룹으로 지정됨

    • 처음 크기 : 처음 생성되는 파일의 크기

    • 자동 증가 : 처음 크기를 넘었을 때 파일의 증가량

    • 경로 : 파일이 저장되는 폴더위치

    • 파일이름 : 파일의 물리적 이름

  • 실습 목표

    • SSMS에서 데이터베이스를 생성해 본다.

    • 파일의 자동증가의 의미와 크기를 제한했을 때 문제점을 파악한다.

    • 로그파일의 증가시에 문제가 발생되는 것을 확인해 본다.

<실습3> SSMS에서 데이터베이스 생성


데이터베이스 생성/관리 : 데이터베이스 생성 (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 ]


<실습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%) -- 자동 증가 %


데이터베이스 생성/관리 : 파일 그룹 (1)

  • 개념

    • 아무런 옵션이 없을 때의 DB 구조

    • 로그파일은 파일그룹과 관련 없음

    • 기본적으로 PRIMARY 라는 파일그룹이 생성되고, 그 내부에 데이터 파일이 1개 존재하게 됨

    • 필요하다면 PRIMARY 그룹에 여러 개의 데이터파일을 둘 수 있음

    • 또한, 여러 개의 파일그룹을 두고, 각각의 파일그룹안에 여러 개의 데이터파일을 둘 수도 있음  SQL Server의 성능향상에 도움이 많이 됨.


데이터베이스 생성/관리 : 파일 그룹 (2)

  • 하나의 파일그룹에 여러 개의 데이터 파일을 사용(동일한 디스크에 저장 시)

    • PRIMARY 그룹 안에 여러 개의 파일을 둬서 데이터파일의 용량 초과시에 관리를 편리하게 사용할 수 있음.

    • 또한 로그파일을 별도의 디스크에 둬서 디스크 경합을 줄임.

    • 데이터 파일이 한 디스크(C:)에 들어 있어서, 관리는 편리하겠으나 성능에는 별 도움이 안됨.


데이터베이스 생성/관리 : 파일 그룹 (3)

  • 여러 개의 파일그룹에 여러 개의 데이터 파일을 사용(서로 다른 디스크에 저장 시)

    • 파일그룹에 소속된 각 데이터파일이 별도의 디스크에 존재하게 됨으로써 디스크의 입출력 속도가 월등하게 향상됨

    • 대용량 데이터베이스에서 필요한 방식이며, 소용량일 경우에는 오히려 관리에만 불편할 수 있음.


<실습5> SSMS 에서 파일그룹을 생성

  • 실습 목표

    • SSMS를 이용해서 파일 그룹을 생성하는 방법을 익힌다.

  • 실습 결과 화면


<실습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 ),


데이터베이스 크기 늘리기 및 줄이기

<실습7> 데이터베이스의 크기 변경

  • 실습 목표

    • 데이터베이스의 크기를 늘리거나 줄이는 방법을 익힌다.

    • 데이터파일의 중간에 빈 공간이 생겼을 때 데이터파일을 줄이는 방법을 확인한다.

    • 여러 개의 데이터파일 중에 일부 데이터 파일을 제거하는 방법을 익힌다.

  • 실습 요약

    • 대량의 데이터 입력을 통한 데이터파일의 용량 증가 확인

    • DBCC SHRINKDATABASE : 데이터베이스 축소

    • DBCC SHRINKFILE : 특정 데이터파일 축소


데이터베이스 수정 (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

      )


데이터베이스 수정 (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


데이터베이스 수정 (3)

  • 데이터베이스 파일의 논리적/물리적 이름 바꾸기

    • SSMS는 논리적 파일명은 변경가능하나, 물리적 파일명은 변경 불가

  • 실습 목표

    • T-SQL을 이용해서 데이터베이스 파일의 논리적/물리적이름을 변경하는 방법을 익힌다.

  • 실습 순서 요약

    • 데이터베이스 이름 변경

    • 논리적 파일명 변경

    • 데이터베이스 오프라인

    • 데이터 파일을 새로운 폴더로 옮기고 이름도 변경

    • 물리적인 파일명 및 경로 변경

    • 데이터베이스 온라인

  • <실습8> 데이터베이스 파일의 이름 변경


    <실습9> tempdb의 데이터 및 로그파일을 변경

    • 실습 목표

      • 시스템의 성능향상을 위해서 tempdb를 빠른 디스크(RAID0)로 이동시키는 방법을 익힌다.

    • 실습 결과 화면


    데이터베이스 수정 (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이름;

    을 사용하면 된다.


    데이터베이스 옵션

    • 데이터베이스 주요 옵션의 일부

      • 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으로 되어 있다


  • Login