I have 12 databases totaling roughly 1.0TB, each on a different physical server running SQL 2005 Enterprise - all with the same exact schema. I need to offload this data into a separate single database so that we can use for other purposes (reporting, web services, ect) with a maximum of 1 hour latency.
It should also be noted that these servers are all in the same rack, connected by gigabit connections and that the inserts to the databases are minimal (Avg. 2500 records/hour). The current method is very flakey: The data is currently being replicated (SQL Server Transactional Replication) from each of the 12 servers to a database on another server (yes, 12 different employee tables from 12 different servers into a single employee table on a different server).
Every table has a primary key and the rows are unique across all tables (there is a FacilityID in each table).
What are my options? There has to be a simple way to do this.
What is the problem with SQL Server Transactional Replication, many places use it? it looks like it is set up nicely, with a FacilityID in each database?