- 4GB ... well I knew of it to begin with
- Linq To SQL No designer support. You can use sqlmetal to generate the dbml file for you though so not a big issue.
- Poor Designer. Really this is what i found most annoying. You cannot even reorder columns (lets not get into the "Its relational and doesn't matter debate... I like my key's on top... enough said). SDF Viewer exists ... but why buy another software?
- Deployment failed. I do not know why. Even when I put all the dll files from the installation into the client machine it failed to work. Even a simple test application purely for connecting does not work. I have no idea why. So I uninstalled my Sql Server compact 3.5 sp1 and installed it again on the development machine. Now even SSMS will not connect to it. Error : "Could not load file or assembly 'Microsoft.SqlServerCe.Client, PublicKeyToken .... ". After trying to install everything from http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx it still will not work. Ah well
- didn't know back then ... right now I think it was probably because of not configuring the DbProviderFactories ... I did it for sqllite. There might be something similar required for SQL Server Compact
Enter SQLite for the rescue : http://sqlite.phxsoftware.com/ Especially check out their video : http://sqlite.phxsoftware.com/sqlite.wmv
One word : AWESOME . Script tables , modify keys, Reorder columns and a whole lot more! Even if the designer does not work in the next version of VS (for a while it will not ... but I will upgrade VS as soon as it comes out stable!) .. I know (and love) python ... so creating my own little script lib to fix it will not be a problem.
Update : An awesome third party open source tool for modification of DB Files: http://sqlitebrowser.sourceforge.net/ Instead of going thorough the headache of providing a data migration tool with EVERY new iteration of my software I can just point my users to this to selectively migrate the data from the old db file as per their demand :)
Not to mention indices / triggers / 2TB official tested.
Maybe : SQL Listening http://www.sqlitening.com/ (But i will hold on to my dear SQL Server knowledge)