database watcher ... need suggestions !!

Hi all,

i'm trying to develope a windows service which will listen on a database table (Oracle database) , when a record updated ,the service will store it on another database . (Sql Server database)

the question is what is the best methodology to detect database update

my idea is to run a query periodically and check the update

is anyone have a better solution

thanks.

Answer this question

database watcher ... need suggestions !!

KIPREAL

The key question here is, do you need every single version of the data
paddyO
I also think that using a trigger would be the best idea. you can always check the database for changes every certain amount of time using checksums.
Leaf.

Hi Hosam,

Sorry I missed that important fact, here is a url describing the differences between Oracle 10g and Sql Server 2005, it might be a bit biased though :) It has a section on the Oracle equivalent of Niotification Services.

http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/SQL2005andOracle10gasDBPlat.doc#_Toc109726831

HTH

Ollie Riches
Mike&#33;

Hi Ollie ,

i agree with you on this solution if the source database is sql server but in my case the source database is Oracle .. .!!
Ken Villines
i need to fetch only the updated record which matches a certain criteria !
Phonics3k

what will be the solution if i don't have an access to the oracle database ... only read access
Jun_1111

IMO the best way to achieve this would be to have the database fire a message back to your windows service when a change occurs that you are interested in, i.e. your service responds to event from the database, a notification service.

Now this is very easy when using .Net 2.0 & Sql Server 2005 because you can use the SqlDependency Class

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx

This utilises Notification Services in Sql Server 2005, which is a much better implementation than the equivalent in Sql Server 2000, which I believe uses triggers to perform notifications.

http://www.microsoft.com/sql/technologies/notification/default.mspx

HTH

Ollie Riches
Brandon T Perry

If you want to catch every version then you will need to use a trigger on the Oracle to post it into another table (which could be inside SQL Server). If you only run a query periodically then you may not catch every version. This will effect performance.

However if you don't need to have every version and only want updates every half hour or so, have you looked at an ETL tool

http://msdn2.microsoft.com/en-us/library/ms141026.aspx talks about SQL Server Integration Services (SSIS) , which may be useful.
database watcher ... need suggestions !!

Answer this question