What I want to do:
Client database (C) is SQL Server 2008, has schema X
My database (D) is PostgreSQL 9.1, has schema Y
I want to transfer data from specific tables in C to D in near real time.
Create DML trigger for each table I want to query
That DML trigger will query
updatedtable and retrieve the data, form an xml and save it in the Service Broker queue
A script/ application that would read the Service Broker message queue and send them to D
Questions I have:
If the DML trigger I created throws an Error the transaction will not be committed. That is really bad. Is there a way to prevent it? or is there a way of sending the inserted data to the Service Broker via other means?
I am really looking for stability and reliability. In case Service Broker can't provide it, does polling the db makes sense?
Open to suggestions if my approach is completely wrong (for instance CDC (change data capture) is an option but the client server might sometime be SQL Server Express, so it's not an ideal solution).