수안이의 컴퓨터 연구실

Tony Patton ( TechRepublic ) 2006/06/09


.NET 애플리케이션 코드에서 예외를 처리하는 것은 매우 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 살펴본다.

개발자들은 예외(exception)를 처리하는데 친절하게도 많은 노력을 기울이기 때문에 사용자들은 알 수 없는 시스템 에러 메시지를 걱정할 필요가 없다. 이런 이유 때문에, 예외 처리는 모든 .NET 애플리케이션의 표준 항목이다. try/catch 블록은 개발자가 예외를 잡아내고 그 시점에서의 애플리케이션 실행을 컨트롤할 수 있도록 해준다. 많은 에러들은 데이터베이스 처리 중에 발생하지만 많은 개발자들은 데이터베이스 부분에서 생기는 에러를 처리하는 것을 알지 못한다. 이 기사에서는 SQL 서버와 T-SQL을 이용한 데이터베이스 코드에서 에러를 처리하는 방법을 알아보자.

T-SQL에서 발생한 에러 처리하기
SQL 서버가 제공하는 T-SQL 언어는 저장 프로시저, 함수 등에서 발생할 수 있는 치명적이지 않은 에러를 쉽게 처리할 수 있게 해주지만, 모든 에러가 쉽게 처리할 수 있게 되는 것은 아니다. 사실, 에러에는 치명적인 에러와 치명적이지 않은 에러가 있는데, 치명적이지 않은 에러와는 달리 치명적인 에러는 실행이 중단된다.

트랜잭션
변경사항이 모두 완료돼 모든 것이 정상인 것을 확실히 하기 위해서는 데이터베이스 코드에 트랜잭션을 사용해야만 한다. SQL 서버 온라인 도움말은 selects, inserts, updates 혹은 deletes와 같은 명령행의 연속으로 이루어진 논리적 작업 단위라고 설명한다. 만약 트랜잭션동안 에러가 없다면 트랜잭션의 모든 변경 사항은 데이터베이스에 적용될 것이며, 만약 에러가 발생하면, 어떤 변경사항도 데이터베이스에 적용되지 않는다.

트랜잭션은 BEGIN TRANSACTION과 END TRANSACTION 명령 사이에 포함된다. ROLLBACK TRANSACTION 명령은 모든 변경사항을 취소하도록 하여, 어떤 변경사항도 이루어지지 않게 한다. COMMIT TRANSACTION 명령은 변경사항을 데이터베이스에 반영한다. 이제, T-SQL에서 에러를 처리하는 방법을 알아보자.

@@Error
@@Error 함수는 T-SQL을 만들 때 에러를 처리하도록 해준다. 이 함수는 시스템의 에러 코드를 돌려준다. 만약 에러가 없으면 0을 리턴 한다. @@Error 함수는 각 T-SQL 명령이 실행되면 초기화되기 때문에, 명령을 호출한 직후 바로 불러야한다.

RAISERROR
RAISERROR 명령은 커스텀 에러 메시지를 만들거나 sysmessages 테이블에 이미 있는 메시지를 사용할 수 있게 해준다. 이 구문의 문법은 온라인으로 볼 수 있지만, 가장 기본적인 형태는 에러의 심각도, 상태와 함께 메시지나 메시지 ID를 포함한다. 상태는 SQL 서버에서 사용하지 않기 때문에 임의의 숫자를 이용해 처리한다. 심각도는 에러의 심각성을 나타내는데 0~18은 사용자가 사용할 수 있으며 19~25는 관리자를 위해 예약돼 있다.

예제 1. 이 예제 저장 프로시저는 Northwind 데이터베이스의 개별 레코드를 업데이트하는데 이 기능들을 사용한다. 에러가 없을 경우 전화 번호 칼럼의 값을 프로시저를 통해 수정한다. 만약 에러가 발생하면 음수를, 에러가 없으면 양수를 돌려주는 리턴 값을 사용한다.

저장 프로시저의 리턴 값 사용하기
.NET 코드에 저장 프로시저의 리턴 값을 사용할 수 있다. SqlCommand 객체는 저장된 리턴 값뿐만 아니라 쉽게 프로시저에 파라미터를 넘길 수 있도록 해준다. 파라미터의 Direction 속성은 저장 프로시저 호출을 통한 리턴 값을 얻는데 사용되는데, 이 속성은 InputOutput과 Output이 될 수 있다. 다음 예제에서는 상태 값을 받기 위해 Output을 사용하였다.

다음 예제는 Northwind 데이터베이스의 customers 테이블의 특정 레코드에 새로운 값을 저장하는 간단한 ASP.NET 페이지이다. id 값은 실제로는 hidden 필드로 저장된다. form을 통해 값을 쉽게 넘길 수 있지만, 데모를 위해 예제와 같이 했다. text 필드에 입력된 값은 phone 필드를 업데이트 하는데 사용된다.

파라미터는 SqlCommand 객체에 추가할 수 있으며 저장 프로시저의 파라미터 값과 정확히 일치해야한다. 이 작업은 SqlCommand 객체의 ExecuteNonQuery를 통해 실행된다. 이것이 실행되면, 파라미터를 통해 리턴 값을 받을 수 있다.

다음 예제는 리턴 값을 검사 하고(-1은 문제가 있음을 뜻한다) Label 컨트롤에 메시지를 표시한다. 추가로 데이터베이스 처리 중에 발생할 수 있는 치명적인 에러를 잡기 위해 try/catch 블록이 사용되었다. 예제 2. 예제 3은 같은 작업을 하는 VB.NET 코드이다

필요한 모든 것 제공
.NET 애플리케이션 코드에서 예외를 처리하는 것은 간단하고 try/catch 코드 블록을 이용한 직관적인 절차이다. 하지만, 데이터베이스 부분에서도 역시 예외를 모니터 할 수 있는데, SQL 서버의 T-SQL은 코드를 실행하면서 확인할 수 있는 모든 것들을 제공한다.@


http://www.zdnet.co.kr/builder/dev/etc/ ··· 2C00.htm

개발자를 위한 튜닝 가이드
쿼리디자인

쿼리 디자인

	번호 수칙 체크 1 SELECT는 필요한 결과값만을 요구하는가? 2 적절한 WHERE조건을 사용하는가? 3 COUNT(컬럼명) 대신 COUNT(*)을 사용하는가? 4 커서 및 임시 테이블의 내용을 최대한 자제하는가? 5 VIEW의 총 사용을 줄였는가? 6 저장 프로시저를 사용하는가? 7 저장 프로시저를 적절하게 리컴 파일 하며 사용하는가 ? 8 작명 된 저장 프로시저 SP외의 접두어를 사용하는가 ? 9 모든 개체의 소유자는 DBO로 지정하며 생성했는가 ? 10 데드락이 발생하는 부분을 라이브락 형태로 변경했는가? 11 SET NOCOUNT ON을 사용하는가? 12 실무 사례: 저장 프로시저 관리 방법

스티브 맥코넬이 이런 말을 했습니다.
뛰어난 디자이너는 습득한 지식을 사용하지 않는 것과 그 지식을 처음부터 확보하지 못한 것을 동일하게 봅니다.
이말 뜻을 다음과 같이 해석하고 싶습니다. 여러분은 쿼리 분석기의 기능들이 어떤 것이 있고, 단축키가 메뉴우측에 작게 표시되어 있다는 것을 대부분 알고 있습니다. 하지만 잘 사용하지는 않고 있을 것입니다. 라고 말입니다. 그래서 먼저 단축키와 그 사용법에 대해 안내하는 시간을 우선 가지도록 하겠습니다.
다음을 실습해보고 자세한 것은 표를 참조합시다


-- CTRL + E , F5 -- 실행하기 ---------------------------------------------------------------- use pubs go select * from titles -- CTRL + T => 결과 Text로 보기 select * from titles -- CTRL + D => 결과 Text로 보기 select * from titles -- CTRL + K => 실행계획 보기 select * from titles -- F8 => 개체브라우저 보이기/감추기 -- CTRL + R => 결과창 보이기/감추기 -- 그외 CTRL + C , CTRL + V , CTRL + X -- CTRL + SHIFT + C => 주석달기 select * from titles

다음의 표를 참조합시다.

	없음	Shift +	Ctrl+	Alt+	Shift+Ctrl+
A			전체 선택
B			중간 구분선 선택
C			복사		주석 달기
D			표 형태로 결과 표시	데이터베이스 선택
E			실행
F			찾기		파일로 결과 저장
G
H			교체
I			인덱스 튜닝마법사
J
K			실행 계획 보기
L			예상 실행 계획 보기		선택 내용을 소문자로
M
N			새 쿼리 윈도우
O			연결
P
Q
R			결과창 보이기/감추기		주석제거
S			저장
T			텍스트로 결과 표시
U					선택 내용을 대문자로
U			선택 내용을 대문자로
V			붙여넣기
W
X			자르기
Y			다시하기
Z			취소
F1	도움말	선택 내용을 도움말로 보기
F8	객체 브라우저보이기감추기

주요 단축키 사용 안내 입니다.

수칙 1. SELECT는 필요한 결과값만을 요구하는가?
select title , price from titles where title_id = 'BU1032'
Select 하는 내용도 필요한 항목만을 가지고 오도록 되어 있어서 리소스가 전혀 낭비되지 않고 있습니다.

select title , price from titles
필요한 칼럼을 가져오기는 하지만 불필요한 전체 행(Row)들을 가져오고 있습니다.

select * from titles
불필요한 칼럼정보, 행(Row) 데이터를 가져오고 있습니다.


따라 하기 - 3개의 쿼리를 한번에 실행하기

1. 3개의 쿼리를 한 Session에서 실행하여 결과3개를 동시에 살펴봅니다.




2. 결과 값으로 출력되는 데이터량의 차이를 확인합니다. 어느 쿼리가 가장 간결한 결과를 반환합니까?
( 반드시 꼭 필요한 결과만 반환하게 하는 것이 좋습니다.
select title , price from titles where title_id = 'BU1032' 가 적절합니다.)


수칙 2. 적절한 WHERE 조건을 사용하는가?

인덱스란 데이터를 빨리 찾기 위해서 사용됩니다. 인덱스가 없다면 특정데이터를 찾기 위해서 모든 데이터페이지를 검색(Table Scan)해야만 합니다 그에 비해 인덱스가 존재하고 그 인덱스가 사용되는 것이 효과적이라면 SQL서버는 해당 인덱스 페이지를 사용하여 쉽게 데이터를 가져올 수 있는데 이를 인덱스 검색(Index Seek)이라 합니다.
그러나 이렇게 인덱스가 있더라도 이를 사용 불가능하게 하는 나쁜 쿼리가 있으니 이는 검색조건에서 불필요하게 칼럼이 변형된 경우입니다. 다음의 여러 나쁜 예를 좋은 예와 비교해 봅시다.
SARG(Search Argument)란 쿼리가 반환하는 결과를 제한하기 위하여 옵티마이저가 인덱스와 결합해서 사용할 수 있는 쿼리 내의 조건절을 말하는데 다음의 형태를 가집니다.

컬럼 연산자/변수

옵티마이저가 쓸모 있게 변환하는 것은 CTRL+K 실행 계획 상부 표시에서 관찰할 수 있습니다.
set showplan_all on select * from authors where au_lname like 'Ma%' -- OBJECT:([pubs].[dbo].[authors].[aunmind]), SEEK:([authors].[au_lname] >= 'Ma' AND [authors].[au_lname] < 'MB'), WHERE:(like([authors].[au_lname], 'Ma%', NULL)) ORDERED FORWARD -- set showplan_all off

따라 하기

1. 인덱스 찾기(Index Seek)를 확인합니다.



2. 다음과 같이 약간의 조건절(where) 변형만으로 인덱스페이지가 사용되지 않음을 확인합니다.



3.그렇다면 조건절(where) 변형하고 싶을 땐 어떻게 해야 할까요?



4. 다음 예제도 복습해 봅시다.



5. 항상 실행 계획을 참조하여 재차 쿼리를 확인해야 합니다.




수칙 3. COUNT(컬럼명) 대신 COUNT(*)을 사용하는가?

COUNT(*) 와 COUNT(컬럼명)의 차이는 중요합니다. COUNT 하는 해당 테이블 컬럼에 NULL 값을 포함하고 있다면 이 두 예제는 서로 다른 결과를 반환합니다. COUNT(컬럼명)은 그룹에 포함된 각 행을 평가하여 NULL이 아닌 값의 개수를 반환합니다. COUNT(*)는 NULL 값과 중복된 값을 포함한 그룹의 항목 개수를 반환합니다.
일반적으로, COUNT(컬럼명)을 사용하여 특정한 컬럼의 행 개수를 세는 것보다 COUNT(*)을 사용하여 옵티마이저가 행의 개수를 반환하는 최상의 방법을 선택하도록 해주는 것을 더 선호하는 방식이다.



[참고] NULL을 처리하는 방법

use pubs go -- 돈 받고 파는 책을 출력하세요 select * from titles where price is not null -- 비매품인 책을 출력하세요 select * from titles where price is null -- 비매품책을 제외한 모든 책의 평균가격? select avg(price) from titles -- 비매품책을 0원으로 두고 계산한 평균가격? select avg(isnull(price,0)) from titles

[유용한 관용구]
칼럼의 중복 행의 수를 찾아봅시다

use pubs go -- 중복 칼럼이 각각 몇 개 항목인지를 찾아보자 -- type별로 몇 개의 책이 있을까? select type,count(*) as [중복 행의 수] from titles group by type having count(*)> 1


수칙 4. 커서 및 임시 테이블의 내용을 최대한 자제하는가?

결론부터 말씀 드리자면 커서보다는 임시테이블이 임시테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다. 단 SQL2000에서만 테이블 변수가 가능합니다.
커서는 내부적으로 임시 테이블을 사용하기 때문에 임시테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건 임시테이블이나 테이블 변수로도 모두 처리가 가능합니다.)


따라 하기 - 다음은 테이블 변수를 사용하여 기존 커서를 대체하는 것을 구현했습니다

1. 훌륭하게 커서를 대신하는 문장입니다.



CTRL + K로 확인하면 테이블 변수로 사용할 경우 실제 테이블에 잠금을 전혀 걸지않는 것을 알 수 있습니다(중요) 그와 반대로 커서를 사용할 경우 프로시저 시작부터 끝까지 지속적으로 사용 부분을 계속해서 잠그고 있어서 다른 작업들이 대기해야 되는 문제가 생깁니다.


수칙 5. VIEW의 총사용을 줄였는가?

VIEW는 보안과 편리성에 관련된 이슈를 다루는 데 있어 최고입니다,
그러나, 일반적으로 보안상에 이슈를 제외한 경우에는 불필요한 부하가 가중될 수 있고 많은 경우에 더 불필요한 데이터를 반환합니다 예를 들면 VIEW에서 10개를 가져오고 거기에 WHERE 조건을 붙여서 7개만 가져오는 경우가 그렇습니다.


select lastname,firstname from employees VS select * from EmployeesView 중간 단계가 있는 쪽이 효율이 떨어집니다.


수칙 6. 저장 프로시저를 사용하는가?

저장 프로시저는 복잡한 SQL문을 단순화 시켜주고, 보안 문제를 해결해주며 더 나아가 빠른 성능에 매개변수,출력매개변수,리턴 값을 사용할 수 있습니다.

제작

1. 구문분석 2. 표준화 3. 보안 점검(프로시저 생성권한) 4. 저장(syscomments)

첫 번째 실행 시

1. 보안 점검(프로시저 실행 권한) 2. 최적화 3. 컴파일과 이에 따른 실행계획을 캐쉬에 저장 4. 실행

반복해서 실행 시

1. 캐쉬에 실행 계획 있을 때는 그대로 실행 2. 캐쉬에 실행 계획이 없을 때는 첫 번째로 저장 프로시저 실행하는 것과 동일

쿼리는 한번만 실행할 때는 일반 SQL이 훨씬 간단합니다. 그러나 반복적으로 실행되면 저장 프로시저가 월등히 빠르고 편리합니다.


수칙 7. 저장 프로시저를 적절하게 리컴 파일 하는가?

데이터가 변화하면(인덱스를 추가하거나 인덱스된 열의 데이터를 변경하는 등의 작업 수행 시) 그에 걸맞게 실행계획도 변화해 갑니다. 그에 대처하기 위해서 다음과 같은 리컴파일 방법를 제공합니다.
저장 프로시저 리컴 파일 모드에는 다음의 3가지가 있습니다.





는 SQL SERVER가 이 저장 프로시저의 계획을 캐시하지 않기 때문에 이 저장 프로시저가 실행 할 때 마다 다시 컴파일 됩니다(실행 속도가 느려짐).



는 지금 이순간만 리컴파일 하고 다시 저장 프로시저 실행하면 예전 실행 계획대로 작동하는 것입니다. 제공하는 매개 변수가 불규칙하거나 저장 프로시저를 만든 다음 데이터가 많이 변경되었을 경우 이 옵션을 사용합니다.



는 저장 프로시저가 다음에 실행될 때 첫 실행처럼 컴파일되고 실행되도록 하는 것입니다.

[문서화되지 않은 DBCC 명령어]
-- pubs 데이터베이스의 모든 저장 프로시저를 재컴파일 해보자 select db_id('pubs') dbcc flushprocindb(5) -- 모든 인덱스를 재구축한다 -- 관리자가 사용할 경우 엄청난 시간이 소요될 수 있습니다 dbcc dbreindexall('pubs')


수칙 8. 저장 프로시저 작명 시 SP외의 접두어를 사용한다

시스템 저장 프로시저는 master 데이터베이스내에서 sp_라는 접두어로 시작하는 것이 좋으며 모든 데이터베이스에서 실행될 수 있습니다. 각 사용자 데이터베이스에서는 다른 접두어를 사용하는 것이 보기에도 좋고 알아보기에도 수월합니다.
또한 시스템 저장 프로시저는 어느 데이터베이스에서 수행하건 해당 데이터베이스의 내용을 참조합니다.


따라 하기

일반sp_ 저장 프로시저를 시스템sp_ 저장 프로시저로 만들어 봅니다.
1. 사용자 정의 저장 프로시저는 master데이터베이스에 존재하더라도 master내용만 참고합니다.



2. 그러나 다음과 같이 시스템 저장 프로시저화 한다면



3. 부연하자면 모든 데이터베이스에서 사용하는 프로시저의 경우 sp_로 시작하게 작성한후 sp_MS_marksystemobject로 시스템 프로 시저화 작업을 하는게 필요합니다. 이 내용은 엄격하게 구분되서 실행되는 것이 혼란을 줄일 수 있습니다.



수칙 9. 모든 개체의 소유자는 DBO 이다

소유자가 다르면 복잡한 소유권 체인문제가 발생합니다.



lucia가 테이블의 소유자 입니다. lucia는 뷰를 만들었는데 maria에게 뷰를 볼 수 있게 했습니다. maria는 이를 Pierre가 볼 수 있게 했는데 Pierre는 Maria가 만든 뷰를 select권한을 받았음에도 불구하고 실행이 안됩니다. 이는 소유권 체인이 중간에 분실 됐기 때문입니다. 불필요한 이런 시스템은 시스템의 성능저하를 가져다 줍니다. 모든 소유자는 dbo로 통일하는 것을 권장합니다.


따라 하기 - 소유자를 dbo로 바꿔보자

1. 소유자를 dbo로 바꿀 때는 다음의 저장 프로시저를 사용하면 됩니다.



2. 추가로 시스템테이블을 업데이트하는 방법을 통해 데이터베이스 차원에서 소유자를 바꾸는 방법도 있으며 커서를 사용하는 방법도 존재합니다.


수칙 10. 데드락이 발생하는 부분을 라이브락 형태로 변경했는가?

데드락이란 라이브락과 반대되는 개념입니다. 둘 이상의 트랜잭션이 서로가 실행해야 될 내용을 이미 잠그고 있어 마치 교차로에서 서로 엉켜 꼼짝할 수 없는 상황을 의미 합니다. 이를 해결하기 위한 SQL서버의 노력은 한쪽을 일방적으로 취소 시키는 것인데 이는 시스템의 성능저하로 나타납니다. 이를 해결하기 위한 가장 좋은 방법은 일방통행 방식으로 변경하는 것입니다. 이것이 라이브락 입니다.


따라 하기

1. 우선 준비를 위해 테이블을 만들고 데이터를 넣습니다.




2. 창을 두 개 열어서 동시에 실행합니다. CTRL+TAB으로 잽싸게 창을 바꿔서 실행해 봅니다.



3. 위의 데드락의 가장 바른 해결방법은 순차적인 라이브락 형태로 변경하는 것입니다.




수칙 11. SET NOCOUNT ON을 사용하는가?

불필요한 메시지가 네트워크 트래픽을 낭비하고 있습니다. 특히'몇 개 행이 적용 되었습니다' 같은 메시지가 그런 대표적인 예입니다.


따라 하기

1. set nocount on을 실행하고 쿼리를 실행합니다.




프로시저를 작성할 때도 set nocount on과 같은 환경설정은 먼저 실행해 두고 프로시저를 작성하면 환경이 저장된 채로 프로시저가 제작되므로 편리합니다.


수칙 12. 실무 사례: 저장 프로시저 관리 방법

저장 프로시저 관리방법





use 데이터베이스명 -- 저장 프로시저는 use 데이터베이스명 문과 같이 써두어야 어디 소속인지 명확히 파악이 가능합니다. go -- 소스 create proc dbo.저장 프로시저명 -- 소유자가 명확하게 dbo로 지정 되 있어야 성능 향상이 이뤄집니다. 컴파일 잠금 시간 대폭 감소 as begin -- 가장 바깥쪽의 begin end 및 불필요한 begin end문은 과감히 생략합니다.(소스만 길어짐) end -- 실행예제 exec 데이터베이스명.dbo.저장 프로시저명 -- 데이터베이스 이름까지 명시해야 오브젝트 참조에서 발생할 수 있는 불 명확성을 줄여줌으로 바람직합니다.

http://www.dbguide.net/tuning_guide/tuning_guide_11.jsp

한용희 | 롯데정보통신 칠성IS 사업팀

운영체제 │ 윈도우 2000, 윈도우 2003, 윈도우 XP
개발도구 │ MS SQL 서버 2005 베타 2, 비주얼 스튜디오 2005 베타 1
기초지식 │ MS SQL 서버 2000, C#
응용분야 │ MS SQL 서버 2005 관리와 개발

SQL 서버 2005의 가장 큰 변화라고 한다면 아마도 닷넷 프레임워크와의 통합일 것이다. 이제는 쿼리문을 C#을 이용해서 개발할 수 있을 뿐만 아니라 C#을 통해서 T-SQL이 하지 못하는 기능을 마음껏 확장할 수도 있다. 이번 호에서는 CLR에 통합된 SQL 서버 2005의 새로운 모습을 살펴본다.

지난 시간에서는 T-SQL의 새로운 모습에 대하여 살펴봤다. T-SQL은 언어 자체가 집합적 언어이기 때문에 여전히 데이터를 조작하고 접근하는 데 있어서는 닷넷 언어보다 더 좋은 성능을 나타낸다. 하지만 T-SQL은 절차적 언어이기 때문에 객체지향적 프로그래밍을 할 수 없다는 단점이 있다. 그러나 닷넷을 이용하면 더 이상 이 문제로 고민하지 않아도 된다. C#, VB.NET, Managed C++를 이용해서 얼마든지 객체지향적 프로그래밍이 가능하다. 또한 복잡한 로직이나 계산, 외부 자원 연동, 코드 재사용 등에 있어서는 T-SQL보다 더 좋은 접근성과 성능을 보여준다. 한 마디로 닷넷 프레임워크와의 통합은 T-SQL을 교체하는 개념이 아니라 더욱 확장하고 강화하기 위하여 도입된 것이라고 보면 된다.

SQL 서버 2005는 닷넷 프레임워크와 통합되면서 안정성이 대폭 향상되었다. 이전 SQL 서버 2000에서 확장 저장 프로시저를 C++를 이용해서 작성하는 경우 간혹 잘못된 코드로 인하여 SQL 서버 전체가 다운되는 경우가 있었다. 그래서 확장 저장 프로시저를 매우 신중하게 만들어야 했으며 만드는 과정 자체도 간단하지가 않았다.

하지만 SQL 서버 2005에서는 기본적으로 닷넷 프레임워크의 호스팅 모델을 따라간다. SQL 서버 2005와 각각의 닷넷 코드로 만들어진 확장 저장 프로시저는 서로의 독립성을 보장한다. 서로 메모리를 직접적으로 침범할 수 없으며, 서로의 실행 환경을 침해할 수도 없다. 각각 별도로 운영된다는 것이다. <그림 1>을 보면 닷넷 프레임워크의 호스팅 모델이 나와 있다. SQL 서버와 외부 어셈블리는 서로 다른 도메인을 가지고 있어 자신의 독립적인 실행 환경을 보호한다. 그래서 이제는 확장 저장 프로시저 때문에 더 이상 SQL 서버가 다운되는 일은 없다.

SQL 서버는 자기 자신만의 특별한 쓰레드 스케쥴링, 동기화, 잠금, 메모리 할당 정책을 가지고 있다. SQL 서버 자체가 워낙 메모리를 많이 사용하고 성능이 중요한 기업용 애플리케이션이기 때문에 보통의 CLR(Common Language Runtime)에서 제공하는 정책을 따르지 않고 자기 자신만의 특별한 방식을 적용해서 운영을 한다. 만약 외부 어셈블리가 CPU나 메모리를 과도하게 많이 써서 SQL 서버를 운영하는데 지장을 준다면, SQL 서버는 이를 즉시 탐지해내고 해당 사용권을 외부 어셈블리로부터 뺏어온다. 이렇게 함으로써 SQL 서버는 더 이상 외부의 간섭에 영향을 받지 않고 자기 자신을 스스로 안정적으로 운영할 수 있는 능력을 가지게 되었다.








간단한 사용자 정의 함수 만들기

먼저 간단한 사용자 정의 함수를 C#으로 만들어 볼 것이다. 복잡한 표현식이나 계산을 요하는 작업의 경우 C#으로 만드는 것이 더 효율적이므로 이번 예제에서는 우편번호를 체크하는 간단한 정규식 표현 함수를 만들어 보자. 먼저 VS.NET을 시작하고 새로운 프로젝트로 SQL 서버 프로젝트를 선택한다. CLREx이라는 새로운 프로젝트를 만들고 AdventureWorks DB 서버에 연결한 후 새로운 아이템으로 IsValidZipCode라는 사용자 정의 함수를 추가한다.
그러면 <화면 2>와 같은 템플릿 코드가 들어 있다. 여기에서 주의해서 봐야 할 것은 함수 위에 있는 속성 [SqlFunction]이다. 이 속성은 다음의 함수가 SQL에서 사용하는 사용자 정의 함수임을 컴파일러에게 알려주는 지시자이다. 이제 기본 코드는 지우고 다음과 같이 코딩을 하자.

using System; using System.Data.Sql; using System.Data.SqlTypes; public partial class UserDefinedFunctions { [SqlFunction] public static bool IsValidZipCode(SqlString ZipCode) { return System.Text.RegularExpressions.Regex.IsMatch(ZipCode.ToString(), @”^\d{3}-\d{3}”); } };

간단하게 해당 문자열이 우편번호식인지 검사하여 결과를 리턴해 주고 있다. 이제 이 코드를 컴파일하여 배포까지 하자. 그러면 자동으로 SQL 서버에 이 어셈블리가 등록된다. 배포를 성공적으로 끝내면 다음과 같이 테스트해 보자.

select dbo.IsValidZipCode(‘333-333’); select dbo.IsValidZipCode(‘333-A33’);

-----
1

(1 row(s) affected)
-----
0
(1 row(s) affected)
잘 작동하는 것을 볼 수 있을 것이다. 사용자 정의 함수를 만들어서 사용해 보았는데 함수를 만들고 배포하는 것이 간단하다는 것을 느꼈을 것이다. 그럼 SQL 서버 내부에는 어떻게 등록되어 있는 것일까?

SELECT * FROM sys.assemblies;

sys.assemblies라는 뷰를 보면 해당 CLREx이라는 어셈블리가 등록되어 있는 것을 확인할 수 있을 것이다.

SELECT * FROM sys.assembly_files;

sys.assembly_files에는 실제 어셈블리의 내용이 들어 있다. 즉, DLL 바이너리 자체를 SQL 서버안에 등록한 것이다. 그러므로 한번 어셈블리를 SQL 서버 안에 배포하면 해당 DLL 파일은 없어도 무방하다. 앞에서는 배포를 VS.NET을 이용해서 자동으로 배포하였지만, 수동으로 배포하는 방법도 있다.

CREATE ASSEMBLY UDF1
FROM ‘\\localhost\Projects\CLREx\CLREx\bin\Debug\CLREx.dll’;

CREATE FUNCTION IsValidZipCode(@ZipCode nvarchar(10))
RETURNS bit
EXTERNAL NAME
CLREx.UserDefinedFunctions.IsValidZipCode;

이와 같이 먼저 어셈블리를 등록하고 해당 함수를 만들어 주면 수동으로도 등록할 수 있다.

저장 프로시저를 C#으로 만든다?

이번에는 저장 프로시저를 만들어 보자. 저장 프로시저를 만들려면 먼저 SQL 문장을 실행해서 결과를 리턴해야 한다. 그러기 위해서는 어셈블리가 DB에 접속을 해서 SQL 문장을 보내줘야 한다. 일반적으로 ADO.NET을 이용해서 DB에 접속을 하지만 기존의 연결 방법을 사용할 경우에는 외부에서 접속해 들어오는 것이므로 성능 상에 문제가 있다. 따라서 내부 접속을 위한 별도의 데이터 프로바이더(Data Provider)가 필요한데 그것이 바로 SQL Server Managed Provider이다. 이 프로바이더는 SQL 서버 내에서 실행되므로 별도의 접속을 맺을 필요 없이 빠르게 수행을 한다. 따라서 open, close와 같은 절차가 필요 없는 데이터 프로바이더이다. 사용 방법은 다음과 같이 선언하면 된다.

using System.Data.SqlServer;

SQL Server Managed Provider에는 효과적인 작업을 위하여 Sql Command, SqlPipe, SqlResultSet, SqlTransaction, SqlTrigger Context와 같은 몇 가지 타입을 제공한다. 이중 대부분은 SqlClient에 있는 것과 동일하고 SqlPipe와 SqlTiggerContext가 이번에 새로 등장한 타입이다. SqlTiggerContext는 트리거 작성을 위한 타입이고, SqlPipe는 테이블과 같은 데이터를 호출하는 쪽에 보내줄 때 사용하는 타입이다. 그러면 SqlResultSet과 뭐가 다르냐고 할 수도 있다. SqlResultSet은 성능 문제로 인하여 사용을 권하지 않는 타입이고(이제는 없어질지도 모른다) SqlPipe가 성능상 더 좋은 타입이다. SqlPipe는 말 그대로 호출자에게 파이프로 물을 보내듯이 데이터를 받는 즉시 바로 보낸다. 성능면에서도 T-SQL의 저장 프로시저와 거의 비슷한 성능을 보여준다. 그러므로 앞으로 테이블 데이터를 리턴받는 경우에는 SqlPipe를 써야 한다. 또한 닷넷 저장 프로시저는 리턴 값으로 int형과 void형만을 리턴할 수 있으므로 어차피 SqlResultSet 형식으로 리턴하지도 못한다.

이번에는 직접 저장 프로시저를 만들어 보자. 이전에 만든 프로젝트에 저장 프로시저를 하나 추가하고 다음과 같이 코딩을 한다.

using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; public partial class StoredProcedures { [SqlProcedure] public static void SelectEmp(SqlInt16 val) { SqlCommand sqlCmd = SqlContext.GetCommand(); sqlCmd.CommandText = “SELECT * FROM HumanResources.Employee “ + “WHERE DepartmentID = @pDeptID”; // 파라미터 값 대입 sqlCmd.Parameters.AddWithValue(“@pDeptID”, (Object)val); // SqlPipe를 이용하여 결과 리턴 SqlContext.GetPipe().Execute(sqlCmd); } };

이번 예제는 사용자 테이블에서 특정 부서의 사람들을 추출하는 저장 프로시저이다. 이를 컴파일하고 배포한 후 다음과 같은 SQL 문장으로 테스트해 보면 결과를 볼 수 있을 것이다.

EXEC dbo.SelectEmp 4;

나만의 데이터 타입을 만들자

SQL 서버에는 기본적으로 char, int와 같은 기본 데이터 타입을 지원한다. 여기에 더 확장하여 우리가 원하는 데이터 타입을 스스로 만들어서 추가할 수도 있다. 예를 들면 위도, 경도, 포인트를 나타내는 데이터 타입이라든지 이메일 주소를 나타내는 데이터 타입을 새로 만들어서 추가할 수 있다. 포인트를 보면 10:30과 같은 표현식을 수용하는 하나의 컬럼을 만들 수도 있다. 그런데 사실 이러한 표현은 기존의 컬럼을 두 개로 나누어서 x, y좌표 값을 저장해도 된다. 굳이 사용자 정의 데이터 타입(User-Defined Data Types, UDT)을 안 만들어도 할 수는 있다. 하지만 의미상 하나로 표현하는 것이 더 타당하고, 그 자료형과 관련된 많은 메쏘드나 행위가 필요할 때에는 하나의 데이터형으로 만드는 것이 바람직하다.

예를 들면 날짜 같은 데이터 타입을 년, 월, 일로 나누어서 3개의 컬럼에 저장하는 것보다는 년월일 하나로 만들어서 하나의 컬럼에 저장하는 것이 더 의미상 더 타당하다는 것은 누구나 알고 있다. 또한 날짜와 관련된 많은 메쏘드와 제약사항들이 있기 때문에 이를 3개의 컬럼으로 나누어서 처리하는 것은 많은 불필요한 코드들을 필요로 한다. 예를 들면 월에 1월을 더하거나 빼는 연산과 같은 것들을 하나의 데이터 타입에 같이 넣어 두면 어디서나 손쉽게 끌어다 쓸 수 있다. SQL 서버의 UDT는 데이터 자체뿐만 아니라 메쏘드도 같이 포함할 수 있으므로(사실 UDT는 클래스나 구조체로 정의한다) 이러한 구현이 가능하다.

그럼 여기서 이런 생각까지 하는 독자가 있을 수도 있다. “UDT를 클래스의 개념으로 볼 수 있으니 이제는 객체를 그대로 DB에 저장할 수 있다는 얘기군. 그럼 아예 사원(Employee) 객체를 통째로 DB에 저장해 볼까?” 여기까지 생각을 하면 “그동안 미들티어에서 했던 OR 맵핑(Object Relational Mapping)이 더 이상 필요 없는 진정한 객체지향의 DB가 탄생했군!”이라고 생각할 수도 있다. 틀린 얘기는 아니다. 하지만 성능과 용량이 문제가 된다. UDT는 8KB라는 크기 제한이 있고, 인덱싱 처리의 제약, 그리고 데이터 업데이트 시 부하가 있다. 그러므로 UDT는 그러한 복잡한 객체를 저장하는 데에는 적절하지 않다. 처음에 예를 들었던 위도, 경도, 포인트와 같이 가벼운 객체를 저장할 때에만 이 UDT를 사용해야 한다.

UDT는 결국 클래스를 하드디스크에 저장하는 것이기 때문에 직렬화를 해야 한다. 직렬화를 위해서는 데이터의 크기가 중요하다. 기본적으로 닷넷 환경에서는 값 타입(Value Type)과 참조 타입(Re ference Type)이라는 두 가지 타입이 있다. 값 타입은 int, char과 같이 실제 데이터가 직접 있는 타입이고, 참조 타입은 string과 같이 실제 데이터가 아닌 데이터의 주소가 들어 있는 타입을 말한다. 따라서 이들 데이터 타입에 따라 저장하는 방법도 달라진다. 값 타입은 대부분 고정된 길이를 가지고 있으므로 컴파일러가 알아서 그 크기를 계산할 수 있지만, 참조 타입의 경우 그 크기가 얼마나 될지 모른다. 그래서 하드디스크에 얼마 정도의 공간을 할당해야 하는지 모르는 것이다. 그래서 참조 타입을 직렬화하는 경우에는 사용자가 직접 그 방법을 정의해줘야 한다. 직렬화 방법을 정리해 보면 다음과 같이 3가지 방법이 있다.

◆ SerializedDataWithMetadata
값 타입이나 참조 타입에 관계없이 어떤 데이터 타입도 저장 가능. 하지만 성능 면에서는 가장 느리다. 아마 베타 3에서는 없어질 포맷이다. 한 마디로 사용하면 안 되는 포맷이다.

◆ Native
크기가 고정된 값 타입의 데이터 형만 저장 가능. 가장 빠르다.

◆ UserDefined
값 타입, 참조 타입 모두 사용가능. 하지만 사용자가 데이터를 읽는 방법과 쓰는 방법을 정의해줘야 한다.

앞의 세 가지 포맷 중 사용자 정의 포맷에서 읽기와 쓰기를 직접 구현하는 것은 간단하지가 않다. 약간 복잡하다. 이번 예제는 UDT를 소개하는 것이 목적이므로 Native 포맷을 이용하는 간단한 포인트 예제를 보여주려고 한다. 기존 CLREx 프로젝트에 새로운 아이템으로 Point라는 사용자 정의 데이터 타입을 추가해 보자. 그러면 기본적인 코드들이 생성되어 있을 것이다. 모두 지우자. 현재 템플릿에서 생성된 코드는 옛날 방식의 코드이다. 기본 구조는 다음과 같다.

[Serializable] [SqlUserDefinedType(Format.Native)] public struct Point : INullable { private Boolean is_null; private Int32 m_x; private Int32 m_y; // 기본 메쏘드 public override string ToString() { ... } public bool IsNull { get; } public static Point Null { get; } public static Point Parse(SqlString s) {...} // 추가한 메쏘드 public Int32 x {...} public Int32 x {...} public decimal DistanceTo(Point other) {...} // 두 포인트 간 거리구하기 }

이 메쏘드들을 채워주면 포인트 UDT가 완성된다. 앞의 가상코드를 보면 직렬화를 지원하고 Native 포맷으로 정의되어 있는 것을 볼 수 있을 것이다. 그리고 class가 아닌 struct로 선언한 것이 보일 것이다. 굳이 class가 아닌 struct를 쓴 이유는 전통적으로 사용자 정의 데이터 타입은 구조체를 썼기 때문이다. 그 이유는 클래스는 힙에 데이터가 저장되지만 구조체는 그렇지가 않다. 따라서 클래스는 가비지 콜렉터가 쉽게 수거해 갈 수 있지만 구조체는 그렇지 않다. 성능 면에서 구조체가 약간 더 빠르다는 것이다. 또한 NULL 값을 구현하는데 있어 구조체는 별도의 초기화 없이 기본적으로 모든 값을 기본 값으로 초기화해 준다.

예를 들면 숫자형은 모두 0으로 자동 초기화를 해준다. 그래서 데이터 형을 다루는 데에는 아무래도 클래스보다는 구조체가 약간 더 편하다고 할 수 있다. SQL 서버에서는 NULL이라는 값이 존재한다. 따라서 UDT를 만들 때에는 NULL이라는 의미를 부여해줘야 한다. 그래서 INullable 인터페이스를 상속받아서 NULL을 구현하고 있다.

포인트를 저장하기 위해서 x, y값을 위한 공간을 마련하고 널 값 체크를 위한 공간도 마련하였다. 그런데 사실 널 값 체크를 위해서 이와 같이 별도의 저장공간을 사용하는 것은 하드디스크 낭비가 될 수 있다. 그래서 어떤 사람들은 이와 같은 경우 Int32.MinValue를 널 값 대신으로 사용하기도 한다. 즉 Int32의 최소 값을 널 값으로 대신하는 것이다.

만약 포인트의 데이터형이 string형이면 이러한 불편이 없다. string형은 참조 타입이기 때문에 null이라는 값을 수용할 수 있기 때문이다. Int32라는 데이터형은 값 타입이기 때문에 NULL을 수용할 수가 없어 이와 같은 방법을 사용하였다. 어떤 방법을 사용하든 그것은 개발자의 몫이니 상황에 따라 적절한 방법을 사용하면 된다. 이번 예제에서는 하드디스크의 공간을 걱정 안 해도 되므로 그냥 따로 널 값 체크를 위한 데이터형을 따로 만들었다. 기본적인 메쏘드의 설명은 <표 1>과 같다.

실제 완성된 코드는 ‘이달의 디스켓’으로 제공하니 참고하기 바란다. 이제 이 UDT를 컴파일하고 배포하면 다음과 같이 테스트할 수 있다.

DECLARE @a Point, @b Point; IF @a is null PRINT ‘null’ ELSE PRINT ‘not null’; SET @a.x = 10; SET @a.y = 20; SET @b.x = 100; SET @b.y = 110; SELECT CAST(@a AS CHAR); SELECT CAST(@b AS CHAR); SELECT @a.DistanceTo( @b ); -- 두 점 사이의 거리구하기
-----------------------------------------------------------------
null
10:20
100:110
127
SUM, MAX와 같은 집합 함수만으로는 더 이상 충분하지 않다

이번에 SQL 서버의 CLR 통합 기능 중에서 제일 반가운 것이 바로 이 기능이다. 기존에 MIN, MAX, SUM, COUNT, AVG 같은 집합 함수를 쓰다 보면 부족함을 느끼는 경우가 많다. 이러한 집합 함수가 있으면 좋을 것이라고 많은 사람들이 원했던 것이 사실이다. 이제는 이러한 집합 함수를 직접 만들어 쓸 수 있다. 만드는 방법은 UDT와 상당히 유사하다. 이번 예제에서는 최대 변이 값을 구하는 함수를 만들 것이다. 즉, 최대 값-최소 값을 구하는 MaxVariance라는 함수이다. 기존 프로젝트에 새로운 아이템으로 Aggregate를 추가하고 이미 있는 템플릿 코드는 역시 옛날 방식이므로 지운다. 기본 구조는 다음과 같다.

[Serializable] [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedAggregate(Format.Native)] public struct MaxVariance { private Int32 m_LowValue; private Int32 m_HighValue; public void Init() {...} public void Accumulate(SqlInt32 Value) {...} public void Merge(MaxVariance Group){...} public SqlInt32 Terminate() { ... } }

데이터 형이 값 타입 밖에 없으므로 Native 포맷으로 했으며, 최대 값과 최소 값을 저장하는 별도의 변수를 만들었다. 각 메쏘드별 설명은 <표 2>와 같다.

자세한 코드는 ‘이달의 디스켓’에 있으니 참고하기 바란다. 앞의 사용자 정의 집합(User-Defined Aggregate, UDA)을 컴파일하고 배포한 후 다음과 같은 코드로 테스트해 보자. 다음 코드는 전체 사원 중에서 휴가 시간이 가장 많은 사람과 가장 적은 사람의 차이를 나타낸 것이다.

SELECT dbo.MaxVariance(VacationHours) FROM HumanResources.Employee; SELECT MAX(VacationHours) - MIN(VacationHours) FROM HumanResources.Employee;
-----------
99
(1 row(s) affected)
99
(1 row(s) affected)
앞뒤의 쿼리문을 대조해 보면 제대로 된 결과가 나왔음을 확인해 볼 수 있다.

클라이언트 ADO.NET의 개선점

이번에 ADO.NET 2.0으로 나오면서 SQL 서버와 관련해서 크게 주목할 부분은 두 가지가 있다. 하나는 비동기 호출기능과 하나의 연결로 다수의 커맨드를 실행하는 기능(Multiple Active Result Sets, MARS)이다. 지난 호에서 ADO.NET에서도 페이징 처리가 가능하다고 했는데, 그 기능이 이젠 없어질 예정이라서 이번에 제외했다.

더 이상 기다릴 필요 없는 비동기 호출

비동기 호출 기능은 기존에 쿼리 문장을 수행시키고 결과가 올 때까지 기다려야 했단 불편을 없애고 클라이언트는 결과가 올 때까지 나름대로의 작업을 할 수 있다. 그러므로 사용자는 쿼리 문장을 날리고 모래시계의 아이콘을 기다릴 필요 없이 다른 작업을 수행할 수도 있다. 이때 처음 DB에 연결을 맺을 때 비동기 호출을 쓴다는 표시를 “Asynchronous Processing=true”와 같이 해줘야 한다. 간단한 예제를 보자.

SqlConnection cnn = new SqlConnection( “Data Source=localhost;” + “Initial Catalog=AdventureWorks;” + “Integrated Security=SSPI;” + “Asynchronous Processing=true”); cnn.Open(); // 2초 간의 딜레이 후 조회 SqlCommand cmd = new SqlCommand( “WAITFOR DELAY ‘00:00:02’;SELECT * FROM Sales.Customer”, cnn); Console.WriteLine(“작업 시작”); IAsyncResult iar = cmd.BeginExecuteReader(); while (!iar.IsCompleted) { Console.Write(“*”); } // 결과 올 때까지 별 찍기 cmd.EndExecuteReader(iar); Console.WriteLine(“\n작업 끝”);


-----------------------------------------------------------------------------
작업 시작
******************
작업 끝
앞의 예제는 고객 데이터를 조회하는데 있어 비동기 호출을 이용하고 있다. 먼저 비동기 호출의 장점을 보려면 DB에서 시간이 오래 걸리는 작업을 돌려봐야 그 효과를 확실히 볼 수 있다. 그래서 2초간 딜레이를 주는 문장을 삽입하여 강제로 시간이 오래 걸리도록 하였다. 그리고 클라이언트는 결과가 올 때까지 계속 별을 찍다가 결과가 오면 끝내는 예제이다. 그런데 이번 예제에서는 간단히 하기 위해서 끝났는지 안 끝났는지를 알아보기 위하여 WHILE문에서 계속 체크를 하였지만, 실제 사용할 때에는 이렇게 할 필요 없이 비동기 콜백 함수를 만들어서 다 끝나면 저절로 그 함수가 호출되게 하는 것이 더 좋은 방법이 될 것이다.

하나의 연결로 다수의 쿼리 실행

기존 ADO.NET에서는 하나의 연결을 맺으면 하나의 커맨드만 실행 가능하였다. 그래서 다른 커맨드를 실행하려면 별도의 연결을 다시 맺어야만 했다. 하지만 이제는 하나의 연결로 다수의 커맨드를 실행할 수 있다. 이렇게 함으로써 매번 새로운 연결을 맺지 않아도 되므로 성능 향상이 있는 것이다. 구현하는 방법은 어렵지 않다. 그냥 쓰면 된다. 다음 예제를 보자.

// 하나의 연결 SqlConnection cnn = new SqlConnection( “Data Source=localhost;” + “Initial Catalog=AdventureWorks;” + “Integrated Security=SSPI;”); cnn.Open(); // 첫 번째 실행 SqlCommand cmd1 = new SqlCommand( “SELECT * FROM Production.Location”, cnn); SqlDataReader dr1 = cmd1.ExecuteReader(); // 두 번째 실행 SqlCommand cmd2 = new SqlCommand( “SELECT * FROM HumanResources.Department”, cnn); SqlDataReader dr2 = cmd2.ExecuteReader(); // 결과 출력 while (dr1.Read() == true && dr2.Read() == true) { Console.WriteLine(dr1[0] + “ | “ + dr2[0] ); }

-----------------------------------------------------------------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
.......
cmd1과 cmd2가 하나의 cnn이라는 연결을 공유해서 쓰고 있다. 전체 예제는 ‘이달의 디스켓’에 있다.

SQL 서버의 변신은 무죄?

처음에 SQL 서버가 닷넷 프레임워크(CLR)에 통합된다고 하였을 때 많은 사람들이 궁금증을 가지고 지켜보았다. 이제는 C#을 공부해야 하는가 하고 걱정하는 사람들도 있었다. 하지만 막상 뚜껑을 열어보니 CLR 통합이라는 기능은 T-SQL을 대체하는 기능이 아닌 좀 더 확장하고 보강하기 위한 기능으로 보는 것이 좋다는 결과가 나왔다. SQL 서버를 개발하는 데 있어 기본은 T-SQL이다. 하지만 거기서 멈추지 않고 더욱 새로운 기능을 추가하고 확장하고 싶다면 닷넷을 이용하면 된다. 다음 시간에서는 DB 관리 툴과 보안에 대해 소개하겠다.


제공 : DB포탈사이트 DBguide.net

출처 : 마이크로소프트웨어 [2005년 3월]

Gregory A. Larsen

만일 당신의 회사가 잦은 직원의 교체로 애를 먹고 있다면, DBA 또한 자주 새로운 계정 생성으로 고생을 할 것이다. 이런 경우 기존의 SQL 서버 로그인과 동일한 권한의 새로운 로그인을 생성하기 위해 당신이 하는 작업들을 무엇인가? 아마도 매번 엔터프라이즈 관리자를 열어서 직접 기존 로그인의 권한을 확인하고 새로운 SQL 서버 로그인을 생성한 후에 적절한 권한을 할당할 것이다. 이렇게 권한 확인과 계정 생성의 과정을 매번 직접 반복하는 것은 지루하고 자칫 과오를 범하기도 쉬워진다. 이러한 관리 업무를 자동화하여 간편하게 할 수 있는 방법을 고려해 본적이 있는가?

필자는 여러 번 이러한 권한 분석 및 로그인 생성 업무 때문에 애를 먹었었다. 반복되는 일련의 작업으로 고생하던 중, 이 과정의 자동화를 결심하게 되었다. 자동화로 바꾼 작업은 권한 분석 업무의 번거로움을 없애주었고, 새로운 로그인을 생성하는 시간을 단축시켜주었으며 수작업으로 인해 발생할 수 있는 문제를 줄여 주었다. 덕분에 나는 새로운 로그인에 실수로 잘못된 권한을 부여하지 않을 수 있게 되었다. 게다가, 이 과정의 자동화 이후로 필자의 고객(Washington State Department of Health?DOH의 내 동업자)은 더 이상 새로운 로그인에 부여할 특정 권한에 대해서 자세하게 알 필요가 없게 되었다. 대신, 기존의 계정이 갖고 있던 권한에 대해서만 알고 있으면 된다. 결국 이것은 관리자와 고객 모두를 편하게 해 준 셈이 된 것이다.

이 작업은 [웹 리스트 1]의 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN라는 저장 프로시저로 구성하였다(완전한 웹 리스트는 http://www.adminmag.com 의 자료실에서 다운로드 받을 수 있다). 이 저장 프로시저는 기존의 로그인과 연관된 SQL 서버 권한을 검색하고 이와 동일한 권한을 갖는 새로운 로그인을 생성할 T-SQL 명령을 생성한다. 이 저장 프로시저는 SQL 서버 2000과 7.0에서 동작하며 SQL 서버와 Windows NT 인증 모두와 사용할 수 있다.

이 저장 프로시저는 sp_helpuser, sp_helpsrvrolemember, 그리고 sp_helprotect 시스템 저장 프로시저를 사용하여 SQL 서버 시스템 테이블에서 로그인 권한 정보를 수집한다. 이 저장 프로시저는 sp_helpuser를 사용하여 기존의 로그인이 액세스할 수 있는 데이터베이스와 데이터베이스 역할들을 확인하고, sp_helpsrvrolemember를 이용하여 기존의 로그인이 속해있던 서버 역할들을 알아내고, sp_helprotect를 사용하여 기존 로그인의 개체-수준의 권한들을 알아낸다.

쿼리 분석기에서 이 저장 프로시저를 실행하면 적절한 권한의 새로운 로그인을 추가하기 위한 T-SQL 명령을 볼 수 있다. 이 코드가 어떻게 시스템 저장 프로시저를 사용하여 새로운 로그인에 서버와 데이터베이스 권한을 부여하는지 알아보도록 하자.

저장 프로시저 세부사항

USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN에는 기존 로그인과 새로운 로그인을 위한 몇 가지 매개변수가 필요하다. 이 저장 프로시저에는 4가지 매개변수로 @OLDLOGIN, @NEWLOGIN, @NEWUSER, 그리고 @PASSWORD가 있다. 앞의 세가지 매개변수는 필수적이지만, @PASSWORD는 추가하려는 로그인의 타입에 따라 선택적이다. @OLDLOGIN 매개변수는 복사하려는 기존의 로그인을 나타내고 @NEWLOGIN은 생성할 로그인을 나타낸다. @NEWUSER 매개변수는 새로운 로그인에 할당할 데이터베이스 사용자를 나타낸다. 보통 이 사용자 계정은 새로운 로그인 계정과 동일한 이름을 가질 것이다. 새로운 로그인의 비밀번호에는 @PASSWORD 매개변수를 사용하는데 이는 SQL 서버 로그인 계정을 생성할 경우에만 필요하다.

[웹 리스트1]의 A 부분에서, 이 저장 프로시저는 필요한 로컬 변수들을 선언하고 임시 테이블 #TMP_LOGIN_RIGHTS를 생성하여 새로운 로그인을 생성할 모든 T-SQL 명령을 저장할 수 있도록 한다. 코드의 다음 부분에서 이 테이블을 사용한다. B부분의 코드는 입력 매개변수를 검사하고 서버에 새로운 로그인을 생성하는 명령을 구성한다. 첫 번째 매개변수 검사는 서버에 @OLDLOGIN 매개변수의 기존 계정이 존재하는지 확인한다. 만일 존재하지 않는다면 에러가 발생하고 프로시저는 종료한다. B부분의 @NEWLOGIN 매개변수 검사는 새로운 계정이 서버에 있는지 확인한다. 만일 이 계정이 이미 존재한다면 에러가 발생하고 저장 프로시저는 종료한다. 이 부분에서는 또한 @NEWLOGIN 매개변수의 백슬러시(\)의 유무를 통해서 새로운 로그인 계정이 도메인 계정인지, SQL 인증 로그인 계정인지도 알아낸다. 만일 @NEWLOGIN에 백슬러시가 포함됐다면 저장 프로시저는 도메인 계정을 추가하는 것이라 여기고 적절한 sp_grantlogin 구문을 생성하여 #TMP_LOGIN_RIGHTS 테이블에 추가한다. 만일 새로운 로그인 계정이 SQL 인증 계정이라면 B부분의 코드는 @PASSWORD 값이 매개변수로서 저장 프로시저에서 처리되었는지 확인한다. 만일 비밀번호 없이 저장 프로시저를 호출하면 저장 프로시저는 에러를 발생하고 종료한다. 저장 프로시저가 비밀번호 테스트를 성공적으로 수행하고 나면, 서버에 @NEWLOGIN SQL 인증 계정을 추가하는 sp_addlogin 구문을 #TMP_LOGIN_RIGHTS 테이블에 삽입한다.

다음으로 C부분에서는 @NEWLOGIN에게 @OLDLOGIN과 동일한 데이터베이스 액세스 권한을 허가한다. 이 작업을 위해 모든 데이터베이스의 목록을 포함하는 ALLDATABASES라는 커서를 선언한다. 또한 기존의 데이터베이스 사용자에 대한 정보를 저장할 #TMPUSERS라는 임시 테이블을 생성한다. 커서가 각각의 데이터베이스를 반환할 때마다 sp_helpuser 시스템 저장 프로시저는 현재 데이터베이스에 선언된 각 사용자 계정에 대한 정보를 #TMPUSERS에 저장한다. 만일 이전의 로그인이 현재 데이터베이스에 대해 액세스 권한이 있다면 새로운 사용자가 이미 데이터베이스의 사용자인지 아닌지를 확인한다. 만일 새로운 사용자가 이미 현재 데이터베이스에 선언되었다면 에러가 발생하고 저장 프로시저는 취소된다. 그런 경우, 다른 사용자 이름을 새로운 로그인 계정에 할당할 수 있다. 만일 새로운 사용자가 현재 데이터베이스에 존재하지 않는다면, 데이터베이스에 새로운 로그인이 액세스 가능하도록 sp_grantdbaccess 명령으로 #TMP_LOGIN_RIGHTS을 구성한다. 이 과정은 새로운 로그인에게 필요한 모든 데이터베이스에 액세스가 가능할 때까지 계속된다.

기존의 로그인은 여러 데이터베이스 역할의 구성원일 수도 또는 그렇지 않을 수도 있다. D부분의 코드는 C부분에서 선언한 커서 (ALLDATABASES)와 임시 테이블 (#TMPUSERS)를 사용하여 각 데이터베이스를 차례대로 처리한다. 각각의 데이터베이스마다, 저장 프로시저는 이전 로그인이 데이터베이스에 액세스 할 수 있었는지, 그리고 어떤 데이터베이스 역할의 멤버였는지를 확인한다. 만일 이전 로그인이 어떤 역할의 맴버였다면 저장 프로시저는 sp_addrolemember 명령을 이용하여 #TMP_LOGIN_RIGHTS 테이블을 구성한다. sp_addrolemember 명령은 이전 로그인이 속했던 각 데이터베이스 역할마다 새로운 로그인을 멤버로 구성한다.

데이터베이스 역할 외에도, 이전 로그인은 특정 서버 역할의 구성원이었을 수도 있다. E부분의 코드는 새로운 로그인이 어떤 서버 역할에 멤버로 구성되야 할지를 결정한다. 이 작업을 수행하기 위해서 E부분의 코드는 임시 테이블 #TMPSRVROLES를 생성하고, sp_helpsrvrolemember 시스템 저장 프로시저를 사용하여 각 서버 역할에 대한 멤버 목록을 저장한다. 만일 이전 로그인이 하나 또는 그 이상의 서버 역할의 멤버라면 코드에서는 sp_addsrvrolemember 시스템 저장 프로시저를 사용하여 새로운 로그인을 이전 로그인이 속했던 각 서버 역할의 멤버로 구성한다. 이렇게 생성된 명령들을 #TMP_LOGIN_RIGHTS 임시 테이블에 저장한다.

F부분의 코드는 이전 로그인이 가지고 있던 개체, 컬럼, 또는 구문 수준의 권한을 확인하고 이와 동일한 권한을 새로운 로그인에게 허용하는 명령을 생성한다. F 부분의 코드는 먼저, #TMPPROTECT라는 임시 테이블을 생성하고 주어진 데이터베이스 별로 로그인의 개체, 컬럼, 그리고 구문 수준의 권한을 저장한다. 코드는 한번에 한 데이터베이스씩 모든 데이터베이스를 처리하여 이전 로그인이 각 데이터베이스에 액세스할 수 있는지 확인하고, 액세스가 가능하다면, @OLDUSER 변수에 @OLDLOGIN과 관련있는 데이터베이스 사용자의 이름을 설정한다. 만일 코드에서 @OLDUSER를 설정하면, 코드는 sp_helprotect 시스템 저장 프로시저를 사용하여 현재 데이터베이스에 대한 모든 개체, 컬럼, 그리고 구문 수준의 권한을 얻어낸다. 코드는 그 권한들을 #TMPPROTECT 임시 테이블에 저장한다. 만일 @OLDLOGIN과 연관된 데이터베이스 사용자 @OLDUSER가 개체, 컬럼, 또는 구문 수준의 권한을 가진다면, 코드는 @NEWUSER에게 동일한 개체, 컬럼, 그리고 구문 수준의 GRANT 권한을 생성할 것이다. 또한 새로운 로그인과 연관된 데이터베이스 사용자를 생성할 것이다. F 부분의 코드는 @OLDUSER 계정의 개체, 컬럼, 또는 구문 수준의 권한에 따라 다른 타입의 GRANT 구문을 생성한다. GRANT 구문은 @OLDUSER가 특정 개체에 대해 WITH GRANT OPTION을 가지고 있는지에 따라 계정을 다룬다. 만일 그렇다면, 코드는 생성된 GRANT 명령에 WITH GRANT OPTION을 포함한다. 데이터베이스 개체 상의 WITH GRANT OPTION은 연관된 데이터베이스 사용자가 다른 데이터베이스 사용자에게 해당 개체에 대한 권한을 허용할 수 있게 해준다.

G 부분의 코드는 쿼리 분석기의 결과 창에서 이전의 여섯 개의 섹션에서 생성한 각 명령을 보여준다. 코드는 #TMP_LOGIN_RIGHTS 테이블의 각 레코드를 담고 있는 커서를 생성한다. 그런 후에 한번에 한 레코드씩 이 커서를 처리한다. 코드는 PRINT 구문을 사용하여 각 레코드를 출력한다. 그러고 나면, 출력된 이 T-SQL 구문을 사용하여 새로운 로그인을 선언하고 이전 로그인과 동일한 서버, 데이터베이스 권한을 허가할 수 있다.

저장 프로시저의 호출과 사용

여기까지, USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN 저장 프로시저가 어떻게 작동하는지 이해하였다. 이제는 이 저장 프로시저를 실행하고 T-SQL 구문을 사용하여 새로운 로그인을 생성하는 것을 보도록 하자. 이 예제에서는 NEWUSER라는 이름의 SQL 인증 로그인을 추가하기로 한다. 새로운 로그인은 다음의 보안 정책을 갖는 EXISTINGUSER라는 기존의 SQL 서버 로그인을 기준으로 생성할 것이다:* 로그인은 SQL 서버 인증을 사용한다.

* 로그인은 Pubs와 Northwind 데이터베이스에 액세스할 수 있으며, 기본 데이터베이스는 Northwind이다.
* 로그인은 Pubs 데이터베이스에서 db_owner 역할의 멤버이다.
* 로그인은 Northwind 데이터베이스에서 dbo.categories 개체와 dbo.order_details개체에 SELECT 권한을 가지고, dbo.customers 개체에 DELETE 권한을 가지며, dbo.[Sales by Year] 개체에 EXECUTE 권한을, 그리고 CREATE VIEW 구문 권한을 갖는 db_accessadmin 역할의 멤버이다.

위의 정보를 토대로, 쿼리 분석기에서 다음 구문을 사용하여 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN을 호출한다.

EXEC DBO.USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN @OLDLOGIN='EXISTINGUSER', @NEWLOGIN='NEWUSER', @NEWUSER='Joe the NewUser', @PASSWORD='#NEWUSER964'

@OLDLOGIN 매개변수는 기존의 로그인을 새로운 로그인의 보안 정책에 대한 모델로 취급한다. @OLDLOGIN 매개변수가 백슬러시를 포함하지 않으므로, 이전 로그인은 SQL 서버 인증 로그인임을 알 수 있다. 만일 새로운 로그인을 NT 사용자의 기존 로그인의 권한에 바탕을 두길 원한다면, @OLDLOGIN 매개변수를 <도메인 이름>\<이전 로그인 이름> 구문을 사용하여 지정한다.
다음 매개변수인 @NEWLOGIN에는 생성하려 하는 새로운 로그인의 이름을 지정한다. @NEWUSER에는 어떤 도메인도 지정되지 않았으므로 이 로그인은 SQL 인증 로그인으로 구성될 것이다. @NEWUSER 매개변수는 새로운 로그인이 액세스할 각 데이터베이스에서 선언될 사용자 이름을 지정한다. 이 경우에는 데이터베이스 사용자 이름이 Joe the NewUser이다. SQL 서버 인증 사용자를 선언했으므로, 로그인에 사용할 비밀번호를 제공할 필요가 있다. 모든 SQL 서버 인증 로그인에는 비밀번호를 구성하는 것이 바람직하기에 USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN 저장 프로시저는 비밀번호 없이는 새로운 SQL 서버 인증 로그인을 생성하는 것을 허용하지 않는다. 저장 프로시저가 실행되고 나면, [그림 1]이 보여주는 T-SQL 결과를 볼 수 있다. 쿼리 분석기는 설정사항에 따라서 이 코드를 결과 창이나 메시지 창, 또는 파일로 보여준다.

새로운 로그인을 생성하기 위해, 쿼리 분석기의 결과 창에서 생성된 EXECUTE와 GRANT 구문을 선택하고 복사해서 빈 쿼리 분석기 창에 복사한다. 쿼리 분석기에 T-SQL을 복사한 다음, 이 코드를 실행하면 새로운 로그인을 추가할 수 있다. 이 T-SQL을 실행하기 전에 잘라내기, 붙여넣기, 실행하기 방법을 사용하여 코드를 확인해보고, 필요하다면 코드의 일부를 수정하거나 삭제할 수도 있다. 예를 들어, 새로운 로그인이 기존의 로그인 계정과 정확하게 동일한 권한을 가질 필요가 없다면 몇 가지 권한을 삭제할 수도 있을 것이다.
새로운 로그인을 추가하는 것 외에도 이 저장 프로시저는 기존의 로그인에 대한 권한을 감사하는 데에도 도움이 될 수 있다. USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LOGIN를 기존의 로그인과 함께 호출하되, 새로운 로그인은 의미 없는 가상의 이름으로 구성하자. 이 방법을 사용하면 기존의 로그인이 어떤 권한을 가졌는지 그리고 여러 데이터베이스와 개체에 어떤 작업을 왜 수행하거나 수행할 수 없었는지 알 수가 있다.

만일 당신이 기존의 로그인과 동일한 SQL 서버 권한을 갖는 새로운 로그인을 구성하기 위해 권한 분석과 계정 생성의 지루하고, 고통스러운 수작업을 반복하고 있었다면, 이 글에서 설명한 것과 같은 저장 프로시저의 구현을 고려해 보는 것도 좋다. DBA들은 너무나 바쁘기 때문에 기존 권한들을 조사하는데 귀중한 시간을 낭비하지 않는다. 따라서 로그인 관리를 합리적으로 하기 위한 어떠한 작업도 충분히 구현해볼 가치는 있는 것이다.

EXECUTE [MASTER].[DBO].[SP_ADDLOGIN] [NEWUSER],[#NEWUSER964]EXECUTE [MASTER].[DBO].[SP_DEFAULTDB] [NEWUSER],[master] EXECUTE [Northwind].[DBO].[SP_GRANTDBACCESS] [NEWUSER],[Joe the NewUser] EXECUTE [pubs].[DBO].[SP_GRANTDBACCESS] [NEWUSER],[Joe the NewUser] EXECUTE [Northwind].[DBO].[SP_ADDROLEMEMBER] [db_accessadmin],[Joe the NewUser] EXECUTE [pubs].[DBO].[SP_ADDROLEMEMBER] [db_owner],[Joe the NewUser] USE [Northwind] GRANT Select ON [Northwind].[dbo].[Categories] TO [Joe the NewUser] GRANT Select ON [Northwind].[dbo].[Order Details] TO [Joe the NewUser] GRANT Delete ON [Northwind].[dbo].[Customers] TO [Joe the NewUser] GRANT Execute ON [Northwind].[dbo].[Sales by Year] TO [Joe the NewUser] GRANT Create View TO [Joe the NewUser]

출처: WINDOWS & .NET[2004년 3월호]