clementhuge (10/14/2010)
Hello,The article is interesting but I believe we have to avoid triggers at all price for performance and maintainability reason. I usually put a timestamp or datetime column like ModificationDate on each table I like to extract data and avoid deletion at all price as well.
No matter what and even if you have triggers, you will not have continuous flow, it is not possible.
Your job if running will not be triggered even though you send an alert to trigger it.
Overall you stay with an asynchroneous mecanism (replication is somewhat asynchroneous as well as it triggers a log to read by the disztribution and apply the changes anyway).
With my previous work, I would rather set up a datetime column and extract the data storing the extract date at the local server to filter what has to be changed. This way you can manage the frequency and you do not touch the OLTP performance of your most important server, the one upfront.
If you even want to improve it, you can add a tinyint column referring the operation (insert, update or delete). This way you know what to do when you extract.
I think that this is misconcept that "we have to avoid triggers at all price for performance and maintainability reason". We should avoid lengthy triggers and avoid place business logic in the trigger. This article is talking about very simple triggers which are helping the processes and do not posting the performance issues. We have many databases and tables with millions of rows. And this mechanism very robast. For 3 years it syncronizing data from one server to 10-12 servers. And those servers running 10,000 clients at pick time. We are not experiencing any performance hits nor any other issues for 3 years.