In this article we look at often overlooked Microsoft SQL Server Compact Edition and understand its relevance.
In a hyper-connected information driven world, databases play a critical role in storing and managing data effectively. Over time several types of databases have evolved which are tailored to specific needs. If we take the case of SQL Server, there are editions which are aimed at large companies (Enterprise Edition) to ones that are targeted towards small businesses (Express edition). Similarly for mobile applications and embedded solutions one can look to use the SQL Server Compact edition. As its name suggests, this database variant has a limited footprint and can be freely distributed.
The SQL Server Compact edition is specifically designed by Microsoft for supporting applications to be used on mobile phones and desktop. It can also be used as a data reserve for computer applications. Its upgraded version is available as Microsoft SQL Server Compact 4.0; it assists the .NET foundation, thereby becoming adept for windows mobile thereafter. This database is free to download which makes it a great tool for developers looking to test their applications.
The compact edition sports an API similar to that of other Microsoft SQL Server editions. It incorporates ADO.NET suppliers for retrieving data by using ADO.NET APIs. It also extends support for Language Integrated Query (LINQ) and EF. One of its unique features is that it can work in tandem in-memory with the application which is hosting it.
It is important to note that SQL Server Compact edition has now been depreciated nevertheless, it is still readily used in many small scale organisations; for instance, it can be useful in maintaining attendance records. Microsoft announced this in 2003 and it has no further policies of providing forth any of its upgraded versions; however it would continue to support it through its normal support lifecycle. One of the key aspects behind its continuing popularity is its small size. The fact that it consumes just 5MB of memory space and takes just 2MB on your disk drive makes it an ideal choice for developers. Several desktop applications and intranet solutions today take advantage of this small footprint to deploy custom applications.
A Bit about file format used in Microsoft SQL Server Compact Edition
For SQL files we use the abbreviations of .sdf files and its maximum size can extend up to 4 GB. This aspect of labelling the file is not a convention; in fact one can use any extension. It is highly flexible, which is to say that one .sdf file can be accessed by multiple users at a given point of time. Secondly, it can be copied to the targeted system for further usage of any kind. The .sdf data file can be protected with 128bit encryption and it is possible to set a password for the database file.
Even the compact edition of SQL Server database can crash
The Ms SQL Server Compact edition database file barely goes over a certain size and is relatively less prone to SQL crashes. However when it does crash, recovering the data is a challenge because typical fail safe measures used on a production server are not commonly used while working on the compact edition. In such a scenario you can call in a sql server recovery tool like DataNumen SQL Recovery to get back the compromised records. This powerful software can extract all the contents stored in the database file with aplomb and can handle nearly every other data type with the exception of XML format.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/