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?