Every table has a primary key and the rows are unique across all tables (there is a Facility ID in each table). I wasn't aware that it was acceptable to replicate multiple databases into one. My experience is that replication is typically stable but since I’ve never seen it the “many-to-one” replication I’m assuming that it was causing the problems.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.To review the document, please download the Consolidating Databases Using Virtualization Planning Guide Word document.
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. 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). It "gets out of sync" about once a week and takes an entire day to re-snapshot all of the databases.
I saw this post: Consolidate data from many different databases into one with minimum latency I didn't understand all of this so let me ask like this: There are some table who have primary keys but don't have source ID, example: Data Base 1 what are you trying to accomplish (application consolidation, reporting, warehousing)?
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.