Guidance on inter-server database or data transfer and system architecture I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, the business would like me to co-locate these databases on one query server; however for data loading purposes (periodic large bulk loads) it is convenient to maintain the 1 database/server model. For some databases the tables are truncated and reloaded on a monthly basis; for others, new data is populated on top of existing data via daily jobs. How do you recommend that I transfer data from the source databases to the query server? I want to maintain control over when the data is transferred.  Upgrading to a newer version of SQL Server is possible.
You could use Availability Groups for this, which I've done in the past. You would have 20 AGs, and each would have the query server as a replica, probably an async replica. You could also use replication for this, especially since it's unlikely that the users need all tables in every database. I don't like supporting replication though. Maybe the query server would be a Data Warehouse.