The Old Joel on Software Forum: Part 3 (of 5)

Exchange like architecture

We are looking to start a project that will involve real time sending of alerts and messages etc. but in relation to a database system we will be developing as part of the project. For example their needs to be a notification go to a certain person when a certain event / type of record is entered into the database (i.e. >$5,000 cost record should alert CFO and CEO).

Because we are an ISV whose software must work in a variety if environments, the only assumption we can make is that they will be running a Windows 2000/2003 server - we cannot leverage the existing infrastructure of exchange or any other vendors products for example.

At the moment my thoughts are to develop a system similar to exchange in 3 components:

1. ASP.Net web application for the database entry side (not high use so Desktop application isn't necessary)
2. A WinForms management application that would run on the server side (or on the client for administration of the server). This is where the users would configure the database event rules (such as the $5000)
and 3. A Service/Application etc running on the server monitoring the database for the rules that have been configured and sending out alerts etc. Also doing other tasks on regular basis such as uploading data from other systems.

We have 1 and 2 sorted, very similar to existing projects we are already doing, but no 3 the actual services on the server doing the database monitoring is where we have some questions:

- Is there anywhere on the net that might explain the design of a system such as this (exchange for example)?
- Does anybody on the board have any experience in this sort of system and can provide pointers where we may experience issues?
- Lastly, my initial thought was to raise an event on the ASPX page when the user saves a record for example that would communicate with a service on the server that is waiting to be woken to run through all the rules that have been set and see if they apply to the new record.

Would a .Net Windows Service be suitable for this sort of thing or should we be looking at other architectures altogether? And if a Windows Service is the best way to do it, what is the best way to "wake it up" - it needs to be able to be called remotely (for example the ASPX pages may be on WEBSERVER1, and the Services could be APPSERVER1) - I have thought of using some sort of remoting to call into it

ChrisO
Tuesday, November 25, 2003

Consider MSMQ:
http://www.microsoft.com/windows2000/technologies/communications/msmq/

It ain't new (i.e. it's reasonably mature) -- at the simplest level it can be considered as "email for apps".

Duncan Smart
Tuesday, November 25, 2003

"Lastly, my initial thought was to raise an event on the ASPX page when the user saves a record for example that would communicate with a service on the server that is waiting to be woken to run through all the rules that have been set and see if they apply to the new record. "

You should have a look at "Rule Engines." This is the kind of thing they are designed to do. Here are a couple examples:

Haley Enterprises (one of the pioneers)
http://www.haley.com/1273510216369154/products.html

JESS - a good rule engine in Java
http://herzberg.ca.sandia.gov/jess/

There are plenty of others out there, and some decent pointers available for rolling your own basic implementation, too. Google for "Rete" or "Rule Engine"

rule breaker
Tuesday, November 25, 2003

If you happen to be running SQL Server 2000 for the database server you might want to look into SQL Server's "Notification Services":

http://www.microsoft.com/sql/ns/default.asp

If not you might want to look at it anyway to crib design ideas.

Mr. Fancypants
Tuesday, November 25, 2003

http://www.jabber.org/
http://www.jabber.com

Frederic Faure
Tuesday, November 25, 2003

Why do people no longer use hyphens? I had to read that subject line twice before I figured out what the author meant. Far better to have written, "Exchange-like architecture".

Your English teacher
Tuesday, November 25, 2003

Thanks all for the tips and the english lesson.

I had never heard of this RETE algorithm, it looks like an interesting thing. Although it seems like it could get very complex very quickly, it may be overkill for our needs. But the SQL Server notification services looks interesting, anybody know if something similar exists for Oracle 9?

ChrisO
Wednesday, November 26, 2003

I have designed a few of these types of systems that have ranged from simple to reasonably complex. A few things I have found useful in the past, given your requirements:

1. Building a rules engine that utilizes XML can be effective since SQL Server has some awesome XML capabilities. It makes it easy to bulk operations.

2. NT Services can be your friend, depending on your needs.

3. Scheduled Tasks often work just as well.

4. Using COM+ events can be effective. By building a data layer using COM+ you can raise events when certain criteria are met. I wouldn't suggest this route unless you have some seriously complex requirements and a lot of time.

5. Triggers are an alternative to COM+ events, if your requirements aren't too complex.

6. We've had problems with MSMQ in the past, but it is my understanding that Server 2003 has solved some of these issues. Over time, we have decided that storing messages in a table is more effective than storing them in queues.

7. Your distribution mechansim will likely be your biggest difficulty --> firewalls, internal corporate standards and the like often impede the best ideas. Webservices have worked pretty well for us. .NET makes this pretty easy with a good firewall on the front. Depending on the sensitivity of your data, authentication can be a tricky task.

Good luck!

Canuck
Wednesday, November 26, 2003

Maybe you could look into reusing most of Notification Services by just writing an custom Event Provider for your application. this could seriously reduce the amount of code you would have to maintain.

Just me (Sir to you)
Wednesday, November 26, 2003

>But the SQL Server notification services looks interesting, >anybody know if something similar exists for Oracle 9?

DBMS_ALERT is your friend in Oracle.

Cut from the Oracle Package description in the database:

This package provides support for the asynchronous (as opposed to polling) notification of database events.
By appropriate use of this package and database triggers, an application can cause itself to be notified whenever values of interest in the database are changed.

Patrik
Wednesday, November 26, 2003