A decade has passed since I first started using SQL CE on my Compaq iPAQ. What started as a great upgrade to Pocket Access turned into the ultimate embedded database for Windows CE, the Pocket PC, Windows Mobile and Windows Phones. The one-two punch of Outlook Mobile synchronizing email with Exchange and SQL Server Compact synchronizing data with SQL Server helped set the mobile enterprise on fire. In 2005, version 3.0 supported Windows Tablets and progressive enhancements to the code base led to full Windows support on both x86 and x64 platforms. With the new version 4.0, the little-database-that-could has grown up into a powerful server database ready to take on the web.
We’ve come a long way and you’re probably wondering what qualifies this new embedded database to take on the Internet:
- Native support for x64 Windows Servers
- Virtual memory usage has been optimized to ensure the database can support up to 256 open connections – (Are you actually using 256 pooled connections with your “Big” database today?)
- Supports databases up to 4 GB in size – (Feel free to implement your own data sharding scheme)
- Developed, stress-tested, and tuned to support ASP.NET web applications
- Avoids the interprocess communications performance hit by running in-process with your web application
- Row-level locking to boost concurrency
- Step up to Government + Military grade security SHA2 algorithm to secure data with FIPS compliance
- Enhanced data reliability via true atomicity, consistency, isolation, and durability (ACID) support
- Transaction support to commit and roll back grouped changes
- Full referential integrity with cascading deletes and updates
- Support ADO.NET Entity Framework 4 – (Do I hear WCF Data Services?)
- Paging queries are supported via T-SQL syntax to only return the data you actually need
Wow, that’s quite a list! SQL Server Compact 4.0 databases are easily developed using the new WebMatrix IDE or through Visual Studio 2010 SP1. I’m loving the new ASP.NET Web Pages. It reminds me of the good old days of building web applications with Classic ASP back in the 90’s with Visual InterDev and Homesite.
What about Mobility?
Since SQL Server Compact owes its heritage to mobile and embedded versions of Windows, you might be wanting to know what our story is there. The good news is that you can build and deploy v4.0 databases on Windows XP, Windows Vista, and Windows 7. If you want to implement an occasionally-connected solution that utilizes the Sync Framework, Remote Data Access (RDA), or Merge Replication, you’ll need to stick with SQL Server Compact 3.5 SP2. Time and resource-constraints prevented the Compact team from enabling these features. Luckily, single-user WPF/WinForms database applications running on Windows Slates, laptops and Windows Embedded Handheld devices will work just fine with the v3.5 SP2 runtime. Get a jumpstart with this by pickup up “Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge Replication” at http://www.amazon.com/Enterprise-Synchronization-Microsoft-Compact-Replication/dp/0979891213/ref=sr_1_1?s=books&ie=UTF8&qid=1281715114&sr=1-1 to start building those MEAP solutions.
With the tidal wave of Windows Slates hitting the market, a secure, powerful mobile database that allows users to work offline and syncs with SQL Server is definitely going to be a hot item!
So run, don’t walk to the Microsoft Download site to download the Next-Gen database for the web:
If you need to support occasionally-connected mobile applications with sync capabilities on muliple Windows platforms, download SQL Server Compact 3.5 SP2:
Sharing my knowledge and helping others never stops, so connect with me on my blog at http://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany