 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / Compact Framework / January 2006SQLite vs. SQL Server 2005 Mobile Edition
Hi all, we are gonna have a project with around 1000 mobile Clients in mobile windows 2003. A am evaluating, which Database is the best for this. I searched in all forums and now I think the best options are SQLite and SQL Server 2005 Mobile Edition. I am not familiar with mobile development neither databases. Can you give me a general comparison!? Ah, we want to use Compact Framework 2.0 and C#. I am unexperienced in this area so fast development time is important. Another maybe most important aspect is the license. Is it free if we get licenses for Visual Studio 2005? Or do we have to pay for each client? Also what I didn't understand... can you synchronize a Server Database with a Client Database in case of SQL Server 2005 Mobile Edition? So does this mean, you don't have to do the inserts, updates, blablabla on the client? In fact each of our clients has different data., Thanks Daniel > I am not familiar with mobile development neither databases. Can you > give me a general comparison!? Ah, we want to use Compact Framework 2.0 > and C#. I am unexperienced in this area so fast development time is > important. SQL Mobile has an full, supported managed interface, so development is fast. I think there's one for SQLite as well, but I have no experience with it, so no idea of the quality. Obviously it comes with no support. You're going to want to add time and cost for more robust testing if you go that route (I would anyway). > Another maybe most important aspect is the license. Is it > free if we get licenses for Visual Studio 2005? Or do we have to pay > for each client? It follows the SQL Server model. If the server has a per-processor license, then the clients don't need CALs. If it's licensed per client, they do. > Also what I didn't understand... can you synchronize a Server Database > with a Client Database in case of SQL Server 2005 Mobile Edition? So > does this mean, you don't have to do the inserts, updates, blablabla on > the client? In fact each of our clients has different data., SQL Mobile has full replication support, so yes synchnonization comes with it. -Chris There are quite a few differences between SQL Mobile and SQLite. I'm not really qualified to give a full overview of SQL Mobile's capabilities, but here are some of the features and limitations of SQLite on the Compact Framework: - SQLite is tiny. The core DLL is only 261kb and the ADO.NET 2.0 Provider is 89kb. These are the only two files required for the Compact Framework. The Full Framework merges the two into a single assembly. - SQLite implements most of the SQL92 spec. I've had a hard time coming up with queries SQLite can't handle. Not supported are stored procs and udf's, but SQLite does support custom collation sequences, scalar and aggregate functions. - SQLite's locking mechanism is a little draconic and designed for simplicity. Only one writer at a time can be writing to the database. Other writers are blocked until the first writer completes. No readers can be actively iterating a table while a write is in progress, and a write will wait until all readers are finished iterating. - No nested transactions. Again, SQLite is designed to be "light". - Full portability. Any SQLite database file can be copied from one platform to another regardless of the CPU's byte-ordering. Build your database files on the PC and just copy them over to any PDA. - SQLite is faster than SQL Mobile, and SQLite's database files are smaller. In a couple of simple tests inserting, selecting and updating an Int64, SQLite was more than 10x faster. Inserts/updates that took minutes in SQL Mobile took seconds in SQLite. - No replication support. If you need to merge the PDA's database file to a database file on the desktop, just copy the file over to the desktop, use SQLite's ATTACH statement to attach the PDA's database to your master database, and then write a few custom queries to merge the changes into the main database. This should be pretty trivial. - SQLite is 100% free, and full source code is available in the public domain. No GPL, LGPL, or any other variation of open source license. - The ADO.NET 2.0 Provider is well supported (by me at the moment), is also free (in keeping with the spirit of SQLite) and sources are available on SourceForge. - The ADO.NET provider has VS2005 design-time support. You can create queries, typed datasets, etc with it. I'm working on expanding designer support to eventually include full database management from within VS2005. Creating tables, modifying table definitions, indexes, constraints, etc. For more details on SQLite, what it does and especially how it works in .NET, head over to http://sqlite.phxsoftware.com Robert > Hi all, > [quoted text clipped - 17 lines] > Thanks > Daniel We just did an application where we are handling about 25 mobile clients and we used Ultralite from ianywhere, a sybase company. It comes with a builtin syncronisation component called mobilink. You can consider that too. I personally feel its a very good option. Their developer community is also very supporting and active. Setting up mobilink is easy as we just need to enter sync scripts in plain SQL using its GUI interface. It also has option for generating scripts automatically. You can sync with any ODBC database, we synced with MS SQL Server in our solution. Check out for more info: http://www.ianywhere.com/developer/sql_anywhere.html Oracle also has their mobile database called OracleLite, though I haven't used it but I read that its latest version is compatible to Grid Computing which is now a feature of Oracle Databases. I also don't know that you can sync it with databases other than oracle or not. You can get more info for OracleLite for www.oracle.com and goto their Products > Database section. Regards, Sachin Palewar Palewar Techno Solutions Pocket PC & Mobile Software Development Nagpur, India www.palewar.com Sybase's Ultralite has quite a few limitations, not the least of which is no ADO.NET-compatible provider, which locks you into their custom API. As for Oracle's mobile product ... I shudder at all things Oracle. As soon as they start mentioning things like "java stored procedures" on the embedded platform, I'm pretty much assured it will run like a 3-legged dog. Robert > We just did an application where we are handling about 25 mobile > clients and we used Ultralite from ianywhere, a sybase company. It [quoted text clipped - 27 lines] > > www.palewar.com Robert I think you checked out sybases's ultralite some time ago. As of now they have a ADO.NET provider. ianywhere.data.ultralite is the namespace for ADO.NET provider, whereas ianywhere.ultralite is for their legacy API which is now being phased out. Regards, Sachin Palewar Palewar Techno Solutions Pocket PC & Mobile Software Development Nagpur, India www.palewar.com thanks for your suggestions! I checked the ADO.NET page and found the encryption point: * "Create and encrypt databases using Win2K+ Encrypting File System (EFS). Supports creating NTFS compressed database files." Does this mean, that my db is safe in case of loss of a mobile device, cause only my application knows how to encrypt the db? We also want to get a backup of the db on a memory card. Which options do those different db's offer? I could think of just copying the file every evening to a memory card. More realtime would of course be, to do a backup action after every commited transaction. Which options do the db's offer? Daniel one other point: we have to synchronize assignments between a server-db from a jboss-webserver to our client. At the moment we think about transferring xml-files between client and server and viceversa. Two problems: transaction security and velocity! With sql mobile or Ultralite would it be possible two handle those two things? Do you have any experience with that? Daniel Daniel, encryption is also supported by most of the DBs, also you can protect your database by a password, so even if your device is lost, database can't be open by others without knowing your software. SQL Server CE has a single SDF file for database, which you can copy to take backup. Ultralite also has UDB file for database. I will say that backup is normally not an issue in device if you are synching your mobile database with your server database continuously. Mobile devices are used as temporary storage devices in most cases and they keep sending data to server periodically. Backup etc can very well be handled at server. Sachin Palewar Palewar Techno Solutions Pocket PC & Mobile Software Development Nagpur, India www.palewar.com Sachin, we will not synchronize with a server database cause we use our own system (application server JBoss), but need to backup the actual data before the syncronization. But of course with a single file it's no problem. Daniel Hi Sachin, > encryption is also supported by most of the DBs, also you can protect > your database by a password, so even if your device is lost, database > can't be open by others without knowing your software. encryption is maybe supported by most of the DBs, but not by SQLite in the free version. I am playing around with SQLite and it is more than 5x faster than SQL Server 2005 Mobile. The great advantage of SQL Server is its integration in the MS development process with Management Studio and so on, and of course, the possibility to safe the database file by setting a password. But these advantages are killed by the bad performance. I have to develop an application which comes with ~ 15.000 records per table, and I've tested it with ~ 1500, so I think I can't use it. I am now looking for a "encryption or password" solution for securing the SQLLite database for unauthorized access. It does not have to be 100% secure, but the user should also not only need to open the file to get the data. To come to the point - is there any way to get it secure without paying 2000 Dollars (this would make the project gainless)? Regards from Germany, Thomas http://blogs.dotnetgerman.com/thomas/ > To come to the point - is there any way to get it secure without > paying 2000 Dollars (this would make the project gainless)? A few weeks (or months?) ago someone posted a link to a self-made encryption extension to the SQLite mailing list. Unfortunately I cannot find the link at the moment... You could also modify the SQLite's OS layer (sqlite3OsRead and sqlite3OsWrite functions for instance) to implement your own encryption and decryption layer. Greetings, Christian Hi Christian, >> To come to the point - is there any way to get it secure without >> paying 2000 Dollars (this would make the project gainless)? > > A few weeks (or months?) ago someone posted a link to a self-made > encryption extension to the SQLite mailing list. Unfortunately I cannot > find the link at the moment... Thanks for advice, I'll be looking about it. > You could also modify the SQLite's OS layer (sqlite3OsRead and > sqlite3OsWrite functions for instance) to implement your own encryption > and decryption layer. Yes I could, but it would propably be cheaper to pay the 2000 $ than to develop it by my self. ;-) Greetings, Thomas http://blogs.dotnetgerman.com/thomas/ >> A few weeks (or months?) ago someone posted a link to a self-made >> encryption extension to the SQLite mailing list. Unfortunately I cannot >> find the link at the moment... > > Thanks for advice, I'll be looking about it. You can get the encryption extension here: http://www.voidbrain.com/sqlitesec.html Greetings, Christian Christian Schwarz schrieb: > You can get the encryption extension here: > http://www.voidbrain.com/sqlitesec.html Thank you very much :) I'll try to combine it with the ADO.NET 2.0 Provider [1] for SQLite. [1] http://sourceforge.net/project/showfiles.php?group_id=132486 Regards, Thomas http://blogs.dotnetgerman.com/thomas/ Hi again, Christian Schwarz schrieb: > You can get the encryption extension here: > http://www.voidbrain.com/sqlitesec.html I tried to combine it with the ADO.NET 2.0 Provider from Robert, but with no success - I have not enough experience in C/C++ to get it running :( Does anyone have this experience? I think it could'nt be a big thing to extend the Interop-Projekt including full SQLite engine to the security features ... Greetings, Thomas I'll be looking into this carefully after the 1st of the year. Robert > Hi again, > [quoted text clipped - 11 lines] > > Greetings, Thomas Windows Mobile doesn't store its files in NTFS format, and the Encrypted File System that's part of NT is not part of Windows CE. However, the idea of encrypted SQLite databases isnot unreasonable given the extensible nature of the underlying file system. Dr. Hipp provides an encryption addon for a fee already, but I have not seen it personally. With any file-based database engine, copying the file to a memory card is a trivial operation. Robert > thanks for your suggestions! > [quoted text clipped - 11 lines] > > Daniel Hi Robert, we made the decision to use SQLite with your ADO.NET Wrapper in our project! Evaluated UltraLite and Oracle Lite and now we think that SQLite offers us all we need. Also first steps with your wrapper went fast! Not so with UltraLite. So, again, to understand that fully I have to ask again: We are planning to buy the encryption-extension of D. Robert Hipp. Is it then possible to start the encryption with your wrapper? (Windows Mobile 2003, Compact Framework 2.0) Thanks for your help Daniel Hi, > So, again, to understand that fully I have to ask again: > We are planning to buy the encryption-extension of D. Robert Hipp. Is > it then possible to start the encryption with your wrapper? (Windows > Mobile 2003, Compact Framework 2.0) good question! :-) I evaluated also some databases and decided a few minutes ago to use SQLite with the ADO.NTE Wrapper of Robert. It's simply faster and more easy to deploy than other solutions especially SQL Server Mobile. My customer decided to do the first version without encryption, but if the product sells well, I also have to implement a encryption extension. And if Robert or somebody else won't implement SQLiteSec in early 2006 I am also planning to buy the extension of Robert Hipp. That's because I am also interested in the answer :) Greetings, Thomas I haven't seen DRH's encryption code, so I can't say for sure. A couple issues: 1. The "official" SQLite source code and distribution only include Win32 and *nix support. PPC support is an add-on, based on the original PPC extensions by Nuno Lucas. Therefore without having seen DRH's encryption pack, I can only speculate whether or not it will work on the PPC. I am reasonably sure it will, but cannot guarantee it because I haven't seen it. 2. If you buy DRH's encryption code you'll have to recompile the SQLite.NET wrapper yourself to include the extensions. Again, I can only assume that everything will work properly. If it doesn't, then technically I'm not allowed to see that source code and therefore can't help you isolate the problem. This would be up to DRH to allow or disallow. 3. The SQLite core codebase naturally allows for a storage extension that can perform encryption and decryption. I am currently evaluating the possibility of spinning the Crypto API into it. If I did this, it would ruin portability of the database to non-Windows platforms -- but if you can live with that, it is could appear in my code as early as January '06. I certainly don't want to take the bread out of DRH's mouth by offering a completely free, portable encryption provider for SQLite, so using the Crypto API seems like a feasible alternative. Robert > Hi Robert, > [quoted text clipped - 11 lines] > Thanks for your help > Daniel Sorry for the old topic bump, but the latest beta version of the ADO.NET 2.0 provider for SQLite now supports encryption at the pager level using the Crypto API, and should work on the Compact Framework. http://sqlite.phxsoftware.com > thanks for your suggestions! > [quoted text clipped - 11 lines] > > Daniel Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.