Thursday, July 14, 2011

Denali CTP3 introduces LocalDB - Think SQL Server Express crossed with SQL CE (or SQL CE grown up or SQL Express made as almost as easy CE)

InfoQ - LocalDB: A Lightweight SQL Express for Developers

"As part of SQL Denali CTP3, Microsoft has included a new version of SQL Express called Local Database Runtime (LocalDB). It offers many of the same features as SQL Express, but installs faster, has fewer prerequisites, and doesn’t require management of a database instance.

LocalDB is meant to be an easier way for developers to get a SQL Express database up and running with minimal configuration or management, but it can also be used in production if it suits the needs of an application. Because it opens as a file with exclusive use, LocalDB is not appropriate for server-based or multi-user environments.

LocalDB fully supports T-SQL, and uses the same client-side providers as other versions of SQL Server (ADO.NET, ODBC, etc). It runs in-process, rather than as a service. When an application connects to LocalDB, the provider checks to see if it’s already running, and if not, will start it up and connect. The connection closes automatically after a few minutes of inactivity. ..."

SQL Server Express WebLog - Introducing LocalDB, an improved SQL Express


It gives me great pleasure to introduce a new version of SQL Express called SQL Express LocalDB.

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. In effect the developers that target SQL Server no longer have to install and manage a full instance of SQL Server Express on their laptops and other development machines. Moreover, if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too.


High-Level Overview

After the lengthy introduction it's time to take a look at LocalDB from the technical side. At a very high level, LocalDB has the following key properties:

  1. LocalDB uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server. The application is using the same client-side providers (ADO.NET, ODBC, PDO and others) to connect to it and operates on data using the same T-SQL language as provided by SQL Express.
  2. LocalDB is installed once on a machine (per major SQL Server version). Multiple applications can start multiple LocalDB processes, but they are all started from the same sqlservr.exe executable file from the same disk location.
  3. LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to "Data Source=(localdb)\v11.0" and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.
  4. LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. LocalDB will attach the specified database file and the connection will be made to it.


LocalDB and SQL Server Compact?

Small and simple database, lightweight installation, connecting to a database file -- this will sound familiar to any developer using SQL Server Compact. The similarities are not accidental, as our goal for LocalDB was to be as easy to use as SQL Server Compact (while being as powerful and compatible with full SQL Server as SQL Express).

There are significant differences between LocalDB and SQL Server Compact:

  1. Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.
  2. Disk usage: all SQL Server Compact binaries amount to some 4MBs, while LocalDB installation takes 140MBs.
  3. Features: SQL Server Compact offers core RDBMS functionality like querying, while LocalDB provides a much richer set of features, including Stored Procedures, Geometry and Geography data types, etc.


Interesting... Kind of mixing the best of both worlds. In my mind, one key will be the installation/deployment for LocalDB. But I dig the direction this is going. I love the thought of having a full power SQL Server available to my app without dealing with some of the gunk SQL Server Express requires (and personally I hate having that service always running).

This is another Denali feature I'll be keeping an eye on...


Related Past Post XRef:
SQL Server Denali Sequence Object Overview
A couple SQL Server ColumnStore references...
Time to start thinking about, and providing feedback on, what you’ll need to upgrade to Denali (aka next version of SQL Server)

1 comment:

ErikEJ said...

I have a feature comparision tabel here: