Microsoft MVP성태의 닷넷 이야기
글쓴 사람
정성태 (techsharer at outlook.com)
홈페이지
첨부 파일

SQL 서버 - DB 테이블의 데이터 변경에 대한 알림 처리

마침, 아래와 같이 질문해 주신 분이 있군요. ^^

주식형태의 프로그램 처럼 SQL서버의 특정 필드 데이터의 변화가 있을때 재 클라이언트가 정보를 갱신 할 수 있게 하는 방법은 없을까요?
; https://www.sysnet.pe.kr/3/0/981

덕분에, Notification Services가 2008부터 누락되었다는 것도 알게 되었습니다. 아래의 글에 따르면, 누락은 되었지만 SQL Server 2005 SP3에 포함된 Notification Services와 연동이 되는 것은 가능하다고 합니다.

SQL Server Notification Services
; http://en.wikipedia.org/wiki/SQL_Server_Notification_Services

어찌되었든, 저도 Notification Services를 사용해 본 적은 없으므로 더 이상 언급할 만한 가치는 없겠고.

이제 다른 방법을 찾아봐야 하는데, 다행히 검색을 해보면 WMI 이벤트로 알림 기능이 제공된다는 글이 있습니다.

wmi Sql table notification
; http://www.ureader.com/msg/14861679.aspx

아하, AUDIT_SCHEMA_OBJECT_ACCESS_EVENT가 있다는군요. 사용 방법에 관한 구체적인 코드는 다음과 같이 하면 되겠습니다.

Sample: Using the WMI Event Provider with the .NET Framework
; https://docs.microsoft.com/en-us/sql/relational-databases/wmi-provider-server-events/sample-using-the-wmi-event-provider-with-the-net-framework




실제로 한번 테스트를 해보았습니다. 우선, 다음과 같이 간단한 DB 및 테이블을 만들고,

sql_wmi_event_1.png

이를 기반으로 WMI Query를 구성하는데, DB 이름으로 'TestDB' 및 테이블 이름으로 'TestTable'로 제한하고 수행되는 쿼리에 "INSERT INTO", "UPDATE" 문자열을 담고 있으면 알림을 받도록 했습니다.

string query = @"Select * From AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Where ObjectName = 
'TestTable' And DatabaseName = 'TestDB' and (TextData Like '%INSERT INTO%' or TextData Like '%UPDATE %')";
            
// Default namespace for default instance of SQL Server 
string managementPath = @"\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER";
ManagementEventWatcher watcher = new ManagementEventWatcher(new WqlEventQuery(query));
ManagementScope scope = new ManagementScope(managementPath);
scope.Connect();
watcher.Scope = scope;
Console.WriteLine("Watching...");
while (true)
{
    ManagementBaseObject obj = watcher.WaitForNextEvent();
    foreach (PropertyData data in obj.Properties)
    {
        Console.Write("{0}:", data.Name);
        if (data.Value == null)
        {
            Console.WriteLine("<null>");
        }
        else
        {
            Console.WriteLine(data.Value.ToString());
        }
    }
}

아쉽게도, 위의 WMI 코드를 실행하려면 '관리자 권한'이 필요하다는 정도겠군요. 동작이 잘 되는지 아래와 같이 INSERT 쿼리를 실행해 보면,

INSER INTO TestTable(id) VALUES(1)

이벤트를 받게 되고, PropertyData 내용은 다음과 같이 출력됩니다.

ApplicationName:Microsoft SQL Server Management Studio - Query
BinaryData:System.Byte[]
ClientProcessID:12312
ColumnPermissions:0
ComputerName:TESTPC
DatabaseID:7
DatabaseName:TestDB
DBUserName:dbo
EventSequence:744
EventSubClass:0
HostName:TESTPC
IsSystem:0
LineNumber:1
LoginName:TESTPC\TestUser
LoginSid:System.Byte[]
NestLevel:0
NTDomainName:TESTPC
NTUserName:TestUser
ObjectName:TestTable
ObjectType:8277
OwnerName:dbo
ParentName:dbo
Permissions:8
PostTime:20110711175034.000470+000
RequestID:0
SECURITY_DESCRIPTOR:<null>
ServerName:TESTPC
SessionLoginName:TESTPC\TestUser
SPID:53
SQLInstance:MSSQLSERVER
StartTime:20110711175034.000470+000
Success:1
TextData:INSERT INTO [TestTable]([Id]) values(@1)
TIME_CREATED:129548478344755975
TransactionID:95564
XactSequence:227633266689

UPDATE의 경우에도,

UPDATE TestTable SET Id = 4

알림으로 받게되는 속성은 다음과 같습니다.

ApplicationName:Microsoft SQL Server Management Studio
BinaryData:System.Byte[]
ClientProcessID:12312
ColumnPermissions:1
ComputerName:TESTPC
DatabaseID:7
DatabaseName:TestDB
DBUserName:dbo
EventSequence:1180
EventSubClass:0
HostName:TESTPC
IsSystem:0
LineNumber:1
LoginName:TESTPC\TestUser
LoginSid:System.Byte[]
NestLevel:0
NTDomainName:TESTPC
NTUserName:TestUser
ObjectName:TestTable
ObjectType:8277
OwnerName:dbo
ParentName:dbo
Permissions:2
PostTime:20110711175410.000623+000
RequestID:0
SECURITY_DESCRIPTOR:<null>
ServerName:TESTPC
SessionLoginName:TESTPC\TestUser
SPID:58
SQLInstance:MSSQLSERVER
StartTime:20110711175410.000623+000
Success:1
TextData:UPDATE [TestTable] set [Id] = @1
TIME_CREATED:129548480515150114
TransactionID:98062
XactSequence:249108103169

아... 아깝군요. 실행 시간 정보까지만 나왔어도 제니퍼 닷넷에 기능 추가를 해보는 것도 좋을 텐데. ^^

첨부된 파일은 위의 코드를 포함한 예제 프로젝트입니다.





[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[연관 글]






[최초 등록일: ]
[최종 수정일: 7/17/2021]

Creative Commons License
이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.
by SeongTae Jeong, mailto:techsharer at outlook.com

비밀번호

댓글 작성자
 



2019-09-04 04시59분
[JangHun] WMI 코드를 실행하려면 관리자 권한이 필요하다고 말씀해주셨는데
코드 실행을 관리자 권한으로 하려면 어떻게 해야하나요?
[손님]
2019-09-04 05시44분
[JangHun] SQL SERVER 2017을 설치하고, 데이터베이스와 테이블을 만들어놓은 상태에서

string query = @"Select * From AUDIT_SCHEMA_OBJECT_ACCESS_EVENT Where ObjectName =
'TestTable' And DatabaseName = 'TestDB' and (TextData Like '%INSERT INTO%' or TextData Like '%UPDATE %')";
            
// Default namespace for default instance of SQL Server
string managementPath = @"\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER";
.
.

위 코드를 따라서 작성하는데, Access-Denied가 됩니다.. 혹시 SQL SERVER버전이 달라져서 뭔가 달라진것일까요..?
[손님]
2019-09-05 08시42분
"코드"만을 관리자 권한으로 실행할 수 있는 방법은 없고, 실행 프로세스 자체를 애당초 관리자 권한으로 실행해야 합니다. 관련해서는 다음의 글을 참고하시고.

ClickOnce - 관리자 권한 상승하는 방법
; http://www.sysnet.pe.kr/2/0/950

관리자 권한이 필요한 작업을 COM+ 에 대행
; http://www.sysnet.pe.kr/2/0/1290

그다음 문제인, Access-Denied가 발생한 것은 버전과는 무관할 것으로 보입니다. 아마도 관리자 권한으로 실행하지 않아서 발생하는 것 같은데, 위의 문제를 해결하면 자연스럽게 없어질 것입니다.
정성태
2022-01-18 01시16분
정성태

[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...
NoWriterDateCnt.TitleFile(s)
13064정성태5/18/202252.NET Framework: 2016. C# - JIT 컴파일러의 인라인 메서드 처리 유무
13063정성태5/18/202287.NET Framework: 2015. C# - 인라인 메서드(inline methods)
13062정성태5/17/2022217.NET Framework: 2014. C# - async/await 그리고 스레드 (4) 비동기 I/O 재현파일 다운로드1
13061정성태5/16/202270.NET Framework: 2013. C# - FILE_FLAG_OVERLAPPED가 적용된 파일의 읽기/쓰기 시 Position 관리파일 다운로드1
13060정성태5/15/2022146.NET Framework: 2012. C# - async/await 그리고 스레드 (3) Task.Delay 재현파일 다운로드1
13059정성태5/14/2022145.NET Framework: 2011. C# - CLR ThreadPool의 I/O 스레드에 작업을 맡기는 방법파일 다운로드1
13058정성태5/13/202289.NET Framework: 2010. C# - ThreadPool.SetMaxThreads 사용법
13057정성태5/12/202254오류 유형: 812. 파이썬 - ImportError: cannot import name ...
13056정성태5/12/2022256.NET Framework: 2009. C# - async/await 그리고 스레드 (2) MyTask의 호출 흐름 [2]파일 다운로드1
13055정성태5/11/2022358.NET Framework: 2008. C# - async/await 그리고 스레드 (1) MyTask로 재현 [4]파일 다운로드1
13054정성태5/11/2022224.NET Framework: 2007. C# - 10진수 숫자를 담은 문자열을 숫자로 변환하는 방법 [9]파일 다운로드1
13053정성태5/10/2022103.NET Framework: 2006. C# - GC.KeepAlive 메서드의 역할
13052정성태5/9/2022174.NET Framework: 2005. C# - 생성한 참조 개체가 언제 GC의 정리 대상이 될까요?
13051정성태5/8/202298.NET Framework: 2004. C# XingAPI - ACF 검색 결과로 구한 CSV 파일을 통해 퀀트 종목 찾기파일 다운로드1
13050정성태5/6/202295.NET Framework: 2003. C# - COM 개체의 이벤트 핸들러에서 발생하는 예외에 대한 CLR의 특별 대우파일 다운로드1
13049정성태5/6/202248오류 유형: 811. GoLand - Error: Cannot find package
13048정성태5/6/202257오류 유형: 810. "ASUS TUF GAMING B550M-PLUS (WI-FI)" 모델에서 블루투스 장치가 인식이 안 되는 문제
13047정성태5/6/202246오류 유형: 809. Speech Recognition could not start
13046정성태5/5/202297.NET Framework: 2002. C# XingAPI - ACF 파일을 이용한 퀀트 종목 찾기(t1857)
13045정성태5/5/202290.NET Framework: 2001. C# XingAPI - 주식 종목에 따른 PBR, PER, ROE 구하는 방법(t3341 예제)
13044정성태5/4/202252오류 유형: 808. error : clang++ exited with code 127
13043정성태5/3/202261오류 유형: 807. C# - 닷넷 응용 프로그램에서 Informix DB 사용 시 오류 메시지 정리
13042정성태5/3/2022142.NET Framework: 2000. C# - 닷넷 응용 프로그램에서 Informix DB 사용 방법파일 다운로드1
13041정성태4/28/202283개발 환경 구성: 642. Informix 데이터베이스 docker 환경 구성
13040정성태4/27/202286VC++: 156. 비주얼 스튜디오 - Linux C/C++ 프로젝트에서 openssl 링크하는 방법
[1]  2  3  4  5  6  7  8  9  10  11  12  13  14  15  ...