So you are a developer who works on applications that run on the desktop; or you are a project manager who has only heard of SQL Server Compact Edition… In any case, if you have hardly heard of SQL Server CE, or you are assuming that SQL Server CE runs only on portable devices, then you should read on to learn about some new powerful options available for your environment of choice.
Well, SQL Server CE in not only intended for devices who run on the Windows CE platform (Windows Mobile, Xbox 360 and many others among then) but also for PCs running XP and Vista:
WHAT? You didn’t know? Never mind, keep reading then…
While SQL Server comes in many flavors, SQL Server Express is not the smallest of them all. Yes, it’s free and it can run in your desktop if you want it… but do you really want to run a SQL Server on your desktop? Is that the smallest you can go?
While you should use a service whenever appropriate, common sense will indicate you to use something even smaller if the application only requires a store for data that can be easily accessed. Yes, you could access that application data if you store it in plain (or custom) data files, or you could build your own XML data structures, but those are plain ASCII file with either little or no security. If you secure them with encryption services, one of the many available APIs, accessing those files will happen at a much higher cost than your plain ASCII counterparts.
So, This is where SQL Server CE comes along to the rescue. SQL Server CE keeps the name "SQL Server" for consistency’s sake but is smaller than a server. In fact, it does not even run as a standalone service, it runs in-proc with your application only when instantiated and provides many of the existing features in SQL Server while keeping execution cost at a minimum. There are versions of SQL Server CE available for the PC and Windows CE (where it originated), but Microsoft saw a strong potential in this tool for the desktop and pushed for its further development as a clear alternative to a full blown service.
SQL Server CE has a smaller footprint than a regular SQL Server service by having some limitations such as running in-proc with your application, not having stored procedures or views. However, running in-proc could be seen as a powerful feature instead of a limitation, because you don’t really need to have your server running and eating precious CPU cycles off your desktop/mobile device all the time. Instead your SQL CE will behave as if it "wakes up when needed" creating the illusion of an always available, but not always eating processor cycles. Additionally; even though it runs in-proc; you could still have multiple applications within a single computer/device accessing the same SQL CE database, or you could have multiple clients accessing a shared database. Again, please have in mind that this version is intended for use where you would have not used SQL Server and would have used a different type of data store. It can be taken to extremes, but at those extremes, the functionality needed may overlap with other SQL Server flavors and you may want to choose those. In summary: choose wisely.
Many features can be brought up as being pretty cool in SQL Server Compact Edition, however; in my opinion, one of the most important strengths of this tool is the ability to synch back to any other SQL Server. This would enable you to store only the part of the corporate data that is needed as a distributed portion right where your application resides and only on scheduled intervals or whenever triggered have the databases synch up for replicating the data in, out or both ways. You could have a complex merge replication scheme to help your different locations see only what pertains to their location without having to distribute a server to them. This would allow for distributed applications with smartly-distributed databases without the need for server distribution… in corporate terms that could amount to an amazing amount in savings!
If that wasn’t enough; here’s the best part: IT’S FREE! Yes, you read it right, you can download SQL Server Compact Edition for free from right here:
- SQL Server Compact 3.5 for Windows Desktop (includes Synchronization Services for ADO.Net): Small 1.8 MB free redistributable runtime database engine for developing and running applications on Windows desktop operating systems.
- SQL Server Compact 3.5 for Windows Mobile: Extend enterprise applications to mobile devices by rapidly building and deploying SQL Server Compact 3.5 based application with Visual Studio 2008.
- SQL Server Compact 3.5 Server Tools: Replicate data between SQL Server Compact 3.5 and SQL Server 2005 by using merge replication and remote data access (RDA).
- SQL Server Compact 3.5 Books Online and Samples: Learn about new features, work through how-to applications and samples and get up to speed easily with SQL Server Compact 3.5.
- Microsoft Synchronization Services for ADO.Net Books Online: Learn more about the new data replication technology and start developing intelligent occasionally connected applications.