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?

  • 3
    If the data is only flowing from master to replicas, you might consider other forms of replication, such as snapshot. I'm fairly sure you can use snapshot via FTP only. Commented Jan 7, 2013 at 14:42
  • Is this something that you need to do on a regular basis (as in, sync data every X period), or is it a once off, as in an initial set up of the replica servers?
    – user2235
    Commented Jan 8, 2013 at 3:25
  • There is daily data transfer.
    – Lybecker
    Commented Jan 8, 2013 at 9:20
  • Snapshot-replication requires full data transfer of all 1 TB of data. The data changes everyday, so that's not Suitable.
    – Lybecker
    Commented Jan 8, 2013 at 9:35

2 Answers 2


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


Transactional Replication is most likely your best option. I think schema changes get replicated as well, but I'm not 100% sure on that. After the initial setup only changes get pushed across the network, it should be easier on the network than constant snapshots.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.