Given that most Windows Azure SQL Database customers are now advised to use with custom sharding pattern for scale-out, we are re-evaluating when you would want to use sync for a sharded environment – is it syncing from on-prem SQL Server to multiple shards in the cloud, synchronizing between the shards in the cloud, or syncing from multiple shards down to on-prem SQL Server? We’d appreciate your input on this.Jonathan Dyke commented
sync on-premises SQL Server with a federated database
Technically, this feature could go 2 ways and both are completely valid uses.
For instance: Say I have 2 physical locations and I've federated on a location ID. The locations should not see each others data. I'd want to sync the data from location #1 to their on-premises SQL Server without syncing any data from location #2.
Now, say I have a 3rd location that is a "central office" of some sort. It should have access to all data from all locations so they can do billing or quaterly reports or whatever. In that case, I'd want to sync all of the data with the on-premises SQL Server.
While it's a headache, both scenerios can already be handled with SQL Server. In fact, this functionality has been around so long that my original implementation used a SQL Server 2000 publisher with Jet 4.x subscribers.
Implementing one or the other will only make some of us happy. To do it right, you'll need to support both scenerios on a single federated database at the same time.