Introducing LocalDB, an improved SQL Express

Updated 2011-11-28: Added reference to the walkthrough of using LocalDB in Visual Studio 2010 and to the new LocalDB Installer.

Updated 2011-11-02: Added reference to .NET Framework 4 support for LocalDB in the Q&A section.

Introduction

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.

Background

Before focusing on technical description of LocalDB, I'd like to provide some background on the direction we took building it.

Today SQL Server Express serves two distinct needs. On one hand it is a free edition of SQL Server. The installation, management and programming of SQL Express in this role is expected to be 100% compatible with other editions. It can be used for learning, training and to run relatively small production database (with less than 10GB of data). Upgrade from SQL Express to paid SQL Server editions is a matter of typing in a license key and no installation is required.

But SQL Express is also SQL Server edition for developers writing applications targeting SQL Server. In this role the programming of SQL Express is still expected to be 100% compatible with other SQL Server editions, but SQL Express is supposed to be small, simple, low-footprint, require no configuration or administration, run as non-admin user, etc.

Our approach so far was to try to make SQL Express perform well in both roles. But as SQL Server product matured, and in effect added more complexity, it became harder and harder for SQL Express to be both compatible with other SQL Server editions and small/simple. The challenge is most visible in installation and configuration of SQL Express. In SQL Server "Denali" we decided to change the approach it and introduce a dedicated version of SQL Express for developers - LocalDB that delivers the simplicity and yet is compatible with other editions of SQL Server at the API level.

Also, by making LocalDB a better SQL Express for developers, we hope to be able to improve the regular SQL Express to be a better free SQL Server. We'd be very happy to hear your feedback in this area, especially if you're using SQL Express as a database server and find any issues caused by the new features that were introduced to fit the needs of developers and desktop environment.

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 Instances

As we said before, at the heart of LocalDB is the same sqlservr.exe as in the regular SQL Express and other editions of SQL Server. In case of LocalDB, it is installed into one central location together with all necessary DLLs. By default it is located at "C:\Program Files\Microsoft SQL Server\110\LocalDB\Binn".

When an application uses any of the client-side providers (like ADO.NET, ODBC or PDO) to connect to "Data Source=(localdb)\v11.0", the provider will first check if LocalDB instance for the current user is started. If it's already started the application will connect to it. Otherwise the LocalDB instance for the current user will be started and then the provider proceeds to connect to it. Note that each user (Windows login) may have their own LocalDB instance that is isolated from instances of other users.

To try LocalDB just use this connection string: "Data Source=(localdb)\v11.0;Integrated Security=true". Note that because the activation logic lives in the client-side provider, you need to install the latest one - SQL Server Native Client "Denali" (for ODBC and OLE DB) or the next .NET Framework (for ADO.NET). We are also shipping a QFE for .NET Framework 4 that adds the support for LocalDB to it (the updated .NET Framework 4 is already available, see this post for details).

Database as a File

LocalDB connection strings support AttachDbFileName property that allows attaching a database file during the connection process. This lets developers work directly with databases instead of the database server. Assuming a database file (*.MDF file with the corresponding *.LDF file) is stored at "C:\MyData\Database1.mdf" the developer can start working with it by simply using the following connection string: "Data Source=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyData\Database1.mdf".

New Installer

In this CTP LocalDB is a shared feature of SQL Server Express available to try as an option during installation of any other version of SQL Express. Our ultimate goal is to offer LocalDB in a separate, simplified and smaller installer. We will keep you posted on our progress here.

LocalDB and User Instances of SQL Express

Users familiar with today's User Instances of SQL Server Express should feel right at home with LocalDB. In essence LocalDB offers the ability to create and start a "user instance" without the hassle of installing and maintaining the parent SQL Express instance. In that respect LocalDB could be seen as an upgrade of the User Instances feature of SQL Server Express.

Let me also take this opportunity to remind everyone that User Instances are on a deprecation path since SQL Server 2008.

LocalDB and SQL Express

LocalDB is not a replacement for SQL Server Express -- it is an addition to SQL Server Express lineup. While LocalDB is meant for developers, the regular SQL Server Express will continue to exist as a free SQL Server edition, fully compatible with and easily upgradeable to higher SQL Server editions.

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.

Send us Your Feedback

Please share your feedback with us! Feel free to start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!

 

 

Q&A

Thank you all for your feedback. Please read below for answers to your questions!

 

Q: You say that LocalDB runs as a separate process. However, the main SQL Server site states "LocalDB has all of the same programmability features as SQL Express, but runs in-process with applications". So which is it?

A: LocalDB runs as a separate process - it does not run in-process. It does however run in the same security context as the calling application.

 

Q: Does SQL Express Denali still have the same memory limitations as that of SQL Server 2005 Express.

A: The memory limitation has not changed for Denali. At Denali RTM, LocalDB will have the same memory limitations as Express.

 

Q: What is the difference between LocalDB and SQL Server Compact Edition?

A: LocalDB is the SQL Server database engine. As such it offers functionality such as online backup, transactions and data integrity.  LocalDB is designed for PCs but unlike Compact does not support small mobile devices like smart phones because of its hardware and disk requirements. Compact Edition is a shared file system database.  It is not fully API compatible with SQL Server and does not support the same level of data integrity. SQL Compact is also not supported by the PHP Driver and PDO.

 

Q: Where can I find the option to install LocalDB?

A: You must install the SQL Server Express Edition and select LocalDB as a feature. You can unselect the Database Engine if you do not require SQL Server Express as a service.

A: Starting from SQL Server 2012 RC0 LocalDB can be downloaded and installed separately, as described in this post.

 

Q: How do you actually create a LocalDB database?

A: Connect to LocalDB and then use the usual T-SQL “CREATE DATABASE …” command.

 

Q: Can LocalDB be a subscriber for merge replication?

A: Merge replication is not supported by LocalDB.

 

Q: Can LocalDB be launched from a service?

A: LocalDB can be launched from a service, as long as the profile is loaded for the service account.

 

Q: Installed OK, but how about a nice little VS2010 C# (working) sample for creating a LocalDB database?

A: We are currently working on a sample application to illustrate the usage of LocalDB in C#.

A: Please see Using LocalDB in Visual Studio 2010 for a sample walkthrough.

 

Q: When will System.Data.SqlClient support LocalDB?

A: LocalDB is planned to be supported with .NET 4.0 with a QFE (not yet shipped) and future versions of .NET

A: LocalDB is supported by System.Data.SqlClient in .NET Framework 4 Update 4.0.2, as announced in this post.