When using SQL Compact on Windows Mobile, do you store the sdf file on the memory card?
Having had some Sql Compact db corruption issues in the past and looking at the article on these , I realized that saving the sdf database file to the memory card significantly increases the risk of data loss due to db corruption.
Is the sdf file saved on the memory card? Have you had any problems caused by this? What to look for when recommending a specific brand or model of SD card to be stable and secure for use with SQL Compact?
a source to share
Having a database on a persistent storage volume (be it a card or internal Flash) increases the potential for corruption, and this is almost always due to a sleep cycle. When the device is paused, the storage is usually shut down, and when it resumes, it is mounted again. Depending on the OEM implementation, connections may or may not survive this breakdown and build well (I've seen devices where a connection made through a sleep / wake cycle always fails, although not always corruption).
My typical methodology for devices is that a device that is in standby / sleep mode while running, always closes the database after every "transaction" with the database. Yes, it slows things down, but it significantly reduces both corruption and connection invalidation.
I even did this to further extremes by watching for power dispatcher events in the DAL and discarding it and repeating any actions on the data it is running during a power state change.
a source to share