How to create .SDF file programmatically on the server for MR

Discussion in 'microsoft.public.sqlserver.ce' started by Dev, Jul 28, 2009.

  1. Dev

    Dev Guest


    We have an SQL CE 2.0 .SDF file that gets generated for the first time
    after a successful Merge Replication process against a SQL 2000 SP4

    The size of the .SDF file is about 18MB and our Windows Mobile
    subscribers that replicate for the first time take about 15 to 20
    minutes (depending on network speed) to download the file on their

    Is there a way to create the .SDF file on the server and once a user
    wishes to replicate for the first time with the server, they would
    download a compressed version of that file instead of initiating a
    full merge replication procedure?

    If this is not possible in SQL CE 2.0, can it be done using SQL
    Compact 3.5 ?

    Thank you for your time,
    Dev, Jul 28, 2009
    1. Advertisements

  2. Hi,
    In fact it is not possible at all with SQL CE 2.0, which was never supported
    on desktop & server Windows, but with SQL Compact 3.5 you can do it. Beware
    that after opening the file on the device the first time, some reindexing
    may happen, which will take some time, and that you should not use the
    platform default encryption mechanism, because it may not be supported on
    the device. Also, if you have dynamic filtering, you cannot share a 'base'
    SDF, you must instead create a SDF for each subscriber.


    Alberto Silva - R&D Manager - Blog
    Microsoft MVP - Device Application Development
    Alberto Silva, MVP, Jul 28, 2009
    1. Advertisements

  3. Well, you might be able to use one of the Windows Mobile emulators, running
    on the PC, and then copy the resulting database file from there back to the
    PC filesystem where it could be synced to the real devices.

    You might also look at:

    It is completely easy in SQL Compact 3.5, as that runs on the PC directly,
    as well as on the devices. You can take your device program, port it to run
    on the PC and the replication will occur directly to wherever your program
    tells it to be placed in the PC filesystem.

    Paul T.
    Paul G. Tobey [eMVP], Jul 28, 2009
  4. Dev

    Dev Guest

    Hi Paul

    So, If I understood correctly, you suggest that I could port the code
    responsible for creating a fresh subscriber db to run as a windows
    desktop application on my server and create a fresh .SDF file
    periodically, say every night at 23:00.

    Then, I could incorporate some functionality in my Windows Mobile
    application to download a compressed version of the .SDF file through
    HTTP and use this instead of initiating a FULL merge replication
    session that could take ages to complete, correct?

    Once I download the .SDF file to my Windows Mobile application, do I
    need to execute any special commands so as to use it, or I can
    normally just open it and start reading/writing data from/to it ?

    Thank you for your help,
    Dev, Jul 29, 2009
  5. Dev

    Dev Guest

    Hi Alberto,

    I do not use dynamic filtering, since all subscribers need to download
    the same data.

    Thank you for the help!
    Dev, Jul 29, 2009
  6. Yes, it should be very simple to generate your *starting point* database on
    the PC using SQL Compact there, rather than on the device. The user
    interface of the code might have to change if you are using lots of Windows
    Mobile-specific stuff, but the data interface to the database should be
    almost a straight copy to the desktop. You might even be able to run a
    generic EXE directly on the desktop without even recompiling. This creates
    an initial state of the local database that you can use anywhere SQL Compact
    3.5SP1 is installed, including on your device.

    Yes, you could then decide what the right thing to do is with respect to
    getting the data to the device. Compress it with ZIP, say, send it over
    HTTP or FTP, and uncompress it on the device. This would give you a new
    starting point for the local database. Once you have that, you can then
    begin using that data for merge replication with the main database, if you
    want. So you have the initial data, which might be large, all in one
    download, and the changes, which are presumably few, compared to the size of
    the initial data, get updated periodically through merge replication. Of
    course, if your database is download-only, you could download a new version
    every night, but that's not actually what I'm suggesting.

    Yes, you can just open it and start using it.

    Paul T.
    Paul G. Tobey [eMVP], Jul 29, 2009
  7. Dev

    Dev Guest

    Hi Paul,
    Thank you very much for your detailed response. I really appreciate
    your time and effort with regards to my question.

    Dev, Jul 29, 2009
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.