Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Damien_The_Unbeliever Jan 7 '13 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? –  fortheworld Jan 8 '13 at 3:25
    
There is daily data transfer. –  Lybecker Jan 8 '13 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 Jan 8 '13 at 9:35
add comment

migrated from stackoverflow.com Jan 7 '13 at 15:25

This question came from our site for professional and enthusiast programmers.

2 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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