locked
Snapshot Initialization of SQL Express db RRS feed

  • Question

  • Hi

    I have modified the WebSharingAppDemo-SqlProviderEndToEnd sample and all is working fine. I have several SQL Server express 2008 clients and a SQL Server 2008 server. Synchronisation is initiated on the clients. There is a lot of data in the database and it can be slow if user is on a VPN connection so i wanted to implement snapshot initialization. I can see lots of info on using SqlCeSyncStoreSnapshotInitialization to initialize SQL Compact Db snapshots but cant find how to do this for SQL Express 2008, if indeed it is possible at all ?

    Hope someone has the answer

    regards
    Eliot 

    Wednesday, May 12, 2010 9:28 AM

Answers

  • afaik, GenerateSnapshot is only available for SQL Compact and has not counterpart for SQLExpress/Server.

    You can however still use a SQL Compact snapshot to transfer the initial set of data to a SQL Express client.

    1. generate a SQL Compact snapshot

    2. On the client side, sync the SQL Compact snapshot to get any incremental changes from the server from the time you did the snapshot

    3. then provision SQL Express using the SQL compact snapshot via SQL Compact's SqlCeSyncDescriptionBuilder.GetDescriptionForScope and SqlSyncScopeProvisioning

    in effect your SQL Express client will get its initial set of data from the SQL Compact snapshot rather than downloading it directly from the server.

    hth

     

     

    Wednesday, May 12, 2010 11:56 AM

All replies

  • afaik, GenerateSnapshot is only available for SQL Compact and has not counterpart for SQLExpress/Server.

    You can however still use a SQL Compact snapshot to transfer the initial set of data to a SQL Express client.

    1. generate a SQL Compact snapshot

    2. On the client side, sync the SQL Compact snapshot to get any incremental changes from the server from the time you did the snapshot

    3. then provision SQL Express using the SQL compact snapshot via SQL Compact's SqlCeSyncDescriptionBuilder.GetDescriptionForScope and SqlSyncScopeProvisioning

    in effect your SQL Express client will get its initial set of data from the SQL Compact snapshot rather than downloading it directly from the server.

    hth

     

     

    Wednesday, May 12, 2010 11:56 AM
  • Hi JuneT

    Thanks for the idea i think this will work great for my circumstances. 

    Eliot

    • Marked as answer by EliotRayner Wednesday, May 12, 2010 4:12 PM
    • Unmarked as answer by EliotRayner Thursday, May 13, 2010 3:20 PM
    Wednesday, May 12, 2010 4:12 PM
  • I have a trouble with multiple clients nodes using sql server express 2008 and a central node using sql server 2008 r2. The problem shows when a insert data in client 1 and sync, the data is copied to central node, then insert data in client 2 and sync, the data is also copied to central node, I sync again each node with the central node and Client 1 is not obtaining the data from client 2 the same goes for client 2 with the data of client 1. The server has the data from all of them. After that, every insert in client 1 is now not reflected in central node, just insert from client 2. What we initially did was to provisioned the server then one client and after that make a backup of the db and restore it in the other client. Any help would be apreciated. Thanks

     

    Monday, January 23, 2012 9:38 PM
  • when you use a backup of a database to initialize a client, make sure you run PerformPostRestoreFixup on the restored copy before you sync.
    Tuesday, January 24, 2012 1:27 AM
  • Thank U for the info and your time. I just have one question in mind now. We may have to do everything from scratch again, the provisioning and everything else. Can and I use a back up from the central server (with its data), deprovision it, copy it to Client 1 node, provision it and after that copy the Client 1 node DB backup to the other clients node and run PerformPostRestoreFixup over those Clients? Could I expect any problems doing that? Thanks in advance.
    Tuesday, January 24, 2012 5:23 PM
  • when you initialize Client 1 with a deprovisioned back up of the server, your client will have the same set of data with the server.

    now, when you provision and sync Client 1, it will try to sync its existing data to the server and since those same data already exists on the server, it will fire conflicts. if you have a million rows, that's a million conflicts and that will slow down the first sync.

    i suggest you just backup the provisioned server database, restore it on every client and just make sure to run PerformPostRestoreFixup on every client restore before they do their first sync.

    Wednesday, January 25, 2012 2:17 AM
  • Hey we have try your solution and looks really good. Thanks for your help and your time. God bless U. 
    Monday, January 30, 2012 3:12 PM