SQLCoffee - What is LocalDB?

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

What is LocalDB?

Applies to: SQL Server 2012.

Note: To learn about SQL Server 2014 LocalDB, please read this article and learn what's new for SQL Server 2014 LocalDB here .

What is LocalDB?

It is a new version of SQL Server Express dedicated to developers to help them avoid a full installation of other editions of SQL Server.

Benefits.

bullet Small installer. The 32-bit version has 28.2 MB and the 64-bit version has 33.7 .
bullet Simplified. It does do not require configuration or administration.
bullet Run as a low privileged user.
bullet Simple installation.
bullet Offers the same T-SQL language as SQL Server Express. It supports stored procedures, geometry and geography data types, triggers, views.
bullet LocalDB uses the same sqlservr.exe as other editions of SQL Server and the same client-side providers.
bullet LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed.
bullet LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location
bullet Service packs of LocalDB can be used update a LocalDB installation or to install a new LocalDB.
bullet One LocalDB installation for all users on a computer.
bullet It supports a silent installation.
bullet Familiar to developers using SQL Server Compact.
bullet It works with ASP .NET
bullet It supports XML (XQuery, XPath) and BLOB.
bullet It supports ADO .NET Sync Framework.
bullet It supports LINQ.
bullet It supports ODBC.
bullet It supports OLEDB.
bullet It supports distributed transactions.
bullet Unlimited local connections.
bullet It is the default development database for SQL Server Data Tools (SSDT).
bullet It supports Service Broker, although only for local queues.
bullet You can create instances of LocalDB programmatically using LocalDBCreateInstance or using system.data.localdb .
bullet LocalDB supports named instances.

Requirements.

bullet It requires SQL Server 2012 Native Client. It does no ship with the LocalDB installer.
bullet It requires administrative rights for installation.
bullet It requires 140 MBs of this disk space.
bullet It requires .NET Framework 4 to be upgraded to 4.0.2 or later.

Limitations.

bullet It does not support Windows XP, Windows Server 2003, Window 2000.
bullet It does not support WOW. LocalDB doesn't support installing 32-bit version on 64-bit Windows.
bullet Allows only local connections. Only Named Pipes connections.
bullet Only SQL Server 2012 Management Studio (on a computer updated with .NET Framework 4.0.2) can be used to manage LocalDB. SQL Server Management Studio of previous versions of SQL Server cannot be used.
bullet Visual Studio 2010 RTM does not support LocalDB.
bullet It does not run on mobile devices.
bullet Database size limit: 10 GB.
bullet LocalDB cannot be used as a merge replication subscriber.
bullet It does not support FileStream. FileStream is not supported on user instances.
bullet Limited to use one CPU.
bullet T-SQL Debugging cannot be used when connected to a LocalDB instance. For more information, click here .
bullet SQL Server Profiler cannot be used against a LocalDB instance.
bullet It does not support JDBC.
bullet It supports ADO.NET but does not support ADO.
bullet The system databases, SQL Server error logs, are stored in the AppData folder in the user profile. Usually they will be located on C:\Users\youruseraccount\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0.
bullet It does not support partitioned tables. **
bullet Linked Servers are not supported. For more information, click here .
bullet The instance collation for LocalDB is SQL_Latin1_General_CP1_CI_AS by default and cannot be changed.
bullet LocalDB automatic instances fail to create on roaming user profiles.

How to install it.

First, download it from here .

As you will notice below, LocalDB only requires accepting the license agreement to be installed.

A silent installation can be used with the following command:

msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES

It can be installed using the GUI.

How to connect to LocalDB using SQL Server Management Studio (SSMS).

You can connect to LocalDB using SQL Server 2012 Management Studio if .NET Framework 4 has been upgraded to 4.0.2 or later.

Use "(localdb)\v11.0" as server name.

SQL Server Management Studio of earlier versions of SQL Server cannot connect to LocalDB.

How to create a new database using SQL Server 2012 Management Studio.

It is created the same as in other editions of SQL Server, just make a right click on "Databases" and select "New database". However, make sure the path where the database will reside is specified.

Troubleshooting LocalDB installation.

You can use the /L*V switch that Windows Installer provides for creating a verbose log installation. For more information please click here to visit a related article at the Advanced Installer User Guide site.

How to create a database using scripts after a successful installation of a LocalDB instance.

Please try the following command:

"C:\Program Files\Microsoft SQL Server110円\Tools\Binn\sqlcmd.exe" -S (localdb)\V11.0 -d YourDatabase -E -b -i "C:\ScriptsFolder\DBScriptCreator.sql"

References.

Introducing LocalDB, an improved SQL Express.

.NET Framework 4 now supports LocalDB!

** Jamie Thomson shared with us his findings about LocalDB limitations.

(追記) (追記ここまで)

What is LocalDB?

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.