ADO.NET 2.0의 쿼리 알림

duraboys 2007. 5. 3. 10:46

2007. 5. 3. 10:46

Bob Beauchemin
DevelopMentor

2005년 4월
업데이트한 날짜: 2005년 6월

적용 대상:
ADO.NET 2.0

요약: ADO.NET 2.0 및 SQL Server 2005에 제공되는 새로운 알림 기술을 사용하여 데이터 변경을 다루는 방법을 배워 보십시오.

소개
캐시 솔루션을 제공하는 SqlDependency
SQL Server 2005의 쿼리 알림
최종 사용자 또는 캐시로 알림 발송
데이터베이스 클라이언트에서 쿼리 알림 사용
SqlDependency 사용
SqlNotificationRequest 사용
ASP.NET에서 SqlCacheDependency 사용
향상된 알림 기능
알림을 사용하지 않는 경우: 교훈
결론

소개

모든 실제 관계형 데이터베이스 응용 프로그램에는 으레 많은 조회 테이블이 들어 있습니다. 그래픽 사용자 인터페이스를 코딩하는 경우 이러한 조회 테이블은 드롭다운 목록 상자를 채우는 목록으로 나타납니다. 필자는 조회 테이블을 읽기 전용 테이블과 읽기 위주(read-mostly) 테이블의 두 가지 유형으로 분류합니다. 이들 간의 차이는 테이블의 변경을 유발하는 부분에 있습니다. 직원 회의나 사용자 회의를 통해 테이블을 변경하는 경우는 읽기 전용 테이블로 볼 수 있으며, 회사의 제품 범주가 포함된 테이블은 이에 대한 좋은 예입니다. 이러한 테이블은 회사에서 신제품을 출시하거나 회사가 재편되지 않는 한 변경되지 않습니다. 읽기 위주 테이블은 비교적 일관되게 유지되지만 최종 사용자가 변경할 수 있는 목록입니다. 이러한 테이블은 대개 드롭다운 목록이 아닌 콤보 상자에 나타납니다. 읽기 위주 테이블의 예로는 호칭(term-of-greeting) 테이블을 들 수 있습니다. 응용 프로그램 디자이너는 대개 Ms., Mr., Mrs. 및 Dr. 같은 일반적인 호칭을 생각할 수 있지만 한 번도 생각해 본 적 없는 호칭을 추가하려는 사용자도 늘 있게 마련입니다. 이러한 일이 얼마나 흔한 경우인지를 보여 주는 예로, 필자가 최근 작업한 중간 크기 제품에는 제3 정규형 관계형 데이터베이스에 350-400개의 테이블이 들어 있었습니다. 이 중 250개 정도가 읽기 전용 또는 읽기 위주 테이블인 것으로 추정됩니다.

3계층 응용 프로그램의 대표적인 예라 할 수 있는 일반적인 웹 응용 프로그램에서는 이러한 유형의 테이블을 가능한 한 많이 캐시하게 됩니다. 이렇게 하면 데이터베이스로의 왕복 횟수는 물론 데이터베이스에서의 쿼리 로드를 줄여 신규 주문 같은 사용 사례에서 응답 성능을 높일 수 있습니다. 읽기 전용 테이블의 경우에는 항상 테이블을 캐시에 유지하고 데이터베이스 관리자(DBA)가 테이블을 다시 로드해야 할 경우에만 이따금 캐시를 다시 로드할 수 있도록 하므로 캐시 작업이 간단합니다. 조직에서 기본 데이터베이스 구조와 콘텐츠를 변경하는 회의를 하는 경우는 아마 거의 없을 것입니다. 중간 계층 캐시에서 읽기 위주 조회 테이블을 새로 고치면 약간의 문제가 발생할 수 있습니다. 일정에 따라 캐시를 간헐적으로 새로 고쳐도 원하는 동작이 만들어지지 않을 뿐 아니라, 사용자는 다른 사용자의 변경 내용을 바로 확인하지 못합니다. 지원 담당자가 다른 응용 프로그램을 사용하여 새 항목을 추가하고 이를 사용하려는 친구에게 인스턴트 메신저 메시지를 보낼 수 있겠지만, 선택한 친구 목록은 새 항목에 포함되지 않습니다. 게다가 다른 사용자가 "누락된 목록 항목"을 다시 추가하려고 하면 항목이 이미 존재한다는 내용의 데이터베이스 오류 메시지가 표시됩니다. 읽기 위주 테이블에 "업데이트 지점"이 둘 이상인 경우에는 대개 이러한 문제로 인해 해당 테이블의 캐시 작업이 이루어지지 않습니다.

예전의 프로그래머들은 메시지 대기열을 사용하는 수작업 솔루션, 파일에 쓰는 트리거 또는 응용 프로그램 외부의 누군가가 읽기 위주 테이블을 업데이트하면 이를 캐시에 알리는 대역 외 프로토콜에 의존했습니다. 이러한 "알림(signaling)" 솔루션은 단순히 행이 추가 또는 변경되었으므로 캐시를 새로 고쳐야 한다는 내용을 캐시에 알리는 역할만 합니다. 특정 행이 변경 또는 추가되었음을 캐시에 알리는 것은 조금 다른 문제이며, 분산 데이터베이스 및 트랜잭션 또는 병합 복제 영역에 해당됩니다. 오버헤드가 낮은 알림 솔루션에서는 프로그램에 "잘못된 캐시"라는 메시지가 표시되면 전체 캐시를 새로 고치기만 할 뿐입니다.

캐시 솔루션을 제공하는 SqlDependency

SQL Server 2005 및 ADO.NET 2.0 사용자는 이제 SqlClient 데이터 공급자와 쿼리 알림이라는 데이터베이스에 기본 제공되는 알림 솔루션을 사용할 수 있습니다. 이는 이러한 일상적인 문제를 해결하는 사용이 편리한 최초의 기본 제공 솔루션입니다. 쿼리 알림은 ASP.NET 2.0의 기본 기능에서도 직접 지원됩니다. ASP.NET 캐시는 알림에 등록할 수 있으며 이 알림은 AST.NET에서 사용되는 페이지 및 페이지 조각 캐시와도 함께 사용할 수 있습니다.

이처럼 유용한 기능을 수행하는 인프라는 SQL Server 2005 쿼리 엔진, SQL Server Service Broker, 시스템 저장 프로시저인 sp_DispatcherProc, ADO.NET SqlNotification(System.Data.Sql.SqlNotificationRequest), SqlDependency(System.Data.SqlClient.SqlDependency) 클래스 및 ASP.NET Cache(System.Web.Caching.Cache) 클래스로 구성되어 있습니다. 요컨대 이 인프라는 다음과 같이 작동합니다.

각 ADO.NET SqlCommand에는 알림 요청을 나타내는 Notification 속성이 들어 있습니다. SqlCommand가 실행될 때 Notification 속성이 있으면 알림 요청을 나타내는 네트워크 프로토콜(TDS)이 요청에 추가됩니다.
SQL Server는 쿼리 알림 인프라를 사용하여 요청된 알림에 대해 구독을 등록하고 명령을 실행합니다.
SQL Server는 SQL DML 문에서 처음에 반환된 행 집합을 변경시킬 수 있는 부분이 있는지 "감시"합니다. 변경이 발생하면 Service Broker 서비스로 메시지가 전송됩니다.
이 메시지는 다음과 같은 작업을 수행합니다.
1. 등록된 클라이언트에 다시 알림이 발생하도록 합니다.
2. 고급 클라이언트가 사용자 지정 처리를 할 수 있도록 Service Broker 서비스 대기열에 그대로 유지됩니다.

그림 1. 쿼리 알림의 상위 개요

ASP.NET SqlCacheDependency(System.Web.Caching.SqlCacheDependency) 클래스 및 OutputCache 지시문은 SqlDependency를 통해 자동 알림 기능을 사용합니다. 더 많은 컨트롤이 필요한 ADO.NET 클라이언트는 SqlNotificationRequest를 사용하고 Service Broker 대기열을 수동으로 처리하여 필요한 사용자 지정 구문을 모두 구현할 수 있습니다. Service Broker에 대한 자세한 설명은 이 기사에서는 다루지 않지만 샘플 서적의 A First Look at SQL Server 2005 for Developers(영문) 부분과 Roger Wolter가 저술한 "A First Look at SQL Server 2005 Service Broker"(영문) 기사를 참조하면 이를 이해하는 데 많은 도움이 될 것입니다.

계속하기 전에, 행 집합이 변경되면 SqlNotificationRequest 또는 SqlDependency에 각각 알림 메시지가 하나씩 전달되는 부분을 확실히 이해하고 넘어가야 합니다. 이 메시지는 변경을 유발하는 부분이 데이터베이스 INSERT 문이든, 하나 이상의 행을 삭제하는 DELETE 문이든, 하나 이상의 행을 업데이트하는 UPDATE 문이든 관계없이 동일합니다. 알림에 변경된 특정 행 또는 변경된 수와 관련된 정보는 들어 있지 않습니다. 캐시 개체나 사용자의 응용 프로그램에서 이러한 단일 변경 메시지를 받으면 전체 행 집합을 새로 고치고 알림에 다시 등록하는 방법 밖에는 없습니다. 여러 개의 메시지를 수신하지는 않으며 하나의 메시지가 발생한 후에는 데이터베이스의 사용자 구독이 종료됩니다. 또한 쿼리 알림 프레임워크는 이벤트에 대한 알림은 많이 받을수록 좋다는 것을 전제로 하여 작동합니다. 알림은 행 집합이 변경될 때는 물론 행 집합에 참여한 테이블이 삭제 또는 변경될 때, 데이터베이스를 재활용할 때 등과 같은 경우에도 전송됩니다. 캐시 또는 프로그램의 응답은 캐시된 데이터를 새로 고치고 알림에 다시 등록하는 것과 관계없이 대개 동일합니다.

지금까지 관련된 일반적인 구문에 대해 알아보았으므로 이제는 다음과 같은 세 가지 관점에서 작동 원리를 자세히 살펴보겠습니다.

SQL Server의 쿼리 알림 구현 방식 및 옵션 발송자의 작동 원리
SqlClient의 SqlDependency 및 SqlNotificationRequest가 클라이언트/중간 계층에서 작동하는 방식
ASP.NET 2.0의 SqlDependency 지원 방식

SQL Server 2005의 쿼리 알림

SQL Server는 서버 수준에서 클라이언트의 쿼리를 일괄적으로 처리합니다. 각 쿼리(여기서 쿼리는 SqlCommand.CommandText 속성임)에는 일괄 처리가 하나만 포함될 수 있지만, 일괄 처리에는 여러 개의 T-SQL 문이 들어 있을 수 있습니다. SqlCommand는 여러 개의 T-SQL 문이 포함될 수 있는 저장 프로시저나 사용자 정의 함수를 실행하는 데도 사용할 수 있습니다. 또한 SQL Server 2005에서는 클라이언트의 쿼리에 알림을 배달하는 Service Broker 서비스 이름, 알림 ID(문자열) 및 알림 시간 제한과 같은 세 가지 추가 정보가 포함될 수 있습니다. 쿼리 요청에 이러한 세 가지 정보가 나타나 있고 요청에 SELECT 또는 EXECUTE 문이 들어 있으면 SQL Server는 해당 쿼리에 의해 만들어진 모든 행 집합에 다른 SQL Server 세션의 변경 내용이 있는지 "감시"하게 됩니다. 저장 프로시저를 실행할 때처럼 여러 개의 행 집합이 생성되는 경우 SQL Server는 모든 행 집합을 "감시"합니다.

그렇다면 여기서 행 집합을 "감시"한다는 것은 어떤 의미이며 SQL Server는 이를 어떤 식으로 수행할까요? 행 집합의 변경 내용을 탐지하는 일은 SQL Server 엔진에서 수행하는 작업의 일부이며, 인덱싱된 뷰의 동기화를 위한 SQL Server 2000의 변경 탐지 기능부터 적용된 메커니즘이 사용됩니다. Microsoft는 SQL Server 2000부터 인덱싱된 뷰 개념을 도입했습니다. SQL Server의 뷰는 하나 이상의 테이블에 있는 열에 대한 쿼리로 구성되어 있습니다. 뷰 이름은 테이블 이름처럼 사용할 수 있습니다. 예를 들면 다음과 같습니다.

CREATE VIEW WestCoastAuthors
AS
SELECT * FROM authors
  WHERE state IN ('CA', 'WA', 'OR')

이제 뷰를 다음과 같이 쿼리의 테이블처럼 사용할 수 있습니다.

SELECT au_id, au_lname FROM WestCoastAuthors
  WHERE au_lname LIKE 'S%'

대부분의 프로그래머에게 뷰는 익숙하지만 인덱싱된 뷰는 생소할 수 있습니다. 인덱싱되지 않은 뷰의 뷰 데이터는 데이터베이스에 개별 복사본으로 저장되지 않으므로 뷰를 사용할 때마다 기본 제공 쿼리가 실행됩니다. 따라서 위의 예에서는 행 집합 WestCoastAuthors를 가져오는 쿼리가 실행되며 이 쿼리에는 필요한 특정 WestCoastAuthors를 끌어오는 술어가 포함됩니다. 인덱싱된 뷰는 데이터의 복사본을 저장하므로 WestCoastAuthors를 인덱싱된 뷰로 만들면 이들 작성자의 데이터 복사본을 두 개씩 얻게 됩니다. 이제 인덱싱된 뷰 또는 원본 테이블 중 하나를 경로로 선택하여 데이터를 업데이트할 수 있습니다. 그에 따라 SQL Server는 두 물리적 데이터 저장소에서 모두 변경 내용을 탐지한 후 다른 데이터 저장소에 이를 적용해야 합니다. 이러한 변경 탐지 메커니즘은 쿼리 알림이 설정되어 있을 때 엔진에서 사용하는 메커니즘과 동일합니다.

변경 탐지 특유의 구현 방식으로 인해 모든 뷰를 인덱싱할 수는 없습니다. 인덱싱된 뷰에 적용되는 이러한 제한 사항은 쿼리 알림에 사용되는 쿼리에도 적용됩니다. 예를 들어 WestCoastAuthors 뷰의 경우 뷰가 작성된 방식으로 인덱싱할 수는 없습니다. 이 뷰를 인덱싱하려면 뷰 정의에 두 부분으로 구성된 이름을 사용하고 모든 행 집합 열 이름을 명시적으로 지정해야 합니다. 그러면 이제 뷰를 변경하여 인덱싱을 수행해 보겠습니다.

CREATE VIEW WestCoastAuthors
WITH SCHEMABINDING
AS
SELECT au_id, au_lname, au_fname, address, city, state, zip, phone
  FROM dbo.authors
  WHERE state in ('CA', 'WA', 'OR')

여기서 인덱싱된 뷰 규칙을 따르는 쿼리만 알림과 함께 사용할 수 있습니다. 쿼리 결과의 변경 여부를 확인하는 데도 동일한 메커니즘이 사용되지만 인덱싱된 뷰에서처럼 쿼리 알림으로 인해 SQL Server에 데이터의 복사본이 생성되지는 않습니다. 인덱싱된 뷰에 대한 규칙 목록은 상당히 방대하며 SQL Server 2005 온라인 설명서에서 확인할 수 있습니다. 쿼리가 알림 요청과 함께 전송되고 규칙을 따르지 않는 경우 SQL Server는 즉시 "유효하지 않은 쿼리입니다"라는 알림을 이유와 함께 게시합니다. 그렇다면 알림은 "어디에 게시"될까요?

SQL Server 2005에서는 Service Broker 기능을 사용하여 알림을 게시합니다. Service Broker는 SQL Server에 기본 제공되는 비동기 대기열 기능이며, 쿼리 알림은 이 Service Broker 서비스를 사용합니다. 이 경우 서비스는 비동기 메시지의 대상이며 메시지는 계약이라는 일련의 특정 규칙을 따라야 합니다. Service Broker 서비스는 항상 물리적 메시지 대상인 대기열과 관련되어 있습니다. 쿼리 알림에 대한 계약은 SQL Server에 기본적으로 제공되며 이름은 http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification입니다.

참고 SQL Server의 계약 개체 이름은 URL이지만 위치를 나타내지 않습니다. 다시 말해 테이블 이름인 dbo.authors처럼 개체 이름에 불과합니다.

여기까지 종합해 보면 쿼리 알림 메시지의 대상은 적절한 계약을 지원하는 서비스라는 점을 알 수 있습니다. 서비스 등을 정의하는 SQL DDL은 다음과 같습니다.

CREATE QUEUE mynotificationqueue
CREATE SERVICE myservice ON QUEUE mynotificationqueue
 ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
GO

이제 쿼리 알림 요청에서 myservice 서비스를 대상으로 사용할 수 있을 것입니다. SQL Server는 서비스에 메시지를 보내는 방식으로 알림을 전송합니다. 고유한 서비스를 사용하거나 SQL Server에서 MSDB 데이터베이스의 기본 제공 서비스를 사용하도록 할 수도 있습니다. 고유한 서비스를 사용하는 경우에는 메시지를 읽고 처리하는 코드를 작성해야 합니다. 하지만 MSDB에 기본 제공되는 서비스를 사용하는 경우에는 메시지를 배달하는 미리 작성된 코드가 제공됩니다. 이 부분에 대해서는 나중에 다시 설명하겠습니다.

쿼리 알림에서 Service Broker를 사용하기 때문에 몇 가지 추가 요구 사항이 적용됩니다.

Service Broker는 쿼리 알림을 실행하는 데이터베이스에서 활성화해야 합니다. 베타 2의 경우 AdventureWorks 샘플 데이터베이스에는 Service Broker가 기본적으로 활성화되어 있지 않지만, "ALTER DATABASE SET ENABLE_BROKER" DDL 문을 사용하여 활성화할 수 있습니다.
쿼리를 전송하는 사용자는 쿼리 알림을 구독할 수 있는 권한을 가지고 있어야 합니다. 쿼리 알림 구독은 데이터베이스마다 수행합니다. 다음 DDL을 실행하면 사용자에게 현재 데이터베이스에서 구독을 수행할 수 있는 'bob' 권한이 부여됩니다.
```
      GRANT SUBSCRIBE QUERY NOTIFICATIONS TO bob
```

최종 사용자 또는 캐시로 알림 발송

지금까지 알림 요청과 함께 올바른 쿼리 일괄 처리를 SQL Server로 전송했습니다. SQL Server는 행 집합을 감시하며 누군가 행 집합을 변경하면 선택한 서비스로 메시지가 전송됩니다. 그렇다면 이제 무엇을 해야 할까요? 먼저, 메시지를 읽고 알림이 발생할 때 필요한 모든 로직을 수행하는 사용자 지정 코드를 작성합니다. 아니면 기본 제공되는 발송자에서 이를 자동으로 수행하도록 할 수도 있습니다. 그러면 발송자에 대해 한번 살펴보겠습니다.

사용자 지정 서비스를 지정하지 않으면 쿼리 알림은 http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService라는 MSDB 데이터베이스의 기본 서비스를 사용합니다.서비스 대기열에 메시지가 도착하면 sp_DispatcherProc이라는 대기열과 관련된 저장 프로시저에서 메시지를 자동으로 처리합니다. 한 가지 흥미로운 점은 이 프로시저는 .NET으로 작성된 코드를 사용한다는 점으로, 쿼리 알림을 자동으로 배달하려면 SQL Server 2005 인스턴스에 .NET CLR(공용 언어 런타임)을 로드하는 기능이 활성화되어 있어야 합니다. .NET CLR의 로드는 SQL Server 인스턴스별로 활성화하거나 비활성화할 수 있습니다.

쿼리 알림 메시지가 도착하면 sp_DispatcherProc(이하 "발송자")은 SqlDependency 알림 대기열의 쿼리 알림 구독 목록을 검색하여 각 구독자에게 메시지를 보냅니다. 서버는 발송자를 사용하여 데이터가 변경된 사실을 클라이언트에 알립니다. 이렇게 되면 클라이언트가 알림에 대해 폴링할 필요가 없을 뿐 아니라 알림을 수신하기 위해 SQL Server에 대한 연결을 열어 놓을 필요가 없기 때문에 매우 유용합니다. 발송자는 HTTP 프로토콜/TCP 및 개인 프로토콜을 사용하여 이 알림을 각 구독자에게 보냅니다. 서버와 클라이언트 간 통신은 선택적으로 인증할 수 있습니다. 알림이 배달되고 나면 활성 구독 목록에서 구독이 삭제됩니다. 클라이언트 구독별로 알림을 하나씩만 받으므로 쿼리를 다시 전송하고 다시 구독하는 일은 클라이언트에서 담당합니다.

데이터베이스 클라이언트에서 쿼리 알림 사용

지금까지 내부 작업을 모두 살펴보았으니 이제 이를 사용하는 ADO.NET 클라이언트를 작성해 보겠습니다. 어째서 비교적 간단한 클라이언트측 코드를 작성하면서 이처럼 많은 설명이 필요했을까요? 코드는 상당히 쉽게 작성할 수 있지만 반드시 규칙을 따라야만 합니다. 가장 일반적으로 발생하는 문제는 알림에 대해 유효하지 않은 쿼리를 전송한 다음 Service Broker와 사용자 권한을 설정하는 과정을 잊어버리는 경우입니다. 이로 인해 이처럼 강력한 기능이 애물단지로 전락하게 되었을 뿐 아니라, 일부 베타 테스터는 기능이 제대로 작동하지 않는다는 생각까지 하게 되었습니다. 따라서 간단한 예비 작업과 조사를 수행하면 많은 도움이 됩니다. 마지막으로 발송자에 대해 Service Broker 서비스 같은 속성과 프로토콜을 지정하게 되므로 먼저 내부 작업을 수행하는 편이 유익합니다. 이제는 이러한 용어들이 어떤 의미인지 알게 되었을 것입니다.

ADO.NET에서 쿼리 알림 클라이언트를 작성할 수 있는 것처럼 여기서는 OLE DB나 새로운 HTTP 웹 서비스 클라이언트를 사용하여 이를 작성할 예정입니다. 하지만 유의해야 할 부분은 쿼리 알림은 클라이언트측 코드를 통해서만 사용할 수 있는 점입니다. 이 기능을 T-SQL과 바로 함께 사용하거나 SqlServer 데이터 공급자를 사용하여 SQL Server와 통신하는 SQLCLR 프로시저 코드와 함께 사용할 수는 없습니다.

System.Data.dll 어셈블리에는 SqlDependency와 SqlNotificationRequest라는 두 개의 클래스가 들어 있습니다. SqlDependency는 발송자를 사용하여 자동 알림을 실행하려는 경우 사용합니다. SqlNotificationRequest는 알림 메시지를 직접 처리하려는 경우에 사용합니다. 그럼, 지금부터 이들 클래스의 예제를 살펴보겠습니다.

SqlDependency 사용

SqlDependency의 사용 단계는 간단합니다. 먼저, 쿼리 알림을 수행해야 하는 SQL 문이 들어 있는 SqlCommand를 만들고, SqlCommand와 SqlDependency를 연결합니다. 그런 다음 SqlDependency OnChanged 이벤트에 대해 이벤트 처리기를 등록합니다. 그런 다음 SqlCommand를 실행합니다. DataReader를 처리하고 닫는 것은 물론 연관된 SqlConnection까지 닫을 수 있습니다. 이제 행 집합이 변경되면 발송자를 통해 알림을 수신하게 됩니다. 이 개체에 대한 이벤트는 서로 다른 스레드에서 발생합니다. 따라서 코드 실행 중에 이벤트가 발생하는 상황을 처리할 수 있도록 대비해야 합니다. 경우에 따라 이벤트가 발생하는 상황에 일괄 처리 결과를 계속 처리할 수도 있습니다. 코드는 다음과 같습니다.

using System;
using System.Data;
using System.Data.SqlClient;
static void Main(string[] args)
{
  string connstring = GetConnectionStringFromConfig();
  using (SqlConnection conn = new SqlConnection(connstring))
  using (SqlCommand cmd = 
   // 2-part table names, no "SELECT * FROM ..."
   new SqlCommand("SELECT au_id, au_lname FROM dbo.authors", conn))
 {
  try
  {
    // cmd와 연관된 종속성을 만듭니다.
    SqlDependency depend = new SqlDependency(cmd);
    // 처리기를 등록합니다.
    depend.OnChanged += new OnChangedEventHandler(MyOnChanged);
    conn.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    // DataReader를 처리합니다.
    while (rdr.Read())
    Console.WriteLine(rdr[0]);
    rdr.Close();
    // 무효화가 완료될 때까지 기다립니다.
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
  }
  catch (Exception e)
   { Console.WriteLine(e.Message); }
 }
}
static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
  Console.WriteLine("result has changed");
  Console.WriteLine("Source " + e.Source);
  Console.WriteLine("Type " + e.Type);
  Console.WriteLine("Info " + e.Info);(참고: 프로그래머 코멘트는 샘플 프로그램 파일에는 영문으로 제공되며 기사에는 설명을 위해 번역문으로 제공됩니다.)
}

Visual Basic .NET에서 친숙한 WithEvents 키워드와 SqlDependency를 함께 사용해도 이와 동일한 코드를 작성할 수 있습니다. 이 프로그램은 기본 결과의 변경 횟수에 관계없이 OnChanged 이벤트를 한 번만 가져와 처리합니다. 실제 사용 시 알림을 받았을 때 해야 할 일은 새로운 알림과 함께 명령을 다시 전송하고 그 결과를 바탕으로 캐시를 새 데이터로 새로 고치는 것입니다. 위 예제의 Main()에 여기서 작성한 코드를 가져와 명명된 루틴으로 이동하면 코드의 형태는 다음과 같습니다.

static void Main(string[] args)
{
    GetAndProcessData(); 
    UpdateCache();
    // 사용자가 프로그램을 종료할 때까지 기다립니다.
    Console.WriteLine("Press Enter to continue");
    Console.ReadLine();
}
static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
    GetAndProcessData(); 
    UpdateCache();
}

일부 단락을 보면 이것이 ASP.NET Cache 클래스를 데이터 캐시로 사용하여 ASP.NET 2.0를 실행하는 것과 완전히 똑같다는 사실을 알 수 있습니다.

SqlDependency를 사용하는 경우에는 SQL Server 2005의 발송자 구성 요소를 통해 클라이언트에 연결하고 알림 메시지를 보냅니다. 이는 SqlConnection을 사용하지 않는 대역 외 통신입니다. 이는 또한 클라이언트가 SQL Server를 통해 "네트워크를 사용할 수 있어야"하며 방화벽 및 네트워크 주소 변환으로 인해 문제가 발생할 수 있다는 것을 의미합니다. 향후 베타 버전이 출시되면 포트 구성에 대한 제어 성능이 강화되어 방화벽을 보다 자유롭게 사용할 수 있을 것입니다. SqlDependency 생성자에 매개 변수를 지정하여 서버와 클라이언트가 통신하는 방식을 완벽하게 구성할 수 있습니다. 다음은 이에 대한 예제입니다.

SqlDependency depend = new SqlDependency(cmd,
    null,
    SqlNotificationAuthType.None,
    SqlNotificationEncryptionType.None,
    SqlNotificationTransports.Tcp,
    10000);

이 SqlDependency 생성자를 사용하면 기본값 대신 다양한 동작을 선택할 수 있습니다. 변경할 동작 중 가장 유용한 것은 서버에서 클라이언트와 연결하는 데 사용하는 유선 전송입니다. 이 예제에서는 SqlNotificationTransports.Tcp를 사용하므로 서버에서는 TCP 또는 HTTP를 사용할 수 있습니다. 이 매개 변수의 기본값은 SqlNotificationTransports.Any이며, 서버에서는 이를 통해 사용할 전송을 "결정"할 수 있습니다. Any가 지정된 경우 서버는 클라이언트 운영 체제에 커널 모드 HTTP 지원이 포함된 경우에는 HTTP를 선택하고, 그렇지 않은 경우에는 TCP를 선택하게 됩니다. Windows Server 2003 및 Windows XP(SP2)에는 커널 모드 HTTP 지원이 포함되어 있습니다. 또한 네트워크를 통해 메시지를 보내기 때문에 사용할 인증 형식을 지정할 수 있습니다. EncryptionType은 현재 매개 변수로 사용되고 있지만 이후 베타 버전에서는 제거될 예정입니다. 현재 두 값 모두에 대한 기본값은 None입니다. SqlNotificationAuthType은 통합 인증도 지원합니다. 또한 구독에 대한 시간 제한 값과 SQL Server Service Broker 서비스의 이름을 명시적으로 지정할 수 있습니다. 서비스 이름은 예제에서처럼 대개 null로 설정되지만 기본 제공 서비스인 SqlQueryNotificationService를 명시적으로 지정할 수도 있습니다. 대개 다시 정의할 확률이 가장 높은 매개 변수는 SqlNotificationTransport와 시간 제한입니다. 이러한 매개 변수는 서버측 발송자의 동작을 지정하므로 SqlDependency에만 적용할 수 있습니다. SqlNotificationRequest를 사용하는 경우에는 발송자를 사용하지 않습니다.

SqlNotificationRequest 사용

SqlNotificationRequest를 사용하려면 SqlDependency보다 약간 복잡한 설정 과정을 거쳐야 하지만 메시지 처리는 프로그램에서 담당하게 됩니다. SqlDependency를 사용하면 서버의 알림이 MSDB의 SqlQueryNotificationService로 전송되어 메시지가 자동으로 처리됩니다. 하지만 SqlNotificationRequest를 사용하는 경우에는 메시지를 직접 처리해야 합니다. 다음은 SqlNotificationRequest와 이 기사의 앞부분에서 정의한 서비스를 사용하는 간단한 예제입니다.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
class Class1
{
  string connstring = null;
  SqlConnection conn = null;
  SqlDataReader rdr = null;
  static void Main(string[] args)
  {
    connstring = GetConnectionStringFromConfig();
    conn = new SqlConnection(connstring));
    Class1 c = new Class1();
    c.DoWork();  
  }
  void DoWork()
  {
    conn.Open();
    rdr = GetJobs(2);
    if (rdr != null)
    {
      rdr.Close();
      WaitForChanges();
    }
    conn.Dispose();
  }
  public SqlDataReader GetJobs(int JobId)
  {
    using (SqlCommand cmd = new SqlCommand(
        "Select job_id, job_desc from dbo. jobs where job_id = @id",
        conn))
    {
      
    try
    {
      cmd.Parameters.AddWithValue("@id", JobId);
      SqlNotificationRequest not = new SqlNotificationRequest();
      not.Id = new Guid();
      // 이것은 notificationqueue라는 대기열과 연관된 pubs 데이터베이스의
      // MyService라는 서비스여야 합니다(아래 참조).
      // 서비스는 QueryNotifications 계약에 따라야 합니다.
      not.Service = "myservice";
      not.Timeout = 0; 
      // 알림 요청을 연결합니다.
      cmd.Notification = not;
      rdr = cmd.ExecuteReader();
      while (rdr.Read())
     Console.WriteLine(rdr[0]);
     rdr.Close();
    }
    catch (Exception ex)
    { Console.WriteLine(ex.Message); }
    return rdr;
    }
  }
  public void WaitForChanges()
  {
    // 알림이 대기열에 나타날 때까지
    // 기다렸다가 직접 읽고 확인합니다.
    using (SqlCommand cmd = new SqlCommand(
     "WAITFOR (Receive convert(xml,message_body) from notificationqueue)",    
      conn))
    {
      object o = cmd.ExecuteScalar();
      // 필요에 따라 알림 메시지를 처리합니다.
      Console.WriteLine(o); 
    }
  }

SqlNotificationRequest를 사용하는 장점(추가 작업이기도 함)은 알림을 기다렸다가 직접 처리해야 한다는 점입니다. SqlDependency를 사용하는 경우에는 알림을 수신하기 전까지 데이터베이스에 다시 연결할 필요가 없습니다. SqlNotificationRequest 알림은 기다릴 필요가 없으며 대기열만 가끔씩 폴링하면 됩니다. SqlNotificationRequest의 또 다른 사용 예로는 알림이 발생하면 실행조차 되지 않은 특수한 응용 프로그램을 작성하는 경우입니다. 응용 프로그램이 시작되면 대기열에 연결되어 이전 응용 프로그램 실행의 "영구 캐시" 결과가 더 이상 유효하지 않음을 확인할 수 있습니다.

알림을 몇 시간 또는 몇 일 동안 기다릴 수 있는 응용 프로그램에 대해 설명하다 보면, "데이터가 변경되지 않으면 알림은 언제 사라집니까?"와 같은 질문을 받게 됩니다. 데이터베이스의 구독 테이블에서 삭제되는 것처럼 알림을 사라지게 하는 유일한 요인은 알림이 발생되거나 알림이 만료되는 경우뿐입니다. 알림 구독이 SQL 리소스를 사용하고 쿼리 및 업데이트에 오버헤드를 추가하기 때문에 이를 기다리는 일이 귀찮은 데이터베이스 관리자는 SQL Server에서 알림을 수동으로 삭제할 수도 있습니다. 먼저 SQL Server 2005 동적 뷰에 대해 알림을 쿼리하고 성가신 알림 구독을 검색한 다음 명령을 실행하여 제거하면 됩니다.

  -- 모든 구독을 찾습니다.
  SELECT * FROM sys.dm_qn_subscriptions
  -- 원하는 구독의 ID를 선택한 다음
  -- ID = 42인 구독을 삭제합니다.
  KILL QUERY NOTIFICATION SUBSCRIPTION 42

ASP.NET에서 SqlCacheDependency 사용

ASP.NET Cache 클래스에도 알림을 연결할 수 있습니다. ASP.NET 2.0에서는 CacheDependency 클래스를 하위 클래스로 지정할 수 있으며, SqlCacheDependency는 SqlDependency를 캡슐화하고 다른 ASP.NET CacheDependency와 마찬가지로 작동합니다. SqlCacheDependency는 사용하는 버전이 SQL Server 2005이든, 이전 버전의 SQL Server든 관계없이 작동하므로 SqlDependency보다 효율성이 뛰어납니다. 물론 SQL Server 2005 이전 버전에서는 완전히 다른 방식으로 구현되어 있습니다.

이전 버전의 SQL Server를 사용하는 경우에는 "감시"할 테이블의 트리거를 사용하는 방식으로 SqlCacheDependency가 작동하게 됩니다. 이러한 트리거는 다른 SQL Server 테이블에 행을 쓰는 역할을 하며, 그러면 이 테이블이 폴링됩니다. 어떤 테이블의 종속성 및 폴링 간격 값을 활성화할지도 구성할 수 있습니다. SQL Server 2005 이전 구현에 대한 자세한 설명은 이 기사의 범위를 벗어나므로 자세한 내용은 Improved Caching in ASP.NET 2.0(영문)을 참조하십시오.

SQL Server 2005를 사용하는 경우 위의 ADO.NET 예제와 유사하게 SqlCacheDependency에서 SqlDependency 인스턴스를 캡슐화합니다. 다음은 SqlCacheDependency를 사용하는 간단한 코드 예제입니다.

// Page.Load를 호출했습니다.
CreateSqlCacheDependency(SqlCommand cmd)
{
  SqlCacheDependency dep = new SqlCacheDepedency(cmd);
  Response.Cache.SetExpires(DateTime.Now.AddSeconds(60);
  Response.Cache.SetCacheability(HttpCacheability.Public);
  Response.Cache.SetValidUntilExpires(true);
  Response.AddCacheDependency(dep);
}

사용이 편리한 뛰어난 기능 한 가지는 SqlCacheDependency가 페이지 또는 페이지 조각 캐시에도 연결된다는 점입니다. 또한 특정 ASP.NET OutputCache 지시문에서 모든 SqlCommands를 선언적으로 활성화할 수 있습니다. 여기에서는 페이지의 모든 SqlCommands에 대해 동일한 SqlDependency가 사용되며, SQL Server 2005 데이터베이스에 사용되는 형태와 유사합니다.

<%OutputCache SqlDependency="CommandNotification" ... %>

CommandNotification은 "SQL Server 2005 및 SqlDependency를 사용한다"는 의미의 키워드 값입니다. 이전 버전의 SQL Server를 사용하는 경우에는 이 지시문 매개 변수의 구문이 완전히 다릅니다. 또한 특정 운영 체제 버전에서 ASP.NET 2.0을 실행하면 CommandNotification 키워드 값만 활성화됩니다.

향상된 알림 기능

SQL Server 쿼리 알림의 디자인 정책은 클라이언트에 대해 알림을 빠뜨리는 것보다는 지나칠 정도로 자주 보내는 게 낫다는 것을 골자로 하고 있습니다. 대개 다른 누군가 행을 변경하여 캐시가 무효화되면 알림을 받게 되지만 항상 그런 것은 아닙니다. 예를 들어 DBA가 데이터베이스를 재활용하면 알림을 받게 됩니다. 또한 쿼리의 테이블 중 하나라도 변경되거나 삭제되거나 잘려도 알림을 받습니다. 쿼리 알림은 SQL Server 리소스를 소비하므로 SQL Server 리소스의 스트레스가 심각해지면 내부 테이블에서 쿼리 알림을 제거하기 시작할 수 있으며 이런 경우에도 클라이언트에서 알림을 받게 됩니다. 또한 각 알림 요청에는 시간 제한 값이 들어 있으므로 구독 제한 시간이 초과되면 알림을 수신합니다.

SqlDependency를 사용하는 경우 발송자는 SqlNotificationEventArgs 인스턴스에 이 정보를 요약합니다. 이 클래스에는 Info, Source 및 Type의 세 가지 속성이 포함되어 있으며, 이를 통해 알림을 유발하는 부분을 지정할 수 있습니다. SqlNotificationRequest를 사용하는 경우 대기열 메시지의 message_body 필드에 이와 동일한 정보가 포함된 XML 문서가 있지만 XPath 또는 XQuery를 사용하여 직접 구문 분석해야 합니다. 다음은 앞의 ADO.NET SqlNotificationRequest 예제에서 만든 샘플 XML 문서입니다.

<qn:QueryNotification 
 xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification" 
 id="2" type="change" source="data" info="update" 
 database_id="6" user_id="1">
<qn:Message>{CFD53DDB-A633-4490-95A8-8E837D771707}</qn:Message>
</qn:QueryNotification>

필자는 job_id = 5인 행에서 job_desc 열 값을 "new job"으로 변경하여 이 알림을 만들었지만 message_body에는 이 정보가 표시되지 않습니다. 이를 통해 알림 프로세스의 마지막 몇 가지 차이점을 확인할 수 있습니다. 알림의 기능은 SQL 문을 통해 어떤 항목이 변경되어 행 집합이 변경될 수 있음을 인식하는 정도에 그친다는 사실입니다. 알림 기능으로는 UPDATE 문이 행의 실제 값을 변경하지 않는 경우까지는 인식하지 못합니다. 예를 들어, 행을 job_desc = "new job"에서 job_desc = "new job"으로 변경하면 알림이 생성됩니다. 또한 쿼리 알림은 비동기적으로 수행되며 명령 또는 일괄 처리를 실행할 때 등록되므로 행 집합 읽기를 마치기 전에 알림을 받을 수 있습니다. 규칙을 따르지 않는 쿼리를 전송하는 경우에도 즉시 알림을 받을 수 있습니다. 여기서 규칙은 앞에서 언급한 인덱싱된 뷰에 대한 규칙입니다.

알림을 사용하지 않는 경우: 교훈

이제 쿼리 알림이 작동하는 방식을 이해했으므로 이를 어디에 사용해야 할지 명백해졌습니다. 바로 읽기 위주 조회 테이블입니다. 각 알림 행 집합은 SQL Server의 리소스를 처리하므로 이를 읽기 전용 테이블에 사용하는 것은 여러모로 쓸데없는 낭비입니다. 또한 동시에 "감시"하는 서로 다른 행 집합 수만 지나치게 많아질 뿐이므로 임시 쿼리에도 사용할 필요가 없습니다. 유용한 내부 작업 관련 정보 한 가지는 SQL Server는 서로 다른 매개 변수 집합을 사용하는 매개 변수가 지정된 쿼리에 대해 알림 리소스를 끌어온다는 사실입니다. 위 예제의 SqlNotificationRequest에서처럼 매개 변수가 지정된 쿼리를 사용하면 항상 이러한 이점을 얻는 것은 물론 성능을 높일 수도 있습니다. 이 이야기를 듣고 염려할 수도 있겠지만 이처럼 강력한 기능을 사용한다고 해서 적절한 알림을 받지 않는 것은 아닙니다. user1이 A-M에서 au_lname인 제작자를 감시하고 user2가 au_lname 값을 매개 변수로 사용하여 N-Z에서 au_lname을 감시하는 경우 각 사용자는 각 하위 집합에 "해당하는" 알림만 받게 됩니다.

마지막으로 한 가지 주의할 점은 어떤 사람은 알림 응용 프로그램을 떠올릴 때 시장 가격이 변화하고 화면이 끊임없이 바뀌며 주식 거래자들로 가득 찬 공간을 상상한다는 점입니다. 이는 이 기능을 명백히 잘못 사용하는 것이며 그 이유는 다음과 같은 두 가지로 볼 수 있습니다.

행 집합은 계속해서 변하므로 네트워크는 쿼리 알림과 쿼리 새로 고침 요청으로 넘쳐 날 수 있습니다.
몇 명 이상의 사용자가 모두 같은 데이터를 "감시"하는 경우 각각의 알림으로 인해 많은 사용자가 동일한 결과에 대해 동시에 다시 쿼리하는 상황이 발생합니다. 이렇게 되면 SQL Server는 동일한 데이터에 대한 지나치게 많은 요청으로 넘치게 될 수 있습니다.

프로그래머가 이 기능을 잘못 사용할 수 있다고 생각되더라도 베타 2 이후의 SQL Server에서는 DBA가 동적 관리 뷰를 통해 이러한 기능을 모니터링할 수 있도록 하는 자세한 정보를 제공하므로 안심해도 됩니다. 현재 이들 뷰에서는 구독만 표시할 수 있습니다. SQL Server 2005에서는 항상 알림이 과도한 양의 리소스를 가져와 이를 제거하도록 "결정"할 수 있음을 기억하시기 바랍니다.

결론

쿼리 알림은 SQL Server 2005에 기본적으로 제공되는 강력하고 새로운 기능으로, ADO.NET 2.0과 ASP.NET 2.0에서 바로 사용할 수 있습니다. ADO.NET 기능(SqlNotificationRequest 및 SqlDependency)은 SQL Server 2005 데이터베이스에 대해서만 작동하지만, ASP.NET에서는 폴링을 사용하는 대체 메커니즘을 통해 이 기능을 "이전 버전과도 호환하여" 사용할 수 있습니다. 이 기능은 구문 및 관련된 영향을 염두에 두고 신중하게 사용해야 합니다. 특히 이 기능의 핵심 부분은 ASP.NET에서 사용되는 읽기 위주 테이블입니다. 이러한 테이블은 다른 응용 프로그램은 물론 웹 응용 프로그램에서도 업데이트할 수 있습니다. 이 시나리오에서 쿼리 알림은 프로그래머들이 수년간 기다려 온 솔루션을 제공하고 있습니다.

저자 소개

Bob Beauchemin은 DevelopMentor의 강사, 과정 저자 및 데이터베이스 교과 과정 연락 담당자로 활동하고 있습니다. 그는 데이터 중심 분산 시스템의 개발자, 프로그래머 및 관리자로 25년 이상 경력을 쌓아 가고 있습니다. 또한 Microsoft Systems Journal 및 SQL Server Magazine 등에 ADO.NET, OLE DB 및 SQL Server와 관련된 기사를 게재한 바 있으며, A First Look at SQL Server 2005 for Developers (영문) 및 Essential ADO.NET (영문)의 저자이기도 합니다.

'.net' 카테고리의 다른 글

2.0에서의 스마트 클라이언트 문제해결 (1)	2007.05.03
ASP.NET 2.0으로 웹 사이트를 구축하여 음악 라이브러리 탐색 (2)	2007.05.03
aspnet_merge.exe 명령을 사용하여 배포용으로 미리 컴파일된 ASP.NET 출력 관리 (0)	2007.05.03
Windows 2003의 서비스팩을 설치한 후에 디버깅이 안되는 현상이 나타날 때 (1)	2007.05.03
Asp.net 2.0 Smtp 메일 보내기 (3)	2007.05.03

L2J with DuraBoys