I have 12 databases with a total volume of about 1.0 TB, each on a different physical server running SQL 2005 Enterprise - all with the same exact scheme. I need to unload this data into a separate single database so that we can use it for other purposes (reporting, web services, etc.) with a maximum delay of 1 hour.
It should also be noted that these servers are in the same rack, connected by gigabit connections, and that inserts into the databases are minimal (average 2500 records / hour). The current method is very wrong: data is currently being replicated (SQL Server transactional replication) from each of 12 servers to a database on another server (yes, 12 different employee tables from 12 different servers to one employee table on another server).
Each table has a primary key, and the rows are unique in all tables (each table has a FacilityID).
What are my options? There should be an easy way to do this.
NTDLS source
share