none
Sync more than two databases at a time RRS feed

  • Question

  • Im using syncFx 2.1 for sync two sql 2008 r2 databses. Is there any way to sync my server database with four client databases(all are in a same group) at a time.? Any helps appreciate
    Friday, May 6, 2011 5:52 AM

Answers

  • your query "select * from sysobjects where type='u'" is picking up the Sync Framework tables and is including them in the tables to be synched.
    Friday, May 6, 2011 12:22 PM
    Moderator
  • nope, what am saying is exclude all Sync Fx created tables from your scope definition. Your query from the sys.tables is picking up the Sync Fx created tables and including them in your scope definition.

    assuming your server has one table called Table1, after provisioning the server and the client, you end up with Table1 plus Table1_tracking, scope_info, scope_config and schema_info tables.

    Now when you provision the second client, you're doing a select from sys.tables which picks up Table1 plus Table1_tracking, scope_info, scope_config and schema_info tables instead of just Table1.

    Since you're scope already exists on the server it doesnt get applied because of your ScopeExists condition.

    however, you're using the same scope definition to provision the client and since the Sync Fx tables are in your scope definition, Sync Fx tries to include them in the scope provisioning on the second client.

    • Marked as answer by sharon 5656 Monday, May 9, 2011 8:23 AM
    Monday, May 9, 2011 7:11 AM
    Moderator

All replies

  • If I understand your question right, you have one server database and 4 client databases - and you want all of these client databases to sync with the server concurrently. If so, yes, this should be possible.

    All you need to do is have your client app that does the sync, run on all the 4 clients. If your app is designed such that it runs on a schedule then you can just leave the app running on all the clients and they should run fine concurrently.


    This posting is provided AS IS with no warranties, and confers no rights
    Friday, May 6, 2011 6:29 AM
  • When i first synced my server with Client 1,there was no problem.

    And when i tried to sync with my server to Client 2, it showing

    'Cannot update identity column 'scope_local_id''.Is it because Im using same scope for this? is there any option for this.?

    Friday, May 6, 2011 9:25 AM
  • how are you provisioning your client databases?
    Friday, May 6, 2011 9:56 AM
    Moderator
  • i was trying to sync by making my server as source DB and provisioned with empty DB Client 1 as destination.

    then again server as source DB provisioned with empty client 2 as destination. At this time i got that exception 'Cannot update identity column 'scope_local_id''.

     DbSyncScopeDescription myScope = new DbSyncScopeDescription(scopeName);
            
            SqlCommand cmd = new SqlCommand("select * from sysobjects where type='u'", sqlSourceConn);
    
            SqlDataAdapter _ADap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            _ADap.Fill(dt);
    
            foreach (DataRow drRow in dt.Rows)
            {
              
              DbSyncTableDescription Customer = SqlSyncDescriptionBuilder.GetDescriptionForTable(drRow["name"].ToString(), sqlSourceConn);
              myScope.Tables.Add(Customer);
            
            }
    
            
            SqlSyncScopeProvisioning sqlServerProv = new SqlSyncScopeProvisioning(sqlSourceConn,myScope);
            sqlServerProv.CommandTimeout = 60 * 50;
            if (!sqlServerProv.ScopeExists(scopeName))
            {
               sqlServerProv.Apply();
                        
            }
            else
            {                   
              textBox3.Text = "SQL Server Database server already provisioned for sync " + DateTime.Now + Environment.NewLine + textBox3.Text;
                        
            }
    
              SqlSyncScopeProvisioning sqlDestinationProv = new SqlSyncScopeProvisioning(sqlDestinationConn, myScope);
              if (!sqlDestinationProv .ScopeExists(scopeName))
              {
                 sqlDestinationProv .Apply();
               }
              else
              {                        
                
               MessageBox.Show("SQL Destination Database server already provisioned for sync " + DateTime.Now);
              }
    
              sqlDestinationConn.Close(); 
              sqlSourceConn.Close();
    Friday, May 6, 2011 10:57 AM
  • your query "select * from sysobjects where type='u'" is picking up the Sync Framework tables and is including them in the tables to be synched.
    Friday, May 6, 2011 12:22 PM
    Moderator
  •  

    Thanks.that was helpful.Can you tell me how it would be..i tried to change into this.

    select * from sys.tables where name not in('schema_info','scope_info','scope_config')

    and sync with client 2. then i get this exception

    "The column 'update_scope_local_id' was specified multiple times for 'changes'."

    inner exception is

     procedure-table_tracking_bulkupdate

    will you help please..
    Saturday, May 7, 2011 5:41 AM
  • your select is still picking up Sync Fx objects. tables with suffix _tracking are used by Sync Framework. Do you have many tables that you want to sync? can you not just add them explicitly?
    Saturday, May 7, 2011 6:52 AM
    Moderator
  • No i cant add them explicitly. I have large amount of tables to sync,around 250 tables. please tell me  how could i do it..
    Saturday, May 7, 2011 7:04 AM
  • exclude all sync fx tables: scope_info, schema_info, scope_config, scope_parameters, scope_templates and all other tables ending with _tracking.

    or assign an ObjectPrefix and exclude all those table starting with that ObjectPrefix

    Saturday, May 7, 2011 11:03 AM
    Moderator
  • So what you are suggesting is that after i synched with my Client 1, i need to deprovision  my serverDB to sync Client 2 ,And for client 3 continue like  this..?  And what i do if i come back to client 1 to sync again? should i start from start? So i cant sync my one database to more than one clients at a same time...

     

    Monday, May 9, 2011 5:27 AM
  • nope, what am saying is exclude all Sync Fx created tables from your scope definition. Your query from the sys.tables is picking up the Sync Fx created tables and including them in your scope definition.

    assuming your server has one table called Table1, after provisioning the server and the client, you end up with Table1 plus Table1_tracking, scope_info, scope_config and schema_info tables.

    Now when you provision the second client, you're doing a select from sys.tables which picks up Table1 plus Table1_tracking, scope_info, scope_config and schema_info tables instead of just Table1.

    Since you're scope already exists on the server it doesnt get applied because of your ScopeExists condition.

    however, you're using the same scope definition to provision the client and since the Sync Fx tables are in your scope definition, Sync Fx tries to include them in the scope provisioning on the second client.

    • Marked as answer by sharon 5656 Monday, May 9, 2011 8:23 AM
    Monday, May 9, 2011 7:11 AM
    Moderator
  • Thanks a lot JuneT. That was really helpful.Now its working perfectly.Iam trying to work it as a scheduled operation.But  I heard from a blogger that sync Fx doent support scheduled or background synchronization of large datas. Is it correct ?And I know schema changes doesnt support syncFx, is there any chance of this feature in future version?

     Once again thanks for your great help..

    Monday, May 9, 2011 8:36 AM
  • i dont work for Microsoft so i cannot comment on features that are to be included in the future :)

    Sync Framework has no functionality for you to schedule or do background sync but that doesnt stop you from writing your own background or scheduled process invoking Sync Framework.

    Sync Framework needs to be invoked (by calling Synchronize), so you can call Synchronize wherever you want it: in a scheduled task, a Windows Service, a timer event, in a button click event, etc...

    If you are synching with SQL Azure, you may want to check out Sql Azure Data Sync Service which allows you to configure and schedule your synchronization topology without programming: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-data-sync-overview.aspx

    Monday, May 9, 2011 9:37 AM
    Moderator