'SQL Server 2005'에 해당되는 글 3건

  1. 2011.09.02 MS-SQL 2005 DDL 트리거
  2. 2008.12.26 SQL Server 2005 데이터베이스 미러링
  3. 2008.12.26 SQL Server 2005 데이터베이스 스냅샷(Snapshot)
2011. 9. 2. 19:23

MS-SQL 2005 DDL 트리거

DDL 트리거는 UPDATE, DELETE, INSERT 등과 같은 명령문에 작동하는 DML 트리거와 달리 테이블이나 뷰에 대한 CREATE, ALTER 및 DROP 또는 사용자 계정이나 로그인 설정, 프로 시저 생성 및 변경, 파티션 생성 및 변경 등과 같은 DDL문에 대하여 동작하는 트리거입니다.

  • 데이터베이스 스키마에 대한 특정 변경 작업을 방지하려는 경우
  • 데이터 스키마가 변경될 때 데이터베이스에서 특정 작업이 수행되도록 하려는 경우
  • 데이터베이스 스키마의 변경 내용이나 이벤트를 기록하려는 경우

DDL 트리거는 SQL 문이 완료된 후에 실행이 되며, INSTEAD OF 트리거로 사용될 수는 없습니다. 또한 DML 트리거와 같이 inserted, deleted 테이블을 생성하지는 않습니다. DDL 트리거는 서버에 대해서 설정할 수도 있고 특정 데이터베이스에서만 수행되도록 설정할 수도 있습니다. 데이터베이스, 사용자, 끝점, 로그인 관련 이벤트는 서버 범위의 이벤트 그룹이며, 테이블, 뷰, 인덱스 등과 같은 데이터베이스 개체 관련 이벤트는 데이터베이스 범위의 이벤트 그룹입니다.

  • DDL 트리거를 디자인하기 전에 다음 사항이 필요합니다.
  • DDL 트리거 영역에 대하여 이해해야 합니다.
  • 어떤 Transact-SQL문(들)에 대하여 트리거를 발생시킬 것인지를 결정해야 합니다.
DDL 트리거 생성
[구문] CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

[따라하기] 테이블의 DROP 및 ALTER 작업에 대하여 DDL 트리거 생성하기

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name =
'safety')
DROP TRIGGER safety ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT '테이블을 변경/삭제하려면“safety”트리거를 비활성화 하세요.'
ROLLBACK;
GO
-- safety라는 DDL 트리거를 비활성화합니다.
DISABLE TRIGGER safety ON DATABASE;
GO
-- safety라는 DDL 트리거를 활성화합니다.
ENABLE TRIGGER safety ON DATABASE;
GO

[따라하기] AdventureWorks 데이터베이스 내의 모든 DDL 문에 대하여, 사용 기록 남기기

USE AdventureWorks;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event
nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA( )
INSERT ddl_log (PostTime, DB_User, Event, TSQL) VALUES
(GETDATE( ),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--생성한 트리거 테스트
CREATE TABLE TestTable (a int); --임시 테이블을 생성
DROP TABLE TestTable ; --생성한 임시 테이블 삭제
GO
--DDL 로그 확인
SELECT * FROM ddl_log ;
GO
--트리거 삭제
DROP TRIGGER log ON DATABASE;
GO
--ddl_log 테이블 삭제
DROP TABLE ddl_log;
GO
DDL 트리거 정보 확인

[따라하기] 데이터베이스 수준의 DDL 트리거 목록 확인하기

SELECT * FROM sys.triggers WHERE parent_class = 0;
GO

[따라하기] 서버 수준의 DDL 트리거 목록 확인하기

SELECT * FROM sys.triggers WHERE parent_class = 0;
GO

[따라하기] 트리거 정의 확인하기

SELECT tr.name, sm.definition
FROM sys.triggers tr JOIN sys.sql_modules sm ON tr.object_id = sm.object_id
WHERE tr.parent_class = 0;
GO


출처 : DBGuide.net

Trackback 0 Comment 0
2008. 12. 26. 21:31

SQL Server 2005 데이터베이스 미러링

들어가는 글

데이터베이스 미러링은 데이터베이스 가용성을 증가시키기 위한 새로운 SQL Server 2005 기술입니다. 데이터베이스 미러링 은 트랜잭션 로그 레코드를 한 서버에서 다른 서버로 직접 전송하고 대기 서버로 신속하게 장애 조치할 수 있습니다. 클라이언 트 응용 프로그램이 연결 정보를 자동으로 재전송하고 장애 조치의 경우 대기 서버와 데이터베이스에 자동으로 연결하도록 코딩할 수 있습니다.

데이터 손실을 최소화하는 빠른 장애 조치는 전통적으로 하드웨어 비용이 높고 소프트웨어 복잡성이 커집니다. 그러나 데이터 베이스 미러링은 커밋된 데이터 손실이 없이 빠르게 장애 조치할 수 있으며, 전용 하드웨어가 필요하지 않고 설치와 관리가 쉽습니다.

데이터베이스 미러링 개요

데이터베이스 미러링에서 원래 SQL Server 2005 인스턴스는 다른 대기 SQL Server 인스턴스에 있는 데이터베이스 복사본으 로 데이터베이스 트랜잭션 로그 레코드를 지속적으로 보냅니다. 원본 데이터베이스와 서버에는 주 서버의 역할이 있으며 수신 데이터베이스와 서버에는 미러 서버의 역할이 있습니다. 주 서버와 미러 서버는 SQL Server 2005의 개별 인스턴스가 되어야 합니다.

모든 SQL Server 데이터베이스에서 실제 데이터 페이지를 변경하기 전에 트랜잭션 로그에 데이터 변경이 기록됩니다. 트랜잭 션 로그 레코드는 먼저 메모리의 데이터베이스 로그 버퍼에 저장되었다가 가능한 빨리 디스크에 플러시(또는‘하드엔디드’) 됩니다. 데이터베이스 미러링에서 주 서버가 주 데이터베이스의 로그 버퍼를 디스크에 쓰는 것과 동시에 로그 레코드 블록을 미러 인스턴스로 보냅니다.

미러 서버가 로그 레코드 블록을 받으면 먼저 로그 레코드를 미러 데이터베이스의 로그 버퍼에 저장한 다음 가능한 빨리 디스 크에 저장하여 보호합니다. 이러한 트랜잭션 로그 레코드는 나중에 미러에서 재생됩니다. 미러 데이터베이스는 주 트랜잭션 로그 레코드를 재생하기 때문에 주 데이터베이스에 데이터베이스 변경을 복제합니다.

주 서버와 미러 서버는 각각 데이터베이스 미러링 세션의 파트너로 간주됩니다. 데이터베이스 미러링 세션은 한 파트너에서 다른 파트너로 데이터베이스를 미러링할 때 파트너 서버 사이의 관계로 구성됩니다. 해당 파트너 서버는 한 데이터베이스에는 주 서버 역할을 갖고 다른 데이터베이스에서는 미러 서버 역할을 가질 수 있습니다.

두 파트너 서버(주 서버 및 미러 서버) 이외에 데이터베이스 미러링 세션은 감시라고 하는 옵션인 세 번째 서버를 가질 수 있습 니다. 감시 서버의 역할은 자동 장애 조치를 수행하는 것입니다. 고가용성을 위해 데이터베이스 미러링을 사용하면 갑자기 주 서버에 장애가 발생하고, 미러 서버가 감시 서버에서 확인을 받은 경우 자동으로 주 서버의 역할을 맡고 몇 초 내에 데이터베이 스를 사용할 수 있도록 만들 수 있습니다.

데이터베이스 미러링에 대해 유의할 몇 가지 중요한 사항은 다음과 같습니다.

주 데이터베이스는 FULL 복구 모델에 있어야 합니다. 대량 로그 작업의 로그 레코드는 미러 데이터베이스로 보낼 수 없습니다.

미러 데이터베이스는 NORECOVERY를 사용하여 주 데이터베이스의 복원에서, 이어서 순차적으로 주 트랜잭션 로그 백업 의 복원으로부터 초기화해야 합니다.

미러 데이터베이스 이름은 주 데이터베이스 이름과 같아야 합니다.

미러 데이터베이스가 복구 중인 상태에 있기 때문에 직접 액세스할 수 없습니다. 미러에 데이터베이스 스냅샷을 만들어 특정 시간에 미러 데이터베이스를 간접적으로 읽을 수 있습니다. 이 백서 뒷부분의‘데이터베이스 미러링 및 데이터베이스 스냅 샷’을 참조하십시오.


주: 데이터베이스 미러링과 관련된 용어에 대한 자세한 내용은 SQL Server 2005 온라인 설명서의“Overview of Database Mirroring”을 참조하십시오.
작동 모드
데이터베이스 미러링 세션에 대해 세 가지 가능한 작동 모드가 있습니다. 정확한 모드는 트랜잭션 안전 및 감시 서버가 미러링 세션의 일부인지 여부를 기초로 합니다.
표 1. 데이터베이스 미러링 작동 모드
작동 모드 트랜잭션 안전 전송 메커니즘 필요한 쿼럼 Witness 서버 장애 조치 유형
고가용성 FULL 동기식 Y Y 자동 또는 수동
높은 수준의 보호 FULL 동기식 Y N 수동만
높은 성능 OFF 비동기 N 없음 강제만
안전이 FULL인 경우 비동기 데이터 전송이 발생하고 데이터베이스 서비스를 위해 쿼럼이 필요합니다. 쿼럼 투표에는 각각의 두 파트너 서버가 재생해야 하는 역할, 주 또는 미러를 결정하기 위해 최소 두 서버가 필요합니다. 세 개의 작동 모드를 자세히 알아 보려면 먼저 트랜잭션 안전과 쿼럼 역할을 자세히 살펴 보십시오.
트랜잭션 안전성
트랜잭션 안전성(또는 그냥‘안전성’)이 FULL로 설정된 경우 주 서버와 미러 서버는 동기 전송 모드로 작동합니다. 기본 서버가 주 데이터베이스 로그 레코드를 디스크에 저장할 때 해당 레코드를 미러 서버에도 보냅니다. 주 서버는 미러 서버의 응답을 기다립니다. 같은 로그 레코드를 미러 서버의 로그 디스크에 저장하면 미러가 응답합니다. 안전성이 OFF로 설정되면 기본 서버는 미러로부터 승인을 기다리지 않으므로 미러는 완전히 동기화되지 않을 수 있습니다. 즉, 미러가 주 서버와 동일한 상태를 유지하지 못할 수 있습니다.

동기 전송은 미러 데이터베이스의 트랜잭션 로그에 있는 모든 트랜잭션이 주 데이터베이스의 트랜잭션 로그와 동기화될 수 있도록 하므로 트랜잭션은 안전하게 전송된 것으로 간주됩니다. 다음을 사용하여 안전성을 FULL로 설정합니다.

ALTER DATABASE [] SET SAFETY FULL;


안전성이 OFF로 설정되면 주 서버와 미러 사이의 통신이 동기화됩니다. 주 서버는 미러가 트랜잭션 레코드의 블록을 저장한 미러에서 승인되는 것을 기다리지 않습니다. 미러는 가능한 빨리 트랜잭션을 기록함으로써 주 서버와 동일한 상태를 유지하려 고 하지만 주 서버에 갑자기 장애가 발생하여 미러가 서비스하도록 하는 경우 일부 트랜잭션이 손실될 수 있습니다. SQL Server 온라인 설명서의‘Forced Service’항목을 참조하십시오.
쿼럼 및 감시 서버
안전성이 FULL이면 데이터베이스 미러링 세션은 쿼럼을 사용하여 데이터베이스 서비스를 유지합니다. 쿼럼은 동기 데이터베 이스 미러링 세션에서 필요로 하는 모든 연결된 서버 사이에 최소 관계를 나타냅니다. 쿼럼에는 최소 두 서버가 필요하기 때문 에 안전성이 FULL이면 주 서버는 데이터베이스의 서비스를 유지하기 위해 최소 하나의 다른 서버와 쿼럼을 구성해야 합니다.

감시 서버는 쿼럼 구성에서 주 서버나 미러를 지원합니다. 감시 서버가 있는 경우 주 데이터베이스나 미러 데이터베이스 중 하나가 손실되면 두 서버가 쿼럼을 구성하게 됩니다. 주 서버가 미러 서버에 연결할 수 없지만 감시 서버와 쿼럼을 구성할 수 있는 경우 데이터베이스의 서비스는 유지할 수 있습니다. 마찬가지로, 미러와 감시 서버가 주 서버에 연결할 수 없고 미러 서버가 감시 서버와 쿼럼을 구성할 수 없는 경우 미러가 새로운 주 서버의 역할을 맡을 수 있습니다.

감시 서버에 장애가 발생하는 경우 주 서버와 미러 서버가 계속 쿼럼을 구성하기 때문에 감시 서버는 데이터베이스 미러링 세 션의 단일 장애 지점으로 간주되지 않습니다. 자세한 내용은 SQL Server 온라인 설명서의“Quorum in Database Mirroring Sessions”를 참조하십시오.
고가용성 작동 모드
고가용성 작동 모드는 주 데이터베이스에 장애가 발생하는 경우 미러 데이터베이스로 자동 장애 조치하여 최대한의 데이터 베이스 가용성을 지원합니다. 이렇게 하려면 안전성을 FULL로 설정하고 감시 서버를 데이터베이스 미러링 세션의 일부로 정의해야 합니다.

서버 간에 빠르고 매우 안정적인 통신 경로를 갖는 고가용성 모드를 사용하는 것이 가장 좋으며 단일 데이터베이스에 대한 자동 장애 조치가 필요합니다. 안전성이 FULL이면 주 서버는 잠시 동안 미러 서버로부터 응답을 기다려야 하므로 미러 서버의 기능이 주 서버의 성능에 영향을 줄 수 있습니다. 단일 데이터베이스 오류로 인해 자동 장애 조치되기 때문에 여러 데이터베이 스 응용 프로그램이 있는 경우 다른 작동 모드를 고려할 수 있습니다. 이 문서 뒷부분에 있는 구현 절의“다중 데이터베이스 문제”를 참조하십시오.

고가용성 모드에서 데이터베이스 미러링은 자체 미러링입니다. 주 데이터베이스를 갑자기 사용할 수 없게 되거나 주 서버가 다운되는 경우 감시 서버와 미러 서버는 쿼럼을 구성하며 미러 SQL Server는 자동 장애 조치를 수행합니다. 이 지점에서 미러 서버 인스턴스는 새로운 주 서버로 역할이 변경되고 데이터베이스를 복구합니다. 미러가 주 서버의 트랜잭션 로그를 재생하고 주 서버와 동기화했기 때문에 미러 서버를 신속하게 사용할 수 있습니다.

또한 SQL Server 2005는 복구 프로세스 초기 단계에서 사용자가 데이터베이스를 사용하도록 만들 수 있습니다. SQL Server 데이터베이스 복구는 분석 단계, 재실행 단계 및 마지막으로 실행 취소 단계 등 세 단계로 구성됩니다. SQL Server 2005에서 새로 복구된 데이터베이스는 재실행 단계가 완료되는 대로 사용할 수 있습니다. 따라서 데이터베이스 미러링 장애 조치가 발생하는 경우 재실행 단계를 완료하는 즉시 복구된 새로운 주 데이터베이스를 사용할 수 있습니다. 미러 데이터베이스가 트랜잭션 로그 레코드를 완전 재생했기 때문에 미러 서버가 수행해야 하는 일은 재실행 프로세스를 완료하는 것입니다. 이는 일반적으로 몇 초 만에 수행될 수 있습니다.
높은 수준의 작동 모드
높은 수준의 작동 모드에서는 트랜잭션 안전성이 FULL이지만 미러링 세션의 일부로 감시 서버가 없습니다. 주 데이터베이스 는 여전히 쿼럼을 구성해야 하지만 감시 서버가 없어 미러 서버만으로 구성해야 합니다. 이 모드에서는 타이 브레이커(tie breaker) 역할을 충족시키는 감시 서버가 없기 때문에 수동 장애 조치만 가능합니다. 주 서버에 장애가 발생하는 경우 미러 서버에는 쿼럼을 구성할 감시 서버가 없기 때문에 자동 장애 조치는 가능하지 않습니다.

안전성이 FULL이면 주 서버와 미러의 쿼럼이 갑자기 상실될 경우 데이터베이스 서비스를 중단해야 합니다. 감시 서버를 일시 적으로 제거해야 할 때는 고가용성의 경우 전환 상태로 데이터베이스 미러링 구성을 제외하므로 높은 수준의 보호 모드는 사용하지 않는 것이 좋습니다.
고성능 작동 모드
고성능 작동 모드에서는 트랜잭션 안전성이 OFF이고 로그 레코드 전송이 비동기화됩니다. 주 서버는 모든 트랜잭션 로그 레코 드가 미러에 기록된 미러에서 승인되기를 기다리지 않습니다. 미러는 주 서버와 같은 상태를 유지하지만 어느 한 시점에 주 서버의 모든 최신 트랜잭션이 미러의 트랜잭션 로그에 저장될 것임을 보장하지 않습니다.

고성능 모드에서는 감시 서버는 아무 역할도 하지 않으며 쿼럼이 필요하지 않습니다. 따라서 자동 및 수동 장애 조치는 고성능 모드에 사용되지 않습니다. 허용되는 유일한 장애 조치 유형은 수동 작업이라고도 하는 forced service 장애 조치입니다.

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS


강제 서비스 장애 조치를 수행하면 미러 데이터베이스가 즉시 복구됩니다. 주 서버의 일부 트랜잭션 로그 블록을 미러에서 아직 받지 못한 경우 복구되면 미러에서 잠재적인 데이터 손실이 발생할 수 있습니다. 고성능 모드는 먼 거리로 데이터를 전송 하거나(즉, 원격 사이트로 재해 복구하는 경우), 약간의 잠재적 데이터 손실을 허용하는 매우 활성화된 데이터베이스를 미러링 하는 경우 사용하면 매우 좋습니다.
데이터베이스 스냅샷 및 미러 데이터베이스
미러 데이터베이스가 복구 중인 상태에 있기 때문에 액세스하거나 읽을 수 없습니다. SQL Server 2005 Enterprise Edition 및 Developer Edition에서는 데이터베이스 스냅샷을 만들어 어느 한 시점에 미러 데이터베이스를 읽을 수 있습니다. 데이터베이스 스냅샷은 데이터베이스의 읽기 전용 보기를 제공하여 스냅샷을 만드는 시점에 일관성 있는 데이터를 표시합니다.

마치 다른 데이터베이스에 있는 것처럼 데이터베이스 스냅샷에 액세스합니다. 데이터베이스 스냅샷을 쿼리하면 데이터베이스 스냅샷 파일에서 스냅샷을 만든 후에 변경된 데이터베이스 데이터의 원본 버전을 읽고 원본 데이터베이스에서 변경되지 않은 데이터를 읽습니다. 결과적으로 스냅샷을 만든 시점에 최신 데이터베이스 데이터를 볼 수 있습니다. 자세한 내용은 SQL Server 온라인 설명서의“Using Database Snapshots with Database Mirroring”을 참조하십시오.

데이터베이스 스냅샷은 미러 서버에 약간의 오버헤드가 요구되기 때문에 데이터베이스 미러링 성능에 어떤 영향을 미칠 수 있는지에 주의해야 합니다. 한 데이터베이스로만 미러링할 수 있으므로 많은 읽기 전용 보고 서버로 확장해야 하는 경우 트랜 잭션 복제가 더 나은 선택이 될 것입니다. 자세한 내용은 뒷부분의“데이터베이스 미러링 및 복제”를 참조하십시오.
클라이언트측 측정 지표
SQL Server 2005에서 ADO.NET 또는 SQL Native Client로 미러링하는 데이터베이스에 연결하는 경우 응용 프로그램은 데이 터베이스 미러링 장애 조치가 발생할 때 자동으로 연결을 리디렉션하는 드라이버 기능을 이용할 수 있습니다. 연결 문자열에 서 초기 주 서버와 데이터베이스를, 선택적으로 장애 조치 파트너 서버를 지정해야 합니다.

연결 문자열을 작성하는 방법은 많지만 여기에서 제시하는 한 예는 서버 A를 주 서버, 서버 B를 미러 및 AdventureWorks를 데이터베이스 이름으로 지정합니다.

“Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;”


연결 문자열에서 장애 조치 파트너는 초기 주 서버 연결이 실패하는 경우 대체 서버 이름으로 사용됩니다. 초기 주 서버 연결이 성공하면 장애 조치 파트너 이름은 사용되지 않지만 드라이버는 클라이언트측 캐시에 있는 주 서버에서 검색하는 장애 조치 파트너 이름을 저장합니다.

클라이언트가 주 서버에 성공적으로 연결되었고 데이터베이스 미러링 장애 조치(자동, 수동 또는 강제)가 발생한다고 가정합시다. 다음에 응용 프로그램이 연결을 사용하려고 시도하면 ADO.NET 또는 SQL Native Client 드라이버는 이전 주 서버 연결이 실패한 것을 발견하고 장애 조치 파트너 이름에 지정된 새로운 주 서버에 자동으로 연결을 재시도합니다. 성공하고 새로운 주 서버에 의해 데이터베이스 미러링 세션에 지정된 새 미러 서버가 있는 경우 드라이버는 새로운 파트너 장애 조치 서버 이름을 검색 하고 클라이언트 캐시에 저장합니다. 클라이언트가 대체 서버에 연결할 수 없는 경우 드라이버는 로그인 시간 초과 기간에 도달할 때까지 각 서버에 연결을 시도합니다.

ADO.NET 및 SQL Native Client 드라이버에 기본 제공되는 데이터베이스 미러링 지원을 사용하는 큰 장점은 데이터베이스 미러링 장애 조치를 처리하기 위해 응용 프로그램을 다시 코딩하거나 응용 프로그램에 특별한 코드를 삽입할 필요가 없다는 것입니다.

ADO.NET 또는 SQL Native Client 자동 리디렉션을 사용하지 않을 경우 응용 프로그램을 장애 조치할 수 있는 다른 기술을 사용할 수 있습니다. 예를 들어, 네트워크 로드 균형 조정을 사용하여 한 서버에서 다른 서버로 연결을 수동으로 리디렉션할 수 있지만 클라이언트는 가상 서버 이름에 연결합니다. 또한 자체 리디렉션 코드를 작성하고 로직을 다시 시도할 수 있습니다.

그러나 데이터베이스 미러링을 사용하여 클라이언트 리디렉션을 통합하는 이러한 모든 기술에는 중요한 제한이 있습니다. 데이터베이스 미러링은 서버 수준이 아니라 데이터베이스 수준에서만 발생합니다. 응용 프로그램이 서버의 여러 데이터베이 스 쿼리에 의존하거나 여러 부분의 개체 이름을 사용하여 여러 데이터베이스에 쿼리하는 경우 주의하십시오. 한 서버에 여러 데이터베이스가 있고 대기 서버에 미러링되면 여러 데이터베이스 중 하나가 대기 서버로 장애 조치될 수 있지만 다른 데이터 베이스는 원래 서버에 남아 있습니다. 이 경우 쿼리하는 데이터베이스 당 하나의 연결이 필요할 수 있으므로 한 데이터베이스만 주 서버이고 나머지는 미러인 대기 서버에서 데이터베이스간 쿼리를 시도하지 마십시오.
Database Mirroring 및 SQL Server 2005 Edition
다음 표는 SQL Server 2005의 다양한 버전에서 어떤 데이터베이스 미러링 기능이 지원되는지 보여줍니다.
표 2. 데이터베이스 미러링 및 SQL Server 2005 Edition
Database Mirroring
주요 특징
Enterprise
에디션
Developer
에디션
Standard
에디션
Workgroup
에디션
SQL
Express
파트너    
Witness
안전성 = FULL    
안전성 = OFF      
장애 조치 후 UNDO
동안 사용 가능
   
병렬 재실행      
데이터베이스 스냅샷      
몇 가지 데이터베이스 미러링 기능은 SQL Server 2005 Enterprise or Developer Edition이 필요합니다.

• 안전성이 OFF인 고성능 모드(비동기 데이터 전송);
• 데이터베이스 스냅샷;
• 미러 데이터베이스에서 트랜잭션 로그를 재생하는데 여러 스레드 사용(병렬 REDO).


SQL Express 및 Workgroup Edition을 감시 서버로 사용할 수 있지만 데이터베이스 미러링에서는 파트너 서버로 사용할 수 없습니다.


Trackback 0 Comment 0
2008. 12. 26. 21:30

SQL Server 2005 데이터베이스 스냅샷(Snapshot)

데이터베이스 스냅샷(Snapshot)

업무에서 데이터베이스를 사용하다 보면 특정 시점의 데이터베이스를 접근 하고 싶은 경우가 있습니다. 예를 들면 오후 6시 시점의 데이터베이스를 대상으로 보고서를 산출하고 싶은 경우가 발생 할 수 있습니다. 하지만 데이터베이스에는 계속해서 데이터가 추가, 변경 또는 삭제 되므로 오후 6시 시점의 데이터베이스를 접근 할 수가 없습니다. SQL 서버 2005에서는 데이터베이스 스냅샷을 이용해 이러한 작업이 가능하게 됩니다.

 

1. 데이터베이스 스냅샷이란
데이터베이스 스냅샷은 특정 시점의 고정된 읽기 전용의 데이터베이스입니다. 특정 시점에 데이터베이스 스냅샷이 만들어지고 나면 원본 데이터베이스의 데이터가 변경되어도 데이터베이스 스냅샷은 변경되지 않습니다. 만일 오후 6시 시점에 데이터베이스 스냅샷을 만들면 이 데이터베이스는 6시 시점의 고정된 데이터를 보여 주므로 6시 시점의 보고서 산출이 가능하게 됩니다.

데이터베이스 스냅샷은 갑작스런 데이터 손상으로부터 복원의 가능성도 제공해 줍니다. 만일 오후 6시에 데이터베이스 스냅샷을 만든 상황에서 잘못해 특정 테이블의 내용을 모두 삭제 했다고 가정한다면, 이러한 상황에서 6시 시점에 만든 데이터베이스 스냅샷에는 문제의 테이블이 지워지기 전 상태로 있기 때문에 이 테이블을 이용해 지워진 원본 데이터베이스의 테이블을 복원 할 수 있습니다.

 

2. 데이터베이스 스냅샷에 대한 제한
데이터베이스 스냅샷을 만들 때 다음과 같은 제한이 따르게 됩니다.

- 원본 데이터베이스와 같은 서버에만 만들 수 있습니다.
- 시스템 데이터베이스에 대해서는 데이터베이스 스냅샷을 만들 수 없습니다.
- 데이터베이스 스냅샷은 백업이나 복원을 할 수 없습니다.
- 데이터베이스 스냅샷은 데이터베이스 분리(Detach) 또는 연결(Attach)할 수 없습니다.
- 데이터베이스 스냅샷은 FAT32 또는 raw 파티션에는 만들 수 없습니다.
- 원본 데이터베이스를 제거하기 위해서는 먼저 데이터베이스 스냅샷부터 제거해야 합니다.
- SQL Server Management Studio에서는 데이터베이스 스냅샷을 만들 수 없습니다.

 

3. 데이터베이스 스냅샷 만들기

데이터베이스 스냅샷 제한 사항에서 언급된 것처럼 데이터베이스 스냅샷은 Management Studio에서는 만들 수 없습니다. CREATE DATABASE 문을 이용해서만 만들 수 있습니다.

다음 예제는 AdventureWorks 데이터베이스에 대해 오후 6시 시점의 데이터베이스 스냅샷을 만드는 예입니다. 데이터베이스 스냅샷의 이름은 이처럼 만들어진 시간대를 알 수 있게 이름에 시간을 포함해 주는 것이 좋습니다.

USE Master
GO

CREATE DATABASE AdventureWorks_dbsnapshot_1800
ON (
   NAME = AdventureWorks_Data,
   FILENAME = 'C:\Data\AdventureWorks_Data.ss'
) AS SNAPSHOT OF AdventureWorks
GO
 

다음 예제에서는 만든 데이터베이스 스냅샷에서 쿼리를 하고 있습니다. 데이터베이스 스냅샷은 읽기전용의 데이터베이스이므로 다양한 방법의 쿼리가 가능하게 됩니다.

USE AdventureWorks_dbsnapshot_1800
GO

SELECT *
   FROM HumanResources.Employee
GO  

위 예제에서처럼 테이블을 쿼리 하게 되면 쿼리 된 결과는 원본 데이터베이스의 테이블이 쿼리 되어 표시됩니다. 데이터베이스 스냅샷이 만들어진 이후에 데이터가 변경되면 변경 전 데이터가 데이터베이스 스냅샷에 기록이 되는데 아직은 변경된 데이터가 없기 때문에 데이터베이스 스냅샷을 원본 데이터베이스로 쿼리 작업을 넘겨 원본 데이터가 쿼리 되게 하는 것입니다.

 

4. 데이터베이스 스냅샷 사용

데이터베이스 스냅샷 데이터를 변경해 볼까요?

USE AdventureWorks_dbsnapshot_1800
GO

UPDATE HumanResources.Employee
   SET LoginID = 'Secret' + LoginID
GO

[결과]
메시지 3906, 수준 16, 상태 1, 줄 1
데이터베이스 "AdventureWorks_dbsnapshot_1800"은(는) 읽기 전용이므로 업데이트할 수 없습니다.  

위 에제 처럼 데이터베이스 스냅샷의 데이터를 변경하려고 하면 에러가 발생합니다. 읽기 전용의 데이터이기 때문에 변경 작업은 불가합니다.

다음 예제에서는 원본 데이터베이스인 AdventureWorks의 테이블을 변경하고 그 결과를 확인한 것입니다. LoginID 앞에 모두 ‘Secret’이 붙어 있는 것을 볼 수 있습니다.

USE AdventureWorks
GO

UPDATE HumanResources.Employee
   SET LoginID = 'Secret ' + LoginID
GO

SELECT LoginID
   FROM HumanResources.Employee
GO

[결과]
LoginID
------------------------------------
Secret adventure-works\alan0
Secret adventure-works\alejandro0
Secret adventure-works\alex0
Secret adventure-works\alice0
(생략)  

하지만 다음 예제처럼 데이터베이스 스냅샷에서 해당 테이블을 조회하면 ‘Secret’이 붙지 않은 즉, 변경 되지 전 데이터를 확인 할 수 있습니다.

USE AdventureWorks_dbsnapshot_1800
GO

SELECT LoginID
   FROM HumanResources.Employee
GO

[결과]
LoginID
--------------------------------------
adventure-works\alan0
adventure-works\alejandro0
adventure-works\alex0
adventure-works\alice0
(생략)  

5. 데이터베이스 스냅샷을 이용한 데이터 복원

다음 예제는 데이터베이스 스냅샷에 기록된 변경 전 데이터를 이용해 원본 데이터베이스의 테이블을 복원하는 방법 중의 하나를 보여줍니다. 결과적으로 ‘Secret’ 가 LoginID 앞에 붙기 전 데이터로 원복 됨을 볼 수 있습니다. 이외에도 여러가지 경우의 복원에 데이터베이스 스냅삿을 이용할 수 잇습니다.

USE AdventureWorks
GO

UPDATE HumanResources.Employee
   SET LoginID = (SELECT LoginID
         FROM AdventureWorks_dbsnapshot_1800.Humanresources.Employee
         WHERE EmployeeID = E1.EmployeeID)
   FROM HumanResources.Employee E1
GO

SELECT LoginID
FROM HumanResources.Employee
GO

[결과]
LoginID
--------------------------------------
adventure-works\alan0
adventure-works\alejandro0
adventure-works\alex0
adventure-works\alice0
(생략)  

6. 데이터베이스 스냅샷 제거

데이터베이스 스냅샷은 다음과 같이 DROP DATABASE 문에 의해 삭제 할 수 있습니다.

USE Master
GO

DROP DATABASE AdventureWorks_dbsnapshot_1800
GO

7. 정리

데이터베이스 스냅샷에 대해 살펴보았습니다. 특정 시점의 보고서 산출이나 개발 및 테스트를 위해 활용할 부분이 충분이 있을것 같습니다. 위에서 언급한 것처럼 데이터 복구에 사용할 수도 있습니다. 갑작스럽게 테이블을 지웠을 경우, 개체를 제거했을 경우 등에서 복구할 수 있는 시점의 데이터베이스 스냅샷이 있다면 이것을 이용해 복구를 하면 됩니다. 그렇다고 이것이 백업과 복원 전략을 대신 할수 없음을 명심해 주시기 바랍니다.


Trackback 0 Comment 0