Re: Replication VS Triggers

FWIW, before ER (i.e. < IDS version 7.2) there was a 3rd-party product that implemented replication via triggers. I don't know if anyone remembers it . . . if this product is still available . . . if it it can update SQL Server . . . Hope this helps. Brice Avila Minneapolis, Minnesota "Doug Lawry" <lawry@nildram.co.uk> wrote in message news:<cbe9rd$khh$1@nntp0.reith.bbc.co.uk>... > You could buy Enterprise Gateway Manager so that you could update SQL Server > from inside Informix triggers: > > www.ibm.com/software/data/informix/tools/egm > > However, it's not cheap. Instead, you could create triggers on these 15 tables, > all using a common stored procedure to maintain the following table: > > CREATE TABLE sql_server_updates > ( > table_id INTEGER NOT NULL, > row_id INTEGER NOT NULL, > action CHAR(1) NOT NULL,> > UNIQUE (table_id, row_id), > CHECK (action IN ('I', 'U', 'D')) > ) > > Right click on "Linked Servers" in the "Security" folder of your instance in SQL > Server Enterprise Manager, and select "New Linked Server", perhaps named > "INFORMIX". This is easy to follow and only requires that you have installed > Client-SDK from > > www14.software.ibm.com/webapp/download/category.jsp?s=c&cat=data > > on your Windows machine as you will need the Informix ODBC driver. You can then > write an SQL Server stored procedure that opens a cursor > > DECLARE audit_cursor CURSOR LOCAL FORWARD_ONLY FOR > SELECT * FROM OPENQUERY(INFORMIX, 'SELECT * FROM sql_server_updates')> > to process each row in turn, getting actual rows from the 15 tables using > singleton OPENQUERY SELECT statements by ROWID, and deleting the audit row with: > > DELETE FROM OPENQUERY(INFORMIX, 'SELECT * FROM sql_server_updates') > WHERE table_id = @table_id AND row_id = @row_id> > You can schedule it to run every few minutes via an "SQL Server Agent" job in > the "Management" folder of your instance in SQL Server Enterprise Manager. > > -- > Regards, > Doug Lawry > www.douglawry.webhop.org > > > "St'phane Gadoury" <stephane.gadoury@transat.com> wrote: > > > Hi list, > > > > I need to replicated a subset of about 15 tables from IDS V7 towards a SQL > > Server. In that table count, I have 3 tables that are always in the top 10 > > in terms IO, during the day. > > > > I have 2 differents set up, which I could used. > > > > 1- Set up triggers on the required tables > > 2- Set up ER for those tables towards another instance > > > > My concern is mainly performance and ease of management (since the data will > > go populate the sqlserver DB). I would tend to think that setting up 15 > > triggers with all of them triggering on insert, delete and update would be > > "heavier" on the performance compare to seting up those tables with ER and > > having replication being set in one direction. > > > > Also, since the data would be used to populate the sqlserver, the > > replication would be easier to manage in case of a crash or any other > > failures. > > > > As usual, comments &/or suggestions are welcome. > > > > Thks ! > > > > St'phane