none
Sync/Merge local sql compact databases to single global database

    Question

  • Hi, I have compact sql databases which will be local on multiple users
    devices. Due to space constraints, for one of the tables i have had to use
    auto incrementing integer which works fine for the local database but i
    would like to merge all of the users databases into a global database. The
    table format can be seen below:

    Code Snippet
    CREATE TABLE Players
    (
    ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(32),
    LastName NVARCHAR(32)
    );

    CREATE TABLE Sessions
    (
    ID INTEGER NOT NULL IDENTITY,
    PlayerID UNIQUEIDENTIFIER ,
    SessionDateTime DATETIME,
    CONSTRAINT pkSessions PRIMARY KEY (ID),
    CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
    );

    CREATE TABLE SessionDetail
    (
    SessionID INTEGER,
    Time real,
    Power real,
    CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
    CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
    );

     


    The Players table will merge fine as GUIDs are used. However, how will the
    sync capabilities of compact SQL handle the sessions table? When it pushes
    the local data to the remote database will it change the Sessions.ID column
    to a unique field (as no doubt lots of people will have 1, 2, 3 in their
    local databases and the global database must have a unique ID), and then
    transfer this changed ID back to the local database? Furthermore, if it
    changes the Sessions.ID column during the merge it will also need to update
    the SessionDetail.SessionID foreign key to maintain referential integrity,
    is this also handled?

    So my question is, how much of this sync and data merge is automated and are
    there any good examples or pointers for my scenario? I cannot reasonably use
    a GUID for Session.ID as there will be lots of SessionDetail entries and the
    size would be far too much.

    Many thanks,

    Chris
    Saturday, June 23, 2007 6:12 AM

Answers

  • Hi Chris,

    as long as SessionDetails are indexed, I still think using Guids will not impact performance.

     

    I am not very familiar with CSLA, but as I understand you, this framework will give your the ability to connect to the remote database via .NET remoting? If so, then considering the limited synch requirement of your application I think you have a viable solution.

     

    Good luck with your project.

     

    Monday, June 25, 2007 3:25 PM

All replies

  • Hi Chris,

     

    you will be entering a world of pain in order to save 12 bytes per row (SD cards are cheap these days!).

     

    The scenario is supported as described in http://technet.microsoft.com/en-us/library/ms152543.aspx

     

     

    Saturday, June 23, 2007 7:52 AM
  • Hi thanks for the reply!  Ok point taken, but i want to save each row as the main database will have tens of thousands of users sessions, and each session will have approx 1000 to 2000 entries. I thought using ints rather than guids would make a big difference in the long run, in terms of speed anyways. I can live with the storage increase as i can just bolt on more disk space, overall im worried the affect of using guids and speed.

     

    The other option is for me to have the same tables in each database but do a manual sync... i.e i would have an additional column in the local database called remoteDatabaseSessionID. On pushing the session to the remote database, i would get returned the session ID (it will be different as it will come from the sql database auto incrememnt column) which i could store in my local database. If i ever deleted the local record i would know which remote SessionID to delete also. I have no requirement to delete from the remote database and merge the deletes with the local database. Basically the sync is only one way.

     

    So to summarise:

     

    1) How do guids affect the speed of the queries (i dont care about space!)

    2) What is the size difference between ints and guids (guids are 8 bytes from memory)?

    3) How does my manual sync scheme sound? Will this be easier than implementing the remote data access sync with sql compact libaries?

     

    Many thanks!

     

    Chris

    Saturday, June 23, 2007 1:55 PM
  • Hi Chris,

    1) Guid = 16 bytes, int = 4 bytes in the database.

    2) More of each data page will be required per records, but if we are talking thousands of rows (and not ten-thousands) (and you have the required indexes) query performance will not be an issue.

    3) No matter if you choose a manual sync scheme or RDA, you will need a trasnport mechanism, and RDA can do that for you, even with schema creation on the device and compression, things you would otherwise have to code. I am not sure I fully understand you requirements to recommend web services or RDA, both are valid options.

    Saturday, June 23, 2007 2:54 PM
  • Hi Erik,

     

    The SessionDetails table will have tens of thousands of rows, however typically each session will have 1000 entries, so although tens of thousands of rows will be searched during a query only around 1000 rows will be returned for any given SessionID. By your reply do you mean speed will not be an issue if I only 'return thousands of rows from my query', or if the 'entire table is limited to thousands (and not tens of thousands) of rows'?

     

    I think any kind of automatic synching is out of the question, as clearly stated in the link you kindly gave: 'To use identity columns in a replication topology that has updates at more than one node, each node in the replication topology must use a different range of identity values, so that duplicates do not occur', and I would have to specify different ranges for each node which will be impossible for thousands of nodes.

     

    Furthermore each node has no real requirement to sync details back from the database, only some records of the local database absolutely have to be sent to the remote database, the rest can remain local. What I do need however is to keep track on the synched records, therefore I have come up with the following plan which is expanded from my previous comments:

     

    Structurally, all tables are the same for the local and remote databases. However the local database will include an additional table called SynchedRecords containing LocalSessionID and RemoteSessionID for the SessionsTable.SessionID of the local and remote databases respectively. When I insert a Session record to the remote database a remote ID will be returned, I would then store this in the SynchedRecords table with the SessionID for the local record which I sent. Whilst not strictly synching this would achieve what I want, and if I ever deleted the local Session in the local database I could easily check if it exists in the SynchedRecords table and delete the corresponding SessionID on the remote database.

     

    Regarding your final comment, I have been using CSLA business objects and the remoting schemes within that for my local access and my objects are typically running on the database server (remote database as its standard SQL) as per the CSLA paradigm. As I am new to this would this be a good way to manage my synching scheme with standard data access through my business objects to the tables directly?

     

    I appreciate the help, I think I am getting close to a solution on paper with your comments.

    Saturday, June 23, 2007 6:12 PM
  • Hi Chris,

    as long as SessionDetails are indexed, I still think using Guids will not impact performance.

     

    I am not very familiar with CSLA, but as I understand you, this framework will give your the ability to connect to the remote database via .NET remoting? If so, then considering the limited synch requirement of your application I think you have a viable solution.

     

    Good luck with your project.

     

    Monday, June 25, 2007 3:25 PM
  • Hi Erik, thanks very much for your the comments. In that case, i will go for GUIDs as it will make my life much easier and as you pointed out, i dont like worlds of pain!

     

    Cheers

    Monday, June 25, 2007 3:31 PM