SQL Server Express LocalDB: A Developer's Quick Guide

Microsoft Sql Server Express LocalDB is a streamlined version of SQL Server Express, specifically designed for developers. It’s included with SQL Server Express edition when you choose the Advanced Services option.

LocalDB simplifies database development by installing a minimal set of files needed to launch the SQL Server Database Engine. Once installed, you can easily establish a connection using a specific connection string. Upon connection, LocalDB automatically sets up and starts the necessary SQL Server infrastructure, allowing your application to utilize databases without the complexities of full server configuration. This provides developers with a convenient SQL Server Database Engine for writing and testing Transact-SQL code, eliminating the need to manage a complete SQL Server instance.

Installation Options for SQL Server Express LocalDB

LocalDB is a selectable feature during the installation of SQL Server Express. It’s available when you download the installation media. When downloading, opt for either Express Advanced or the standalone LocalDB package.

For users of Visual Studio 2019 and Visual Studio 2022, installing SQL Server 2019 Express edition is recommended to get LocalDB.

The LocalDB installer, SqlLocalDB.msi, is included in the installation media for all editions except Express Core. You can find it within the <installation_media_root><lcid>_ENU_LPx64Setupx64</lcid></installation_media_root> folder. LCID represents the locale identifier or language code; for instance, 1033 corresponds to en-US.

Alternatively, LocalDB can be installed via the Visual Studio Installer. It’s offered as part of the Data Storage and Processing workload, the ASP.NET and web development workload, or as an individual component.

How to Install LocalDB

You can install LocalDB through the SQL Server installation wizard or by directly running the SqlLocalDB.msi program. LocalDB is presented as a feature option when you are installing SQL Server Express LocalDB.

During the installation process, on the Feature Selection/Shared Features page, ensure you select LocalDB. It’s important to note that only one installation of the core LocalDB binary files is allowed per major SQL Server Database Engine version. However, multiple Database Engine processes can run concurrently, all utilizing the same set of binaries. A SQL Server Database Engine instance initiated as LocalDB operates under the same limitations as SQL Server Express.

Management of SQL Server Express LocalDB instances is done using the SqlLocalDB.exe utility. SQL Server Express LocalDB is designed to be the replacement for the deprecated SQL Server Express user instance feature.

Understanding SQL Server Express LocalDB

The LocalDB setup utilizes SqlLocalDB.msi to deploy the necessary files to your machine. Once set up, LocalDB functions as an instance of SQL Server Express capable of creating and opening SQL Server databases. The system database files for these databases are stored in a hidden local AppData path, typically located at C:Users<user>AppDataLocalMicrosoftMicrosoft SQL Server Local DBInstancesLocalDBApp1</user>. User database files, on the other hand, are stored in a location specified by the user, commonly within the C:Users<user>Documents</user> folder.

For developers embedding LocalDB in applications, Visual Studio provides excellent integration. Refer to Visual Studio’s Local Data Overview and the guide on how to Create a database and add tables in Visual Studio.

For in-depth information about the LocalDB API, consult the SQL Server Express LocalDB Reference.

The SqlLocalDB command-line utility is essential for managing LocalDB instances. It allows you to create new instances, start and stop existing ones, and offers various options for LocalDB administration. Detailed usage instructions are available in the SqlLocalDB utility documentation.

The default collation for LocalDB instances is SQL_Latin1_General_CP1_CI_AS, and this cannot be altered at the instance level. However, database, column, and expression-level collations can be set as usual. Contained databases adhere to the collation rules for metadata and tempdb as defined in Contained database collations.

Limitations of SQL Server Express LocalDB

While LocalDB is powerful for development, it has certain limitations:

No Remote Management: LocalDB instances cannot be managed remotely through SQL Server Management Studio.
Replication Restrictions: LocalDB cannot function as a merge replication subscriber.
FILESTREAM Incompatibility: FILESTREAM data type is not supported in LocalDB.
Local Service Broker Queues: Service Broker in LocalDB is limited to local queues only.
Built-in Account Issues: Instances owned by built-in accounts like NT AUTHORITYSYSTEM might encounter manageability problems due to Windows file system redirection. It’s recommended to use a standard Windows account as the owner.

Automatic vs. Named Instances in LocalDB

LocalDB offers two types of instances to suit different development needs: automatic and named instances. Multiple users on a single machine can have instances with the same name, as each instance operates within its user’s process.

Automatic Instances

Automatic instances are designed for ease of use and are public. They are automatically created and managed by the system for the user and can be accessed by any application running under that user’s context. For each LocalDB version installed, there is one automatic instance. This “it just works” approach simplifies application deployment and migration. If a target machine has the required LocalDB version, the automatic instance for that version is immediately available. Automatic instances follow a specific naming convention within a reserved namespace to prevent conflicts with named instances. The name for the automatic instance is always MSSQLLocalDB.

Named Instances

Named instances are private and application-specific. They are owned and managed by a single application, providing isolation and potentially better performance by reducing resource contention. Named instances must be explicitly created either through the LocalDB management API or implicitly via an app.config file in managed applications. Each named instance is linked to a specific LocalDB version. Instance names are of sysname data type, allowing up to 128 characters, and can include any Unicode characters valid in a filename. If a named instance uses the automatic instance name (MSSQLLocalDB), it effectively becomes an automatic instance.

Sharing LocalDB Instances

To accommodate scenarios where multiple users need to connect to a single LocalDB instance, LocalDB supports instance sharing. An instance owner can grant other users on the same computer permission to connect. Both automatic and named instances can be shared. Sharing involves assigning a shared name (alias) to the instance. This shared name must be unique across the computer as it’s visible to all users. Shared names adhere to the same format as named instances.

Only administrators can create shared LocalDB instances. Unsharing can be done by an administrator or the instance owner. Instance sharing and unsharing are managed using the LocalDBShareInstance and LocalDBUnShareInstance methods in the LocalDB API, or through the share and unshared options of the SqlLocalDB utility.

Connecting to SQL Server Express LocalDB Instances

Connecting to LocalDB is straightforward, whether it’s an automatic, named, or shared instance.

Connecting to the Automatic Instance

The simplest method to use LocalDB is connecting to the automatic instance owned by the current user. The connection string is: Server=(localdb)MSSQLLocalDB;Integrated Security=true. To connect to a specific database file, use a connection string like: Server=(LocalDB)MSSQLLocalDB;Integrated Security=true;AttachDbFileName=D:DataMyDB1.mdf.

Note: The first connection attempt to LocalDB by a user triggers the creation and startup of the automatic instance. This initial setup time might cause a timeout error. If this occurs, wait briefly for the process to complete and try connecting again.

Creating and Connecting to a Named Instance

Besides the automatic instance, LocalDB supports named instances. Use the SqlLocalDB.exe utility to manage named instances.

REM Create an instance of LocalDB
"C:Program FilesMicrosoft SQL Server160ToolsBinnSqlLocalDB.exe" create LocalDBApp1
REM Start the instance of LocalDB
"C:Program FilesMicrosoft SQL Server160ToolsBinnSqlLocalDB.exe" start LocalDBApp1
REM Gather information about the instance of LocalDB
"C:Program FilesMicrosoft SQL Server160ToolsBinnSqlLocalDB.exe" info LocalDBApp1

Executing the info command provides instance details similar to this:

Category	Value
Name	`LocalDBApp1`
Version
Shared name	`""`
Owner
Auto create	`No`
State	`Running`
Last start time
Instance pipe name	`np:\.pipeLOCALDB#F365A78Etsqlquery`

For applications using .NET Framework versions older than 4.0.2, direct connection to the named pipe is necessary. The “Instance pipe name” value provides the required named pipe. The portion after LOCALDB# changes with each instance restart. To connect using SQL Server Management Studio, enter the instance pipe name in the “Server name” field in the “Connect to Database Engine” dialog. In custom programs, a connection string like SqlConnection conn = new SqlConnection(@"Server=np:\.pipeLOCALDB#F365A78Etsqlquery"); can be used.

Connecting to a Shared Instance

To connect to a shared LocalDB instance, prepend . to the connection string to reference the shared instance namespace. For example, to connect to a shared instance named AppData, use (localdb).AppData in your connection string. Users connecting to a shared instance they don’t own need to use Windows Authentication or SQL Server Authentication.

Troubleshooting LocalDB

For troubleshooting tips and common issues with LocalDB, refer to Known SQL Server 2012 setup and migration issues. While the title mentions SQL Server 2012, much of the setup and troubleshooting information remains relevant for later versions.

LocalDB Permissions and Security

SQL Server Express LocalDB instances are created and used by individual users. Any user on the system can create databases within a LocalDB instance, store database files in their user profile, and run processes under their own credentials. By default, access to a LocalDB instance is restricted to its owner. Security for data in LocalDB relies on file system access control to the database files. If database files are stored in shared locations, anyone with file system access can open them using their own LocalDB instance. For files in protected locations like user data folders, only that user and administrators with folder access can open the database. Importantly, LocalDB files can only be opened by one LocalDB instance at a time.

LocalDB always operates under the security context of the user who owns the instance. It never runs with local Administrator group credentials. This means all database files must be accessible using the instance owner’s Windows account, regardless of local Administrator group membership.