Dispatching change notifications in SQL Server and processing these in a windows service
Peter
automatically when the system starts and interacts with SQL Server via ODBC.
The service can run on multiple machines using the same
backend database.
I now have a requirement where I want to make sure all my Windows Services
running on separate machines are notified if changes are made to specific
tables (if you're familiar with Oracle, its dbms_alert package does exactly
what I want and is extremely simple to use).
Under SQL Server, it appears that Service Broker might be an option to
implement this. However, after going through the Books Online I'm still
unclear how this could be achieved in my case.
Here are some of the things I'd like to be able to do:
- add insert, update and delete triggers to the table(s) of interest which
send messages with relevant details to all queues (I assume that each
service will create a receive queue at startup)
- implement the service such that a dedicated thread "waits" until a
notification appears in the queue (note: if I need to "periodically check"
for new messages in the queue I can use other techniques which I find easy
to implement)
- if I add another machine with my windows service, I don't want to modify
all triggers. Instead, I'd like to create a new queue which the existing
triggers would recognize
If all this is possible, are there any training wheels available (perhaps in
the form of a small sample application)?
Any pointers are greatly appreciated.
Pete
Bob Beauchemin
I'm not familiar with dbms_alert but there's a feature in SQL Server known
as query notifications that does this type of thing using service broker
without triggers. Though most of the examples are in ADO.NET using an
additional client-side class named SqlDependency, there may be an example in
ODBC in the set of SQL Server API samples on CodePlex. Here's the relevent
book online section http://msdn.microsoft.com/en-us/library/ms130764.aspx.
Hope this helps,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:%23Aeyvcr...@TK2MSFTNGP06.phx.gbl...
Peter
There is clearly a fair literature to go throguh. At a first glance it looks
as though it is something that is supported in .Net so I might have a bit of
an uphill struggle at my hands implementing this using ODBC.
But again, thanks for the pointer.
Pete
"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:eToJk6x0...@TK2MSFTNGP06.phx.gbl...
Peter
is all I have installed on the development machines. Yiekes.
"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:eToJk6x0...@TK2MSFTNGP06.phx.gbl...
Bob Beauchemin
SQL Server Notification Services and Query Notifications are two totally
different things (which unfortunately both use the word "notification".
Notification Services is an application framework for writing notification
applications that use SQL Server as a repository for its metadata. AFAIK,
these apps don't run on SQL Express and SQL Server Notificaton Services
didn't ship with SQL Server 2008 either.
Query Notifications are built in to the SQL Serve engine and the client
APIs. It uses SQL Server's service broker feature (available on all
versions) to collect the notifications information. It will notify you at a
query level, if a change has been made that would affect a particular query
(you specify it as a property of the query command), but not which specific
rows have changed.
There are a couple of other change tracking mechanisms built into SQL Server
2008, but what you described (and my quick reading of dbms_alert doc page)
sounded most like query notifications.
Hope this helps,
Cheers,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:%23Ez0oSB...@TK2MSFTNGP05.phx.gbl...
Peter
I talked to Erland briefly about this on
news://microsoft.public.sqlserver.programming and he referred me to this
group. Yes indeed, I am still interested in an ODBC/C++ example.
In the meantime I'm studing your document as I hit a new snag on both
machines:
The sample code I sent with my last message now all of a sudden fails when I
try to subscribe to update notifications (this is line 59). Although I can
verify that all ODBC statements I execute upto and including 59 return
success, any pending WAITFOR (RECEIVE ...) will return immediately as soon
as line 59 returns and the RECEIVE resultset contains a single record with
this info:
status
1
priority
0
queuing_order
5
conversation_group_id
63DBAB97-3D43-DE11-9DB8-444553544200
conversation_handle
64DBAB97-3D43-DE11-9DB8-444553544200
message_sequence_number
0
service_name
APALSvc
service_id
65536
service_contract_name
http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification
service_contract_id
1
message_type_name
http://schemas.microsoft.com/SQL/Notifications/QueryNotification
message_type_id
3
validation
X
message_text
<qn:QueryNotification
xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
id="0"
type="subscribe"
source="statement"
info="invalid"
database_id="0"
sid="0x01">
<qn:Message>
RBAC Changed
</qn:Message>
</qn:QueryNotification>
As you can see, the root element in message_text QueryNotification has an
info attribute that indicates some sort of failure. I tried to delete the
service, the queue and disable the service broker on the database and then
redoing everything. I also tried restarting the database service, but all to
no avail.
The server log contains loads of messages like:
2009-05-18 10:02:28.79 spid24s The query notification dialog on
conversation handle '{81E6EC1E-6B42-DE11-B070-0016CFD19800}.' closed due to
the following error: '<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote
service has been dropped.</Description></Error>'.
I stumbled overthis article
http://support.microsoft.com/default.aspx?scid=kb;en-us;958006&sd=rss&spid=2855
and in turn updated SQL Server 2005 express SP2 on one machine with SP3 and
then applied the Hotfix 1 for that release. Still, the problem didn't
disappear.
For now, I can't make any progress until I can get this going again. If you
know what this is, I'd be really keen to hear.
Pete
"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:%23ozD8Bp...@TK2MSFTNGP04.phx.gbl...
> Hi Pete,
>
> One way to start would be to check out how it's done by .NET
> SqlDependency. In this whitepaper
> (http://www.microsoft.com/technet/prodtechnol/sql/2005/scaleout.mspx), I
> have some trace events (or a description of what happens). Basically,
> there is a separate listener connection that call WAITFOR with a timeout
> value. There are trace events that you can look at (you'll need a version
> of SQL Server that comes with SQL Profiler, Express doesn't have it) to
> get a good idea what is going on. The XML schema is simple. You can use
> the XML processor MSXML(3,4, or 5) that is either part of the platform SDK
> or at least downloadable
> (http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en).
> Query notifications is scalable to a few machines, it should complain on 2
> separate notification requests, but you will need two separate instances
> of the notification and listener programs.
>
> I only other person I'm aware of that's done query notifications outside
> of .NET is Erland (and I believe he used OLE DB and Perl). I'll try and
> get an example in ODBC/C++ if you're still interested.
>
> Hope this helps,
> Cheers,
> Bob Beauchemin
> SQLskills
>
> "pete" <pe...@kapiti.co.nz> wrote in message
> news:u2VEEbn1...@TK2MSFTNGP04.phx.gbl...
>> Thanks Bob, I was clearly running in the wrong direction. I now had a
>> bit of time researching this topic.Here is what I've done:
>>
>> - I have a database (SQL Server 2005 EE) called SCHENCK_606_MD
>>
>> - The database has a table like this
>> CREATE TABLE D3Role
>> (
>> ID int IDENTITY(1,1) NOT NULL,
>> Name varchar(64) NOT NULL,
>> Enabled bit NOT NULL
>> )
>>
>> - Executed the following T-SQL statements to ensure ServiceBroker is
>> enabled on the database and the service and queue exist:
>> ALTER DATABASE SCHENCK_606_MD SET ENABLE_BROKER;
>>
>> CREATE QUEUE APALSvcRBACChanges
>> CREATE SERVICE APALSvc ON QUEUE APALSvcRBACChanges
>>
>> ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
>>
>> - Wrote the attached C++ program which should do this:
>> 1. register a Query Notification for changes to the D3Role table
>> 2. listen for any changes to the D3Role table in the
>> APALSvcRBACChanges queue
>> 3. continue with 1.
>>
>> Unfortunately though, I already wonder if it is worth the trouble taking
>> this any further. Here are some of the problems I encountered:
>>
>> - the ODBC headers included with Microsoft Platform SDK for Windows
>> Server 2003 R2 do not include the all important statement attributes
>> SQL_SOPT_SS_QUERYNOTIFICATION_xxx. (to continue to make progress, I stole
>> the ones from the sqlncli.h included in SQL Server 2005 Express Edition)
>>
>> - when I call WAITFOR (RECEIVE * FROM queuename) the statement blocks as
>> expected and fires when an event occurs, but I have found nothing in the
>> docs that tells me how I can gracefully cause WAITFOR to stop blocking
>> (KILL QUERY SUBSCRIPTION doesn't do it, but even if it did, I wouldn't
>> know what the subscription ID is anyway). The manual states that if I
>> make the same subscription request again, I can change the timeout
>> option. This might be a way for me to trigger to the WAITFOR to return.
>> I'll try this next.
>>
>> - In my case, I run multiple servers (each on a separate machine) against
>> the same SQL Server database. Since each of the server caches the same
>> "thing", I need a notification to each server when the "thing" changed.
>> So far I have not managed to get notifications to two machines, but this
>> could be another problem as on one machine the attached program returns
>> immediately from the WAITFOR indicating some problem. It is a mystery to
>> me how I can detect what the problem is. I also wonder if I need
>> dedicated services and queues for each machine. This will be my next
>> step.
>>
>> - where on earth is the queue content documented? All I'm interested in
>> is that the content of table has changed, but if notifications are sent
>> in other cases (e.g. server restart), I need to know how I can
>> differentiate.
>>
>> - The message_body the RECEIVE statement collects from the queue is
>> apparently XML. IOW, I'd have to parse this to get more details. This
>> will add substantial overhead to the application (adding a 3rd party XML
>> parser), but unless other attributes received from the queue reliably
>> indicate that the table changed, I will have no option but to delve into
>> XML.
>>
>> I guess the main problem really is that I seem to be the only person on
>> the planet trying to use this via ODBC (ok, I saw some other people
>> asking similar questions but I never saw anybody progressing further). So
>> I hope someone else trying to use Query Notifications with an ODBC client
>> stumbles over this thread I find it helpful.
>>
>> Pete
>>
>> "Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
>> news:u2jGMFC1...@TK2MSFTNGP05.phx.gbl...
Bob Beauchemin
The notification message indicates that the statement for the notification
(that's "SELECT ID, Name, Enabled FROM dbo.D3Role" in the version that you
sent) is invalid for query notifications. If the code used to work, did you
change the statement?
The statements that are supported for query notifications are listed in the
SQL Server books online here:
http://msdn.microsoft.com/en-us/library/ms181122.aspx.
You could also check that the program doesn't try to issue the query
notification (set the options) on the handle used by RECEIVE, but that
doesn't seems to be the case, as you're freeing and reallocating the handle.
I thought there was an ODBC QN example in the SQL Server 2005 samples, but I
don't see it here, I'll have a look around and see what I can find.
Cheers,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:epBsx501...@TK2MSFTNGP04.phx.gbl...
Bob Beauchemin
sqlncli.h. You do need to use sqlncli.h header and link your program with
sqlncli.lib instead of odbcbcp.lib.
Cheers,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:epBsx501...@TK2MSFTNGP04.phx.gbl...
Peter
Indeed did I change something incorrectly: during a bout of absenteeism I
must have changed the FROM predicate to D3Role when the bible clearly
demands the two part name dbo.D3Role. Voila, it now works again.
That gave me a chance to continue playing which is much more fun than
reading black and white word documents (though I already learned heaps).
So now I have the test program X working on machine A and B against an SQL
Server 2005 EE SP3 database on machine A. If I start X on A and then
manually add a record to D3Role, it correctly responds and immediately waits
again. I can repeat this ad infinitum.
However, if I start X on B and then make a change to D3Role, only X:B
responds to the change. Again, I can make more changes and they all get
reported, but only by X:B. If I restart X on A, this behavior is reversed.
To add more confusion, if I just start X:A, every change to D3Role is
reported. Now, while X:A waits for a notification, I execute this query in
SQL Server Management Studio:
WAITFOR (RECEIVE * FROM APALSvcRBACChanges)
What happens now is that the next time I change D3Role, the above query
returns, but X:A isn't notified. Even more interesting is that if I make
another change (now no query is running under SQL Server Management Studio)
X:A doesn't receive a notification.
It's all a bit bizarre really. I'll see how far I get with dedicated queues
and services for registrants.
WRT using sqlncli.h, this really scares my pants off. I can't see a 'lean
and mean' option to include this file. There is so much .NET stuff I really
don't want to include and bloat my app. If there was no other way, I'd be
inclined to implement another process and use middleware which I already use
for communication between servers.
Again thanks for your support
Pete
"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:ulISt121...@TK2MSFTNGP04.phx.gbl...
Bob Beauchemin
Absolutely, I'd try a different service and queue for each registrant.
Unless you know the same of the service broker conversation that each
registrant is using, the programs can get them confused because each is
waiting on any message to arrive at the queue. Or have a single registrant
and when you get the query notification, dispatch (i.e. whatever program
does to respond to a change) to multiple machines.
A few more things that might help. You can create and read a SQL trace
without using profiler
http://blogs.msdn.com/sqlsecurity/archive/2008/12/12/how-to-create-a-sql-trace-without-using-sql-profiler.aspx
so this can help yor troubleshooting. There are specific profiler events for
query notifications and for service broker.
Also, don't forget that a query notification can respond with other messages
than the change notification. They are enumerated here.
http://msdn.microsoft.com/en-us/library/ms189308.aspx. Note that the message
is so simple that you don't necessarily need to parse the XML with an XML
parser, just look for the appropriate name="value" pairs with
string-handling functions.
Finally, check out the SQL Native Client blog, especially this article:
http://blogs.msdn.com/sqlnativeclient/archive/2006/09/27/774287.aspx. It
shows how to get rid of the symbols in sqlncli.h that aren't ODBC. "Since
sqlncli contains symbols for both ODBC and OLE DB, there's a chance you may
get a name clash between one of your own symbols and a symbol defined for
use by the 'other' API (the one you're not going to be using). In this case
you can add a #define to get rid of the symbols for the API you don't need.
If you're using OLE DB you #define _SQLNCLI_OLEDB_ and if you're using ODBC
you #define _SQLNCLI_ODBC_. " I think it's OLE DB symbols you're seeing
rather than .NET symbols.
Hope this helps,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:uHpEK651...@TK2MSFTNGP02.phx.gbl...
Bob Beauchemin
The query notification may outlive the program that registers it, that's
actually expected in some cases. For service pause you can pick up listening
when you active the service again. To acheive more granular control of QN
shutdown, you'd need to use service broker conversation timers that expire
(and send a "conversation expired" message, which you can use to start up
another convrsation timer). You need to use the service broker metadata to
get the conversation handle (remember that QN uses broker conversations) for
your service's conversation to issue the timer (select conversation_handler
from sys.conversation_endpoints where service = {yourservice} and
is_initiator = 1) IIRC.
Note: I've sent the listener trace and code to your email, if you don't get
it, send me email.
Here's a trace that shows how SqlDependency's listener thread does
it. Note that the use a separate listener and service to listen on the
queue. At program shutdown, these folks drop the listener service and queue.
The SqlNotificationRequest's listener is started as a separate
thread/connection/service. Then the QN (or multiple QNs) take place over
time. When the program shuts down they stop and delete the
service/connection/thread. This is a
.NET component, there's nothing similar for ODBC (or OLE DB), but it shows
what can be done. It keeps the listener and query notification separate,
even WRT connection.
You can also use sys.dm_qn_subscriptions to identify your subscription and
issue the command kill query notification subscription N, if you're
concerned about getting rid of your subscriptions on service shutdown.
Hope this helps,
Bob Beauchemin
SQLskills
"Peter" <peteATkapiti.co.nz> wrote in message
news:Onhzl3C2...@TK2MSFTNGP03.phx.gbl...
> Hi Bob,
>
> Thanks for all the pointers.
>
> I've tried using sqlncli.h with _SQLNCLI_ODBC_ defined even before you
> sent the link and got errors with some interface declarations. If I have
> to include sqlncli.h, I'll write another service that will be the only
> registrant and the other services can then communicate with that service
> through some middleware. But this is a last resort as the real application
> is multi-platform (windows service or unix daemon) and this technique will
> impose restrictions.
>
> Thanks for the SQL Trace pointers. I managed to trace all broker events,
> though I have to admit the info contained in the file doesn't make that
> much sense to me. If you want, I can send a trace file. I have attached a
> slightly altered version to deal specifically with service broker events
> and make the @trace_id more dynamic.
>
> I'm well aware that I need to check the XML from the queue and this
> doesn't worry me technically, its only that this is more extra work and
> time than I wasn't budgeting for.
>
> Well, there is some good news: using separate services and queues means I
> get all the desired notifications.
>
> What remains to be sorted is a graceful shut-down: I need a reliable way
> to cause the WAITFOR thread to return in a timely manner. This is
> particularly important when the service is paused or stopped. I thought
> I'd do that by re-issuing the subscription with a different timeout value.
> According to the manual, instead of creating a new subscription it can
> resubsribe with all the same values except the TIMEOUT which would simply
> alter the existing subscription.
>
> Unfortunately, what I found is that the TIMEOUT is another thing that
> doesn't behave as expected. Here is what I observe:
>
> 1. subscribe with a TIMEOUT of 10 seconds causes the WAITFOR an event wait
> for 65 seconds before it returns with a timeout message
>
> 2. subscribe with a TIMEOUT of 120 seconds causes the WAITFOR an event
> wait for 130 seconds before it returns with a timeout message
>
> 3. if the timeout is 120 secs and I launch a separate process resetting
> the subscription to 1 sec, the first process will stop waiting after 65
> secs provided it has been waiting for less than 65 secs. If it has been
> waiting for more than 65 secs when I reset the TIMEOUT, it will stop
> waiting after 130 secs.
>
> So it seems the minimum timeout is one Microsoft minute (or 65 standard
> seconds).
>
> This behaviour is clearly not in line with the manual which describes it
> as follows: "The number of seconds that the query notification is to
> remain active. The default is 432000 seconds (5 days). The minimum value
> is 1 second, and the maximum value is 2^31-1 seconds."
>
> Is there another way to cancel a subscription which produces a message in
> the queue?
>
> Pete
>
> "Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
> news:eG8Otj81...@TK2MSFTNGP06.phx.gbl...
Peter
I was wanting to keep things 'simple'. Therefore, the resetting of the
timeout seemes still a good option. While I'm reluctantly prepared to accept
that it may take a minute for the application to shut down, I worry that
since the timeout value doesn't behave as documented it might even worse on
other systems. I guess I'll gather experience in the future.
Thanks for the e-mail and code. I will of course do something very similar.
At startup each service instance will drop a instance specific service and
queue (currently we have 3 instances max). It the recreates these and
subscribes to query notifications. It then kicks off another thread that
wait for messages in the queue. When the service is requested to stop, it
will reset the timeout for the current subscription to the shortest possible
value (in my experience 65 secs). When the WAITFOR thread terminates, I drop
the service/queue and exit.
Maybe a future SQL Server Service Pack or Hotfix will have a fix such thet
the TIMEOUT value behaves as documented.
Cheers
Pete
"Bob Beauchemin" <no_bob...@sqlskills.com> wrote in message
news:uv1AvVD2...@TK2MSFTNGP04.phx.gbl...