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?