MSSQL 어셈블리 만들기 - How to Implementing CLR Object DLL

시나리오
1. SQL Server에서 PDW(Parallel Data Warehouse)로 동적 쿼리를 만들어서 날리려고 하는데 8000자 이상의 쿼리문을 수행해야 한다.
2. A서버의 SQL Server (가정 주소:321.321.1.321,9999)에서 B서버의 PDW (가정 주소 : 123.123.1.123,8888)로 쿼리를 날려 SELECT 결과를 받아오고자 한다.
3. 특정 사용자에게만 DB 접근권한을 주고 계정을 분리하고자 한다.

해결방법
CLR Object DLL를 만들어서 ASSEMBLY로 등록하고 CLR을 통해 쿼리를 수행한다.
CLR을 통해 DB 접근 권한을 분리하고 8000자 이상의 동적쿼리를 쿼리를 수행할 수 있다.

  고려사항
얼마 전 MSSQL이 설치된 윈도우 서버에서 메모리가 부족한 현상을 겪었고 덕분에 수 번의 장애를 겪었다. 윈도우 서버의 메모리가 약 100기가 이상이 되지만 보통의 경우에는 DB에 대부분의 메모리를 할당하고 최소한의 메모리를 OS에 할당하겠지만 필자의 경우에는 MSSQL에서 PDW로 CLR을 호출하는 경우 딱 그 시점에 윈도우 서버의 메모리가 확 튀며 장애 현상을 겪었다. 이런 경우에는 MS 엔지니어가 메모리를 DB에 적당히 할당한 후 안전하다고 판단되는 수준까지 점진적으로 올릴 필요가 있다고 한다.


방법

1. DLL을 만들기 위해 Visual Studio에서 Visual C# CLR Database Project를 만든다.

(파일 -> 새 프로젝트 -> SQL Server -> SQL Server 데이터베이스 프로젝트 생성)

2. 솔루션 탐색기에서 추가 -> 새항목 -> SQL CLR C# -> SQL CLR C# 저장 프로시저 생성한다.

3. 쿼리를 날릴 때 SelectPdwByLogin 함수를 호출할 것이고 이 함수 내부에서 분리시키고자 하는 login_name을 "exuser"를 구분했다.

4. 빌드를 하고 DLL(CLR_PDW.dll)을 얻는다. 이를 서버(321.321.1.321,9999)의 적당한 위치에 복사한다.

5. 서버(321.321.1.321,9999)에서 CREATE ASSEMBLY문으로 CREATE ASSEMBLY를 하고

이 때 위에서 만든 CLR_PDW.dll의 경로를 입력해준다. 그리고 프로시저를 하나 생성하여 어셈블리를 수행하도록 한다.

6. 테스트를 한다.

A 서버 SQL Server(321.321.1.321,9999)에서 USP_CLR_PDW를 호출하여 B 서버의 PDW(123.123.1.123,8888)의 TEST DB CUSTOMER 테이블을 SELECT 해본다.

※ 별도로 test DB에는 로그인 생성, 사용자 추가, 롤에 사용자를 추가해야한다.

--로그인 생성
CREATE LOGIN exuser WITH PASSWORD = 'exuser',
CHECK_EXPIATION = OFF,
CHECK_POLICY = OFF;

--TEST DB에 사용자 추가
USE test;
CRATE USER exuser WITH DEFAULT_SCHEMA=dbo;

--롤에 사용자 추가
EXEC test..sp_addrolemember db_datareader, exuser;