How to do MS SQL replication with 100+ hosts?

Question

SQL

We have 100+ hosts scattered around the world with Internet access at speeds from 512Kb/s to 20 Mb/s. 1 server with good network access. Everything is united in one network by means of VPN.
Each host has MS sql express (mostly 2005) 1 base 1.5-2GB. Data changes relatively infrequently.
Purpose: to have copies of all databases on the server with a relevance of 2-3 hours.
The first idea is 100+ Linked Servers per server and 100+ databases respectively. With several bases, I believe that this will work, with all 100+ I'm not sure.
How to properly replicate so many databases?

Answer 1

Perhaps you simply do not set the task correctly, and you just need to follow the path of centralization, and make one database server after all? (or two or three).

Answer 2

On all hosts, set up a cron-job on a schedule (2-3 hours), which will throw backups of the latest changes to the desired server, unpack them and deploy them to the local server as they arrive. On it, start a service that will inform all other hosts about the readiness of the data. Those. when all 100 backups are deployed to the server, the service will give an answer to the hosts that data can be synchronized, all hosts are waiting for this command, and when it arrives, start synchronization. This approach is easy to implement, but the load on the main server will be very large. You can distribute hosts to multiple servers.

Answer 3

another option is to calculate what will be more convenient to move the subd to the clouds, the same Azure, make several instances there in different regions and synchronize them with each other.
and forget about 100+ servers

Answer 4

Good afternoon,
Depending on the complexity and number of tables in the databases, I would get confused by writing upload / download interfaces. I believe that the central office distributes some master data to remote points, and receives movements / transactions back. If so, then you need to write an interface for the central database that will create files with master data changes for 100 points, distribute them over the network, and the points will already download them. And in the reverse order - the points form bundles with transactions (changes) and send them to the server, and on it the batch already writes everything to the main database (DWH). Because I don't know the background - I don't pretend to be the right answer :)
Good luck!