I'd like to build an application that will react to specific changes to
data in a set of tables in a database. The application would replicate
these data changes to another database. The target database won't be
SQLServer. Neither is this simple replication, at times the application
will need to get extra data from the source database before the target
is updated.

In other DBMS systems I am involved with the DBMS has the facilty to
write to an application message queue so that the monitoring application
only has to monitor the queue rather than a database. What I'd like to
do is something like this:

1. Some application changes data in a table.
2. A trigger reacts to the change and writes a message to an application
queue.
3. A windows service/process monitors the queue and picks up the
message. It then carries out whatever replication/DB actions are necessary.

This would mean defining a number of new triggers on existing tables and
developing the windows service/process. Existing applications and the
existing tables in the database would remain unchanged. I'm not a
windows programmer but I have someone in my team who is and who will
build the windows service/process.

The bit I'm unsure about is how a trigger can write to an application
queue or communicate with the windows service/process. I may be using
the wrong terminology as I have more knowledge of Unix than Windows.

Could anyone help with how I can do this or suggest any alternative
strategies.

Thanks In Advance.

Laurence

You can get all the queuing infrastructure for free. SQL Server 2005 includes a functionality called
"Service Broker", where your trigger does SEND to write to the queue and the service uses RECEIVE to
read off of the queue.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Laurence Breeze" <i.l.breeze@open.ac.uk> wrote in message news:43A2EAFC.5020500@open.ac.uk...
> I'd like to build an application that will react to specific changes to data in a set of tables in
> a database. The application would replicate these data changes to another database. The target
> database won't be SQLServer. Neither is this simple replication, at times the application will
> need to get extra data from the source database before the target is updated.
>
> In other DBMS systems I am involved with the DBMS has the facilty to write to an application
> message queue so that the monitoring application only has to monitor the queue rather than a
> database. What I'd like to do is something like this:
>
> 1. Some application changes data in a table.
> 2. A trigger reacts to the change and writes a message to an application queue.
> 3. A windows service/process monitors the queue and picks up the message. It then carries out
> whatever replication/DB actions are necessary.
>
> This would mean defining a number of new triggers on existing tables and developing the windows
> service/process. Existing applications and the existing tables in the database would remain
> unchanged. I'm not a windows programmer but I have someone in my team who is and who will build
> the windows service/process.
>
> The bit I'm unsure about is how a trigger can write to an application queue or communicate with
> the windows service/process. I may be using the wrong terminology as I have more knowledge of
> Unix than Windows.
>
> Could anyone help with how I can do this or suggest any alternative strategies.
>
> Thanks In Advance.
>
> Laurence
>

Thanks Tibor,

We currently run SQL Server 2000 but will upgrade to 2005 in time. If
there's no way to do this in 2000 I'll have to wait a bit.

Thanks Again.

Laurence


Tibor Karaszi wrote:
> You can get all the queuing infrastructure for free. SQL Server 2005
> includes a functionality called "Service Broker", where your trigger
> does SEND to write to the queue and the service uses RECEIVE to read off
> of the queue.
>

There is nothing that stop you from doing this now. Just have the trigger write to a table and have
the app polling the table, reading off of it and removing the rows that has been handled. But, I
think you should look into Service Broker as it provide you with so much infrastructure code. At
least grab a book and read about SB (or play with it), so you can make the right decision. I
wouldn't be surprised if you in the end decide to wait until 2005 is implemented to cut down on the
dev time for this (thanks to Service Broker).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Laurence Breeze" <i.l.breeze@open.ac.uk> wrote in message news:43A7CA1D.6060804@open.ac.uk...
> Thanks Tibor,
>
> We currently run SQL Server 2000 but will upgrade to 2005 in time. If there's no way to do this
> in 2000 I'll have to wait a bit.
>
> Thanks Again.
>
> Laurence
>
>
> Tibor Karaszi wrote:
>> You can get all the queuing infrastructure for free. SQL Server 2005 includes a functionality
>> called "Service Broker", where your trigger does SEND to write to the queue and the service uses
>> RECEIVE to read off of the queue.
>>
>