Is there a difference between SQL Server Express (2012) and LocalDB?

Question

In his excellent and popular comparision chart, ErikEJ draws a distinction between SQL Server Express 2012, and SQL Server 2012 LocalDB. However, I can't find such a distinction anywhere else in the MSDN documentation ("LocalDB" isn't even mentioned in the official MS SS12 book or on the MSDN SQL Edition comparison page.) This leads me to suspect that Express and LocalDB aren't really two separate products as ErikEJ suggests, but rather just two different terms for the same thing (which would explain why, on this MSDN page, it is called "SQL Server 2012 Express LocalDB").

If there really is a difference between the products, can anyone point me to some official documentation comparing them?

Windywindzer

Answer 1

OK, I think I have found the answer to my own question. (It was buried under the tabs on the SS Express product page.)

Here is a summary of the distinctives of LocalDB I found on that page:

• LocalDB is a lightweight deployment option for SQL Server Express Edition with fewer prerequisites and quicker installation.
• LocalDB has all of the same programmability features as SQL Express, but runs in "user mode"* with applications and not as a service.
• LocalDB is not intended for multi-user scenarios or to be used as a server. (If you need to accept remote connections or administer remotely, you need SQL Server Express.)
• "Express with Tools" (which includes SS Management Studio Express, Azure, etc) can be used with LocalDB or without. (The same goes for "Express with Advanced Services".)

UPDATE: I just found this useful description in Windows IT Pro (Jul '12, p.23):

LocalDB isn’t SQL Server Express, nor is it SQL Server Compact. LocalDB uses the same sqlservr.exe engine as the other editions of SQL Server, but it runs in user mode—not as a service. LocalDB is used for offline development by tools such as SSDT to ensure that the code you develop is 100-percent compatible with your production SQL Server database.

If I am reading this correctly, LocalDB seems to be more like a configuration option of Express than an entirely separate product. So apparently if I download Express (or Express w/ Tools), I will have the option to install the LocalDB version, which is supposedly easier ("zero-configuration") than the full Express. (Update: with VS2012, LocalDB comes installed by default.)

One other important distinction, according to this post, is that "currently Visual Studio 2010 doesn't really work with LocalDB." (We have to use SS Management Studio instead, at least for now.)

*(The concept of "User Mode" or "User Instances" is a key distinctive of LocalDB. In fact, according to this post, "LocalDB can be seen as an upgrade of the User Instances feature of SQL Server Express." For more about User Instances, refer to the MSDN blogpost "What is a RANU?")

UPDATE - Feb 2021 LocalDB is SQL Server Express edition

From official documentation

Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications. It is the best choice for independent software vendors, developers, and hobbyists building client applications. If you need more advanced database features, SQL Server Express can be seamlessly upgraded to other higher end versions of SQL Server. SQL Server Express LocalDB is a lightweight version of Express that has all of its programmability features, runs in user mode and has a fast, zero-configuration installation and a short list of prerequisites.

The docs provides a very detailed comparison of features between editions.

Windywindzer

+1, good answer. I have used some of your text to create the LocalDB tag wiki. – Humor

If you are on Visual Studio 2012, you can use LocalDB from Visual Studio: msdn.microsoft.com/en-us/library/vstudio/… – Pasteur

And it is OK, if I use LocalDB in application server Server (windows service), so I will be able to handle multi-user scenarios by wrapping to REST-API. It is good idea or do I need SQL Server Express for this scenario? – Ronel

@qub1n: sorry, I don't have any experience with that. But as far as I know, LocalDB does not run as a service and is not intended for multiuser scenarios. – Windywindzer

Think of LocalDB as a server (not service) process that runs on a single PC. Multiple users and apps can access the database instance but only from that same PC. If the MDF file is saved on a network share (for example) multiple instances of LocalDB running on separate computers cannot concurrently access the file. In that sense it is not "multi-user". (Interestingly, Microsoft Access MDB/ACCDB files can be shared in this way.) – Vanir

Do we also use ports, user and password to connect to LocalDB? – Tyrocidine

Answer 2

I use a table from ErikEJ, which shows: the features and differences between SQL CE 3.5, SQL CE 4, Local DB and SQL Server 2012. According to this table the differences between Local DB and SQL Server 2012 are:

Installation size:
SQL Server 2012: 120 MB download size; 300 MB expanded on disk
Local DB: 32 MB download size; 160 MB on disk

Runs as Service
SQL Server 2012: Yes
Local DB: No (runs as process started by app)

FILESTREAM support
SQL Server 2012: Yes
Local DB: No

Subscriber for merge replication
SQL Server 2012: Yes
Local DB: No

Number of concurrent connections
SQL Server 2012: Unlimited
Local DB: Unlimited (but only local)

NB: Sorry this isn't "official documentation", but hope it's useful to the next bod as it answers the underlying query about the differences.

Lobworm

Answer 3

The major difference are Server Express run as a service while LocalDB doesn't need any server or intensive processing to run.

Edmonson

this answer adds nothing new to the question. OP made that point already in accepted answer. – Wilheminawilhide