This is a geeky post. You have been warned.
Microsoft SQL Server is one of those funky advanced database management systems that supports replication. One of even fewer that supports merge replication – where data edited and updated at two separate databases can be merged on a schedule and the changes reproduced at both ends, and any conflicts (for example, two different operators on different sites changing the same record in different ways) can be brought to the attention of an operator. This is very good.
Sadly, it’s not terribly well thought-out as far as anonymous internet replication is concerned. You see, it works wonderfully if the replicating servers are on the same Windows domain, but that isn’t always possible.
We’ve got a set-up where a client’s database engages in merge replication with a separate database on our web server. As the two servers are on different Windows domains, and because of restrictions in the firewall configuration, we’ve set up Microsoft SQL Server 2000 to perform the replication over the FTP protocol. Therein lies our first problem.
There is a bug in Microsoft SQL Server 2000 that prevents anonymous internet merge replication from working over FTP… unless you install Service Pack 3 or above… What the fuck? It took until the third service pack before they fixed this?
In any case, having fixed this, another problem rears it’s ugly head. Back in the dark ages (well, 1985), when FTP was first invented, we didn’t have firewalls. Not anything like the ones we do now, anyway. And so it made perfect sense for various technical reasons to make the FTP protocol work as follows:
Client connects to Server
Client: Hi there, Server.
Server: Hi there, Client.
Client: Can you send me file XYZZY, please?
Server: Sure can. Where to?
Client: Send it to my IP address, 1.2.3.4, on port 1026.
Server: It’s on it’s way…
When the internet suddenly became a nasty place, full of firewalls and NAT and proxy servers and restrictions, this didn’t work any more, because many clients wouldn’t accept incoming connections – they would say “send me it on port 1026”, for example, and would then refuse to listen on port 1026 (or a computer between them and the server would refuse for them). So we invented “passive FTP“, and renamed the old version “active FTP“, retrospectively. Passive FTP works like this.
Client connects to Server
Client: Hi there, Server.
Server: Hi there, Client.
Client: I’m a passive FTP client, by the way.
Server: That’s fine with me, so long as you aren’t also homosexual.
Client: Can you send me file XYZZY, please?
Server: Sure can. Because you’re passive, I’ll need you to connect to my IP address, 5.6.7.8, on port
1098.
Client: Am doing that now.
Server: It’s on it’s way…
And that works through even the most stubborn firewalls and all sorts of other restrictions. Wonderful.
For some reason known only to Microsoft, Microsoft SQL Server will not use passive FTP. And our client has a restrictive firewall. How Microsoft could have neglected to write into their program this most simple and fundamental bit of the FTP protocol is beyond me (Internet Explorer and several other Microsoft programs support passive FTP flawlessly).
Grr.