We are developing a system that must extract data from an existing SQL Server 2008 R2 cluster. One of our design goals is to limit the performance impact on that cluster as much as is practical.
Our system must have access to data that is < 1 minute old. So we are considering both a straightforward SELECT from the source clusterexecuted at regular intervals (say every 20 seconds), or alternatively, establishing transaction replication from the source cluster to our own system for subsequent processing.
We have a test instance of the cluster, and I would appreciate any recommendations on how to measure impact on the source cluster for each of these functions for the purposes of making an architectural decision.
I'm familiar with the use of the profiler, and that seems like a reasonable tool for analyzing the impact of the SELECTs, but I am running into difficulty when trying to find similar impact data for replication.
Thanks! John Jeheimer