1

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?

TT.
  • 15,774
  • 6
  • 47
  • 88
Lamar
  • 1,761
  • 4
  • 24
  • 50
  • Interesting topic, though wrong SO. Try the one for [database administrators](http://dba.stackexchange.com) – clifton_h Oct 31 '16 at 10:55
  • @clifton_h Hi Clif, to avoid cross-posting it is best to vote to close the question and move to dba. – TT. Oct 31 '16 at 11:13
  • @TT. True, but that would happen whether i wrote this or not. My goal is to educate,rather than answer. – clifton_h Oct 31 '16 at 11:15

1 Answers1

0

Replication is picky about server names. In my experience, if you use anything but what select @@SERVERNAME; returns, something will break. That said, as long as the servers in the topology can resolve that name to your (dynamic) IP through DNS, I can't think of a reason why your configuration wouldn't work. Have you tried it? If so, what errors have you seen?

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I haven't tried it yet. The publication servers are not actually hosted with a domain. They are located in laptop keep moving (geographically). – Lamar Nov 01 '16 at 06:47
  • I see. In that case, you may want to check out merge replication. It is designed for the scenario your describing (multiple frequently disconnected clients, one central place to synchronize) – Ben Thul Nov 01 '16 at 12:14
  • Can we do merge replication without the master db knowing about the publisher db connection? – Lamar Nov 01 '16 at 13:16
  • In merge replication, the role names are flipped. That is, your central server would be considered the publisher and the disconnected clients the subscribers. I'd suggest reading up on that too see if it matches your situation. I think it does. – Ben Thul Nov 01 '16 at 13:30
  • From your description it doesn't seem to help. The data is being added to the disconnected clients, and needs to be merged instantly into the centralized database. I need to read about merge replication further though. – Lamar Nov 02 '16 at 06:27
  • Pardon my ignorance, but if the client is disconnected, how is it going to upload instantly? The classic use case for merge replication is a bunch of stores that upload data to a central headquarters once a day. – Ben Thul Nov 02 '16 at 12:15
  • Sorry if I got you confused. They will be connected as long as possible, but there might be outage occasionally, and I need to "synch" the data as soon as they are connected back again. – Lamar Nov 03 '16 at 07:39