I have 2 instances with SQL Server 2008R2. I would like to build Transactional replication between those servers. One is in the Local Network (Local_Instance) and the second server is over Internet (Internet_Instance). The Internet Server is running an FTP service. I have read something that I can use replication using FTP but I don't have any idea how to make this. I have built the publication but I need help to configure the suscription. The distribution database is in the Local Network.


You have probably read that you are able to deliver the initial snapshot via FTP. This is covered in Deliver a Snapshot through FTP.

It is possible to replicate two SQL Servers across a VPN. This is covered in Publish Data over the Internet Using VPN.

Once the VPN is setup, you should be able to connect and authenticate as though the servers are on a LAN or WAN.

If you have anymore questions please let me know. I hope this helps.

| improve this answer | |

For trans rep to work one server needs to be able to see the other - and for FTP snapshots to work it has to be that the subscriber can see the publisher and that you set up a pull subscription.

A push subscription is set up on the publisher and that's where the jobs run. A pull subscription is set up in the subscriber and that's where (some of) the jobs run.

You'll need to use SQL accounts to login whichever way seeing as you won't be able to use AD authentication.

If you do decide to use a pull subscription and need FTP snapshots then go into the distributor properties in SSMS and set the default snapshot location. It's usually a network path/share the FTP site. Then go into the publication properties and there's an option to enable snapshot files over FTP and for you to specify the site name, login, and password. There's also an option here to override the snapshot location set above; you may not need to touch it (or you can change this instead of the above distributor level one).

Also note that if you're not using a VPN your data isn't secure while it's moving over that FTP.

| improve this answer | |