LocalDB: Microsoft's new generation of lightweight databases

What is LocalDB

With the release of SQL Server 2012, LocalDB jumps into our line of sight, which can be seen as a lightweight version of SQL Server Express. LocalDB is created specifically for developers, it's very easy to install, almost no management, compatible with the T-SQL language, programming interfaces with SQL Server Express indistinguishable. With LocalDB, developers don't need to install and maintain a large instance of SQL Server on their notebooks. In addition, the LOCALDB is also suitable for small application environments where developers can use it for small production environments or embedded environments.

Tailor-Made for developers

Before we look at the technical details of LOCALDB, let's look at the background to build LocalDB. SQL Server Express has been meeting both of our needs for a long time. It is both a free version of SQL Server and powerful enough to be compatible with other versions. Of course, its capabilities also have some limitations, that is, its storage capacity of each database can not exceed 10G. As a mature database product, SQL Server still has a fairly complex express version. But as developers, we prefer to reduce the complexity of management and focus on development. The development goals of LOCALDB, which are lightweight SQL Server Express, are tailored for developers.

Core Technical Features

Having said so much, now let's look at the core technical features of LOCALDB:

1) compatible with other versions of SQL Server, using Sqlservr.exe as a service process, using the same client access interface (such as ADO, ODBC, or PDO), compatible with the T-SQL programming language.

2) There is no need to install multiple localdb on the same computer, and different applications can execute multiple LOCALDB processes in parallel, but all processes are started from the same executable file (Sqlservr.exe).

3) LocalDB does not create any system services, and the LOCALDB process automatically starts and stops as needed. The application simply connects to "Data source= (localdb) \ Mssqllocaldb", and LocalDB is started as a child of the application. As the connection terminates, the LOCALDB process also stops.

4) LocalDB supports the AttachDbFileName property, which allows the developer to specify the database file location. For example:

Data Source = (localdb) \ Mssqllocaldb;

Integrated Security = true;

AttachDbFileName = C:\MyData\Database1.mdf

Lightweight database comparison (LocalDB vs. SQL Express vs. Compact)

LocalDB is not a complete replacement for SQL Server Express, it simply provides a different option for developers and guarantees consistent compatibility with SQL Server Express.

There is a big difference between localdb and the SQL Server compact:

1) operating mode : The SQL Server Compact is an in-process (IN-PROC) Dll,localdb Run as a standalone process.

2) disk footprint:The SQL Server Compact has a volume of only 140MB after the 4MB,LOCALDB installation.

3) Features:The SQL Server Compact provides only the core functionality of the RDBMS, while LocalDB provides richer functionality, such as stored procedures, geometries, and geographic data types.

The final note

LocalDB as a feature-rich lightweight database, does it provide a good management interface?

The answer is yes. LOCALDB provides a command-line administration Tool SqlLocalDB.exe, we can also manage LocalDB in SQL Server Management Studio (SSMS), or you can use visual Studio 2012 Simple SQL Server management tools that are built into the development tools (and above).

The Visual Studio 2012+ development tool now has LOCALDB installed by default after installation, and the corresponding LOCALDB version differs depending on the VS version.

The above content is organized according to the content provided below.

Original English Address:

https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

Original Chinese Address:

http://www.csdn.net/article/2012-03-29/313675

LocalDB: Microsoft's new generation of lightweight databases