I have a case where many SQL Server instances need to be merged and centralized in one master database.
This needs to be near real-time solution. After looking around in Google, I’ve found that transactional replication can do the trick.
The problem is that I have (n) publisher databases, and they don’t even have static IPs. I had the impression that push replication will be enough to send the data to the centralized database without knowing about the publisher connection, but it seems like I was wrong.
My question is: How can I set up an solution to merge/replicate multiple publisher databases which don’t have static IPs, and might suffer frequent outage from the network, all that in near real-time?
A VPN connection can be used to connect to each other. But moving a huge data over Internet is done using DHCP connection, And may be slower depending on your internet connection.