Sync Framework RRS feed

  • Question


    I have some developers using the Sync Framework for the ETL process. I was understand the understanding this framework was designed for CE devices, not to synchronize databases with tables pushing 140 million records between two databases on the same SQL instance. To me this makes no sense at all, and from what I'm seeing is not the right choice. Anyone have similar experience with this framework and Willie Coyote plans?

    Monday, February 13, 2012 8:56 PM

All replies

  • Sync Framework is not designed exclusively for synching CE devices (in fact, you can't use it to sync directly between devices). The Sync Framework providers allows you to sync between SQL Compact, SQL Express, SQL Server or SQL Azure. You may also write your own custom providers for other databases.

    Without understanding much about what you're synching, presence of any custom logic for handling conflicts, etc., it would be hard to determine if its fit for what you're doing. Sync Framework can do the Extract and Load parts of an ETL if its just a straightforward incremental synchronization of changes. On the other hand, it may not be fit for purpose for complex Transformations.

    Given that you're just synching between databases on the same SQL Server instance, i would explore using SSIS or depending on your SQL version, SSIS coupled with SQL Change Data Capture.

    • Edited by JuneT Wednesday, February 15, 2012 12:17 AM
    Tuesday, February 14, 2012 1:22 AM
  • JuneT

    We captured counters for the EMC CX3-80 connected to the ES7000 host, monitored switches, perf mon SQL and windows metrics, and as well as SQL counters looking for a cause of performance issues, and have turned up nothing. From a server perspective they shouldn't have any problems 16 procs, 64 GB RAM, connected to 2 HBA's active/active. So I started digging through the code and  I found this a little C# app that uses the sync framework to synchronize hundreds of thousands of changes between two large SQL server databases. From what I see it looks like the wrong tool for the job. I doubt this was the intention when it was developed by Microsoft. I have CDC enabled on the server and they choose this route, very inefficient design choice. So far we have been haunted by performance problems, semaphore timeouts, and long run times. I think it best to not use this framework for ETL processes that synchronize hundreds of thousands of records. For those cases TSQL, SSIS, and BCP are the best tools and they offer proven reliability, performance as well as are scalable. Any thoughts?

    mike fuller

    Tuesday, February 14, 2012 3:08 PM
  • If by ETL in the context of Data Warehousing, yes, I'd probably use SSIS, that's what its built for. Having said that, I know of some who actually uses Sync Framework in loading to Data Warehouses as well.

    Is the synchronization being done one-way or bidirectional? if it's bidirectional, Sync Framework might actually work best.

    try enabling Sync Framework tracing in verbose mode so you can see how long its processing and which process is taking long.

    on another note, if you have CDC enabled, then you actually have two change tracking mechanism in effect. Sync Framework has triggers to track changes and you have CDC tracking as well. so every insert/update/delete you do is actually tracked and recorded twice.

    Wednesday, February 15, 2012 12:26 AM