none
Issue with On-Premise SQL and SQL Azure Sync - Bulk Insert SP not executing continously RRS feed

  • Question

  • We are facing a strange issue and it has been blocking our Production deployment.

    We use Sync framework 2.1 to sync our tables from On-Premise to the Azure DB.  We tested this code in our Dev, test and UAT environments. It works fine. However, when we began our deployment to production two days ago, we noticed that the initial Sync is just very slow. More querying on the DMVs and sys tables revealed that the Sync process, after inserting a few thousand records into a table halts for long durations (for hours sometimes) before it kicks off again and then halts again. There is no specific point where it halts. It just halts wherever and then kicks off after a long break. There is no other process running on the server. The target tables are empty. What I see from further querying is that the _insert and _select (Sync Framework SPs for the table being synced) SPs are being executed continuously but the _BulkInsert SP is not being executed continuously. This causes the halt. When after the long break, the _BulkInsert SP is executed, the rows begin into be inserted. Any pointers on why this Bulk Insert SP halts for long durations will be greatly appreciated.

    Thanks

    Friday, June 7, 2013 6:54 PM

All replies

  • have you run sync framework tracing to get a detailed log?

    the bulkinsert sp shouldn't be called that often, its doing bulk inserts and depending on the number of rows, may take some time as it also tries to populate the tracking table. this being azure sql, have you also checked just in case it got throttled?

    also, if youre seeing calls to _insert, then its not doing bulk inserts. are you seeing calls to _selectrow as well?
    Saturday, June 8, 2013 1:27 AM
    Moderator
  • Thanks June.

    Our issue seemed to be with the SQL Azure instance itself. It is strange that when we procured a new SQL Azure Server using a new Subscription, the sync worked at the expected speed and our deployment went well. However, the older Instances still have very poor performance.

    Anyways, Now that you have mentioned, how can I run a Sync framework tracing?

    Since ours was an initial sync and the targets empty, I was assuming that the Bulk insert SP would do most of the work.  

    Yes. I am also seeing calls to _selectrow as well. Both the _insert and _select are being called numerous times without actual inserts happening on the Target table (the row count is not increasing).

    Also, can you point me a place where I can read about these internals of Sync Framework specially in regard to Azure Sync. There seems to be no detailed architecture of the sync itself or may be I am not able to find it.

    Thanks

    Monday, June 10, 2013 6:53 AM
  • _selectrow is executed when a conflict or an error is encountered applying a change.

    if you're tables are empty, you should check if there are constraints that's preventing the inserts from happening.

    the documentation that comes installed with Sync Framework provides some insight about its internals.

    To enable tracing, see: http://msdn.microsoft.com/en-us/library/cc807160.aspx

    Tuesday, June 11, 2013 1:15 AM
    Moderator