Load balanced environment and use of END CONVERSATION causes issue

[smillerk2]

Hi Dmitrii,

If I have more than 1 server listening for the event notification, I am finding that the first server "wins" and other servers do not receive the notification event.

Is this because of:

BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

Should that line be removed so that all servers receive the message or is there a setting I am missing?

[cdfell]

In our case we implemented GoF observer (design) pattern in lieu of
changing SqlDependencyEx. All registered observers get the event, you can
version up SqlDependencyEx without too much worry about integrating
site-specific changes, and adding new observers is fairly straightforward.

http://www.dofactory.com/net/observer-design-pattern

On Tue, Aug 23, 2016 at 1:10 PM, smillerk2 notifications@github.com wrote:

Hi Dmitrii,

If I have more than 1 server listening for the event notification, I am
finding that the first server "wins" and other servers do not receive the
notification event.

Is this because of:

BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

Should that line be removed so that all servers receive the message or is
there a setting I am missing?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#29, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AE9of3UX4DIxjBCWkzLTM51qjto5pJYMks5qizeHgaJpZM4JrOJ-
.

[smillerk2]

Thanks for the quick response - much appreciated. So if I understand correctly, you're saying that if Server 1 calls

BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

Server 2 will still pick up the notification and call the same END CONVERSATION?

Is there anyway to verify if that is happening easily as it doesn't look like this is occurring for me?

[cdfell]

You make no changes to SqlDependencyEx software implementing 'observer'
design pattern.

I made an assumption that could be wrong about your implementation of
SqlDependencyEx. I assumed you wrote a C# listener (server) that registered
with SqlDependencyEx to receive table change events.

The registered listener of table change events is the 'subject' in an
observer design pattern implementation, and your first and second servers
become registered 'observers' of the 'subject'. When you apply this pattern
you can have 1 or 100 observers, all getting notified of the table change.

I included the link not with the intent you purchase software, but had in
mind you could use their excellent examples to implement your own solution.
In the end, your question about second server picking up the notification
and calling END CONVERSATION becomes moot.

cf

On Tue, Aug 23, 2016 at 1:38 PM, smillerk2 notifications@github.com wrote:

Thanks for the quick response - much appreciated. So if I understand
correctly, you're saying that if Server 1 calls

BEGIN TRY END CONVERSATION @ConvHandle; END TRY BEGIN CATCH END CATCH

Server 2 will still pick up the notification and call the same END
CONVERSATION?

Is there anyway to verify if that is happening easily as it doesn't look
like this is occurring for me?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#29 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AE9of4bsdoaM4cGrnTSyhgOmdSnyylfNks5qiz4rgaJpZM4JrOJ-
.

[dyatchenko]

Hi @smillerk2,

As far as I understood you have a database and many clients which listen to the same table. It might not be a good idea to use the component as a basis for a client-server application, but if you want to get notifications simultaneously on many clients you should create a SqlDependencyEx instanse for each client with different identities. It is not recommended though. By creating an instanse of the component you create a DML trigger and a notification queue which might cause performance problems if there are many clients. The best practice here would be to have a single super client which receives every event from the db and many minor clients which receive notifications from the super client instead of the database.

Let me know if there is a misunderstanding or you have other questions.

Thanks,
Dmitrii

[smillerk2]

Hi Dmitrii,

That is very helpful. I "only" have 2 clients (web servers in a cluster) and they both need to listen to specific tables as you have described. There are about 20~ tables of interest that currently do have a DML trigger and their own notification queue so if I opt for unique identities then this will obviously double in size.

Although not optimal, this might be less work than refactoring to use a super client as I know I will never have more than 2 clients. So, I think my SQL 2014 with 64Gb RAM should be able to handle 40-50~ queues on a database. Do you agree?

Great library by the way.

[dyatchenko]

Hi @smillerk2,

It might not be possible to implement. As far as I remember there is a converstation queue which is basically a table of some kind and a special SQL server instruction - WAITFOR RECEIVE TOP which hangs the thread from which it has been called until there is a message (aka a row) appeared in the conversation queue. That instruction will relase the calling thread every time when something is in the conversation queue. If you don't delete a conversation message from the queue when you receive it in C# - you will receive the same message again in the next loop cycle. Here is the question: WHO should cleanup the conversation queue? If you have two clients which of them should clean it up? How can a client be sure that other clients have already received the message and confidently remove it?
There is a possible solution with timeouts though. E.g. one client don't clean the queue at all, but other client removes messages with a timeout to let the first client receive everything. That would work but nobody guaranties that the first client fires UNIQUE messages only (duplicates are likely possible).

Thanks,
Dmitrii

[cdfell]

Hi Dmitriy - I'm not sure who this comment is directed to, I apologize if
this comment is taken out of place.

If directed to me, and the context of discussion is implementation of
observer pattern, then you are confused. This pattern is not implemented
until after the registered 'listener' recieves the table change event,
therefore your concern for message being removed from 'conversation' queue
is moot.

We have used this pattern in in production capacity, at a large Fortune 2
company, with SqlDependencyEx for over a year, and it has been a smashing
success. I think you misunderstand what has been done.

I am all to happy to discuss technical details, please let me know.

Curtis

On Wed, Aug 24, 2016 at 9:38 PM, Dmitriy Dyatchenko <
notifications@github.com> wrote:

It might not be possible to implement. As far as I remember there is a
converstation queue which is basically a table of some kind and a special
SQL server instruction - WAITFOR RECEIVE TOP which hangs the thread from
which it has been called until there is a message (aka a row) appeared in
the conversation queue. That instruction will relase the calling thread
every time when something is in the conversation queue. If you don't delete
a conversation message from the queue when you receive it in C# - you will
receive the same message again in the next loop cycle. Here is the
question: WHO should cleanup the conversation queue? If you have two
clients which of them should clean it up? How can a client be sure that
other clients have already received the message and confidently remove it?
There is a possible solution with timeouts though. E.g. one client don't
clean the queue at all, but other client removes messages with a timeout to
let the first client receive everything. That would work but nobody
guaranties that the first client fires UNIQUE messages only (duplicates are
likely possible).

Thanks,
Dmitrii

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#29 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AE9of5xQRCpTLi3kcrhw0GGvw8gTD8D1ks5qjQAmgaJpZM4JrOJ-
.

[dyatchenko]

Hi Curtis,

Sorry for the churn, but the comment was addressed to @smillerk2 as an answer on his last question.
Glad to hear that the component is helpful!
The Observer pattern is obviously the right solution here. That's what I'm also suggesting.
Let me know if you have any questions or problems with the component.

Thanks,
Dmitrii

[smillerk2]

Thanks Curtis and Dmitrii for the discussion. So, summarizing, we can say that my original idea of not ending the conversation is an extremely bad one because of what Dmitrii says in his last post around who ends the conversation and if it's not ended it would simply remain on the queue.

But in an earlier comment from Dmitrii he did say that using multiple unique identities would work as this would result in multiple triggers being created per table, each with their own queue, but it just wouldn't scale very well.

[dyatchenko]

Hi @smillerk2,

Yes, the solution with different identities would work but it might cause performace problems. Hovewer, if you have two clients only and you are not going to increase this number - that might not be a big problem. You can try it at least and see how it goes. But the observer solution is definitely better.

Thanks,
Dmitrii

[shan75]

Hi I am coming to this thread after one year it was closed. Not sure anyone still can help me. I downloaded this and I can see the table is being watched. I am trying to make it work for multiple listeners. There is a mention about some links for samples, may I know is there any sample example exist for multiple listeners

Please advice. I would appreciate if some one can get back asap?

Many Thanks
Shan

[shan75]

Sorry I mean examples with Observer being implemented. Can some one post the solution or point me in the right direction if any such samples exist.

Thanks
Shan

[shan75]

Also is it possible to set for example notify observer1 only for certain criteria, like AccountId <= 25000
notify observer2 only for certain criteria like AccountId > 250000 and AccountId <= 500000
and so on this way, I don't need to worry about checking each notice in all the observers? Is it possible? How to achieve this?