Data Science Lab

DDL Trigger를 이용한 데이터베이스 변경 사항 추적 Version : SQL Server 2005, 2008, 2008R2, 2012 누군가 데이터베이스에 접속하여 프로시저를 생성하고, 테이블을 생성하고, 스키마를 변경한다. 어떻게 추적할 수 있을까? DDL 트리거를 이용하여 데이터베이스의 변경사항을 추적하여 보자. 변경이력을 저장할 테이블을 생성한다. CREATE TABLE dbo.DDLEvents( EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, EventType NVARCHAR(64), EventDDL NVARCHAR(MAX), EventXML XML, DatabaseName NVARCHAR(255), SchemaName NVARCHAR(25..

Trigger를 이용한 SQL Server 커넥션 풀링 확인 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server를 운영할 때 응용프로그램이 SQL Server에 연결하는 비용은 매우 중요하다. 사용자 커넥션 비용은 (3 * network_packet_size) + 94KB 이다. 기본 네트워크의 패킷 사이즈는 4KB 이다. 결국 사용자 1명당 필요한 연결 비용은 약 130KB가 소모 된다. 커넥션 풀링은 응용프로그램이 커넥션을 필요로 하는 시점에 커넥션을 만드는게 아니라 미리 일정 수의 커넥션을 만들어 놓고 필요로 하는 응용프로그램에 전달하는 방식이다. DDL Trigger를 활용하여 응용프로그램이 커넥션 풀링을 사용하고 있는지 알아 보자. 커넥션 기록을..

SQL Server Trigger Version : SQL Server 2000, 2005, 2008, 2008R2, 2012 데이터베이스 서버 활동에서 감사가 필요할 때(시스템 변경, 데이터 조작 등)변경 사항 등을 수집 할 수 있다. SQL Server 2000에서는 로그인 감사 및 DML(데이터 조작 언어)을 사용하여 데이터 변경 사항을 캡처 하였다. SQL Server 2005 부터는 DDL(데이터 정의 언어) 트리거가 도입 되었다. [DML 트리거] CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDAT..

인덱스에 대한 SORT_IN_TEMPDB 옵션 Version : SQL Server 2005, 2008, 2008R2, 2012 인덱스를 만들거나 리빌드 할 때 발생하는 디스크 공간의 추가 요구 사항이 발생하는 케이스를 살펴 보았다. [인덱스 작업의 디스크 공간 요구 사항] http://sqlmvp.kr/140180087406 인덱스를 생성할 때 SORTIN_TEMPDB를 사용하여 성능 향상을 시킬 수 있다고 하였는데 SORT_IN_TEMPDB에 대해서 자세히 알아 보자. SORT_IN_TEMPDB 옵션은 인덱스를 만들거나 리빌드 할 때 SQL Server 데이터베이스 엔진에서 인덱스를 만드는데 사용되는 중간 정렬 결과를 tempdb에 저장한다. tempdb가 데이터가 위치한 드라이브가 아닌 다른 빠른 ..

인덱스 DDL 작업의 디스크 공간 요구 사항 Version : SQL Server 2000, 2005, 2008, 2008R2, 2012 디스크 공간은 인덱스를 생성, 다시 작성, 삭제 할 때 고려해야 할 주요 사항이다. 디스크 공간이 부족하면 성능이 저하되거나 인덱스 작업이 실패 할 수도 있다. 인덱스를 생성할 때 고려해야 할 일반적인 사항을 정리하여 보자. [인덱스 작업 시 추가 공간 불필요] DROP INDEX, 넌클러스터 인덱스를 삭제할 때 추가 공간 불필요. DROP INDEX, 넌클러스터 인덱스가 없는 경우 MOVE TO 절을 지정하지 않고 오프라인으로 클러스터형 인덱스를 삭제 할 때 추가 공간 불필요. [인덱스 작업 시 추가 공간 필요] CREATE INDEX CREATE INDEX WITH..

XML nodes() 함수를 이용한 OPENXML 교체 Version : SQL Server 2005, 2008, 2008R2, 2012 OPENXML은 XML문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 있을 수 있는 T-SQL 문에 OPENXML을 사용 할 수 있다. XML 문서의 내부 표시는 sp_xml_preparedocumnet 를 호출하여 만들 수 있다. 실습을 위해 간단히 XML 문서를 정의 한다. 1002012-01-01 2002012-02-06 [SELECT 문에 OPENXML 사용] Sp_xml_preparedocument를 사용하여 XML 이미지의 내부 표현을 만든다. OPENXML 행 집합..

XQuery를 사용한 XML 데이터 업데이트 Version : SQL Server 2005, 2008, 2008R2, 2012 XML의 값을 수정하는 방법에는 무엇이 있을까? XML 편집기 등을 이용하여 사용자가 직접 수정 할 수도 있지만 XML노드와 값을 정확하게 수정하려면 XQuery를 사용하여 수정 할 수 있다. XQuery는 구조화 되었거나 반구조화된 XML 데이터를 쿼리할 수 있는 언어이다. XQuery는 기존의 XPath 쿼리 언어를 기반으로 더 나은 반복 성능 및 정렬 결과를 위한 지원이 추가 되었다. 자세한 내용은 MSDN을 참고한다. [노드 삽입] 삽입 키워드는 다음과 같은 구조를 사용한다. Insert Expression1 ({as first | as last} into | after ..

BCP XML 파일 형식 Version : SQL Server 2005, 2008, 2008R2, 2012 SQL Server 환경에서 데이터를 가져오거나 내보내기 할 필요가 있다. SQL Server 2000 까지는 DTS를 사용하였고 SQL Server 2005 부터는 SSIS라는 SQL Server 통합서비스를 사용하였다. BCP라는 대량 복사 프로그램도 있다. BCP 정리 : http://sqlmvp.kr/140176987081 SSIS의 경우에는 GUI 기능 제공으로 많은 유연성을 제공한다. BCP의 경우에도 많은 기능이 향상 되었다. SQL Server 2005 부터는 데이터를 정의하는 형식의 파일을 생성하는데 XML 서식을 사용 할 수 있게 되었다. XML 형식 파일의 이점은 쉬운 생성, 읽..

SQL Server로 데이터 가져오기 Version : SQL Server 2005, 2008, 2008R2, 2012 DBA로 일하면서 많은 하는 작업 중 하나가 데이터를 이동하는 일이다. 데이터를 이동하는 방법에는 여려가지 방법이 있다. 가장 직관적인 방법은 방법은 마법사를 이용한 [가져오기/내보내기]이다. 하지만 이외에 여러가지 옵션이 있다. 예를 들어 엑셀이나 텍스트 문서의 파일 데이터를 가져오는 경우도 있다.(아마도 파일을 가져오는 작업이 더 많을 듯 하다.) 이런 작업이 계속 반복 될 때 매번 마법사를 통하기에는 많은 불편함이 있다. 또한 로컬이 아닌 위치에 있을 때는 어떻게 할까? 다양한 옵션을 통해서 데이터를 가져오는 방법을 알아 보자. 실습에 사용할 텍스트 파일을 생성. 실습용 테이블 생..

SQL Server Stored Procedure 암호화 Version : SQL Server 2000, 2005, 2008, 2008R2, 2012 웹 서버나 기타 응용 프로그램에서 SQL Server에 데이터를 입력하거나 조회, 삭제 등의 작업을 할 때 실제 코드를 알 수 없도록 Stored Procedure(SP)를 만들어서 사용한다. SP의 장점은 캡슐화가 되어 있어 외부 사용자가 코드를 확인 할 수 없으며 성능상에서도 많은 이점이 있기에 거의 대부분이 사용한다. 하지만 관리자나 개발자는 SP의 코드를 볼 수가 있다. 중요한 로직의 경우 SP 자체를 암호화 하는 방법을 없을까? 방법은 간단하다. 프로시저 생성시 암호화 옵션을 추가 하면 된다. 암호화 옵션은 WITH ENCRYPTION 을 사용한다..