Bob Beauchemin
DevelopMentor
2005년 4월
수정일: 2005년 6월
적용 대상:
ADO.NET 2.0
요약:ADO.NET 2.0 및 SQL Server 2005의 새로운 알림 기술을 사용하여 ad-hoc 데이터 새로 고침을 처리하는 방법을 살펴봅니다.
목차
소개
캐싱을 위한 솔루션을 제공하는 SqlDependency
SQL Server 2005의 쿼리 알림
최종 사용자 또는 캐시에 알림 디스패칭
데이터베이스 클라이언트에서 쿼리 알림 사용
SqlDependency 사용
SqlNotificationRequest 사용
ASP.NET에서 SqlCacheDependency 사용
열성적 알림
알림을 사용하지 않는 경우: 주의
결론
소개
중요한 모든 관계형 데이터베이스 응용 프로그램은 많은 조회 테이블을 갖도록 바인딩됩니다. 전문적으로 그래픽 사용자 인터페이스(GUI)를 코딩하는 개발자의 경우 GUI를 드롭다운 목록 상자를 채우는 목록으로 알고 있습니다. 필자는 조회 테이블을 읽기 전용 및 대부분 읽기(read-mostly)의 두 가지 유형으로 분류합니다. 이 두 가지 유형의 차이점은 이들 테이블이 변경되도록 할 수 있는 요소입니다. 테이블을 변경하는 데 직원 회의 또는 사용자 회의가 필요한 경우 읽기 전용 테이블이라고 생각할 수 있습니다. 이에 대한 좋은 예는 회사의 제품 종류를 포함하는 테이블입니다. 이 테이블은 회사가 새로운 제품을 출시하거나 회사가 구조 조정되는 경우가 아니라면 변경되지 않습니다. 대부분 읽기(read-mostly) 테이블은 비교적 지속적이지만 최종 사용자에 의해 변경될 수 있는 목록입니다. 이러한 테이블은 주로 드롭다운 목록보다 콤보 상자에 제공됩니다. 대부분 읽기(read-mostly) 테이블의 예는 인사말 테이블이 될 수 있습니다. 응용 프로그램 설계자는 대개 Ms., Mr., Mrs. 및 Dr.와 같은 가장 일반적인 용어를 생각할 수 있지만 결코 생각한 적이 없는 직함을 가진 사용자 또는 그러한 직함을 테이블에 추가하려는 사용자가 항상 존재합니다. 이러한 사례가 얼마나 흔한지를 보여주는 예로서, 필자가 최근 작업한 중간 규모의 제품은 350-400개의 테이블을 포함한 제3 정규형 관계형 데이터베이스를 사용했습니다. 대략 250개가 읽기 전용 또는 대부분 읽기(read-mostly) 테이블이었던 것으로 추정됩니다.
일반적인 웹 응용 프로그램(3계층 응용 프로그램의 전형적인 예)에서 여러분은 이러한 테이블의 유형을 가능한 많이 캐시하려 할 것입니다. 이렇게 하면 데이터베이스로의 라운드 트립 횟수가 줄어들 뿐만 아니라 데이터베이스에 대한 쿼리 부하가 감소되어 새로운 주문과 같은 사용 사례에 보다 신속하게 응답할 수 있습니다. 읽기 전용 테이블은 캐시하기 쉽습니다. 항상 테이블을 캐시에 두고 아주 가끔씩 테이블을 다시 로드해야 하는 경우에 데이터베이스 관리자(DBA)에게 캐시를 다시 로드하는 방법을 제공하면 되기 때문입니다. 조직에서 기본적인 데이터베이스 구조와 내용을 변경하는 회의는 아주 드물게 발생합니다. 중간 계층 캐시의 대부분 읽기(read-mostly) 조회 테이블의 새로 고침에는 좀 더 많은 문제가 있습니다. 일정에 따라 캐시를 자주 새로 고치지 않을 경우 원하는 동작(behavior)을 생성하지 않습니다. 사용자가 서로의 변경 사항을 그 즉시 보지 않기 때문입니다. 지원 직원이 다른 응용 프로그램을 사용하여 새로운 항목을 추가하고 이것을 사용하려는 친구에게 Instant Messenger 메시지를 보낼 수 있지만 친구의 선택 목록에는 새로운 항목이 포함되어 있지 않습니다. 더군다나 두 번째 사용자가 “누락된 목록 항목”을 다시 추가하려고 시도할 경우 그 항목이 이미 존재한다는 데이터베이스 오류를 받습니다. 이 같은 문제 때문에 두 개 이상의 “업데이트 지점”이 있는 경우에는 일반적으로 대부분 읽기(read-mostly) 테이블 캐시가 수행되지 않습니다.
과거에 프로그래머들은 응용 프로그램의 외부에서 누군가가 대부분 읽기(read-mostly) 테이블을 업데이트한 시기를 캐시에 알리기 위해 메시지 대기열, 파일에 기록하는 트리거 또는 대역외 프로토콜을 사용하는 수작업 솔루션에 의존해 왔습니다. 이와 같은 “신호” 솔루션은 단순히 행이 추가되거나 변경되었다는 사실을 캐시에 알려주어 캐시를 새로 고쳐야 한다는 것을 나타냅니다. 구체적으로 어떤 행이 변경되거나 추가되었는지에 대해 캐시에 알려주는 것은 별개의 문제이며 이는 분산 데이터베이스의 영역 및 트랜잭션 또는 병합 복제의 영역입니다. 낮은 오버헤드 신호 솔루션에서 프로그램이 “캐시가 잘못되었습니다”라는 메시지를 받은 경우 프로그램은 단순히 전체 캐시를 새로 고칩니다.
캐싱을 위한 솔루션을 제공하는 SqlDependency
SQL Server 2005 및 ADO.NET 2.0을 사용하는 경우에는 이제 SqlClient 데이터 공급자에 기본 제공되는 신호 솔루션과 Query Notifications라는 데이터베이스가 있습니다. 마침내 이러한 일반적인 문제에 대한 사용하기 쉬운 기본 제공 솔루션이 제공된 것입니다. 게다가 쿼리 알림(Query Notifications)은 ASP.NET 2.0의 기본 제공 기능에서 직접 지원됩니다. ASP.NET Cache는 알림을 등록할 수 있고 알림은 ASP.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.NETSqlCommand는 알림 요청을 나타내는Notification속성을 포함합니다. SqlCommand가 실행될 때 Notification 속성이 존재하면 요청에 추가될 알림 요청을 나타내는 네트워크 프로토콜(TDS) 패킷이 발생됩니다.
- SQL Server는 쿼리 알림 인프라를 사용하여 요청된 알림에 대한 구독을 등록하고 명령을 실행합니다.
- SQL Server는 처음에 반환된 행 집합의 변경을 초래할 수 있는 요소에 대한 SQL DML 문을 “관찰”합니다. 변경이 발생하면 Service Broker SERVICE에 메시지가 전송됩니다.
- 이 메시지는 다음 중 하나를 수행할 수 있습니다.
- 등록된 클라이언트에 응답하기 위해 알림이 발생하도록 합니다.
- 고급 클라이언트에 의한 사용자 지정 프로세싱에 사용할 수 있는 Service Broker의 서비스 대기열(QUEUE)에 참가합니다.
그림 1. 쿼리 알림에 대한 고급 개요
ASP.NETSqlCacheDependency(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는 클라이언트로부터의 쿼리를 일괄 처리에서 처리합니다. 일괄 처리는 여러 T-SQL 문을 포함할 수 있지만 각 쿼리(쿼리를SqlCommand.CommandText속성으로 간주)는 하나의 일괄 처리만을 포함합니다. 또한 SqlCommand를 사용하여 여러 T-SQL 문을 포함할 수 있는 저장 프로시저 또는 사용자 정의 기능을 실행할 수 있습니다. SQL Server 2005에서는 클라이언트의 쿼리가 세 가지 추가 정보 즉, 알림을 전달할 Service Broker 서비스의 이름, 알림 ID(문자열) 및 알림 시간 제한을 포함할 수도 있습니다. 이 세 가지 정보가 쿼리 요청에 들어 있고 요청에 SELECT 또는 EXECUTE 문이 포함된 경우 SQL Server는 다른 SQL Server 세션에서 적용된 변경 사항에 대한 쿼리에 의해 생성된 모든 행 집합을 “관찰”합니다. 예를 들면 저장 프로시저 실행에서 여러 행 집합이 생성된 경우 SQL Server는 행 집합을 전부 “관찰”합니다.
그렇다면 행 집합 “관찰”의 의미는 무엇이고 SQL Server가 이것을 수행하는 방법은 무엇입니까? 행 집합의 변경 탐지는 SQL Server 엔진의 일부이고 SQL Server 2000 이래로 계속 사용해 온 메커니즘(인덱스된 뷰 동기화에 대한 변경 탐지)을 사용합니다. SQL Server 2000에서 Microsoft는 인덱스된 뷰의 개념을 도입했습니다. SQL Server의 뷰는 하나 이상의 테이블에 있는 열에 대한 쿼리로 구성됩니다. 뷰는 테이블 이름처럼 사용될 수 있는 이름이며 예를 들면 다음과 같습니다.
CREATE VIEW WestCoastAuthorsAS 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 WestCoastAuthorsWITH SCHEMABINDINGASSELECT 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를 사용합니다. 이 경우에 SERVICE는 비동기 메시지에 대한 대상이며, 메시지는 CONTRACT로 알려진 특정 규칙 집합을 따라야 할 수 있습니다. Service Broker SERVICE는 항상 물리적 메시지 대상인 QUEUE와 연관됩니다. 쿼리 알림용 CONTRACT는 SQL Server에 기본 제공되며 그 이름은 http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification입니다.
참고 CONTRACT의 SQL Server 개체 이름이 URL로 나타나지만 이것은 위치에 대한 어떤 정보도 암시하지 않습니다. dbo.authors가 테이블의 이름인 것처럼 이 URL은 단지 개체 이름입니다.
이 모든 것들을 종합적으로 판단해보면 쿼리 알림 메시지에 대한 대상은 해당 CONTRACT를 지원하는 모든 SERVICE가 될 수 있습니다. 서비스처럼 정의한 SQL DDL은 다음과 같은 형태일 수 있습니다.
CREATE QUEUE mynotificationqueueCREATE SERVICE myservice ON QUEUE mynotificationqueue ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])GO이제 쿼리 알림 요청에서 myservice SERVICE를 대상으로 사용할 수 있습니다. SQL Server는 SERVICE에 메시지를 전송하여 알림을 보냅니다. 자신의 고유한 SERVICE를 사용하거나 MSDB 데이터베이스에 기본 제공된 서비스를 SQL Server가 사용하도록 할 수 있습니다. 고유한 SERVICE를 사용하는 경우 메시지를 읽고 처리하는 코드를 작성해야 합니다. MSDB에 기본 제공된 서비스를 사용하는 경우 메시지를 전달하는 미리 작성된 코드가 있습니다. 이에 대해서는 나중에 다시 설명하겠습니다.
쿼리 알림은 Service Broker를 사용하기 때문에 다음과 같은 몇 가지 추가 요구 사항이 있습니다.
- Service Broker는 알림 쿼리가 실행되는 데이터베이스에서 활성화되어야 합니다. 베타 2에서는 Service Broker가 AdventureWorks 샘플 데이터베이스의 기본값으로는 활성화되지 않지만 “ALTER DATABASE SET ENABLE_BROKER" DDL 문을 사용하여 활성화될 수 있습니다.
- 쿼리를 제출하는 사용자는 쿼리 알림을 구독할 수 있는 권한이 있어야 합니다. 이 작업은 각 데이터베이스마다 수행됩니다. 다음 DDL은 현재 데이터베이스에서 구독할 수 있는 권한을 사용자 'bob'에게 제공합니다.
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO bob
최종 사용자 또는 캐시에 알림 디스패칭
지금까지 SQL Server에 대한 알림 요청과 함께 올바른 쿼리 일괄 처리를 제출했습니다. SQL Server가 행 집합을 관찰하고 있으므로 누군가가 행 집합을 변경할 경우 여러분이 원하는 SERVICE로 메시지를 전송할 것입니다. 그럼 이제 무엇을 할까요? 알림이 발생할 경우 메시지 읽기 및 원하는 모든 논리의 수행을 처리하는 사용자 지정 코드를 작성할 수 있습니다. 또는 기본 제공 디스패처가 여러분을 대신하여 그러한 작업을 처리하도록 할 수 있습니다. 그러면 디스패처를 살펴봅시다.
사용자 지정 SERVICE를 지정하지 않은 경우, 쿼리 알림은 MSDB 데이터베이스의 기본 SERVICE(이름: http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService)를 사용합니다. 메시지가 이 SERVICE의 대기열에 도착하면 sp_DispatcherProc라는 대기열과 연결된 저장 프로시저에 의해 자동으로 처리됩니다. 흥미로운 점은 이 프로시저가 .NET에서 작성된 코드를 사용하므로 자동 쿼리 알림 전달이 작동하도록 .NET 공용 언어 런타임(CLR)이 SQL Server 2005 인스턴스에서 활성되어야 한다는 것입니다. (.NET CLR 로딩은 각 SQL Server 인스턴스마다 활성화 또는 비활성화할 수 있습니다.)
쿼리 알림 메시지가 도착하면sp_DispatcherProc(지금부터는 "디스패처"로 명명함)는 쿼리 알림 구독의SqlDependency알림 대기열 목록을 살펴보고 각 구독자에게 메시지를 보냅니다. 디스패처를 사용할 때 서버는 클라이언트에게 데이터가 변경된 사실을 알린다는 점을 알아두십시오. 이것은 다음과 같은 두 가지 이유에서 좋습니다. 즉, 클라이언트가 알림을 폴링할 필요가 없고 클라이언트가 알림을 받기 위해 열린 SQL Server에 대한 연결을 유지할 필요가 없습니다. 디스패처는 HTTP 프로토콜 또는 TCP 및 개인 프로토콜을 사용하여 이 알림을 각 구독자에게 보냅니다. 서버와 클라이언트 사이의 통신은 선택적으로 인증될 수 있습니다. 알림이 전달된 후 해당 구독은 활성 구독 목록에서 삭제됩니다. 각 클라이언트 구독은 하나의 알림만을 받으며, 쿼리를 다시 제출하고 다시 구독하는 것은 클라이언트가 결정한다는 사실을 기억해 두십시오.
데이터베이스 클라이언트에서 쿼리 알림 사용
모든 내부 연결 작업에 대해 알아보았으므로 이제 이것을 사용하는 ADO.NET 클라이언트를 작성해 봅시다. 비교적 간단한 클라이언트측 코드를 작성하기 전에 이러한 모든 설명을 하는 이유는 무엇일까요? 코드 작성은 꽤 쉬운 편이지만 규칙에 따라야 한다는 것을 기억해야 합니다. 가장 일반적인 문제는 알림에 대한 잘못된 쿼리를 제출하고 Service Broker 및 사용자 권한 설정을 잊어버린다는 점입니다. 이로 인해 이 강력한 기능이 제대로 실행되지 않게 되자 심지어 일부 베타 테스터들은 이 기능은 잘 작동하지 않는다는 인식을 갖게 되었습니다. 사소한 준비 작업과 조사가 먼 길을 갑니다. 결정적으로 본질적인 작업을 먼저 수행하는 것이 좋았습니다. 왜냐하면 앞으로 Service Broker SERVICE 및 디스패처용 프로토콜과 같은 속성을 지정할 것이고 이제 이러한 용어의 의미를 알고 있기 때문입니다.
OLE DB를 사용하거나 심지어 새로운 HTTP 웹 서비스 클라이언트를 사용하여 ADO.NET에서 쿼리 알림 클라이언트를 작성할 수 있지만, 기억해 둘 사항은 쿼리 알림은 클라이언트측 코드를 통해서만 사용할 수 있다는 는 점입니다. 직접적으로 T-SQL을 통해 또는 SQL Server와 통신하기 위해 SqlServer 데이터 공급자를 사용하는 SQLCLR 절차적 코드를 통해 이 기능을 사용할 수 없습니다.
System.Data.dll어셈블리에는 사용할 수 있는 두 개의 클래스SqlDependency및SqlNotificationRequest가 있습니다. 디스패처를 사용한 자동 알림을 원하는 경우에는SqlDependency를 사용할 수 있고 직접 알림 메시지를 처리하기를 원하는 경우에는SqlNotificationRequest를 사용할 수 있습니다. 각각에 대한 예제를 살펴보겠습니다.
SqlDependency 사용
SqlDependency를 사용하는 단계는 간단합니다. 먼저 쿼리 알림을 원하는 SQL 문을 포함하는SqlCommand문을 작성합니다.SqlCommand를SqlDependency와 연결시킨 다음SqlDependency OnChanged이벤트에 대한 이벤트 처리기를 등록합니다. 그런 다음SqlCommand를 실행합니다. 이제 DataReader를 처리하고 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 { // create dependency associated with cmd SqlDependency depend = new SqlDependency(cmd); // register handler depend.OnChanged += new OnChangedEventHandler(MyOnChanged); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); // process DataReader while (rdr.Read()) Console.WriteLine(rdr[0]); rdr.Close(); // Wait for invalidation to come through 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);}SqlDependency와 함께 익숙한WithEvents키워드를 사용하여 Visual Basic .NET에서 동일한 코드를 작성할 수 있습니다. 이 프로그램은 기본 결과가 변경되는 횟수에 관계 없이 하나의OnChanged이벤트만을 얻고 처리한다는 점에 주의하십시오. 모든 중요한 사용의 경우 알림을 받을 때 수행하고자 하는 작업은 새 알림과 함께 명령을 다시 제출하고 그 결과를 사용하여 새 데이터로 캐시를 새로 고치는 것입니다. 위 예제에서Main()에 있는 코드를 가져와 명명한 루틴으로 이동하면 코드의 형태는 다음과 같을 수 있습니다.
static void Main(string[] args){ GetAndProcessData(); UpdateCache(); // wait for user to end program 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생성자를 사용하여 기본값과 다른 동작(behavior)을 선택할 수 있습니다. 변경에 대한 가장 유용한 동작(behavior)은 서버가 클라이언트에 연결하는 데 사용하는 유선 전송입니다. 이 예제에서는SqlNotificationTransports.Tcp를 사용하며 서버는 TCP 또는 HTTP를 사용할 수 있습니다. 이 매개 변수의 기본값은SqlNotificationTransports.Any이며 이 값을 통해 서버는 사용할 전송을 결정할 수 있습니다.Any가 지정되면, 클라이언트 운영 체제에 커널 모드 HTTP 지원이 포함된 경우 서버는 HTTP를 선택하고, 그렇지 않은 경우에는 TCP를 선택합니다. Windows Server 2003 및 Windows XP SP2에는 커널 모드 HTTP 지원이 포함되어 있습니다. 네트워크를 통해 메시지를 보내는 중이기 때문에 사용할 인증 유형을 지정할 수 있습니다.EncryptionType은 현재 매개 변수지만 이후의 베타 버전에서는 삭제될 것입니다. 현재 기본값은 두 값에 대해 모두None입니다. 또한 SqlNotificationAuthType은 통합 인증을 지원합니다. 그리고 구독에 대한 시간 제한 값 및 SQL Server Service Broker SERVICE의 이름을 명시적으로 지정할 수 있습니다. SERVICE 이름은 예제에서와 같이 대개 Null로 설정되지만 기본 제공 서비스SqlQueryNotificationService를 명시적으로 지정할 수 있습니다. 오버라이드(override)할 가능성이 가장 높은 매개 변수는SqlNotificationTransport및 시간 제한입니다. 이 매개 변수는 서버측 디스패처의 동작(behavior)을 지정하기 때문에SqlDependency에만 적용 가능하다는 것에 주의하십시오.SqlNotificationRequest에서는 디스패처를 사용하지 않습니다.
SqlNotificationRequest 사용
SqlNotificationRequest사용은 설정 측면에서SqlDependency보다 약간 더 복잡할 뿐이지만 복잡한 정도는 메시지를 처리하는 프로그램에 달려 있습니다.SqlDependency를 사용하는 경우 서버의 알림이 자동으로 메시지를 처리하는 MSDB의SqlQueryNotificationService에 전달됩니다.SqlNotificationRequest에서는 메시지를 여러분 스스로 처리해야 합니다. 다음은 이 문서의 앞부분에서 정의한 SERVICE 및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(); // this must be a service named MyService in the pubs database // associated with a queue called notificationqueue (see below) // service must go by QueryNotifications contract not.Service = "myservice"; not.Timeout = 0; // hook up the notification request 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() { // wait for notification to appear on the queue // then read it yourself using (SqlCommand cmd = new SqlCommand( "WAITFOR (Receive convert(xml,message_body) from notificationqueue)", conn)) { object o = cmd.ExecuteScalar(); // process the notification message however you like Console.WriteLine(o); } }SqlNotificationRequest사용에서의 능력(및 추가 작업)은 알림을 기다리거나 직접 알림을 처리해야 하는 것입니다.SqlDependency를 사용하는 경우 알림을 받을 때까지 데이터베이스에 다시 연결하지 않아도 됩니다.SqlNotificationRequest알림을 실제로 기다릴 필요는 없으며 이따금씩 대기열을 폴링하면 됩니다.SqlNotificationRequest의 또 다른 용도는 알림이 발생될 때 실행되지 않는 특수한 응용 프로그램의 작성이 될 수 있습니다. 응용 프로그램이 시작되면 응용 프로그램은 대기열에 연결되고 (이전의 응용 프로그램 실행으로부터) “지속성 캐시” 의 어떤 결과가 이제 유효한지 판단할 수 있습니다.
알림을 몇 시간 또는 며칠 동안 기다릴 수 있는 응용 프로그램에 대한 논의에서는 “데이터에 변경이 없다면 알림은 언제 사라집니까?”라는 의문이 제기됩니다. 알림이 사라지도록 즉, 데이터베이스의 구독 테이블에서 삭제되도록 하는 것은 알림이 발생할 때 또는 알림이 만료될 때뿐입니다. 데이터베이스 관리자는 대기하고 있는 알림 구독이 있다는 것을 성가시게 여길 수 있으므로(알림 구독이 SQL 리소스를 사용하여 쿼리 및 업데이트에 오버헤드를 추가하기 때문) SQL Server에서 알림을 수동으로 삭제하는 방법을 사용합니다. 우선 SQL Server 2005의 동적 뷰를 쿼리하여 위반하는 알림 구독을 찾은 다음 이를 제거하는 명령을 실행합니다.
-- look at all subscriptions SELECT * FROM sys.dm_qn_subscriptions -- pick the ID of the subscription that you want, then -- say its ID = 42 KILL QUERY NOTIFICATION SUBSCRIPTION 42ASP.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 이전 버전 구현에 대한 자세한 설명은 이 문서의 범위를 벗어나므로 자세한 정보는 “ASP.NET 2.0에서의 향상된 캐싱”을 참조하십시오.
SQL Server 2005를 사용하는 경우SqlCacheDependency는 위에서 나온 ADO.NET 예제와 유사한SqlDependency인스턴스를 캡슐화합니다. 다음은SqlCacheDependency사용을 보여주는 간략한 코드 예제입니다.
// called from Page.LoadCreateSqlCacheDependency(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가 사용된 경우에는 이 지시어 매개 변수의 구문은 완전히 다르다는 것을 주의하십시오. 또한CommandNotification키워드 값은 특정 운영 체제 버전에서 ASP.NET 2.0을 실행하는 경우에만 활성화됩니다.
열성적 알림
SQL Server Query Notifications의 설계 정책은 클라이언트에게 매우 자주 알리는 것이 알림을 누락하는 것보다는 낫다는 것입니다. 캐시를 무효화하는 행을 누군가가 변경한 경우에 알림을 받는 경우가 대부분이지만 항상 그렇지는 않습니다. 예를 들어 데이터베이스가 DBA에 의해 재활용되는 경우 알림을 받게 됩니다. 쿼리에 있는 테이블이 변경, 삭제되거나 잘린 경우 알림을 받게 됩니다. 쿼리 알림은 SQL Server 리소스를 차지하므로, SQL Server가 심한 리소스 압박에 놓여 있는 경우 내부 테이블에서 쿼리 알림을 제거하기 시작할 수 있습니다. 이 경우에도 클라이언트에 알림을 받게 됩니다. 그리고 각 알림 요청은 시간 제한 값을 포함하기 때문에 구독이 시간 초과되면 알림을 받습니다.
SqlDependency를 사용하는 경우 디스패처는 SqlNotificationEventArgs 인스턴스에 이 정보를 포함시킵니다. 이 클래스는 알림을 발생시키는 요소를 찾아낼 수 있게 해주는 세 개의 속성 Info, Source, Type을 포함합니다.SqlNotificationRequest를 사용하는 경우 대기 중인 메시지의message_body필드는 동일한 정보가 포함된 XML 문서를 포함하지만 XPath 또는 Xquery로 직접 구문 분석해야 합니다. 다음은 이전 ADO.NETSqlNotificationRequest예제에서 생성된 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"으로 변경하면 알림이 발생할 수 있습니다. 또한 쿼리 알림은 비동기식이고 명령 또는 일괄 처리를 실행하는 순간 등록되기 때문에 행 집합 읽기를 끝내기 전에 알림을 받을 수 있습니다. 규칙(앞에서 언급한 인덱스된 뷰 규칙)을 따르지 않는 쿼리를 제출할 경우에도 즉각적인 알림을 받을 수 있습니다.
알림을 사용하지 않는 경우: 주의
이제 쿼리 알림이 작동하는 방법을 알고 있으므로 쿼리 알림을 사용하는 위치(대부분 읽기(read-mostly) 조회 테이블)를 이해하는 것이 상당히 수월합니다. 각 알림 행 집합은 SQL Server의 리소스를 차지하는데, 읽기 전용 테이블에 대해 리소스를 사용하는 것은 낭비일 수 있습니다. 게다가 여러분은 ad-hoc 쿼리에 읽기 전용 테이블을 사용하기를 원하지 않습니다. 여기에는 동시에 “관찰되는” 서로 다른 행 집합이 너무 많이 있을 수 있기 때문입니다. 알아두면 유용한 내부적 세부 사항은 SQL Server는 서로 다른 매개 변수 집합을 사용하는 매개 변수화된 쿼리에 대한 알림 리소스를 미리 계산한다는 점입니다. 위의SqlNotificationRequest예제에서와 같이 항상 매개 변수화된 쿼리를 사용하면 이러한 이점을 활용하고 성능 향상을 얻을 수 있습니다. 이 설명을 들은 후에 걱정된다면 이 성능 특징으로 인해 적절한 알림을 얻지 못하게 되지는 않는다는 사실을 명심하십시오. au_lname 값을 매개 변수로 사용하여 user1이 A-M에서 au_lname을 가진 저자를 관찰하고 user2가 N-Z에서 au_lname을 관찰하는 경우 각 사용자는 하위 집합에 대해 “올바른” 알림만을 받게 될 것입니다.
마지막 주의 사항: 일부 사람들은 알림 응용 프로그램을 생각할 때, 시장 가격 변동으로 인해 각 화면이 끊임없이 바뀌는 주식 중개인으로 가득한 방을 상상합니다. 이 생각은 다음과 같은 두 가지 이유에서 이 기능의 완전히 잘못된 사용이라 단언할 수 있습니다.
- 행 집합이 계속해서 변경되므로 네트워크에 쿼리 알림 및 쿼리 새로 고침 요청이 쇄도할 수 있습니다.
- 적지 않은 사용자가 있고 이 사용자들이 모두 동일한 데이터를 “관찰”하는 경우 각 알림으로 인해 많은 사용자들이 동일한 결과를 위해 동시에 다시 쿼리하게 됩니다. 이로 인해 SQL Server는 동일한 데이터에 대한 많은 요청으로 가득차게 될 수 있습니다!
프로그래머가 이 기능을 남용하지 않을까 염려스럽다면 베타 2 이후의 SQL Server는 DBA가 동적 관리 뷰를 통해 이 기능을 모니터링할 수 있는 더 많은 정보를 제공할 수 있으므로 안심하셔도 됩니다. 현재 이러한 뷰는 구독을 보여줄 수만 있습니다. SQL Server 2005에서는 알림이 너무 많은 리소스를 사용하고 있다고 “판단”하면 그 리소스를 자체적으로 제거하는 것이 항상 가능하다는 점을 기억해 두십시오.
결론
쿼리 알림은 ADO.NET 2.0 및 ASP.NET 2.0에서 직접 사용할 수 있는 SQL Server 2005에 기본 제공된 새로운 강력한 기능입니다. ADO.NET 기능(SqlNotificationRequest및SqlDependency)은 오직 SQL Server 2005 데이터베이스에서만 작동하지만 ASP.NET이 폴링을 사용하는 대체 메커니즘을 통해 이 기능을 “이전 버전에서 사용 가능”하게 해줍니다. 그 의미와 미치는 영향을 명심하고 이 기능을 현명하게 사용하십시오. 이 기능을 가장 유용하게 사용할 수 있는 대상은 웹 응용 프로그램을 비롯하여 다른 응용 프로그램에서 업데이트될 수 있는 ASP.NET에서 사용되는 대부분 읽기(read-mostly) 조회 테이블일 것입니다. 이런 시나리오를 위해 쿼리 알림은 프로그래머들이 오래 동안 바래왔던 솔루션을 제공합니다.
저자 정보
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 (영문)의 저자입니다.