DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
마이크로소프트가 .NET 이라고 불리는 개발 플랫폼을 발표한지도 5년여가 지났으며, .NET Framework은 윈도우즈, 웹, 그리고 모바일 어플리케이션들을 아울러서 혁신적인 차 - 세대 개발 플랫폼으로 발전시켜 나아가고 있습니다. 더블어 .NET에 관련된 기술들은 마이크로소프트의 많은 소프트웨어와 서비스의 적용되는 기반 기술이 되고 있습니다. SQL Server 또한 예외가 아닙니다. 어쩌면 .NET Framework를 가장 포괄적으로 통합시킨 제품이 아닐까 합니다. SQL Server 2005는 이제 .NET Framework과 통합되었습니다. 데이터베이스 사용자 혹은 개발자들은 C# 이나 VB 같은 .NET 언어를 사용해서 저장 프로시저, 함수, 트리거, 사용자 - 정의 집계, 그리고 사용자-정의 형을 작성하고 이를 SQL Server에서 기존의 데이터베이스 개체처럼 사용할 수 있도록 지원됩니다. 이번엔 데이터베이스 관리자 측면에서 .NET Framework과의 통합이 가져오는 의미와 그 내용 그리고 기타 관련된 사항들을 간략하게 살펴보고 SQL Server 2005에서 어떻게 적용 해 나갈 것인지 판단하는데 도움이 되고자 합니다. CLR은 .NET 코드의 실행 환경을 제공하는 Framework 핵심부입니다. .NET 코드로 작성된 어플리케이션을 실행하는데 있어서 필요한 많은 핵심 서비스들을 제공하는 것입니다. 예를 들어, 메모리 관리, 개체 생존주기 관리, 쓰레드 관리, 타입 안전성 검사, 보안, 그리고 I/O 관리 등등에 해당합니다. 이러한 CLR 실행 환경하에서 실행되는 코드들을 흔히 관리 코드(Managed Code)라고 부릅니다. 반대로 .NET 에서 Win32 API 나 COM 오브젝트를 호출하는 경우 비 관리 코드(Unmanaged Code)라고 부릅니다. Win32 API나 COM 기반 코드는 CLR에 의해서 제어되지 않기 때문입니다. CLR은 또한 다른 프로그램에서 의해서 호스트(Host)되어질 수 있습니다. 즉 프로그램에서 .NET 런타임을 로드하고(Hosting, 혹은 Host 프로그램), .NET 관리 환경하에서 코드를 실행하는데 사용하는 것입니다. 그 대표적인 호스트 프로그램이 바로 Internet Explorer, ASP.NET, 그리고 SQL Server 2005에 해당합니다. SQL Server 2000 버전까지 데이터베이스의 개발과 관리를 위한 주된 언어는 Transact- SQL(T-SQL) 이었습니다. T-SQL로는 해결할 수 없는 경우 예를 들어, 운영체제 수준의 기능이나 암호화와 같은 별도의 서비스 기능이 요구되는 경우엔 C++ 과 개방형 데이터 서비스(ODS, Open Data Service) API를 사용한 확장 저장 프로시저(Extended Stored Procedure) 를 개발해서 구현이 가능했었습니다. 반면에 확장 저장 프로시저가 SQL Server 내부 프로세스 공간에서 직접 수행되므로 신뢰성과 안정성 측면에서 문제가 될 수 있었습니다. SQL Server 2005에서도 물론 가장 주된 언어는 T-SQL 입니다. 더욱이 T-SQL은 많은 향상기능들로 인해서 그 활용도와 중요성은 더욱 강조가 될 것입니다. 그리고 이제 SQLCLR을통해서 T-SQL 을 확장시키기 위한 보다 안전하고, 신뢰할 수 있는, 효율적이고도 쉬운 방법이 새로 제공이 됩니다. SQLCLR을 통해서 CLR 모듈(함수, 프로시저 등)을 작성하고 SQL Server 2005로 등록 및 호출하기 위한 기본 구성은 다음의 절차를 따릅니다. 그러면, CLR 함수를 하나의 예로 해서, SQLCLR를 만들어가는 과정을 순서대로 살펴보도록 하겠습니다. 간단히 다음 절차에 따라서 CLR 작성합니다. A. Visual Studio 2005 개발자는 IDE에서 제공하는 자동 배포 기능을 사용할 수 있습 니다. 빌드(Build) 메뉴에 있는 Deploy 메뉴를 이용하면 어셈블리를 빌드, 등록, TSQL 개체로의 연결 작업이 자동으로 수행됩니다. 아주 편리하지만 실제 내부에 서 수행하는 작업 방식에는 부담스러운 부분이 많이 있습니다. 실제 수행 방법을 보고자 한다면, SQL 프로필러를 작동시키고 서버에서 수행되는 작업을 추적해 보면 알 수가 있습니다. 그 내용을 간단하게 정리를 하면, 우선 기존의 연결된 TSQL 개체 즉 저장 프로시저, 함수, 트리거 등을 제거하고, 다음에 어셈블리를 제 거합니다, 그 어셈블리를 다시 로드한 다면, 어셈블리 내에 정의된 T-SQL 개체를 다시 불러들여서 재 정의(연결) 합니다. 이 때 Visual Studio 2005가 데이터베이스 에 추가하는 파일이 해당 어셈블리 파일 뿐만 아니라 관련 소스 코드, 디버거 용 파일(*.pdb) 등을 모두 추가하게 됩니다. 이는 카탈로그 뷰 sys.assembly_files 를 실행해서 확인할 수 있습니다. 전체 파일이 등록되는 것을 조정하고 싶다면, 프로 젝트의 옵션을 변경을 하거나 T-SQL DDL 구문을 사용해서 직접 작업을 하는 것 입니다. B. 위에서 언급한 대로, 어셈블리와 CLR 모듈에 대한 작업은 T-SQL DDL 구문을 직접 사용해서 처리하는 것이 최적화 됩니다. 다음에서 관련된 DDL 구문과 예제 를 통해서 작업 방법을 살펴보겠습니다. SQL Server 사용자는 물론이고, SQL Server 관리자에겐 특히 .NET 어셈블리와 CLR 모듈에 대한 관련 작업들은 DDL 구문을 이용해서 직접 작업하는 것이 권장됩니다. 어셈블리 등록 작업은 CREATE ASSEMBLY 구문을 사용합니다. Visual Studio 에서 배포(Deploy)를 통해서 등록되는 경우 FROM 절의 로 직접 처리가 되는 반면, T-SQL로 작업 시에는 를 사용해서 어셈블리 파일 경로만 지정하면 내부적으로 바이너리 코드를 읽어 들여서 저장하게 됩니다. 따라서 어셈블리가 데이터베이스에 등록되고 나면 그 파일은 필요 없어집니다. 그러나 해당 소스 코드나 필요한 파일들은 ALTER ASSEMBLY...ADD FILE 구문을 사용해서 데이터베이스에 저장해 두는 것을 권장합니다. CREATE 나 ALTER ASSEMBLY 구문에서는 WITH PERMISSION_SET 절에서 권한 관련 옵션을 선언할 수 있습니다. 이는 기존의 T-SQL 개체에서 사용하는 구문들 예를 들어 CREATE PROC, CREATE FUNCTION 등과 동일하며 다만, AS EXTERNAL NAME 절에서 어셈블리 이름, 클래스 이름, 그리고 메서드 이름을 사용 연관된 CLR 모듈과 메서드를 지정해 주면 됩니다. [따라하기] T-SQL 구문을 사용한 CLR 모듈 등록 DROP ASSEMBLY 구문을 사용해서 어셈블리를 제거할 수 있습니다. 이 때 종속된 모든 T-SQL 개체가 먼저 제거되어야 합니다. 데이터베이스 엔진 내에서 .NET 코드의 실행을 지원함으로써 얻을 수 있는 이득은 다음과 같습니다. 이제 SQL Server 개발자의 양 손의 두 가지 도구가 제공이 됩니다. T-SQL 언어가 관계형 데이터베이스 주된 언어임에는 틀림이 없지만 활용할 수 있는 보다 폭 넓은 도구가 가능한만큼 그 최적 사례에 따라서 좋은 결과를 만들 수 있을 것입니다. 두 가지 접근 방법의 장점 들을 간단히 비교하면 다음과 같이 정리할 수 있습니다. SQL Server 2005에서 CLR 어셈블리와 관련 개체들을 관리하기 위한 여러 가지 카탈로그 뷰들을 제공합니다. 다음은 관련된 내용입니다. 동적 관리 뷰(DMV, Dynamic Management View), SQL 프로필러 추적, 성능 모니터 개체와 관련 카운터 값 등을 통해서 SQLCLR 의 동작을 모니터 할 수 있습니다.SQL Server 2005와 .NET의 통합
SQL Server 2005와 .NET의 통합
공용 언어 런타임(CLR, Common Language Runtime)
SQL CLR, .NET Framework과의 통합
.NET Framework과 SQL Server 2005의 통합으로 제공되는 기능
또한 두 가지 새로운 확장성 기능을 제공합니다.
SQLCLR 구성 개요
SQLCLR 구성 절차
사용해서 어셈블리와 해당 오브젝트를 직접 등록[참고]
현재 버전에서, SQLCLR은 서버 속성으로 정의되어 있으며, 기본적으로 Disabled 상태 입니다.
어셈블리와 CLR 모듈를 등록하는 것은 관계가 없지만, 실행을 위해서는 해당 속성을 Enabled로 설정해야만 합니다.
기본적으로 두 가지 방법을 사용할 수 있습니다.
1) sp_configure 를 사용해서 설정하는 방법
EXEC sp_configure‘ clr enabled’, 1
RECONFIGURE
2) SQL Server 구성 도구 중의 하나인, SQL Server Surface Area Configuration 툴을 사용하는 방법
CLR 함수 작성 예제
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Factorial(SqlInt32 Number)
{
if (Number < 1)
return 1;
else
eturn Number * Factorial(Number - 1);
}};
SQL Server에 어셈블리/함수 카탈로그 등록
[참고]
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { | [ ,...n ] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
[ ; ]
어셈블리에 대한 세 가지 권한 옵션은
어셈블리가 등록되고 나면, CLR 모듈을 작성하고 어셈블리 내 메서드(혹은 타입)와 연결하는 작업을 수행합니다.
USE AdventureWorks
GO
-- 어셈블리 등록
-- 어셈블리 파일이 C:\Temp 에 있다고 가정합니다.
CREATE ASSEMBLY SQLCLRDemo
FROM‘ C:\Temp\SQLCLRExample.dll’-- 실제 어셈블리 경로를 지정
WITH PERMISSION_SET = SAFE
GO
-- 옵션: 필요한 경우 스키마 생성
CREATE SCHEMA CLRDemo
GO
-- T-SQL 함수로 연결
CREATE FUNCTION CLRDemo.uf_Factorial(@Number int)
RETURNS int
AS
EXTERNAL NAME SQLCLRDemo.UserDefinedFunctions.Factorial
GO
-- 함수 호출 및 테스트
SELECT CLRDemo.uf_Factorial(3)
SELECT CLRDemo.uf_Factorial(10)
GO
SQLCLR 통합으로 인한 이득
도입효과
설명
향상된 프로그래밍 모델
ADO.NET을 사용하는 경우, 어플리케이션과 데이터베이스 에 단일 언어를 사용해서 유사한 코드로 개발할 수 있으며 서버에도 개체 지향적 프로그램 방법을 적용할 수 있습니다. 또한 구조적.
.NET Framework
기본 클래스
라이브러리 활용 가능NET Framework이 제공하는 방대한 분량의 클래스 라이브 러리를 사용해서, 전문적인 산술 연산, 통계, 암호화, XML 및 문자열 처리 등을 손 쉽게 처리할 수 있습니다.
성능
T-SQL 은 인터프리터 언어인 반면, .NET 코드는 처음 실행될 때 JIT(Just-In-Time) 컴파일러와 캐시 처리를 통해서 보다 나은 성능을 제공할 수 있습니다.
확장 저장 프로시저 대체
앞서 언급한 대로, 기존 확장 저장 프로시저의 문제점인 안정성과 신뢰성을 제공할 수 있으며 지금까지 언급한 .NET 관리 코드의 이득을 취할 수 있습니다.
안전성과 신뢰성
SQLCLR 은 기본적으로 Disabled 상태입니다. 어셈블리에 대한 권한 제어를 통해서 코드 보안을 강화할 수 있으며, SQL Server 가 .NET CLR의 호스트로서 메모리, 쓰레드 관리, 동기화 등을 제어합니다. 또한 .NET의 HPA(Host Protection Attributes)를 사용함으로 잘못된 시스템 자원의 접근으로 인한 문제에서 보호할 수 있습니다. 그 외에도 다양한 모니터링, 추적 방법을 통해서 성능 및 안전성 관리가 가능합니다.
.NET 코드 vs. T-SQL
데이터 액세스 중심적인 코드
비-데이터베이스 코드
SQLCLR 메타데이터
카탈로그 뷰
설명
sys.assemblies
데이터베이스에 저장된 어셈블리에 대한 정보
sys.assembly_files
어셈블리 관련 각 파일들에 대한 정보
sys.assembly_modules
저장 프로시저, 함수, 트리거와 같은 각 모듈에 대한 정보
sys.assembly_references
다른 어셈블리를 참조하는 경우의 참조 정보
sys.assembly_types
사용자-정의 형에 대한 정보
SQLCLR 모니터링
동적 관리 뷰는 sys_dm_clr_* 시작하는 뷰들로 다음과 같습니다.
SQL 프로필러에서는 CLR 이벤트 그룹을 추적할 수 있습니다.
성능 모니터 개체