Proper use of Event Notifications

Hi.

I'm developing an app that uses Service Broker queues to allow a customer to create "events" that fire using a timer or a query notification. When these events fire, a message is sent to a Service Broker queue for processing. Because there is much managed code involved in processing these messages, I decided to use the External Activator application and an Event Notification to process these messages. My question is "what is the difference between using the External Activator application to launch another application (which simply RECEIVEs a message from the target queue and processes it) and creating a windows service that simply monitors the target queue (with a WAITFOR = -1 clause) and processes it?"

I guess I'm not sure how using the QUEUE_ACTIVATION Event Notification is really helping me.

Thanks,

Chris

If all you need is a single instance of your service and don't mind it running all the time, you could implement this as a Windows Service that does a WAITFOR with no timeout. But if you want multiple instances of your service to be dynamically activated depending on the rate of incoming messages and how quickly your service is able to consume them, the external activator becomes useful. The main purpose of the external activator is to make services scalable.

The external activator is also capable of monitoring multiple queues, each configured with its own service program. So if you had 10 services, you do not need to have 10 windows services running even when queues are idle. You will have a single external activator running which will dynamically launch the service programs as messages arrive.

Hope that helps,

Rushi

|||

Rushi,

After doing some more digging into the External Activator, I understand more clearly now. It seems that the scalability benefits are the real key for us. That and doing a WAITFOR with an indefinite timeout isn't so easy in a Windows Service.

Thanks,

Chris

|||The external activator does some of the hard things, like maintaining a recovery log so that if the process was to terminate and it came back up, it would recover state and not miss any notifications thus ensuring that queued messages do not get orphaned.

Propagation of event notification when tables are updated.

I would like to propagate an event signal to an external application
when a table in my MSSql2000 server is updated.

Prog A; I have an external application adding records to a table.
Prog B; I have another external application using this table as well.

When Prog A creates a new record in the Table, how can I have Prog B be
notified of the event without polling the table or creating a link
between Prog A and Prog B.

Thanks.> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event

Depends what you mean by "notified". If you want to invoke some code within
the current process (B) then the obvious way is to have B read a table or
watch for some other event and then act accordingly. What exactly do you
want to achieve? Is this simply about optimistic locking?

--
David Portas
SQL Server MVP
--|||dubian (collatz@.bigtexansoftware.com) writes:
> I would like to propagate an event signal to an external application
> when a table in my MSSql2000 server is updated.
> Prog A; I have an external application adding records to a table.
> Prog B; I have another external application using this table as well.
> When Prog A creates a new record in the Table, how can I have Prog B be
> notified of the event without polling the table or creating a link
> between Prog A and Prog B.

By far, the easiest way is to poll.

The other way would be to have a trigger on the table, that fires of an
extended stored procedure or OLE object to somehow send a singal to
Process B. Since extended stored procedures and local OLE objects in
the same memory space as the rest of SQL Server, they are somewhat
dangerous: if they crash on an access violation, the entire server
goes belly-up.

I believe that you also can use the sp_OAxxx routiens to start an
OLE object on a remote server. In this there is less risk for crashes.
But this is like to take time, and when you are in a trigger you are
in a transaction hold locks. If the update frequency is high, this
trigger can kill your throughput.

A variation is to have the trigger to write to a table, and then
run a job from SQL Server Agent that reads the table and alerts the
other process. Such a job would run once a minute or so. Since this job
could be an ActiveX task you could signal with XP:s and that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp