SP 변경 이력 추적 쉽게 하기

오랜만에 블로그에 글을 쓰네요..

sql server 데이터베이스 작업중

SP가 빈번히 변경되는데 변경 이력 추적이 어려울때 사용하면 좋은 방법입니다.

물론 소스 관리 툴을 쓰는게 가장 좋은것 같고..

가장 간편하게 적용 할 수 있는 쿼리 입니다.

변경 이력을 추적할 데이터 베이스에서 해당 쿼리를 실행합니다.


CREATE TABLE dbo.SPLOG(
idx int IDENTITY(1,1) NOT NULL,
sp_name varchar(100) NULL,
division varchar(20) NULL,
SQLCMD varchar(max) NULL,
author varchar(20) NULL,
modified_date datetime NULL,
 CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED
(
idx ASC
))

GO


먼저 위의 쿼리로 변경이력을 저장할 테이블을 만듭니다.




CREATE TRIGGER TRG_SPLOG ON DATABASE
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER

AS

DECLARE @DATA XML

SET @DATA = EVENTDATA()

INSERT INTO DBO.SPLOG (sp_name, division, SQLCMD, author, modified_date)
VALUES
(@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)'),
 HOST_NAME(),
 GETDATE())

다음은 위의 쿼리로 데이터베이스 트리거를 등록합니다.


트리거 등록 후에는 프로시저, 뷰, 펑션, 트리거를 만들거나 수정, 지우게 될 경우

이력이 SPLOG 테이블에 남게 됩니다.

Transaction-Level Read Consistency (트랜잭션 수준 읽기 일관성)

트랜잭션 수준 읽기 일관성이란?

문장수준 읽기 일관성(Statement-Level Read Consistency)

• 쿼리가 시작된 시점을 기준으로 데이터를 일관성 있게 읽어 들이는 것

트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)

• 트랜잭션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽어 들이는 것

이상 현상 3가지

• Dirty Read ( = Uncommitted Dependency )

          커밋되지 않은 수정 중인 데이터를 다른 트랜잭션에서 읽을 수 있도록 허용할때 발생
          대부분 DBMS가 기본 트랜잭션 고립화 수준을 레벨1 로 설정하고 있어 Dirty Read는 발생하지 않음
          =  커밋된 데이터만 읽을 수 있도록 허용
          SQL Server, Sybase등은 데이터를 읽을 때 공유 Lock을 사용,
          갱신중인 레코드에는 배타적 Lock이 걸림, 

          이는 공유 Lock과는 호환되지 않아 갱신중인 레코드는 읽지 못함(Lock에 의한 동시성 저하 발생)

• Non-Repeatable Read ( = Inconsistent Analysis )

          한 트랜잭션 내에서 같은 쿼리를 두번 수행할 때 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제함으로써
          두 쿼리의 결과가 상이하게 나타나는 비일관성이 발생하는 것을 말함

• Phantom Read 

          한 트랜잭션 안에서 일정 범위의 레코드를 두번 이상 읽을 때, 
          첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 현상

          이는 트랜잭션 도중 새로운 레코드가 삽입되는 것을 허용하기 때문에 나타나는 현상.

이상현상은 트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency)때문에 생기는 현상이며

고립화 수준을 조정함으로써 해결 할 수 있다.

하지만 고립화 수준을 높이면 데이터 일관성은 확보 되지만 동시성이 현격히 저하되는 결과를 초래 할 수 있다.

Transaction Isolation Level

트랜잭션 수준으로 읽기 일관성을 강화 하려면 트랜잭션 고립화 수준을 높여 주어야 한다. 

트랜잭션 고립화 수준을 조절하는 방법은 네가지 수준이 있다.

ANSI / ISO SQL standard(SQL92)에서 정의함

■ 레벨 0 ( = Read Uncommitted )

 - 트랜잭션에서 처리중인 / 아직
커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용

 - Dirty Read, Non-repeatable Read, Phantom Read 현상 발생

■ 레벨 1 ( = Read Committed )

 - Dirty Read 방지 :
트랜잭션이 커밋되어 확정된 데이터만 읽는 것을 허용

 -
대부분의 DBMS가 기본모드로 채택하고 있는 일관성 모드

 - Non-Repeatable Read, Phantom Read 현상 발생

■ 레벨 2 ( = Repeatable Read )

 - 선행 트랜잭션이 읽은 데이터는 트랜잭션이 종료될 때까지
후행 트랜잭션이 갱신하거나 삭제하는 것을 불허함으로써 같은 데이터를 두번 쿼리했을 때 일관성 있는 결과를 리턴

 - Phantom Read 현상 발생

■ 레벨 3 ( = Serializable Read )

 - 선행 트랜잭션이 읽은 데이터를 후행 트랜잭션이 갱신하거나 삭제하지 못할 뿐만 아니라 중간에 새로운 레코드를 삽입하는 것도 막아줌

 -
완벽한 읽기 일관성 모드를 제공

False Sharing - 거짓 공유

거짓 공유를 설명하기 전에 필요한 

캐싱과 지역성에 대해 먼저 설명 하겠습니다

캐싱의 기본은 지역성에 근거하는데 

지역성(Locality)은 아래의 추정에 근거합니다

1. 지금 읽힌 데이터는 이후에도 자주 사용될 가능성이 높다.

2. 지금 읽힌 데이터와 인접한 데이터는 이어서 사용될 가능성이 높다.

멀티 코어 CPU는 데이터를 읽어올 때, 캐시 라인 (cache line)이란 단위로 읽어옵니다. 

캐시 라인이라 함은 지역성에 근거해 인접한 데이터를 미리 읽어옴으로써 속도향상을 노리는 것이지요.

하지만 이는 장점이자 독이 되기도 합니다.

이제 본론으로 들어가면

False Sharing 이란 멀티코어 환경에서 각각의 코어에서 접근하고 있는 데이터가 같은 cache line에 있어서 실제로 공유하는 데이터가 아님에도 하드웨어(프로세서)에서는 이를 공유하는 것으로 착각하여 cache coherence 트래픽이 발생하는 것을 의미한다.

그림을 보면, 2개의 스레드가 서로 다른 데이터를 접근하는데 이 데이터가 같은 Cache line에 들어있게 되며 

실제로 인접메모리일 뿐 동시 접근이 일어나지 않는 코드라고 하더라도, 

해당 코드가 어떻게 작성되었는지는 중요치 않습니다. 

캐시 라인은 인접 메모리 접근만으로도 성능 손해를 보면서라도 

데이터의 유효성을 높이고자 하는 판단을 내릴 수 밖에 없습니다.

심각한 경우 성능 하락의 원인이 되기도 합니다.

해결하기 위해서는 간단한 방법으로 data structure 에 적절한 padding(dummy) 을 삽입하여서 막는 방법이 있다. 좀더 나은 방법으로는 data structure 를 각 스레드에 맞도록 privatization 하는 방법이 있다.

결국  메모리를 손해보더라도 속도에서 이득을 보라는 얘기죠.

My-SQL 백업 및 복원

- 특정 데이타베이스 하나만을 백업

백업형식 : myslqdump -u DB계정명 -p 백업대상데이터베이스명 > 저장할파일명

복구형식 : mysql -u DB계정명 -p 복구할테이터베이스명 < 저장할파일명

# ./mysqldump -u root -p mysql > mysql.sql      //mysql 디비 백업 예

# ./mysql -u root -p mysql < ./mysql.sql           //mysql 디비 복구 예

위 백업결과물인 sql 파일은 디비를 생성하지는 않는다.. 다시말해 복구하려는 디비에 mysql이란 디비가 없다면 복구가 되지않는다.

복구하려는 mysql 디비에 테이블이 이미 존재한다면 복구 실패... sql 파일에서 해당 디비의 테이블을 생성하므로.. 따라서 특정 데이타베이스 하나만을 복구 할 시에는 데이타베이스의 이름만 생성한 다음에 위 방법의 복구하여야 함

- 특정 데이타베이스의 특정 테이블 하나만을 복구 백업

백업형식 : myslqdump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할파일명

복구형식 : mysql -u DB계정명 -p 데이터베이스명 < 저장할파일명

#./mysqldump -u root -p mysql user > user.sql

#./mysql -u root -p mysql < user.sql

- 여러개의 데이터베이스 한번에 백업과 복구

백업형식 : mysqldump -u root -p --databases [옵션] DB1 [DB2 DB3] > 파일명

복구방법 : mysql -u root -p < 파일명

# ./mysqldump -u root -p --databases mysql test > dbs.sql   //mysql, test 디비를 백업

# ./mysql -u -root -p < dbs.sql                                            //백업된 두 디비를 복구

databases 옵션 시 일반 백업과 달리 "CREATE DATABASE ..."문과 "USE DB .." 문이 추가됨

//달리 애기하면 DB를 생성할 필요가 없이 바루 디비가 생성되고 생성된 디비로 전환되고 테이블을 생성하고 인서트로 데이타를 붓는다.. 데이타베이스를 지정하면 안됨

- Mysql 의 전체 데이타베이스 백업

백업형식 : ./mysqldump -u root -p --all-databases > 파일명.sql

복구형식 : mysql -u root -p < 파일명.sql

원본 데이타베이스명과 동일한 디비가 생성됨

- 기존 테이블 삭제후  백업된 파일로 복구를 위한 백업

 (mysqldump문으로 데이타베이스 백업 시 각각의 create table문 앞에 drop table문을 삽입한 형태가 된다)

백업형식 : ./mysqldump -u root -p --add-drop-table test > test.sql

test 데이타베이스를 기존 태이블 삭제문을 추가하여 백업한다

복구형식 : ./mysql -u root -p test < test.sql

기존 테이블을 삭제하고 복구한다는것에 유의

- 기타 옵션
   -A, --all-databases : 모든 DB 덤프
   -B, --databases : 특정 DB를 덤프 

   --opt : 버퍼링을 비확성화 하고, 많은 데이터를 덤프에 있는 소량의 SQL구문에 기록해 더효율적으로 동작하다록 한다.
   --allow-keywords, --quote-names : 예약어를 사용하는 테이블을 덤프하고 복원할 수 있게 한다.
   --lock-alltables : 전역적으로 일관된 백업을 만들도록 "FLUSH TABLES WITH READ LOCK"을 사용한다. 
   --tab : "SELECT INTO OUTFILE"로 파일을 덤프하여, 덤프 및 복원 속도가 매우 빠르다.
   -d, --no-data :  데이터는 제외하고 스키마만 덤프
   -t, --no-create-info : 스키마는 제외하고 

로그 복구 방법

delete나 truncate 같은 명령으로 데이터를 지웠을때 복구하는 방법입니다.
mysql의 모든 명령들은 로그를 남기기때문에 가능합니다.
일단 mysql의 DB가 쌓이는 디렉토리로 이동합니다. 
대게 /usr/local/mysql/data /usr/local/mysql/var 등으로 설정해 놓으셨을겁니다.
해당 디렉토리로 이동하면 xxx-bin.001 xxx-bin.002 같은 바이너리 파일들이 존재할겁니다.
이게 바로 DB를 첨 생성할때부터 지금까지 쌓여온 로그인데요 이 파일을 활용하시면 삭제된 데이터도 살릴수 있습니다.
바이너리 파일이기때문에 그냥 접근하시면 이상한 문자들이 보이구요
mysqlbinlog 명령을 이용하셔서 사용해야 합니다.
다음은 예제입니다.

일단 DB 디렉토리로 이동한다.
# cd /usr/local/mysql/data

바이너리 파일을 소스파일로 변환한다.
# ../mysqlbinlog leopit-bin.002 > rescue.sql

필요한 구문만 가져온다.
# grep "insert" rescue.sql > rescue1.sql

이렇게 하시면 rescue1.sql은 지금까지의 인서트구문이 다 들어옵니다.
여기서 필요없는 부분은 지우세요

이제 최종적으로 DB에 삽입만 하시면 됩니다.
# mysql -u 계정아이디 -p DB이름 < rescue1.sql

DBA의 정기 업무

일일 작업

1. 네트워크를 포함한 필요 서비스가 시작(작동)중인지 확인

2. 윈도우 이벤트 뷰어를 통한 오류 및 경고등의 중요 메시지를 점검 및 문제 해결

3. SQL Server 로그에서 오류 및 중요 메시지를 점검하고 문제를 해결한다.

4. SQL Server 에이전트 로그에서 오류를 포함한 중요 메시지를 점검하고 문제를 해결한다.

5. 에이전트 서비스에 정의해둔 각 작업(job)의 성공 여부를 점검하고 필요한 조치를 취한다.

6. Windows Server와 SQL Server의 주요 카운트를 모니터하면서 이상 증상을 확인한다.

7. Windosw 성능 모니터의 성능 로그 및 경고 | 카운트 로그 에서 성능 통계 정보를 수집한다.

8. Profiller와 같은 도구를 이용 과도한 리소스 소비, 잠금 유발, 차단(blocking)문제 데드락을 

   유발하는 쿼리를 추적, 조치를 취한다.

9. 디스크 공간이 충분한지 점검한다.

10. 통계 업데이트 실시

주간 작업

1. 인덱스 조각화 상태를 점검하고 필요시 적절한 방법으로 조각모음을 수행한다.

확인 :  DBCC SHOWCONTIG ['tbl_name']

조치 :  조각모음이 가능하지만 단편화가 심한경우 재생성이 더 빠르다. 

DBCC REINDEX - 오프라인 작업, 비서비스 시간에 인덱스를 재생성 할때 사용

재생성 되는 인덱스의 밀도가 높지만 작업중 락이 걸려 다른 작업을 할 수 없음 

스케줄링해서 사용하기 좋음

DBCC INDEXDEFRAG - 온라인 작업, 서비스 시간에도 온라인으로 작동

엔터프라이즈 이상 버전에서만 사용 가능, 장시간 락을 사용하지 않으며

쿼리 및 업데이트가 가능, 조각화가 심하면 REINDEX보다 오래걸림

새로 생성보다 밀도가 엉성함, 통계 자동 업데이트가 안됨 수동작업 필요

2. 시스템 및 사용자 데이터베이스의 전체 백업 혹은 차등 백업을 수행한다.

3. 통계 업데이트 실시 ( Update STATISTICS,  SP_UPDATESTATS)

     통계의 확인은 DBCC SHOW_STATISTICS ('테이블명', '통계명') 

4. 데이터와 로그 파일에서 불필요하게 과도한 여유 공간을 줄임

월간 작업

1. 운영체제 전체 백업

2. 시스템 및 사용자 데이터베이스의 전체 백업 혹은 차등 백업 수행

3. 데이터베이스 무결성 검사 수행 결과에 따라 조치

    (DBCC CHECKTABLE 혹은 DBCC CHECKDB 명령을 사용)

4. 테스트 장비에서 시스템 및 사용자 데이터베이스를 완적히 복구 및 복원할 수 있또록 시연

5. SQLDIAG.EXE 수행 및 검토

6. 각 서버별로 지난 1개월간 수집한 성능 통계 정보를 기존의 성능 통계 정보와 비교

    향후 소모되는 S/W, H/W 용량을 예측한다.

비정기 작업

1. 트랜잭션 로그 파일이 일정수준 이상으로 채워진 경우, 

    로그 백업등을 이용하여 로그 사이즈를 줄인다.

2. 데이터 베이스 구조 변경, 로그인 변경, 서버 구성 옵션 변경 등이 있으면 MASTER를 백업

3. 에이전트 서비스의 작업, 경고, 운영자 및 유지 관리 계획등이 변경되면 MSDB를 백업

4. 시스템 및 사용자 데이터베이스 개체를 추가 혹은 변경한 경우 

    해당 데이터베이스의 스크립트 백업

디비 관리용 쿼리 모음

로그 남은 양 확인

DBCC SQLPERF(LOGSPACE)

로그 강제로 비우기

   

  2008 버젼 이후부터는 NO_LOG, TRUNCATE_ONLY 옵션이 안먹힌다.

  그러니까 아래와 같이 실행해주자

   

   

  ALTER DATABASE DB명 SET RECOVERY SIMPLE

  GO

   

  DBCC SHRINKFILE(로그명)

  GO

   

  EXEC sp_helpdb DB명    -- 용량 확인

  GO

   

  ALTER DATABASE DB명 SET RECOVERY FULL

  GO

디피 퍼포먼스 확인

SELECT DB_NAME(st.dbid) DBName

        ,OBJECT_NAME(st.objectid) StoredProcedure

        ,max(cp.usecounts) Execution_count

        ,sum(qs.total_worker_time) total_cpu_time

        ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time

   

   FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle

        CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

   where DB_NAME(st.dbid) is not null and cp.objtype = 'Proc'

   group by DB_NAME(st.dbid), OBJECT_NAME(objectid) 

   order by sum(qs.total_worker_time) desc

Idemtity 확인, 초기값 변경

확인

use 데이터베이스명

DBCC CHECKIDENT('테이블명')

초기값변경

DBCC CHECKIDENT('테이블명',RESEED,'값')

랜덤값 뽑기 

order by NEWID()

디비 저장 경로 변경

--------------------------------------------------------------------------

alter database DB명 set offline

go

alter database DB명

modify file

(name = DB명,

filename = 'F:\SQL Data\파일명.mdf')

go

alter database DB명

modify file

(name = DB명_log,

filename = 'F:\SQL Data\파일명_log.ldf')

go

alter database DB명set online

go

sp_helpdb DB명

------------------------------------------------------------------------

프로시저 안에서 with(nolock)과 같은 효과 내기

create proc usp_GetLogon

 @uid int

 as

   set nocount on;

   set transaction isolation level read uncommitted;

   select SessionID from Logon where UserID=@uid

  go

MS-SQL 백업 및 복원

복구 모델

전체 / 대량 로그 / 단순(로그백업불가)

백업 종류

전체 백업 (Full backup)

   - 처음 DB 생성시

   - 트랜잭션 로그를 비웠을시

   - DB에 변경이 생겼을때(ALTER DB)

   - 차등, 로그 백업 전 한번 이상

      BACKUP DATABASE 디비이름 TO 파일&장치

차등 백업 (Differential Backup)

   - 마지막 백업 이후에 변경된 모든 데이터를 백업

   - 복구시 마지막 차등백업과 

      마지막과 가장 가까운 전체백업 파일

      BACKUP DATABASE 디비이름 TO 파일&장치 

      WITH DIFFERENTIAL

로그 백업 (Log Backup)

   - 백업 속도가 빠르고 복원속도가 느리다

   - 중간 로그백업파일 유실 시 복구 불가

     

      BACKUP LOG 디비이름 TO 파일&장치 

부분 백업 (Partial Backup)

   - 파일 그룹을 백업

   - 주 파일 그룹, 읽기/쓰기 파일그룹만 백업

   - 단순 복구 모델에 가장 적합

      BACKUP DATABASE 디비이름

      READ_WRITE_FILEGROUPS TO 장치

미러 백업 (Mirror Backup)

  - 백업 데이터의 유실이나 파손 시

      데이터 유실을 막기 위해 사용

  - 백업 장치 이외에 하나의 장치에 똑같이 백업

      BACKUP DATABASE 디비명

      TO DISK = 'C:\a.bak'

      MIRROR TO DISK = 'D:\a.bak'

      WITH FORMAT

복사 전용 백업 (Copy Backup)

   - 데이터 베이스의 복사나 이동 테스트시 사용

      BACKUP DATABASE 디비이름 TO 장치

      WITH COPY_ONLY

체크섬 기능 (CHECKSUM)

   - 백업시 데이터의 이상 유무를 체크

   - 백업 체크섬을 해야 복원시 체크섬가능

      BACKUP DATABASE 디비이름 TO 장치

      WITH CHECKSUM

백업 매체 초기화

   - 기존 백업 내용이 삭제 디폴트는 NOINIT

      BACKUP DATABASE 디비이름 TO 장치

      WITH INIT

다중 백업 장치 초기화

   - 다중 백업 장치를 초기화 한후 백업 수행

   - 대개 다른 내용이 들어있는 여러 디스크에

     처음 백업 할 때 사용

      BACKUP DATABASE 디비이름 TO 장치1, 장치2

      WITH FORAMT

비밀번호 지정

   - 백업 및 복원시 비밀번호 지정

      BACKUP DATABASE 디비이름 TO 장치

      WITH PASSWORD ='비밀번호'

백업 중 오류발생시 계속 여부

   - 잘못된 체크섬 발생시에도 계속 백업 진행

   - 디폴트는 STOP_ONERROR 오류시 백업 중지

      BACKUP DATABASE 디비이름 TO 장치

      WITH CONTINUE_AFTER_ERROR

진행률 표시

   - 대용량 백업 진행시 현재 상황을 보는데 편리

      BACKUP DATABASE 디비이름 TO 장치

      WITH STATS

문제 발생시 로그 백업 (비상 로그 백업)

   - NO_TRUNCATE 옵션 사용 이상시에도 가능

      BACKUP LOG 디비이름 TO 장치

      WITH NO_TRUNCATE

----------------------------------------------------------------------------------------

복원

전체 복원

   - 전체 파일을 복원한다.

      RESTORE DATABASE 디비이름 FROM 장치

차등 복원

   - 차등 복원. 전체복원과 동일

   - WITH DIFFERENTIAL 옵션 필요 없음

      RESTORE DATABASE 디비이름 FROM 장치

로그 복원

   - 로그 파일을 복원

      RESTORE LOG 디비이름 FROM 장치

복원 완료 및 복원 중

   - 전체 백업과 로그 백업을 병행한 경우 복원 완료까지

      접근을 제어 할 수 있다. 

   - WITH RECOVERY(default)

      RESTORE DATABASE 디비이름 FROM 장치

      WITH NORECOVERY

복원 후에 제한된 사용자만 접근 허용

      RESTORE DATABASE 디비이름 FROM 장치

      WITH RESTRICTED_USER

      RESTORE DATABASE 디비이름 FROM 장치

      WITH RECOVERY

복원 시 데이터 파일의 이동

   - 기존 디스크가 꽉찼거나 문제가 있을 시

      RESTORE DATABASE 디비이름 FROM 장치

      WITH MOVE 'AdventureWorks_data' TO 'D:\adv.mdf'

, MOVE 'AdventureWorks_log' TO 'D:\adv.ldf'

오류가 발생해도 계속 복원

      RESTORE DATABASE 디비이름 FROM 장치

      WITH CONTINUE_AFTER_ERROR

원래 파일이 있으면 덮어쓰기

      RESTORE DATABASE 디비이름 FROM 장치

      WITH REPLACE

정확한 시점까지만 복원하기

      RESTORE DATABASE 디비이름 FROM 장치

      WITH STOPAT = '날짜와 시간'

데이터베이스 스냅숏으로 복원하기

      RESTORE DATABASE 디비이름 FROM DATABASE_SNAPSHOT = '스냅숏 이름'

페이지 복원

      RESTORE DATABASE 디비이름 PAGE='파일번호:페이지번호' FROM 백업장치

증분 복원

   - 파일 그룹 단위로 복원 진행

      RESTORE DATABASE 디비이름 FROM 장치

      WITH PARTIAL