database replication vs. DTS / SSIS vs. ETL
Posted on 2006-03-29
We have significant infrastructure standards being changed. We are standardizing on MS SQL Server 2005 and Oracle and for data movement, we are strongly favoring Informatica and eventually migrating to MS SSIS. The questions that I have are specifically related to the data movement aspects of our network rebuild. At this time, database replication is not even mentioned in our standards. It is obvious that database replication is a viable and most appropriate solution for many systems. How would this group recommend that I characterize the standard to identify when database replication would be used in lieu of DTS/SSIS or Informatica? There are hundreds of servers and applications and data reliability and latency are frequently critical considerations. The staff that supports the Informatica product indicates that they can maintain sub 3 second latency and full transactional consistency with very high data volumes. I am concerned about the ability to maintain the low latency while data volumes increase significantly. I also have concerns about the ability of the ETL solutions to guarantee delivery.
Even in a distributed solution, we always want asynchronous data movement between systems due to the fact that a single server or network issue cannot take other systems offline (no two phase commits).
Consolidation and virtualization are key guidelines for the new infrastructure with no discussion on isolation and distribution across systems. The solutions I am asking about would not include warehousing considerations.