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 및 테이블을 만들고,
이를 기반으로 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
아... 아깝군요. 실행 시간 정보까지만 나왔어도
제니퍼 닷넷에 기능 추가를 해보는 것도 좋을 텐데. ^^
첨부된 파일은 위의 코드를 포함한 예제 프로젝트입니다.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]