Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.I think the problem is when your client polls the Q and does a RECEIVE, the
message is lost, thus if another client is also looking for the same
message, it will not be there.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
> Hello.
> I'd like to reveive notifications on the client application when table
> data is changed.
> How could I use Service Broker, queues and services? In my tests I
> have test queue (NotifyTestQueue), test service (NotifyTestService).
> If only one client application instance is running - all works fine.
> Application runs sql query
> WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
> in the separate thread and thread execution is continued after message
> in the queue is available.
> But if I run two ore more client application instance only one of them
> receives notification :(
> Help me please - how could I set up service broker or queue or service
> to enalbe notification for several client about the same event in the
> queue?
> Should I give up and use individual table with records as events for
> clients?
> Thank you.
>|||Consider using Notification Services.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140520111.673015.124620@.z14g2000cwz.googlegroups.com...
Hello.
I'd like to reveive notifications on the client application when table
data is changed.
How could I use Service Broker, queues and services? In my tests I
have test queue (NotifyTestQueue), test service (NotifyTestService).
If only one client application instance is running - all works fine.
Application runs sql query
WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)
in the separate thread and thread execution is continued after message
in the queue is available.
But if I run two ore more client application instance only one of them
receives notification :(
Help me please - how could I set up service broker or queue or service
to enalbe notification for several client about the same event in the
queue?
Should I give up and use individual table with records as events for
clients?
Thank you.|||The easiest thing is to use QueryNotification which is one of the new
features within SQL Server 2005.
HTH, jens Suessmeyer.|||Hello Jens.
I've use query notification but it works only for last client. Here is
the code:
code:
private static void NotificationTest()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"SELECT GenderID, IsActive, DescrLocal FROM dbo.cls_Genders",
connection);
SqlNotificationRequest notifyRequest = new SqlNotificationRequest();
notifyRequest.UserData = "Any User Data";
notifyRequest.Options = "service=NotifyTestService";
notifyRequest.Timeout = 600;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
Console.WriteLine(rdr[0] + "\t" + rdr[2] + "\t" + rdr[1]);
}
Console.WriteLine("notifyRequest was sent");
Console.ReadKey();
WaitForChanges();
}
private static void WaitForChanges()
{
using (SqlConnection connection = new
SqlConnection(_connectionStringLocal))
{
connection.Open();
SqlCommand cmd = new SqlCommand(
"WAITFOR (Receive convert(xml, message_body) from NotifyTestQueue)",
connection);
cmd.CommandTimeout = 0;
object o = cmd.ExecuteScalar();
Console.WriteLine(o);
}
}
So after key is pressed and event is fired on the server only one
client gots event.|||I should explain what is happened with the code above.
So when one client executes NotificationTest method all works fine.
Client gets notification from the SQL server.
But when two or more client were started only one (last started)
receives notification - all others don't. As SriSamp says one client
application polls the queue and other clients miss event. How could I
leave message in the queue when RECEIVE statement is executed? I cann't
find anything that may help me with it :(
Thank you.|||A message can only be dequeued once. There's no way around this.
What you want to do is to multiply that one message into N messages, for the
N clients. Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN. Create activated
stored procedure on the NotifyTestQueue that when it receives a message it
forwards this message to all clients (by SENDing the message N times). This
is basically a pub-sub system implemented on top of dialogs. A practical
example on how to do this is at
http://blogs.msdn.com/remusrusanu/a.../12/502942.aspx
Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification, each
of these statements will block one thread on the server, and you'll hit the
upper configured limit max_worker_threads
http://msdn.microsoft.com/library/d... />
g_09wu.asp
If you need a large number of clients the solution must involve partitioning
out the N client services/queues on a number of different SQL Instances,
since SSB can deliver the notification remotely.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140536881.331233.150770@.f14g2000cwb.googlegroups.com...
>I should explain what is happened with the code above.
> So when one client executes NotificationTest method all works fine.
> Client gets notification from the SQL server.
> But when two or more client were started only one (last started)
> receives notification - all others don't. As SriSamp says one client
> application polls the queue and other clients miss event. How could I
> leave message in the queue when RECEIVE statement is executed? I cann't
> find anything that may help me with it :(
> Thank you.
>|||Hello Remus.
Thank you for fast reply.
Can I use RETENTION = ON parameter on queue?
> Instead of clients waiting on the NotifyTestQueue, each client
waits on a dedicated queue, NotifyClient1...NoifyClientN.
Its not suitable for me - I don't know how many clients will be
connected to the database at future.
> Note that you cannot do this if you have too many clients (hundreds). If
each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
each
of these statements will block one thread on the serve
I'd like to perform RECEIVE without WAITFOR from time to time in the
separate thread on the client.
It seems to me that there is still only one method to notify connected
clients that changes in data occured. Fill some special notify table
and polls this table from time to time at the client. Is it true?
Thank you.|||See inline
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"oleksa borodie" <oleksa.borodie@.gmail.com> wrote in message
news:1140594793.299405.111750@.g44g2000cwa.googlegroups.com...
> Hello Remus.
> Thank you for fast reply.
> Can I use RETENTION = ON parameter on queue?
Remus: No, retention does not allow for a message to be receive more than
one time. Retention keeps a copy of the message in the queue if compensation
is needed, but this copy is not receivable.
> waits on a dedicated queue, NotifyClient1...NoifyClientN.
> Its not suitable for me - I don't know how many clients will be
> connected to the database at future.
Remus: That's manageable, clients can create a queue and then
subscribe/unsubscribe. Separate queues allow for clients to fan out and be
partitioned on several machines for scalability.
>
> each client has a WAIFOR(RECEIVE) blocked waiting for a notification,
> each
> of these statements will block one thread on the serve
> I'd like to perform RECEIVE without WAITFOR from time to time in the
> separate thread on the client.
Remus: Any reason for this? Pooling is in general worse than WAITFOR. Note
that
WAITFOR (RECEIVE) does not pool, it uses a notification mechanism.
> It seems to me that there is still only one method to notify connected
> clients that changes in data occured. Fill some special notify table
> and polls this table from time to time at the client. Is it true?
Remus: No, this is not true. Pooling for changes or pooling for change
notifications is limited in scalability. There is the potential to overload
the server with just answering the queries that are pooling for changes. The
way to address this is to fan out change notifications for each subscribed
client.
You can actually decide which approach is better, based on the specifics of
you case. Some back of the envelope cacluclation will give you a rough
answer:
First, lets figure out the pooling case. Consider you have N clients, each
pooling at an interval of t seconds and the pooling query batch consumes r
seconds on the server. On any interval of time T you are gonna consume a
total pooling runtime on the server R = r x t/N x T . The R/T ratio gives
how much of the total server runtime is consumed serving the pooling
queries. So basically the r x t/N number is giving you this ratio.
In the case of WAITFOR (RECEIVE), you're gonna have N clients blocked on the
server waiting for notifications. Considering each individual client is
going to be notified (per second) with a probability p and the server
runtime consumed for seting up the WAITFOR, delivering the notification and
returning the result is w, then the total runtime consumed on the server
during the interval T will be w x p x T x N. Again, dividing this by T gives
you the ratio of server runtime consumed in servicing these notifications.
So basically the ratio is w x p x N.
So in the end you're left comparing the r x t/N versus w x p x N.
If you consider the pool query runtime r as being based on a SELECT over one
clustered index and the WAITFOR w time as a RECEIVE from a queue, a rough
approximation of w vs. r should be w = 3 x r (maybe 4 x r), because the
WAITFOR will execute a similar scan as the SELECT, but twice (once before
suspending itself, once when is notified) and it also executes a write
operation (the dequeue)
So is all about comparing t/N vs. 3 x p x N.
- smaller t means clients cannot tolerate dirty cache, they need to pool
frequently, larger t means clients are OK with presenting potentially
outdated data. A large t favors the pooling model.
- larger p means the data is updated often and the cache will be invalidated
often for each client, smaller p means the cache invalidation is unfrequent.
A small p favors the WAITFOR.
Is also important if there is a corelation between p and N. If
each client is interested in a separate partition of the possible data
changes, then increasing N will likely decrease p, favouring the WAITFOR (or
at least hurting it less than the pooling). If all clients are interested in
same changes p does not decrease with an increase of N, this case hurst both
models.
Also, given a sufficient large N both models will crumble as the ratio
approaches 1, meaning all of the server resources are required just to
service the notifications. This case must reside to a fan-out mechanism the
WAITFOR model is better suited for.
> Thank you.
>|||Hello.
I cann't ever express how you post helps me. Thank you very much.
I should read more about Notification services and how it could be
connected with Service borker.
Good luck.
