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

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


  • 105 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

뇌를 자극하는 SQL Server 2005

7장.

데이터베이스 생성과 관리


Sql server 2005

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

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

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

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

  • 데이터 파일

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

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

  • 트랜잭션 로그파일

    • “*.ldf” 로 생성됨.

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

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


Sql server 2005

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

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

    • SELECT 문 : 1  2  4

    • UPDATE 문 : 1  3  2  4


Sql server 2005

트랜잭션의 개념 (1)

  • 트랜잭션 개념

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

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

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

  • 구문 형식

    BEGIN TRANSACTION(또는 BEGIN TRAN)

    SQL 문장들

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


Sql server 2005

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


Sql server 2005

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

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

    BEGIN TRAN

    UPDATE 1  11

    UPDATE 2  22

    UPDATE 3  33

    COMMIT TRAN

  • BEGIN TRAN 실행


Sql server 2005

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

  • UPDATE 1  11 실행


Sql server 2005

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

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


Sql server 2005

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

  • COMMIT TRAN 실행


Sql server 2005

사고 후의 복구 방식 (1)

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


Sql server 2005

사고 후의 복구 방식 (2)

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

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

    • 위와 동일한 작업을 함


Sql server 2005

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

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


Sql server 2005

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

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


Sql server 2005

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

  • master 데이터베이스

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

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

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

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

  • model 데이터베이스

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

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

  • msdb 데이터베이스

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

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


Sql server 2005

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

  • tempdb 데이터베이스

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

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

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

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

  • 복제 관련 데이터베이스

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

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

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

  • 예제데이터베이스

    • AdventureWorks 및 AdventureWorksDW


Sql server 2005

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

  • SSMS를 이용해서 생성

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


Sql server 2005

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

  • 항목 설명

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

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

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

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

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

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

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

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

  • 실습 목표

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

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

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

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


Sql server 2005

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

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


Sql server 2005

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

  • 개념

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

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

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

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

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


Sql server 2005

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

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

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

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

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


Sql server 2005

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

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

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

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


5 ssms

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

  • 실습 목표

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

  • 실습 결과 화면


6 t sql

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


Sql server 2005

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

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

  • 실습 목표

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

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

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

  • 실습 요약

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

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

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


Sql server 2005

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

      )


Sql server 2005

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


Sql server 2005

데이터베이스 수정 (3)

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

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

  • 실습 목표

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

  • 실습 순서 요약

    • 데이터베이스 이름 변경

    • 논리적 파일명 변경

    • 데이터베이스 오프라인

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

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

    • 데이터베이스 온라인

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


    9 tempdb

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

    • 실습 목표

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

    • 실습 결과 화면


    Sql server 2005

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

    을 사용하면 된다.


    Sql server 2005

    데이터베이스 옵션

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

      • 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