.net, microsoft, professional, sql

Microsoft LocalDB: another option for local databases

6 Comments

Microsoft is launching SQL Server 2012 on  March 7th 2012. In Microsoft’s world “launches” do not always coincide with the availability of release code, which may come before or after, but they are usually not far apart.

The big news in SQL Server 2012 is in new BI (Business Intelligence) features and the ability to import and export from the open source Hadoop framework. Microsoft is also supporting Hadoop on Windows Server and Windows Azure. Robert Sheldon has an excellent article on TechTarget which describes the Hadoop integration.

At the other end of the scale though there is a new approach to local databases, which interests me as this is the kind of thing an application developer might use for local applications. SQL Express LocalDB uses the full SQL Server Express engine but does not require a SQL Server service to be running or even installed. In summary:

  • The LocalDB binaries can be installed with a separate installer or as part of the SQL Server Express.
  • LocalDB instances are isolated to the user.
  • The LocalDB system databases are buried deep in AppData in the user profile. The default location for user databases is the root of the user profile.
  • The old SQL Server User Instances are now deprecated

A driver for LocalDB has to know how to fire up the SQL Server binaries if they are not running, which means that old drivers will not work. Microsoft has patched System.Data.SqlClient in .NET 4 to work with LocalDB.

LocalDB Pros and cons

The advantage of LocalDB over the cut-down Compact Edition is that you get full access to SQL Server features including transactions, stored procedures, geographical data types and so on. It is meant to improve on the old user instances by simplifying matters for the user: no need to run a service, and management of SQL Server completely hidden.

The disadvantage is that your app still has the overhead of SQL Server running in a separate process. A SQL Server LocalDB install also takes around 140MB, which bumps up the download size if your app is distributed on the web.

If you need a local database, it seems to me that Microsoft still has nothing that quite matches SQLite, which runs in-process, is lightning fast, and which does not require any hidden system databases.

On the other hand, it might make sense to use SQL Server if you want to integrate with a server database, or if you are familiar with coding for SQL Server.

I would like to see Windows ship with a local database engine documented as something developers can rely on being there, as with Core Data on the Mac. It would also help if the SQL Server team got together with the Office team and worked out how to get Access and SQL Server Express to use the same database engine – yes, I know Access can use SQL Server data, but it still defaults to its own .ACCDB format and JET database engine.

6 thoughts on “Microsoft LocalDB: another option for local databases”

  3. Microsoft should probably just bite the bullet and integrate SQLite.

    @Samuel: the author in the article you link seems to have forgotten to use transactions in his benchmark, see in the comments… Once they are used, the figures reverse dramatically in favor of SQLite. Also, CE DB isn’t really ACID compliant.

  4. Microsoft is not targeting SQL Express at the SQLite market. SQL Express is designed for low-volume server applications that may grow later. I once saw an EMR application that installed using SQL Express by default (a physician just starting a practice), but offered the option of moving to SQL Server later on (the successful physician brings in a bunch of his friends as partners, and the practice grows to 10x the original volume).

    As Samuel Jack points out, Microsoft’s SQLite competitor is SQL CE. The trouble is that Microsoft isn’t focusing enough attention or promotional effort on it. For example, WinRT is moving in the *wrong* direction by *removing* the System.Data namespace. Windows Phone also omitted System.Data for 7.0, but then integrated SQL CE in 7.5. The difference is — Windows Phone takes a year between releases, while Windows releases a new version every three years.

    Esent reminds me of the 1990s. SQL has won the battle — even for embedded databases. No SQL = no-go.

    —

    P.S. The benchmarks that Samuel Jack points to are highly misleading — which is not at all unusual for information found on random Codeproject pages. The guy forgot to wrap the SQLite commands in a transaction — which would account for the 1000x performance differential.

  5. Thanks Tim, great summary!

    One clarification on the size of LocalDB; 140 MB is the disk size of LocalDB binaries after installation. The download size of the LocalDB installer is less than 35 MB.

    -Krzysztof

  6. Don’t forget Firebird Embedded.
    * 32-bits and 64-bits versions
    * runs on windows, linux and mac.
    * fully compatible with the full version in any aspect, stored procs, views, triggers etc….
    * free

    I’ve used it for many years.

Comments are closed.