Localdb is a minified version of SQL Express that require less number of pre-req to install and offers a fast, zero-configuration installation. – Chirag Shah
In SQL Server "Denali" we decided to change the approach 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.
LocalDBManager.exe was the original name of the tool. Just before shipping CTP3 we decided to change the name to SqlLocalDB.exe, since LocalDBManager.exe seemed a little bit over the top for a simple command line tool. "Manager" part of the name suggests something much more comprehensive than SqlLocalDB.exe, something like the old SQL Server Enterprise Manager or IIS Manager. - Krzysztof Kozielczyk
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.
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.
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.
SQL Express User Instance
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.
SQL 2012 LocalDB
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.
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.
Read the article here.
Feature Comparison
Erik Jensen has put together an excellent table to compare features as we know them today. Se his original article here.
| Feature | CE v3.5 SP2 | CE v4.0 | Express 2008 R2 | ”Denali” LocalDB |
Deployment/Installation Features
| Download Size | 2.5MB | 2.5MB | 74MB | 32MB |
| Size On Disk | 12MB | 18MB | 300MB | 160MB ? |
| ClickOnce | Yes | Yes | Yes | Yes |
| Installed with MSI | Yes | Yes | Yes | Yes |
| Embedded w/App | Yes | Yes | No | No |
| Non-admin install | Yes | Yes | No | No |
| On ASP.Net | No | Yes | Yes | Yes |
| On Windows Phone | Yes | No | No | No |
| Runs in-process | Yes | Yes | No | No |
| 64-bit support | Yes | Yes | Yes | Yes |
| Runs as a service | No | No | Yes | No |
Data file features
| Single File | Yes | Yes | Multiple | Multiple |
| File on Net share | No | No | No | No |
| Support for different file extensions | Yes | Yes | No | No |
| Max Size | 4 GB | 4 GB | 10 GB | 10 GB |
| XML storage | As ntext | As ntext | Yes | Yes |
| BLOB storage | As image | As image | Yes | Yes |
| FileStream storage | No | No | Yes | No |
| Code free, document safe, file format | Yes | Yes | No | No |
Programmability
| Transact-SQL | Yes | Yes | Yes | Yes |
| Procedural T-SQL | No | No | Yes | Yes |
| Remote Data Access | Yes | No | No | No |
| ADO.NET Sync | Yes | No | Yes | Yes |
| LINQ to SQL | Yes | No | Yes | Yes |
| ADO.NET EF 4.1 | Yes | Yes | Yes | Yes |
| Merge replication | Yes | No | Yes | No |
| Simple | Yes | Yes | Yes | Yes |
| Distributed | No | No | Yes | Yes |
| XQuery / XPath | No | No | Yes | Yes |
| Stored Procedures, Views, Triggers | No | No | Yes | Yes |
| Role-based security | No | No | Yes | Yes |
| Concurrent connections | 256 (100) | 256 | Unlimited | Unlimited (local) |