SQL CE 3.5 and Data Loss on SD Card

Discussion in 'microsoft.public.sqlserver.ce' started by lindstjp, Apr 6, 2009.

  1. lindstjp

    lindstjp Guest

    We have an application running on a hand device that utilizes SQL CE 3.5 for
    data storage and retrieval. The database is resides on a 2 Gig SD card. We
    are seeing ocassional data loss after the device is powered off and back on
    in the field. In testing we cannot reproduce the behavior. The data loss is
    significant, recently we lost about 2.5 hours worth of transactions from one
    database. The database itself doesn't report corrupt, it just comes back on
    reboot as if it cleanly rolled back all transactions. Printouts and user
    testimony indicate it was functioning correctly prior to the data loss. We
    suspect the SD card may be the culprit. Any thoughts?
     
    lindstjp, Apr 6, 2009
    #1
    1. Advertisements

  2. Jeff,

    As I read your message, my first thought was that the flush interval for
    SqlCe was not set, but even using the default value you shouldn't lose 2.5
    hours worth of transactions. I've never seen anything like that (thank
    goodness!) so I'd agree that trying a different SD card might be the next
    thing to do.
     
    Ginny Caughey MVP, Apr 6, 2009
    #2
    1. Advertisements

  3. lindstjp

    lindstjp Guest

    Actually, the approach we are reviewing is to move the active database off
    the SD card onto the device. We would then use the SD card for checkpoint
    backups during the day to provide quick recovery in the event of device
    failure. Our database size is expected to remain the 10 Meg range. Are
    there any benefits to keeping the database on the SD card?
    --
    Thank you,
    LindstJP


     
    lindstjp, Apr 6, 2009
    #3
  4. As long as you have enough room on the device, there's no real advantage of
    keeping the database on the card unless you plan to retrieve it using a card
    reader attached to a computer or something like that.

    --

    Ginny Caughey
    Device Application Development MVP



     
    Ginny Caughey MVP, Apr 6, 2009
    #4
  5. Hi Jeff,
    Are you using a consumer device or a rugged device? Do you have the latest
    firmware on it?

    The reason I ask is that some devices power down the SD card while in
    stand-by, which may lead to database corruption. We had that issue with
    Symbol MC70 devices, which was fixed with a newer firmware and some new
    settings on the control panel.

    Alberto Silva

    __________ Information from ESET Smart Security, version of virus signature database 3990 (20090406) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
     
    Alberto Silva, Apr 7, 2009
    #5
  6. lindstjp

    lindstjp Guest

    We are using Symbol MC9094 devices. I will check with the supplier
    concerning the firmware. Do you know what settings you adjusted in control
    panel?
     
    lindstjp, Apr 7, 2009
    #6
  7. I don't remember the exact name of the control panel applet - I can check it
    tomorow - but I do remember we had to change the SD card access from 'MMC'
    to 'SD'.

    Alberto Silva

    __________ Information from ESET Smart Security, version of virus signature database 3994 (20090407) __________

    The message was checked by ESET Smart Security.

    http://www.eset.com
     
    Alberto Silva, Apr 8, 2009
    #7
  8. We use Motorola MC9094 devices too, most typically with an SD card. We have
    NOT seen such problems, quite the contrary. We did have some issues with
    corruption in SQLCE 2.0, but not on th MC9094. (And, as you state, you ahve
    no corruption, but rather data loss.) We have found that high quality SD
    cards are faster and more reliable, but have never had loss. We have even
    done evil things to stress test, such as start a loop inserting 20000 rows,
    displaying row count on the screen, and then slamming the battery out onto
    the ground; this prevent the "nice shutdown" logic from being executed when
    the battery is removed in a more typical scenario.

    We almost always use the very latest Motorla platform releases, I think it's
    1.43 on my test device.

    Anyway, so great, I'm not having problems. The main reason I'm writing this
    is to document that the MC9094 can actually be very reliable, and SQL
    database on the SD card can (somehow) be reliable too. I wish I knew how
    our scenarios differed. The best of luck to you in getting to the bottom of
    this.

    James
     
    James Hunter Ross, May 14, 2009
    #8
  9. Hi Alberto,

    Do you know what settings you had to do? Were experiencing similar problems
    on MC70 devices.

    Best regards,
    Johannes
     
    Johannes Wikman, Jul 29, 2009
    #9
  10. lindstjp

    Ron Weiner Guest

    I am also using Sql CE 2.0 databases that resides on SD memory cards on a
    Motorola MC50's. In nearly three years with this application on over 200
    devices we have experienced only one corrupted SDF database. That happened
    when the customer attempted to open the SDF on her laptop with (drum roll
    please) Microsoft Word.

    I attribute our success to opening a connection just before every DB access
    and closing it immediately afterwards. The connection object is global for
    the entire application, so it does not have to be created and destroyed each
    time data access is required. As Sql CE 2.0 is a single trick pony (allows
    only one connection at a time), I check the connection state before each
    open to insure it is not already opened by another process. I also have
    some code that checks to make sure the SD card is ready before attempting
    data access.

    This strategy has worked well for us, and no, we have not seen any
    performance issues related to the constant opening and closing of the
    connection object.

    Rdub
     
    Ron Weiner, Jul 29, 2009
    #10
  11. Since we're running on the Micorosft Dynamics Mobile platform we don't have
    the option to change to Sql CE 2.0. :(

    I canont change the opening/closing logic either.

    So if someone knows some settings that makes Sql CE 3.5 (or 3.0) run better
    with a database on SD card I would be greatful.

    /Johannes
     
    Johannes Wikman, Jul 29, 2009
    #11
  12. Johannes,

    You might consider changing the flush interval on the connection string to
    force writes to "disk" to happen more quickly. If you have the option to
    move to Sqlce 3.5 sp1 that might help too. Generally I wouldn't consider
    going backwards to SqlCe 2.0 to be a wise move even if you could do it and
    in spite of Ron's success with it.
     
    Ginny Caughey, Jul 29, 2009
    #12
  13. I agree with you that a downgrade of Sql ce isn't really a wanted solution.

    The default flush interval is 10 seconds, but we've got databases that seems
    to be missing days of data... So even if a shorter flush interval could be a
    good thing, it doesn't seem to help us here.

    We'll problably have to upgrade to latest version to see if that helps.

    /Johannes
     
    Johannes Wikman, Jul 29, 2009
    #13
  14. lindstjp

    RDub Guest

    Heck! I wouldn't downgrade either. I did not mean to even suggest that the
    OP do a downgrade. I believe that the success we have had has been due to
    the special care we have taken handling the connection object and making
    sure the SD card is ready before we attempt data access.

    Frankly the ONLY reason we have not moved forward with CF 2 and Sql CE 3.5
    is that:
    A) The current solution works.
    B) So many other projects to get done.

    If the OP can not change the way his application interfaces with the
    database, I doubt there is much that can be done in the way of settings that
    will prevent "days of missing data".

    Ron W
     
    RDub, Jul 29, 2009
    #14
  15. Ron,

    I agree that your approach has probably saved you the data loss and
    corruption that others sometimes encounter. I also agree that days of
    missing data is pretty strange unless the database is frankly corrupted.
     
    Ginny Caughey, Jul 29, 2009
    #15
  16. Hi,
    I don't have a Motorola rugged device with me right now - maybe next week I
    will - but as far as I remember, on settings, system tab page, there's an
    applet that will let you change how the SD card is accessed, with 2 options,
    one named like 'SD-MMC' and another which I don't recall. You should switch
    to this one.

    Also make sure you have the latest firmware, I think that some had
    corruption issues on storage cards.

    --

    Alberto Silva
    http://www.moving2u.pt - R&D Manager
    http://msmvps.com/AlbertoSilva - Blog
    Microsoft MVP - Device Application Development


    __________ Information from ESET NOD32 Antivirus, version of virus signature database 4290 (20090730) __________

    The message was checked by ESET NOD32 Antivirus.

    http://www.eset.com
     
    Alberto Silva, MVP, Jul 30, 2009
    #16
    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.