I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows from selected tables across the 8 source servers into a single instance of those tables on the reporting server. This is one-way replication (no changes will be made to the reporting server). I will need to replicate changes from the source databases with relatively low latency (say 20-30 seconds).
Furthermore, I'd like to find methods to achieve this with as little impact to the source servers as is practical. 3rd-party agents, triggers, or schema mods to those servers are difficult in my environment.
- What are promising architectures and technologies for achieving this goal?
- I've looked at SQL Server Merge Replication, but I am concerned about latency. Is this an appropriate technology for this goal?
- Are there many-to-one architectures for transactional replication?
- Should I be looking at 1-to-1 replication into 8 databases on my reporting server, followed by some custom merge function (a 2-step replication)?