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.



Small installer. The 32-bit version has 28.2 MB and the 64-bit version has 33.7 .

Simplified. It does do not require configuration or administration.

Run as a low privileged user.

Simple installation.

Offers the same T-SQL language as SQL Server Express. It supports stored procedures, geometry and geography data types, triggers, views.

LocalDB uses the same sqlservr.exe as other editions of SQL Server and the same client-side providers.

LocalDB doesn't create any database services; LocalDB processes are started and stopped automatically when needed.

LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location

Service packs of LocalDB can be used update a LocalDB installation or to install a new LocalDB.

One LocalDB installation for all users on a computer.

It supports a silent installation.

Familiar to developers using SQL Server Compact.

It works with ASP .NET

It supports XML (XQuery, XPath) and BLOB.

It supports ADO .NET Sync Framework.

It supports LINQ.

It supports ODBC.

It supports OLEDB.

It supports distributed transactions.

Unlimited local connections.

It is the default development database for SQL Server Data Tools (SSDT).

It supports Service Broker, although only for local queues.

You can create instances of LocalDB programmatically using LocalDBCreateInstance or using system.data.localdb.

LocalDB supports named instances.





Requirements.



It requires SQL Server 2012 Native Client. It does no ship with the LocalDB installer.

It requires administrative rights for installation.

It requires 140 MBs of this disk space.

It requires .NET Framework 4 to be upgraded to 4.0.2 or later.





Limitations.



It does not support Windows XP, Windows Server 2003, Window 2000.

It does not support WOW. LocalDB doesn't support installing 32-bit version on 64-bit Windows.

Allows only local connections. Only Named Pipes connections.

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.

Visual Studio 2010 RTM does not support LocalDB.

It does not run on mobile devices.

Database size limit: 10 GB.

LocalDB cannot be used as a merge replication subscriber.

It does not support FileStream. FileStream is not supported on user instances.

Limited to use one CPU.

T-SQL Debugging cannot be used when connected to a LocalDB instance. For more information, click here.

SQL Server Profiler cannot be used against a LocalDB instance.

It does not support JDBC.

It supports ADO.NET but does not support ADO.

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.

It does not support partitioned tables. **

Linked Servers are not supported. For more information, click here.

The instance collation for LocalDB is SQL_Latin1_General_CP1_CI_AS by default and cannot be changed.

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 Server\110\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.



