SQL Server 2005 개발자 가이드[2/13]

SQL Server 2005와 .NET의 통합

마이크로소프트가 .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, Common Language Runtime)

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 CLR, .NET Framework과의 통합

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 을 확장시키기 위한 보다 안전하고, 신뢰할 수 있는, 효율적이고도 쉬운 방법이 새로 제공이 됩니다. ■ .NET Framework과 SQL Server 2005의 통합으로 제공되는 기능    ㆍ .NET 언어를 사용한 함수, 프로시저와 트리거 구현    ㆍ T-SQL 내장 함수 라이브러리를 보다 쉽게 확장    ㆍ SQL Server 외부 데이터 접근을 위한 보다 쉽고 효율적인 방법 제공    ㆍ T-SQL 보다 빠른 프로시저 로직과 연산 처리    ㆍ 투명한 구현 방법 - 기존 T-SQL 개체와 동일한 사용 방법을 제공 ■또한 두 가지 새로운 확장성 기능을 제공합니다.    ㆍ 스칼라 타입 시스템을 확장할 수 있는, 사용자 - 정의 형(UDT, User-Defined Types)    ㆍ 집계 프레임워크를 확장할 수 있는, 사용자 - 정의 집계 (UDA, User-Defined Aggregates)

SQLCLR 구성 개요

SQLCLR을 통해서 CLR 모듈(함수, 프로시저 등)을 작성하고 SQL Server 2005로 등록 및 호출하기 위한 기본 구성은 다음의 절차를 따릅니다. ■ SQLCLR 구성 절차    ㆍ Visual Studio 2005에서 SQL Server Project 형식으로 필요한 CLR 모듈을 작성    ㆍ 빌드를 통해서 어셈블리를(*.DLL) 생성한 후 SQL Server 자동 배포하거나 혹은 관련 DDL 구문을        사용해서 어셈블리와 해당 오브젝트를 직접 등록    ㆍ T-SQL에서 일반 개체와 동일하게 호출 및 사용 [참고] 현재 버전에서, SQLCLR은 서버 속성으로 정의되어 있으며, 기본적으로 Disabled 상태 입니다. 어셈블리와 CLR 모듈를 등록하는 것은 관계가 없지만, 실행을 위해서는 해당 속성을 Enabled로 설정해야만 합니다. 기본적으로 두 가지 방법을 사용할 수 있습니다. 1) sp_configure 를 사용해서 설정하는 방법 EXEC sp_configure‘ clr enabled’, 1 RECONFIGURE 2) SQL Server 구성 도구 중의 하나인, SQL Server Surface Area Configuration 툴을 사용하는 방법 그러면, CLR 함수를 하나의 예로 해서, SQLCLR를 만들어가는 과정을 순서대로 살펴보도록 하겠습니다.

CLR 함수 작성 예제

간단히 다음 절차에 따라서 CLR 작성합니다. 1. Visual Studio 2005에서 새로운 프로젝트(File | New Project)를 시작합니다. 예제는 C#으로 구성했습니다. 2. 프로젝트 타입은 데이터베이스(Database) | SQL Server Project 이며, 프로젝트 이름은 SQLCLRExample 이라고 해서 특정 폴더에 지정합니다. 3. 데이터베이스 참조 추가(Add Database Reference) 대화 상자에서 자동 배포에서 적용할 데이터베이스를 선택합니다. 예제에서는 AdventureWorks 를 선택했습니다. 4. 프로젝트(Project) 메뉴 | 사용자 정의 함수 추가(Add User-Defined Function) 메뉴를 선택해서, 함수를 추가합니다. 5. 새 항목 추가(Add New Item) 대화상자에서 사용자 - 정의 함수가 선택되고, 이름은 Factorial.cs 라고 지정합니다. 6. Factorial.cs 에 Factorial 함수를 추가 작성합니다. 소스 코드는 다음과 같습니다. 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); } }; 7. 빌드(Build) 메뉴에서 프로젝트를 빌드합니다(Ctrl-Shift-B). 빌드를 수행한 결과로 어셈블리 파일(SQLCLRExample.dll)이 생성됩니다. 8. 해당 어셈블리를 SQL Server 2005로 불러와서 등록하고 어셈블리 내의 메서드를 TSQL 함수로 연결하는 작업을 수행할 수 있습니다. 이 때 개발자와 SQL Server 관리자 측면에서 두 가지 방법을 사용할 수 있습니다. 이 두 가지 방법의 차이점을 이해하는 것도 중요한 내용이 될 것입니다. 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 사용자는 물론이고, SQL Server 관리자에겐 특히 .NET 어셈블리와 CLR 모듈에 대한 관련 작업들은 DDL 구문을 이용해서 직접 작업하는 것이 권장됩니다. 어셈블리 등록 작업은 CREATE ASSEMBLY 구문을 사용합니다. [참고] CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM { <client_assembly_specifier> | <assembly_bits> [ ,...n ] } [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ] [ ; ] Visual Studio 에서 배포(Deploy)를 통해서 등록되는 경우 FROM 절의 <assembly_bits> 로 직접 처리가 되는 반면, T-SQL로 작업 시에는 <client_assembly_specifier>를 사용해서 어셈블리 파일 경로만 지정하면 내부적으로 바이너리 코드를 읽어 들여서 저장하게 됩니다. 따라서 어셈블리가 데이터베이스에 등록되고 나면 그 파일은 필요 없어집니다. 그러나 해당 소스 코드나 필요한 파일들은 ALTER ASSEMBLY...ADD FILE 구문을 사용해서 데이터베이스에 저장해 두는 것을 권장합니다. CREATE 나 ALTER ASSEMBLY 구문에서는 WITH PERMISSION_SET 절에서 권한 관련 옵션을 선언할 수 있습니다. ■ 어셈블리에 대한 세 가지 권한 옵션은    ㆍ SAFE - 가장 제한적인 권한 집합으로, 어셈블리 내의 코드는 파일, 네트워크, 또는 레지스트리와        같은 외부 자원에 접근할 수 없습니다.    ㆍ EXTERNAL_ACCESS - 파일, 네트워크, 레지스트리와 같은 외부 자원에 접근이 가능합니다.    ㆍ UNSAFE - 제한이 없습니다. 또한, .NET 이 아닌 비 관리 코드도 호출될 수 있습니다.        가장 위험한 권한 집합이므로 신뢰할 수 있는 어셈블리에 대해서 부여되어야 합니다. ■ 어셈블리가 등록되고 나면, CLR 모듈을 작성하고 어셈블리 내 메서드(혹은 타입)와 연결하는 작업을 수행합니다. 이는 기존의 T-SQL 개체에서 사용하는 구문들 예를 들어 CREATE PROC, CREATE FUNCTION 등과 동일하며 다만, AS EXTERNAL NAME 절에서 어셈블리 이름, 클래스 이름, 그리고 메서드 이름을 사용 연관된 CLR 모듈과 메서드를 지정해 주면 됩니다. [따라하기] T-SQL 구문을 사용한 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 DROP ASSEMBLY 구문을 사용해서 어셈블리를 제거할 수 있습니다. 이 때 종속된 모든 T-SQL 개체가 먼저 제거되어야 합니다.

SQLCLR 통합으로 인한 이득

데이터베이스 엔진 내에서 .NET 코드의 실행을 지원함으로써 얻을 수 있는 이득은 다음과 같습니다. 도입효과 설명 향상된 프로그래밍 모델 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

이제 SQL Server 개발자의 양 손의 두 가지 도구가 제공이 됩니다. T-SQL 언어가 관계형 데이터베이스 주된 언어임에는 틀림이 없지만 활용할 수 있는 보다 폭 넓은 도구가 가능한만큼 그 최적 사례에 따라서 좋은 결과를 만들 수 있을 것입니다. 두 가지 접근 방법의 장점 들을 간단히 비교하면 다음과 같이 정리할 수 있습니다. ■ 데이터 액세스 중심적인 코드    ㆍ Transact-SQL 이 한증 더 유리합니다.    ㆍ .NET 런-타임을 로드할 필요가 없으며, 데이터 계층에 직접 액세스가 수행됩니다.    ㆍ 절차적 프로그래밍을 통해서 데이터 처리에 보다 밀접한 작업을 수행합니다. ■ 비-데이터베이스 코드    ㆍ .NET 코드가 더 유리합니다.    ㆍ 복잡하거나 전문적인 산술/통계 연산을 수행할 수 있습니다.    ㆍ SQL Server 외부의 자원 접근에 보다 강력한 기능을 제공합니다.    ㆍ 개체 지향적 프로그래밍 모델을 사용할 수 있습니다.

SQLCLR 메타데이터

SQL Server 2005에서 CLR 어셈블리와 관련 개체들을 관리하기 위한 여러가지 카탈로그 뷰들을 제공합니다. 다음은 관련된 내용입니다. 카탈로그 뷰 설명 sys.assemblies 데이터베이스에 저장된 어셈블리에 대한 정보 sys.assembly_files 어셈블리 관련 각 파일들에 대한 정보 sys.assembly_modules 저장 프로시저, 함수, 트리거와 같은 각 모듈에 대한 정보 sys.assembly_references 다른 어셈블리를 참조하는 경우의 참조 정보 sys.assembly_types 사용자-정의 형에 대한 정보

SQLCLR 모니터링

동적 관리 뷰(DMV, Dynamic Management View), SQL 프로필러 추적, 성능 모니터 개체와 관련 카운터 값 등을 통해서 SQLCLR 의 동작을 모니터 할 수 있습니다. ■ 동적 관리 뷰는 sys_dm_clr_* 시작하는 뷰들로 다음과 같습니다.    ㆍ sys.dm_clr_appdomains    ㆍ sys.dm_clr_loaded_assemblies    ㆍ sys.dm_clr_properties    ㆍ sys.dm_clr_tasks ■ SQL 프로필러에서는 CLR 이벤트 그룹을 추적할 수 있습니다.    ㆍ Assembly Load 이벤트 항목을 추적할 수 있으며, 이벤트는 어셈블리가 로드될 때 발생합니다.        어셈블리 로드에 대한 성공/실패 여부, 관련 성능 문제를 확인할 수 있습니다. ■ 성능 모니터 개체    ㆍ SQLServer: CLR - CLR Execution 카운터가 제공됩니다. 이는 CLR 전체 실행 시간 (ms)을 반영        합니다.

[출처] DBGuide.net