SQL Server SP_Congifure 변경 사항 캡처

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server가 처음 구성되면 DBA는 많은 구성관리를 한다. DBA는 승인되지 않은 구성 옵션의 변경을 감지하고 변경된 내용을 추적 할 수 있어야 한다.

 

기본적으로 추적에 사용할 수 있는 도구는 Profiler 또는 'fn_trace_gettable(@trc_Path) 가 있다. 이번 실습에서는 'fn_trace_gettable(@trc_path)'를 사용하여 변경사항을 확인해 보자.

 

우선 기본 추적이 활성화 되어 있는지 쿼리 이용하여 확인해 보자.

SELECT

     NAME, CASE WHEN VALUE_IN_USE = 1 THEN 'ENABLED'

                WHEN VALUE_IN_USE = 0 THEN 'DISABLED' END AS [STATUS]

FROM SYS.CONFIGURATIONS

WHERE NAME = 'DEFAULT TRACE ENABLED'

 

 

 

변경된 히스토리를 보관할 테이블을 생성한다.

CREATE TABLE SQLCONFIG_CHANGE (

TEXTDATA NVARCHAR(500),

HOSTNAME NVARCHAR(155),

APPLICATIONNAME NVARCHAR(255),

DATABASENAME NVARCHAR(155),

LOGINNAME NVARCHAR(155),

SPID INT,

STARTTIME DATETIME,

EVENTSEQUENCE INT

)

 

 

구성설정이 변경된 이력을 캡처하고 저장하는 저장 프로시저를 생성한다.

CREATE PROCEDURE USP_SQLCONFIG_CHANGE

 

AS

 

CREATE TABLE #TEMP_CONFIGURE(

TEXTDATA NVARCHAR(500),

HOSTNAME NVARCHAR(155),

APPLICATIONNAME NVARCHAR(255),

DATABASENAME NVARCHAR(155),

LOGINNAME NVARCHAR(155),

SPID INT,

STARTTIME DATETIME,

EVENTSEQUENCE INT

)

 

DECLARE @TRC_PATH NVARCHAR(500)

SELECT @TRC_PATH = CONVERT(NVARCHAR(500), VALUE) FROM FN_TRACE_GETINFO (DEFAULT) WHERE PROPERTY = 2

 

INSERT INTO #TEMP_CONFIGURE

SELECT TEXTDATA, HOSTNAME, APPLICATIONNAME, DATABASENAME, LOGINNAME, SPID, STARTTIME, EVENTSEQUENCE

FROM FN_TRACE_GETTABLE (@TRC_PATH, 1)

WHERE TEXTDATA LIKE '%CONFIGURE%'

AND SPID <> @@SPID

AND EVENTSEQUENCE NOT IN (SELECT EVENTSEQUENCE FROM SQLCONFIG_CHANGE)

AND TEXTDATA NOT LIKE '%INSERT INTO #TEMP_CONFIG%'

ORDER BY STARTTIME DESC

 

INSERT INTO SQLCONFIG_CHANGE

SELECT * FROM #TEMP_CONFIGURE

 

 

 

구성 설정을 변경하고 저장 프로시저를 호출하여 보자. 그리고 변경된 이력을 저장하는 테이블을 조회하면 변경 이력이 캡처 된 것을 확인 할 수 있다.

SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1

GO

RECONFIGURE WITH OVERRIDE

GO

 

SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1

GO

RECONFIGURE WITH OVERRIDE

GO

 

EXEC USP_SQLCONFIG_CHANGE

GO

SELECT * FROM SQLCONFIG_CHANGE

 

 

 

 

생성된 프로시저를 SQL Serve Agent를 통하여 1분 또는 상황에 따라 예약 작업을 설정해 놓으면주기적으로 변경사항을 캡처 할 수 있다. 또한 변경이력을 캡처하는 프로시저에 Email 또는 SMS 서비스를 호출하는 기능을 추가하면 변경 사항이 발생 하였을 때 DBA가 신속히 보고를 받을 수 있다.



강성욱 / jevida@naver.com

Microsoft SQL Server MVP

Blog : http://sqlmvp.kr

Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 14478
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8885
» SQL Server SP_Congifure 변경 사항 캡처 jevida(강성욱) 2016.09.13 2098
1693 SQL Server Net Libraries jevida(강성욱) 2016.09.13 1150
1692 SQL Server 별칭 설정 및 사용 jevida(강성욱) 2016.09.13 2850
1691 SQL Server에 연결된 어플리케이션 드라이버 버전 확인 jevida(강성욱) 2016.09.13 967
1690 SQL Server 성능 대시보드 jevida(강성욱) 2016.09.13 2350
1689 SSMS를 이용한 데이터베이스 복사 하기 jevida(강성욱) 2016.09.13 11833
1688 SQL Server에서 DB2 링크드 서버 연결 방법 jevida(강성욱) 2016.09.13 3448
1687 SQL Server 인덱스 활성 / 비활성 하기 jevida(강성욱) 2016.09.13 2436
1686 파티션 오프셋과 SQL Server에 대한 디스크 할당 단위 크기 jevida(강성욱) 2016.09.13 1797
1685 Read the End of a Large Error Log (에러로그 끝 부분 읽기) jevida(강성욱) 2016.09.13 1339
1684 데이터베이스 여유공간 확인 jevida(강성욱) 2016.09.13 1492
1683 FILE GROUP 간 데이터 이동 jevida(강성욱) 2016.09.13 1638
1682 문서화 되지 않은 sys.fn_PhysLocFormatter 함수 jevida(강성욱) 2016.09.13 1009
1681 SQL Server 관리자 전용 연결(DAC) jevida(강성욱) 2016.09.13 3574
1680 파일 접근 권한으로 인한 DB복원 실패 작업 해결하기 jevida(강성욱) 2016.09.13 2059
1679 DBCC CHECKDB 실행과 히스토리 관리 jevida(강성욱) 2016.09.13 1895
1678 MDF를 이용한 데이터베이스 복원 jevida(강성욱) 2016.09.13 1201
1677 EventID 3041 Error jevida(강성욱) 2016.09.13 1532
1676 데이터베이스 미러 백업 jevida(강성욱) 2016.09.13 1357
1675 SNAPSHOT을 이용한 SQL Server 복원 jevida(강성욱) 2016.09.13 2608





XE Login