Re: Replication VS Triggers

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