sql azure - Microsoft sync framework slow for tables without identity field (and composite primary key) -

sql azure - Microsoft sync framework slow for tables without identity field (and composite primary key) -

i'm using microsoft sync framework 2.1 one-way replication our on-premises database azure sql database. when syncing tables don't have identity field , composite primary key, sync running 4 sql statements every row in of these tables.

update [mytable_tracking] set [update_scope_local_id] = @sync_scope_local_id, [scope_update_peer_key] = @sync_update_peer_key, [scope_update_peer_timestamp] = @sync_update_peer_timestamp, [local_update_peer_key] = 0, [local_update_peer_timestamp] = @nextrowversion, [sync_row_is_tombstone] = @sync_row_is_tombstone ([primarycol1] = @p_1 , [primarycol2] = @p_2) , (@sync_check_concurrency = 0 or [local_update_peer_timestamp] = @sync_row_timestamp) select [side].[primarycol1], [side].[primarycol2], ... , [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] sync_row_timestamp, case when ([side].[update_scope_local_id] null or [side].[update_scope_local_id] <> @sync_scope_local_id) [side].[local_update_peer_timestamp] else [side].[scope_update_peer_timestamp] end sync_update_peer_timestamp, case when ([side].[update_scope_local_id] null or [side].[update_scope_local_id] <> @sync_scope_local_id) case when ([side].[local_update_peer_key] > @sync_scope_restore_count) @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end sync_update_peer_key, case when ([side].[create_scope_local_id] null or [side].[create_scope_local_id] <> @sync_scope_local_id) [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end sync_create_peer_timestamp, case when ([side].[create_scope_local_id] null or [side].[create_scope_local_id] <> @sync_scope_local_id) case when ([side].[local_create_peer_key] > @sync_scope_restore_count) @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end sync_create_peer_key [mytable] [base] right bring together [mytable_tracking] [side] on [base].[primarycol1] = [side].[primarycol1] , [base].[primarycol2] = [side].[primarycol2] [side].[primarycol1] = @p_1 , [side].[primarycol2] = @p_2 select @was_tombstone = [sync_row_is_tombstone] [mytable_tracking] ([primarycol1] = @p_1 , [primarycol2] = @p_2) if not exists (select * [mytable_tracking] [primarycol1] = @p_1 , [primarycol2] = @p_2)

a lot of these tables many-to-many tables. have identity field sync framework doesn't back upwards scenarios identity other primary key had exclude identity column sync. before database synced 1000000 database queries slows sync downwards takes more 24 hours sync , azure bill growing.

can somehow instruct sync framework when provisioning these tables can optimize sync?

the target database read-only, i.e. sync writes can remove constraints etc. have created database without foreign keys help if created target database without primary keys?

update: if create target database without indexes error

microsoft.synchronization.data.dbsyncexception: failed execute command 'bulkinsertcommand' table 'cnt_contentnode'; transaction rolled back. ensure command syntax correct. ---> system.data.sqlclient.sqlexception: tables without clustered index not supported in version of sql server. please create clustered index , seek again.

-mathias

i ended removing primary keys tables composite keys , replacing them clustered unqiue indexes in stead. it's working properly.

-mathias

sql-azure microsoft-sync-framework composite-primary-key