MSDE on flash memory

[Guest]

Suppose to install an MSDE database on a system based only on a solid state memory (operating system is XP embedded)
The system has not an UPS, so user can power down it in any moment
Of course last transaction could be lost, but which are the best setting for MSDE to grant that the database works properly on next restart?

 

[KM]

flashgordon,

Have you considered having a DISK Overlay? You could move the SQL transaction log and data to protected partition (better be not the
system one) and commit changes on shutdown? Then the transaction log will keep integrity.

Although it may not be a good idea on a solid state memory as you wear out your flash, you seem not to have EWF protection now
anyway.

--
Regards,
KM

Suppose to install an MSDE database on a system based only on a solid state memory (operating system is XP embedded).
The system has not an UPS, so user can power down it in any moment.
Of course last transaction could be lost, but which are the best setting for MSDE to grant that the database works properly on

next restart?

 

[Guest]

KM
I have remarks and question
Rem1: in every case I could have an integrity problem, because there is not a shutdown procedure, but (it is an industrial machine) the user "turn off" the machine. So I think (or I am wrong?) that if I have a poweroff during transaction log writing......
Q2: Normally we have about 20000 records. After reaching 20000 we delete the oldest and insert a new one (FIFO) every 5 minutes. Can MSDE (on xpe) manage his datafile to "distribute" writing on different flash memory areas to maximize flash lifetime
Q3: Has SQL server CE (normally on flash memory???) the same problems or the architecture has been studied to avoid flash memory lifetime problems

We have a lot of question, but the MOST important thing is to understand if MSDE (XPE ) or eventually SQL server CE (Windows CE platform) on a solid state memory disk is applicable or if it is a bad idea
The most important thing for me is
1. HAVE A SOLID STATE MEMORY AND NOT A TRADITIONAL HD
2. POSSIBILITY TO POWEROFF SYSTEM WITHOUT SHUTDOWN PROCEDURE WITHOUT CORRUPTING ANYTHING (TRANSACTIONS LOST ARE NOT IMPORTANT

flashgordon

 

[Slobodan Brcin \(eMVP\)]

Q2, Q3:

Check out:
http://www.m-sys.com/Content/Products/DiskOnChip.asp
http://www.m-sys.com/Content/Corporate/technology/TrueFFS.asp


1. This is not a problem if you can fix your data and image on flash medium.
2. You must or at least should use RAM EWF to protect C: (XPe partition), so this will save your registry and OS.

Regarding second unprotected partition used for data storage. You will have to be careful about your database and filesystem
choices.
I always preferred using RAW partitions from our applications and storing data in manners that are suitable for certain medium and
usage.

So if you want to use data locally for your application only. Use raw partition for 200000+ entries if FIFO manners, this will allow
writes so same block at 50 minutes instead of 5.

Also be ware that to write to flash it must first be deleted. And this is usually done in blocks of size 512+some internal integrity
check data not seen by os.
So if you write records of size less that 512 bytes be ware that you might write to same cell few time until you go to next cell.
So you should gather at least 512 bytes if RAM before you do write to some flash sector.


My question is: What is the size of your record?

Regards,
Slobodan

KM,
I have remarks and question:
Rem1: in every case I could have an integrity problem, because there is not a shutdown procedure, but (it is an industrial

machine) the user "turn off" the machine. So I think (or I am wrong?) that if I have a poweroff during transaction log
writing.......

Q2: Normally we have about 20000 records. After reaching 20000 we delete the oldest and insert a new one (FIFO) every 5 minutes.

Can MSDE (on xpe) manage his datafile to "distribute" writing on different flash memory areas to maximize flash lifetime?

Q3: Has SQL server CE (normally on flash memory???) the same problems or the architecture has been studied to avoid flash memory lifetime problems?

We have a lot of question, but the MOST important thing is to understand if MSDE (XPE ) or eventually SQL server CE (Windows CE

platform) on a solid state memory disk is applicable or if it is a bad idea.

 

[KM]

flashgordon,

Comments to your Remark1: that was exactly the reason why I suggested DISK Overlay (not RAM). The DISK Overlay will allow you to
have a few levels of the caching. Therefore you may end up corrupting the transaction log in top level but still have some "old"
version of the db data and transaction log in lower levels to restore from.
Again, this might not be a good solution (or not a solution at all) in your case depending on your device usage.

Btw, I was wondering if it is considerable for you to move the DB on a server? E.g., having a good scalable and client
transactioning server (SQL 2000 or if you think of very many client - more scalable db engine) may help you to ensure data integrity
on the server side only. Terminals will only have to send data over network and commit the transaction.
Of course, this approach is useless to you if you cannot have network on the device.

Q2: I don't think MSDE has any "embedded" specific integration including flash random writing or etc. Keep in mind that MSDE on XPE
is just a componentized (not even ported!) version of MSDE for XP so the binaries are the same.
Up to some point you may think this way - if you can't potentially implement what's you need with XP Pro, you will unlikely make it
working with XPe (some exceptions, of course, are there - EWF, other EEFs).

Q3: I don't think SQL CE (not coming Yukon, though) has any flash related issues fixed either. They even use the compaction process
to maintain the database to avoid fragmentation and get better performance. As you can image it is in contradictory to what you are
trying to get for your device.
Don't forget that SQL server may be used on many different devices and it would be hard (or useless) to optimize it for flash device
use only.
Basically, SQL CE is a big port of SQL 2000. Although, this needs to be confirmed by experts from SQL CE team (newsgroup).

As Slobodan has already pointed out, you may want to switch to your own DB format and implementation. Then you may (will) be able to
implement your custom embedded device scenario with even data transactions and logs. However, you would definitely need to think
more of the data you are FIFO'ing on the device and about the flash (or whatever storage you are going to use).

--
Regards,
KM, BSquare Corp.

KM,
I have remarks and question:
Rem1: in every case I could have an integrity problem, because there is not a shutdown procedure, but (it is an industrial

machine) the user "turn off" the machine. So I think (or I am wrong?) that if I have a poweroff during transaction log
writing.......

Q2: Normally we have about 20000 records. After reaching 20000 we delete the oldest and insert a new one (FIFO) every 5 minutes.

Can MSDE (on xpe) manage his datafile to "distribute" writing on different flash memory areas to maximize flash lifetime?

Q3: Has SQL server CE (normally on flash memory???) the same problems or the architecture has been studied to avoid flash memory lifetime problems?

We have a lot of question, but the MOST important thing is to understand if MSDE (XPE ) or eventually SQL server CE (Windows CE

platform) on a solid state memory disk is applicable or if it is a bad idea.

 

[KM]

A little correction here..

Q3: I don't think SQL CE
(not coming Yukon, though)

Meant Laguna (SQL CE 3.0) here, of course. Sorry for any confusions.

Interesting optimization feature (runtime improment) coming with Laguga -
"Re-use spent storage pages"
(http://download.microsoft.com/download/e/1/e/e1e86586-a01e-41e9-91a9-6a12de
d994e4/Windows%20Mobile%20Developer.ppt).
This doesn't sound good if you use it on flash (without TrueFFS in place, of
course).

I've also heard that they are going to use 4k cache for flashing data to
storage. This may improve usage on CF a bit. However, FS changes are
required anyway.