LocalDB is a lightweight deployment option for SQL Server Express Edition with the same features but with a simple installation. To support scenarios where multiple users of the computer need to connect to a single instance of LocalDB, LocalDB supports instance sharing but does not support sharing among multiple computers.
SQL Server Express LocalDB, introduced in SQL Server 2012, is a light-weight version of SQL Server Express that replaces User Instances, which were introduced in SQL Server 2005. LocalDB (as it is commonly referred to) instances are created per user, and the instance and their databases are by default installed into a user's local profile directory. (It's possible to save the database file elsewhere, including on a server share). By default these instance are private, but they can be shared with other users on the same computer.
LocalDB runs as a background process for each user that starts their own private instance rather than as a service that is shared by all users and would have its own security context. Connectivity is only through Named Pipes; external connectivity is not available. However, specifying the correct named pipe to connect to can be problematic due to the instance name changing each time it starts (i.e. instance name = LOCALDB#{8_character_alphanumeric_id}
, named pipe = np:\\.\pipe\LOCALDB#{8_character_alphanumeric_id}\tsql\query
).
Fortunately, a new special syntax for the Server Name was added to use in connection strings. You can specify (localdb)\InstanceName
in SSMS, SQLCMD, and via the SqlConnection
class in .NET applications (though be aware that this was not available until .NET Framework version 4.0.2). This syntax provides two benefits:
- It allows for using a consistent server name in your code
- It will automatically start the LocalDB instance if it is not already running.
LocalDB can be started, stopped, and otherwise managed via the SQLLocalDB.exe
utility. Additionally, LocalDB will be automatically started when using the (LocalDB)\InstanceName connection string syntax, and it will automatically stop about 5 minutes after the last connection closes.
The easiest way to use LocalDB is to connect to the automatic instance owned by the current user by using the connection string "Server=(localdb)\MSSQLLocalDB;Integrated Security=true"
. To connect to a specific database by using the file name, connect using a connection string similar to "Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf"
.
To connect to a shared instance of LocalDB add .\ (dot + backslash) to the connection string to reference the namespace reserved for shared instances. For example, to connect to a shared instance of LocalDB named AppData use a connection string such as (localdb)\.\AppData
as part of the connection string. A user connecting to a shared instance of LocalDB that they do not own must have a Windows Authentication or SQL Server Authentication login.
For more info, please see: