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.
Goddam those pesky server client pacman video games
3 possible solutions to this at first thought:
1. Try SQL 2000 SP4 (in RC now IIRC)
2. SQL 2005 is due out in early November this year
3. Create a VPN between the 2 sites. This will allow you to do pretty much whatever you want. You could either use PPTP (tcp 1723) or IPSec (udp 500 and proto 50) (may need firewall changes, but only minor).
HTH
Thanks, Steve.
1. Already did. SP4 doesn’t fix it, either.
2. Am using 2005 beta on some of our dev. boxes – it’s really pretty cool. Don’t know if it solves this replication issue (haven’t tried replication with it yet), but in any case: it’s not out ’til November, and we can’t insist that our client uses unreleased software to which they don’t have a license. =o)
3. We considered this, but we’d also need to configure some clever routing & remote access stuff (the connection is transitory, not permanent). It’s a good idea, thanks… and I’d go for it, if I hadn’t already told the client it’s his problem and he needs to get a decent firewall (most sensible firewall solutions understand the unusual requirements of the FTP protocol and accomodate for active FTP). But if he’s objectionable I’ll look into a VPN. =o)
Thanks.