Microsoft SQL Server Express LocalDB is a streamlined version of SQL Server Express, specifically designed for developers. It is included with SQL Server Express edition when you choose the Advanced Services option.
LocalDB simplifies the initial setup by installing a minimal set of files needed to run the SQL Server Database Engine. Once installed, you can easily establish a connection using a specific connection string. Upon connection, the necessary SQL Server infrastructure is automatically set up and started. This allows applications to leverage databases without the complexities of configuring a full SQL Server instance. Developer tools utilize SQL Server Express LocalDB to provide developers with a SQL Server Database Engine for writing and testing Transact-SQL code efficiently, eliminating the need to manage a complete server instance of SQL Server.
Installation Options for SQL Server Express LocalDB
LocalDB is a selectable feature during the installation of SQL Server Express. It is accessible when you download the installation media. When downloading, ensure you choose either Express Advanced or the LocalDB package to include this feature.
For users of Visual Studio 2019 and Visual Studio 2022, it’s recommended to install SQL Server 2019 Express edition to get LocalDB.
The LocalDB installer, SqlLocalDB.msi, is available within the installation media of all editions except Express Core. You can find it in the folder: <installation_media_root><lcid>_ENU_LPx64Setupx64</lcid>. Here, LCID represents the locale identifier or language code; for instance, 1033 corresponds to the en-US locale.
Alternatively, LocalDB can be installed through 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 a standalone individual component.
Step-by-Step Installation of LocalDB
You can install LocalDB either through the SQL Server installation wizard or by directly running the SqlLocalDB.msi program. Remember, LocalDB is an installable option within SQL Server Express LocalDB.
During the SQL Server installation process, on the Feature Selection/Shared Features page, make sure to select LocalDB. It’s important to note that for each major version of the SQL Server Database Engine, only one installation of the LocalDB binary files is permitted. However, you can initiate multiple Database Engine processes, all of which will utilize the same set of binaries. A SQL Server Database Engine instance started as LocalDB will have 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 recommended as a replacement for the deprecated SQL Server Express user instance feature.
Understanding SQL Server Express LocalDB
The LocalDB setup primarily uses the SqlLocalDB.msi program to deploy necessary files onto your system. Once installed, 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, by default, stored in the hidden local AppData path. For example, this might be C:Users<user>AppDataLocalMicrosoftMicrosoft SQL Server Local DBInstancesLocalDBApp1</user>. User database files, however, are typically stored in a location specified by the user, often within the C:Users<user>Documents</user> folder.
For detailed information on incorporating LocalDB into your applications, refer to Visual Studio’s Local Data Overview and the guide on how to Create a database and add tables in Visual Studio.
Further details about the LocalDB API can be found in the SQL Server Express LocalDB Reference.
The SqlLocalDB utility is instrumental in managing LocalDB instances. It allows you to create new instances, start and stop existing ones, and offers various options to aid in LocalDB management. More information on the SqlLocalDB utility is available in the SqlLocalDB utility documentation.
The default collation for LocalDB instances is SQL_Latin1_General_CP1_CI_AS, and this setting cannot be modified. However, database-level, column-level, and expression-level collations are supported as standard. Contained databases adhere to the collation rules for metadata and tempdb as defined by Contained database collations.
Limitations of LocalDB
While SQL Server Express LocalDB is powerful, it has certain restrictions:
- No Remote Management: LocalDB instances cannot be managed remotely via SQL Server Management Studio.
- Replication Limitations: LocalDB cannot function as a merge replication subscriber.
- FILESTREAM Not Supported: FILESTREAM is not supported in LocalDB.
- Local Service Broker Queues: LocalDB only supports local queues for Service Broker.
- Built-in Account Issues: Instances owned by built-in accounts like
NT AUTHORITYSYSTEMmay encounter manageability issues due to Windows file system redirection. It’s recommended to use a standard Windows account as the owner instead.
Automatic vs. Named Instances in SQL Server Express LocalDB
LocalDB supports two types of instances, catering to different usage scenarios: automatic instances and named instances. It’s possible for different users on the same computer to have instances with identical names, as each instance operates under the respective user’s process.
Automatic Instances
Automatic instances in LocalDB are public and designed for ease of use. They are automatically created and managed on a per-user basis and can be accessed by any application. For each version of LocalDB installed on a user’s machine, there is one automatic instance. These instances simplify instance management, requiring no explicit creation steps – they are ready to use. This feature streamlines application installation and migration across different computers. If a target machine has the required LocalDB version, the corresponding automatic instance will be available. Automatic instances follow a specific naming convention within a reserved namespace to prevent conflicts with named instances. The predefined name for an automatic instance is MSSQLLocalDB.
Named Instances
Named instances of LocalDB are private and are typically managed by a specific application. The application is responsible for the creation and management lifecycle of these instances. Named instances offer isolation, preventing interference from other instances, and can lead to improved performance by reducing resource contention with other database users. Named instances must be created explicitly, either through the LocalDB management API or implicitly via the app.config file in managed applications. Each named instance is associated with a specific LocalDB version, pointing to the relevant LocalDB binaries. Instance names for LocalDB are of the sysname data type and can be up to 128 characters long. This is a notable difference from regular SQL Server named instances, which are limited to 15 ASCII characters due to NetBIOS naming constraints. LocalDB instance names can include any Unicode characters valid in a filename. If a named instance is created using the automatic instance name (MSSQLLocalDB), it effectively becomes an automatic instance.
Sharing SQL Server Express LocalDB Instances
To accommodate scenarios where multiple users on a single computer need to connect to the same LocalDB instance, LocalDB supports instance sharing. An instance owner can grant permissions to other local users to connect to their instance. Both automatic and named instances can be shared. To share an instance, the owner assigns a shared name, or alias. This shared name must be unique across the computer as it is visible to all users. Shared names for LocalDB instances adhere to the same format as named instances.
Only administrators have the authority to create shared LocalDB instances. Unsharing an instance can be done by an administrator or by the owner of the shared instance. Instance sharing and unsharing are managed using the LocalDBShareInstance and LocalDBUnShareInstance methods of the LocalDB API, or through the share and unshared options of the SqlLocalDB utility.
Connecting to SQL Server Express LocalDB
Connecting to LocalDB instances is straightforward, whether it’s an automatic, named, or shared instance.
Connecting to the Automatic Instance
The simplest way to utilize LocalDB is by connecting to the automatic instance owned by the current user. This is done using the connection string: Server=(localdb)MSSQLLocalDB;Integrated Security=true. To connect to a specific database file, use a connection string similar to: Server=(LocalDB)MSSQLLocalDB;Integrated Security=true;AttachDbFileName=D:DataMyDB1.mdf.
Note: The first time a user attempts to connect to LocalDB on a machine, the automatic instance needs to be created and started. This initial setup time might cause the first connection attempt to time out. If this occurs, wait briefly for the creation process to complete and then try connecting again.
Creating and Connecting to a Named Instance
Besides the automatic instance, LocalDB also supports named instances. The SqlLocalDB.exe utility is used to create, start, and stop named LocalDB instances. For comprehensive details on SqlLocalDB.exe, refer to the SqlLocalDB utility documentation.
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 LocalDBApp1Executing the last line in the code snippet above will yield information resembling the table below:
| Category | Value |
|---|---|
| Name | LocalDBApp1 |
| Version | |
| Shared name | "" |
| Owner | |
| Auto create | No |
| State | Running |
| Last start time | |
| Instance pipe name | np:\.pipeLOCALDB#F365A78Etsqlquery |
Note: If your application uses a .NET Framework version older than 4.0.2, you must connect directly to the named pipe of the LocalDB instance. The Instance pipe name value provides the named pipe that the LocalDB instance is listening on. The part of the Instance pipe name after LOCALDB# will change each time the LocalDB instance restarts. To connect via SQL Server Management Studio, input the Instance pipe name into the Server name field in the Connect to Database Engine dialog. In custom programs, a connection string similar to SqlConnection conn = new SqlConnection(@"Server=np:\.pipeLOCALDB#F365A78Etsqlquery"); can be used.
Connecting to a Shared Instance
To connect to a shared LocalDB instance, append . (backslash, dot, backslash) to the connection string. This references the namespace reserved for shared instances. For example, to connect to a shared instance named AppData, use a connection string like (localdb).AppData. Users connecting to a shared instance they don’t own must authenticate using Windows Authentication or SQL Server Authentication.
Troubleshooting SQL Server Express LocalDB
For troubleshooting tips and solutions related to LocalDB, consult Known SQL Server 2012 setup and migration issues. While it refers to SQL Server 2012, many principles and troubleshooting steps remain relevant for newer versions.
Permissions and Security in SQL Server Express LocalDB
A SQL Server Express LocalDB instance is created and used by an individual user. Any user on a computer can create databases, store database files within their user profile, and run processes under their own credentials using LocalDB. By default, access to a LocalDB instance is restricted to its owner. Security for data in LocalDB is primarily managed through file system access controls on the database files. If database files are stored in shared locations, any user with file system access can open the database using their own LocalDB instance. Conversely, if files are in protected locations, like user data folders, only the user and administrators with folder access can open them. It’s crucial to note that LocalDB files can only be opened by a single LocalDB instance at any given time.
LocalDB always operates under the security context of the user who starts it. It never runs with local Administrator group credentials. This means all database files accessed by a LocalDB instance must be accessible via the owning user’s Windows account, irrespective of local Administrator group membership.
