locked
How do I initialize a client database for sync if the schema is custom generated? RRS feed

  • Question

  •  

    Hello,

     

    I have a problem initializing the client database for synchronization. I generate the client db with a schema generator. After db generation that I sync with the server. I get the changes from the server. I insert data into the client db and start sync, but no data is selected and transferred to the server. If I close the programm and restart with a following insert into the client db, I can sync that data with the next synchronization.

     

    I image the problem is that my local database does not  do the insert into the system tables that are provided by sync services (just after the db is created). If this is the cause how can I initialize my local database and is there a way to see what data is inserted into the sync system tables at the client. I have found the following information in another thread:

     

    On the client side (SQLCE), change tracking is enabled in SQL CE 3.5 when the first sync session is established.  The system tables store a sequence number for every row (_SysChangeTxBSN and _sysInsertTxBSN) and the last sent sequence number.  There are additional system tables (__sysOCSDeletedRows) that store tombstone entries.  The client ID is stored in the registry for each user.

     

    Is there a way to view these system tables? What must I do to initialize my local database for synchronization (that is custom generated)?

     

    Regards and thanks

     

    Thomas

     

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 8:56 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, January 29, 2008 12:25 PM

Answers

  • Hello,

     

    I finally got the solution to the problem. We are using the CE DB with a single connection for performance reasons. If you have a custom generated DB then the system columns are added at the the moment the Sync process starts

    e.g calling SyncAgent.Synchronize(). If my data connection remains open, the following inserts into the client table will not put integer values into the __sysInsertTxBsn column but insert NULL instead. So what you need to do is  to close your connection after the Synchronize() method call. The following inserts will then add system  column values as they should.

     

    Hope this helps others as well - I am not sure if this is caused by our data access architecture.

     

    Regards Thomas

    Thursday, January 31, 2008 10:44 AM

All replies

  • Hello,

     

    I got a little step further with the thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1811443&SiteID=1

     

    So I use the following code to initialize the Synchronization:

     

    SyncTable specSheetFolder = new SyncTable("SPECIFICATIONSHEETFOLDER");

    specSheetFolder.CreationOption = TableCreationOption.UseExistingTableOrFail;

    specSheetFolder.SyncDirection = SyncDirection.Bidirectional;

    SyncTable specSheetItem = new SyncTable("SPECIFICATIONSHEETITEM");

    specSheetItem.CreationOption = TableCreationOption.UseExistingTableOrFail;

    specSheetItem.SyncDirection = SyncDirection.Bidirectional;

    clientSyncProvider.CreateSchema(specSheetItem, null);

    clientSyncProvider.CreateSchema(specSheetFolder, null);

     

    I understand that I can now set anchor values:

     

    clientSyncProvider.SetTableReceivedAnchor("SPECIFICATIONSHEETITEM", receivedAnchor);

    clientSyncProvider.SetTableSentAnchor("SPECIFICATIONSHEETITEM", sentAnchor);

     

    I don't understand how to create the two Anchors and set them to start values.

     

    Help very much apreciated (I am spending many hours to get along)

     

    Interesting is the information provided in the select form the system table __sysSyncArticles

     

    select * from __sysSyncArticles which shows the values for the anchors,

    Tuesday, January 29, 2008 3:59 PM
  • I have tried to set anchor values such as, in an attempt to set the time anchor values to 0:

     

    clientSyncProvider.CreateSchema(specSheetItem, null);

    clientSyncProvider.CreateSchema(specSheetFolder, null);

    MemoryStream memStream;

    BinaryFormatter binFormatter = new BinaryFormatter();

     

    memStream = new MemoryStream();

    byte[] arr = BitConverter.GetBytes(0L);

    binFormatter.Serialize(memStream, arr);

    SyncAnchor anchor = new SyncAnchor(memStream.ToArray());

    SyncAnchor sentAnchor = new SyncAnchor(BitConverter.GetBytes(0x0100000000000000));

    clientSyncProvider.SetTableReceivedAnchor("SPECIFICATIONSHEETITEM", anchor);

    clientSyncProvider.SetTableSentAnchor("SPECIFICATIONSHEETITEM", sentAnchor);

     

    The anchor values get inserted into the __sysSyncArticles table.

    I insert 100 values into the Client Database and start the Sync process, but no data goes to the server!!

    Is there something else thats need to be initialized?

     

     

     

     

    Tuesday, January 29, 2008 6:14 PM
  • Hello,

     

    I could probably narrow down the problem to the fact that inserted rows at client side are tracked by a __sysInsertTxBsn system column. After calling:

     

    clientSyncProvider.CreateSchema(specSheetItem, null);

    clientSyncProvider.CreateSchema(specSheetFolder, null);

     

    and inserting data, the value of __sysInsertTxBsn is null (client local database). If I shut down the program and resync and then do local inserts (by some miracle) I get integer values for the inserted data and those are transferred.

     

    Now I need your help!!

    What must I do to get not null values into my inserted data (at the client)? Trying to manually change system column values results in an exception. I am sure there must be a better way.

     

    Regards

     

    Thomas

     

    Wednesday, January 30, 2008 1:45 PM
  • Hello,

     

    I finally got the solution to the problem. We are using the CE DB with a single connection for performance reasons. If you have a custom generated DB then the system columns are added at the the moment the Sync process starts

    e.g calling SyncAgent.Synchronize(). If my data connection remains open, the following inserts into the client table will not put integer values into the __sysInsertTxBsn column but insert NULL instead. So what you need to do is  to close your connection after the Synchronize() method call. The following inserts will then add system  column values as they should.

     

    Hope this helps others as well - I am not sure if this is caused by our data access architecture.

     

    Regards Thomas

    Thursday, January 31, 2008 10:44 AM
  • Hi Thomas,

     

    I am glad to see you resolved the problem by yourself.

     

    By saying "using a single connection" to the database, do you meant to say the same connection was used for both sync ( the client provider ) and the some other routine ( your data connection )? are there any pending transaction on the connection when the synchronize() method was called ?

     

    thanks

    Yunwen

    Thursday, January 31, 2008 7:57 PM
    Moderator
  • Hello Yunwen,

     

    I am glad to hear from you. As far as I can see the clientprovider only gets a connection string and probably builds its own connection. Before starting the initial sync I have created the db schema (there should be no pending transactions). I used a sample form the sync framework and adapted that to find the error. So this is the code for the clientprovider:

     

    public class SampleClientSyncProvider : SqlCeClientSyncProvider

    {

    private readonly string connString =

    @"Data Source = c:\db\ssce\SyncData.sdf; Password=xxxxxxx; SSCE:Max Database Size=4091";

    public SampleClientSyncProvider()

    {

    //Specify a connection string for the sample client database.

    ConnectionString = connString;

    this.CreatingSchema += new EventHandler<CreatingSchemaEventArgs>(SampleClientSyncProvider_CreatingSchema);

    this.SchemaCreated += new EventHandler<SchemaCreatedEventArgs>(SampleClientSyncProvider_SchemaCreated);

    }

    }

     

    The singleton connection is used to create the database and does all selects, inserts, etc.

    It was interesting that when I configured my application to use altering connections, there was no problem. The problem is the performance (for example I tried 100000 inserts using altering connections - which took 2 min compared to 14 sec when using the same connection)

     

    I am wondering by what mechanism the system column gets its values (looks like a sort of system enumerator) at following inserts and why it can happen that an insert following an initial sync can produce null values in the system column (tracking the inserts) of the inserted rows.

     

    I have moved my code now to using the api from sync framework but there does not seem to be a lot of difference (that was easy to adopt)

     

     Regards

     

    Thomas

     

    Thursday, January 31, 2008 8:56 PM
  • hi Thomas,

     

    the client handles its tracking mechanism internally ( as oppsite to the server side which is open ). at a high level, the logic is the same. the client uses the transaction sequent number from the db engine as its anchor value.

     

    I am very interesting in the perf delta ( 2 minutes vs. 14 seconds is a huge difference ), could you provide more details on it ? only on different connection should not make such huge difference.

     

    thanks

    yunwen

    Friday, February 1, 2008 7:22 PM
    Moderator
  • Hallo Yunwen,

     

    this is a simple sample showing an exteme difference in performance ( 8s vs - 3min, having some indexes on the table). So opening and closing connections on sql server ce seem to use a lot of time. We are using a db access layer and perform inserts via business objects e.g. Person.Persist() this can be one Person or several Person objects. So we do not want to specifiy on every Persist operation if we want altering connections or not. If you do not have a multisuer environment using a ce db then it probably makes sense to work with a single open connection, what do you think?

     

    using System;

    using System.Collections.Generic;

    using System.Data.SqlServerCe;

    using System.Linq;

    using System.Text;

    namespace SqlCePerformanceTest

    {

    class Program

    {

    static void Main(string[] args)

    {

    PerformanceTester tester = new PerformanceTester();

    System.DateTime start = DateTime.Now;

    tester.DoTableOperationWithSingleConnection();

    System.TimeSpan span = start - System.DateTime.Now;

    Console.WriteLine(span);

    Console.ReadLine();

    }

    }

    class PerformanceTester

    {

    readonly string ClientConnString = @"Data Source='c:\db\ssce\SyncData.sdf'; Password='12345'";

    public void DoTableOperationWithAlteringConnection()

    {

    for (int i = 0; i < 10000; i++)

    using (SqlCeConnection clientConn = new SqlCeConnection(ClientConnString))

    {

    SqlCeCommand command = clientConn.CreateCommand();

    command.CommandText =

    "INSERT INTO [ORDER] (OID, IDCONTRACT, NETSUM) VALUES (" +

    "'" + Guid.NewGuid().ToString() + "'," +

    "'8512fcf9-d647-4938-a5f1-4fbed6481f6f', " +

    " 1344.00 )";

    clientConn.Open();

    command.ExecuteNonQuery();

    clientConn.Close();

    }

    }

    public void DoTableOperationWithSingleConnection()

    {

    SqlCeConnection clientConn = new SqlCeConnection(ClientConnString);

    clientConn.Open();

    for (int i = 0; i < 10000; i++)

    {

    SqlCeCommand command = clientConn.CreateCommand();

    command.CommandText =

    "INSERT INTO [ORDER] (OID, IDCONTRACT, NETSUM) VALUES (" +

    "'" + Guid.NewGuid().ToString() + "'," +

    "'8512fcf9-d647-4938-a5f1-4fbed6481f6f', " +

    " 1344.00 )";

    command.ExecuteNonQuery();

    }

    clientConn.Close();

    }

    }

    }

     

    Monday, February 4, 2008 12:22 PM
  • it makes sense why there is a such difference when seeing the code snippet above. just think about for 10,000 time you construct the connection object ( all the way from managed layer to the native SSCE engine layer , to OS layer to opened the SSCE database file ) and then dispose it (when the using block exists ), how much time it would spend here.

     

    this is definately NOT two connections vs single connection, it is actually 10,000 connections vs single connection !

     

    btw, SSCE 3.5 ( actualy 2.0 or above ) does support mutiple connections, so you probably can utilize it in you solution.

     

    also, as a recommendataion when insert many rows with a similar query, this modified code could even do better:

     

    public void DoTableOperationWithSingleConnectionWIthParameterizedQuery()

    {

    SqlCeConnection clientConn = new SqlCeConnection(ClientConnString);

    clientConn.Open();

    SqlCeCommand command = clientConn.CreateCommand();

    command.CommandText =

    "INSERT INTO [ORDER] (OID, IDCONTRACT, NETSUM) VALUES (" +

    " ? , " +

    "'8512fcf9-d647-4938-a5f1-4fbed6481f6f', " +

    " 1344.00 )";

    command.Parameters.Add("@id", SqlDbType.UniqueIdentifier, 16, "OID");

    command.Prepare();

    for (int i = 0; i < 10000; i++)

    {

    command.Parameters[0].Value = Guid.NewGuid();

    command.ExecuteNonQuery();

    }

    clientConn.Close();

    }

    }

     

    Thanks

    Yunwen

     

    Sunday, February 10, 2008 1:23 AM
    Moderator
  • Hello Yunwen,

     

    thanks for your response. The point I was trying to make is that performance of compact db gets very slow if many open() and close operations() are executed on the connection. I have done a measurement of time (ANTS Profiler) and get for 1000 subsequent inserts values of

    9s for 1000 open() calls

    4.4 s for 1000 close() calls

    1.4 s for creating 1000 times the connection etc.

     

    So this is very slow. The compact db is extremly fast using a single connection (even faster with prepared statements )

     

    If you use in your architecture a presentation layer, a business layer and a data access layer it is very intuitive to work in the data access layer with a single connectcion (in fact i read in other forums that many people do so).

     

    So lets go back to the initial problem. I was able to reproduce the problem of missing values for inserts in the tracking column if you use a single connection with on of rafiks samples (OfflineAppDemo-TSQL+SProc.csproj). I will post the code below ( I will send rafik a zip file of the project, if you give me your email I will post the file to your address).

     

    The only changes I have made besides changing the connection parameters is

    1. Introduce a private connection variable

    2. Use this connection for creation of custom table "orders" and for inserting order data

    private void buttonApplyOrdersInserts_Click(object sender, EventArgs e)

     

    So I guess to reproduce the effect just load Rafiks sample, replace the class SyncForm and adjust the connection strings to the local and remote database.

     

    If you now press "Synchronize" and do "Random Inserts" those inserted values will have NULL values in the insert tracking column and will of course never be synchronized to the server. I think this could be a problem for other users of the sync framework.

     

    Regards

     

    Thomas

     

     

    Wednesday, February 27, 2008 9:17 AM