none
Initial Sync doesn't download existing data RRS feed

  • Question

  • I am setting up sychronization on an existing database. Everything seems to work except the initial sync doesn't transfer the existing data from the SQL Server 2008 database to the SQLCE database. As I can tell the pieces below are what handle the intial sync. Isn't SyncDirection.Bidirectional supposed to add all data during the first sync?

    SyncTable tableOrders = new SyncTable("orders");
                    tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                    tableOrders.SyncDirection = SyncDirection.Bidirectional;
    
    SqlSyncAdapterBuilder ordersBuilder = new SqlSyncAdapterBuilder();                
                    ordersBuilder.Connection = serverConnection;
                    ordersBuilder.SyncDirection = SyncDirection.Bidirectional;
    
    
    
    



    Ken

    Friday, August 17, 2012 12:15 AM

Answers

  • can you profile the sql query on initial sync?

    i'm guessing the issue is because the receivedanchor is null on first sync.

    • Marked as answer by Ken Alexander Monday, August 20, 2012 4:09 PM
    Monday, August 20, 2012 3:41 AM
    Moderator

All replies

  • did you use the wizard to generate the sync codes?  because if you did, there's some extra work apart from changing the sync direction:

    Walkthrough: Extending the Local Database Cache to Support Bidirectional Sync 

    you might want to enable sync fx tracing as well to figure out if its encountering issues.

    Friday, August 17, 2012 2:53 AM
    Moderator
  • I did not use the wizard. I enabled tracing and no issues were found. After the initial sync any additional updates/inserts are synchronized fine it's just the initial isn't pulling in all the existing data. Here is the entire block of code I am using:

    private void BeginSync(object sender, EventArgs e)
            {
                DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();
    
                SyncAgent syncAgent = new SyncAgent();
                syncAgent.RemoteProvider = serverSyncProvider;
    
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(_CurrentSyncSenario.ServerConnection);
    
                SqlConnection serverConnection = new SqlConnection(builder.ConnectionString);
    
                serverSyncProvider.Connection = serverConnection;
    
                SqlCeConnectionStringBuilder ceBuilder = new SqlCeConnectionStringBuilder(_CurrentSyncSenario.LocalDataConnection);
                if (!File.Exists(ceBuilder.DataSource))
                {
                    SqlCeEngine clientEngine = new SqlCeEngine(ceBuilder.ConnectionString);
                    clientEngine.CreateDatabase();
                    clientEngine.Dispose();
                }
    
                SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(ceBuilder.ConnectionString);
                syncAgent.LocalProvider = clientSyncProvider;
    
                SqlSyncAdapterBuilder TableBuilder = null;
                SyncTable tableOrders = null;
    
                List<string> ExcludeColumns = new List<string>() { "create_timestamp", "update_timestamp", "update_originator_id" };
    
                SyncGroup orderGroup = new SyncGroup("SyncAll");
    
                foreach (Table SenarioTable in _CurrentSyncSenario.Tables)
                {
                    tableOrders = new SyncTable(SenarioTable.Name);
                    tableOrders.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
                    tableOrders.SyncDirection = SyncDirection.Bidirectional;
                    tableOrders.SyncGroup = orderGroup;
    
                    syncAgent.Configuration.SyncTables.Add(tableOrders);
    
                    TableBuilder = new SqlSyncAdapterBuilder();
                    TableBuilder.Connection = serverConnection;
                    TableBuilder.SyncDirection = SyncDirection.Bidirectional;
                    TableBuilder.TableName = SenarioTable.Name;
                    TableBuilder.TombstoneTableName = SenarioTable.Name + "_tombstone";
    
                    foreach (string ColName in TableExtensions.ListColumns(new SqlConnection(builder.ConnectionString),SenarioTable.Name))
                    {
                        if (ExcludeColumns.Contains(ColName))
                        {
                            continue;
                        }
                        TableBuilder.DataColumns.Add(ColName);
                        TableBuilder.TombstoneDataColumns.Add(ColName);
                    }
    
                    TableBuilder.CreationTrackingColumn = @"create_timestamp";
                    TableBuilder.UpdateTrackingColumn = @"update_timestamp";
                    TableBuilder.DeletionTrackingColumn = @"update_timestamp";
                    TableBuilder.UpdateOriginatorIdColumn = @"update_originator_id";
    
                    SyncAdapter TableSyncAdapter = TableBuilder.ToSyncAdapter();
                    ((SqlParameter)TableSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_last_received_anchor"]).DbType = DbType.Binary;
                    ((SqlParameter)TableSyncAdapter.SelectIncrementalInsertsCommand.Parameters["@sync_new_received_anchor"]).DbType = DbType.Binary;
                    serverSyncProvider.SyncAdapters.Add(TableSyncAdapter);
                }
    
                SqlCommand anchorCmd = new SqlCommand();
                anchorCmd.CommandType = CommandType.Text;
                anchorCmd.CommandText = "Select @" + SyncSession.SyncNewReceivedAnchor + " = @@DBTS";
                anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
    
                serverSyncProvider.SelectNewAnchorCommand = anchorCmd;
                
                SqlCommand clientIdCmd = new SqlCommand();
                clientIdCmd.CommandType = CommandType.Text;
                clientIdCmd.CommandText = "SELECT @" + SyncSession.SyncOriginatorId + " = " + _CurrentSyncSenario.ClientID.ToString();
                clientIdCmd.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;
    
                serverSyncProvider.SelectClientIdCommand = clientIdCmd;
    
                clientSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);
                clientSyncProvider.ApplyChangeFailed += new EventHandler<ApplyChangeFailedEventArgs>(ShowFailures);
                serverSyncProvider.SyncProgress += new EventHandler<SyncProgressEventArgs>(ShowProgress);
                SyncStatistics syncStats = syncAgent.Synchronize();
    
            }


    Ken



    Friday, August 17, 2012 4:42 PM
  • can you profile the sql query on initial sync?

    i'm guessing the issue is because the receivedanchor is null on first sync.

    • Marked as answer by Ken Alexander Monday, August 20, 2012 4:09 PM
    Monday, August 20, 2012 3:41 AM
    Moderator
  • It was.  Thank you!

    Ken

    Monday, August 20, 2012 4:10 PM