Can I do SQL Server 2005 merge replication through an SSH tunnel?

Question

Our usage case is a database responsible for accounts, sessions, licenses, etc. — it needs to be polled upon client startup, so high reliability is crucial. As such, we'd like to replicate across different servers in physically separate networks, just in case an entire datacenter happens to go down. I have successfully set up a push subscription to another server in the same network, so this generally works. It's the connection to servers outside the network that I need to get working now.

Replication doesn't work directly across networks. Microsoft suggests either a VPN or web replication. The latter strikes me as inefficient (and they do say "Note: Web synchronization is designed for synchronizing data with portable computers, handheld devices, and other clients. Web synchronization is not intended for high-volume server-to-server applications.")

The former, a VPN, would work, but I still consider it a needlessly complex solution (not to mention the added potential for security issues) to continuously run a VPN connection. I'd much rather just run an SSH tunnel. Yet, I can't find any article that explains how to do this and isn't about MySQL.

I've got an SSH server set up on the subscriber, and have used PuTTY to establish a tunnel from <publisher>:1434 to <subscriber>:1433. I cannot, however, get SQL Server Management Studio to connect this way (I'm not sure it supports the : port syntax). I've also set up an alias on <publisher>'s SQL Server Configuration Manager, with @@servername of <subscriber>, pointing to localhost:1434. Connecting to that doesn't appear to work either.

I used the push subscription option, so I figure the publisher needs to connect to the subscriber, not the other way 'round.

Both servers use the default instance, and run SQL Server 2005 SP 2 on Windows Server 2003 SP 2. The SSH server on the subscriber is freeSSHd 1.2.1.

Is this possible at all? Is there another way I can leverage SSH to pretend the machine exists within the same network? Or do I have to go with a VPN?

Samora

I added some words on this domain issue in my answer – Wonderment

Answer 1

When you talk about different locations for the servers, does it also mean different domains? Your comment on web replication is true, but applies only to the daily changes made to the database. Your initial snapshot can be of any size, even if it is a huge database. If your daily updates are limited (a few 1000's seems ok to me), go for it. It is quick!

EDIT:

There is a major domain/security issue when you begin to think about exchanging data between servers. If you need to connect servers belonging to different domains through a VPN or any dedicated link, and if you don't want to go through SQL Security (avoid it!), you will face specific (and licensing!) issues related to domain trust. One of the smart sides of Web replication is to avoid this kind of issue (and extra cost) by securing the connection through https protocol.

Wonderment

The domain is the same (though I don't see what difference that makes), but the networks are wholly separate. I'll look into web replication and see if that's good enough, thanks! – Tragacanth

Answer 2

I know this is an old post but hopefully this helps: SQL Server Management Studio uses a comma to separate the host from the port.

How do you specify a different port number in SQL Management Studio?

Mcclung

Answer 3

As a practitioner, I do NOT think an SSH tunnel (it is to allow the client to call itself in order to call to a server) will work. If you turn on an ssh tunnel, you probably need to keep the ssh session open. SSH is not designed for long-opening connections as first priority and it is going to fail.

The best bet is a proper Virtual Private Network (not those proxy servers from internet charging you $10 a year). You can use OpenVPN if you want to be cost effective. The good thing about VPN is all these nodes (db servers) can pretend they are in the same local network and ... it will work.

Naked