Consolidate data from many different databases into one with minimum latency

Question

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.

Answer 1

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?

Answer 2

As you have Enterprise Edition and with the data sizes, same schemas you mentioned, one approach could be to use Partitoned tables, 1 for each of the different sources on the Destination Server, Example you could use for the Partition Key the [FacilityID] column you mentioned assuming this is unqiue for each source, then just following normal data move operations that areused to move data between partition, this would then give you a consolidated table (partitioned) you sound like you are after.

Answer 3

Depending on how many tables you're dealing with it might be easier to set up some SSIS ETL packages that move the data from each of the tables. If you set up package configurations and the schema is the same you should be able to use the same set of packages for all the databases. If you have over 10-20 tables I would probably either not do the ETL packages or I would make sure that the creation of them was automated somehow.