Sql-server – Oneway sync of two SQL Servers via FTP

merge-replicationreplicationsql server

I need to replicate data from a ‘master’ SQL Server out to a number of replicas. Luckily the data only flows from the ‘master’ to the replicas.

I need to replicate both schema and data. Data size approx. 1 TB. I have full control of ‘master’ and replica servers, but they cannot communicate directly – only via a file share.

I’ve looked at Merge Replication and MS Sync Framework.

Merge Replication requires direct communication between the SQL Servers.
MS Sync Framework does not handle schema changes, but that can be solved with SQL Server Data Tool and DACPAC. Unfortunately I have to create a SQL Server to file share as partial participant and then another the other way from file share to SQL Server. Is there any way to get around this?

I would like to support SQL Server version agnostic replication e.g. ‘master’ running SQL Server 2008 R2 and replicas running SQL Server 2008 R2+ (E.g. SQL Server 2012).

Any other implementation suggestions?

Best Answer

We are using a merge replication topology between our servers where:

  • Initial snapshot is made available to subscribers through ftp
  • further data exchange (in our case, merge sync between servers) is done through web replication

Here is a script I once proposed to launch such a replication. You will notice that one of the sp_addmergepullsubscription_agent parameters, @use_ftp, is set to True. The @internet_url parameter sets the https adress used for replication. There are other options allowing the use of an alternate snapshot location and/or its compaction (which could make sense when you are talking about a 1To database ...)

Hope it helps

Related Question