Configuring SQL Server LocalDB

Database, SQL Server

SQL Server LocalDB is a uselful lightweight version of the SQL Server database system that allows for easy delevopment of SQL Server related applications.

There are a number of versions which can be used, and they can all be installed and used on the same system at the same time. This feature makes it easy to develop applications on the same computer that target different versions of SQL Server.

Version downloads

SQL Server LocalDB 2012

SQL Server LocalDB 2014

SQL Server LocalDB 2016

For localdb 2012 and 2014, select the correct version of SQLLocalDB.msi for your operating system (x32 or x64).

For localdb 2016, download the SQL Server Express install, run it, select the Download Media buton, select the SQLLocaldb option and save it.

Instance default database locations

The default placement of instances of SQL Local DB are in the AppData folder of the user:

%USERPROFILE%\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances

Command line

SQL LocalDB has a command line tool for managing database instances.

> sqllocaldb.exe

Versions installed

To see the versions currently installed:

>sqllocaldb v

Microsoft SQL Server 2012 (11.0.2318.0)
Microsoft SQL Server 2014 (12.0.2000.8)
Microsoft SQL Server 2016 (13.0.1601.5)

Instances and names

The installed instances can be seen asking for info. Providing an instance name will print detailed info about the instance.

> sqllocaldb i

MSSQLLocalDB
projects2012
projects2014
projects2016
v11.0


> sqllocaldb i projects2016
Name:               projects2016
Version:            13.0.1601.5
Shared name:
Owner:              DESKTOP-1\user1
Auto-create:        No
State:              Running
Last start time:    3/27/2017 4:23:17 PM
Instance pipe name: np:\\.\pipe\LOCALDB#875A98AF\tsql\query

Creating instances

If more than one version of SQLLocalDB is installed and it is desired to use them all, you can create specific instances of each version with names that indicate what version the instance is - instead of just having the default “MSSQLLocalDB” instance.

Here is a sample for creating named instances of all three. The “-s” parameter causes the instance to be started as well.

> sqllocaldb c "projects2012" "11.0.2318.0" -s
> sqllocaldb c "projects2014" "12.0.2000.8" -s
> sqllocaldb c "projects2016" "13.0.1601.5" -s

Starting instances

An instance can be started or stop witht he command line.

# start instance
> sqllocaldb s "instanceName"

# stop instance
> sqllocaldb p "instanceName"

Removing instances

Instances can also be removed.

> sqllocaldb d "instanceName"

Connecting to an instance in Visual Studio

To connect to the LocalDB instance in Visual Studio, open the Server Explorer tool window, add a connection, specfify the following connection string (altered for your instance name), select the database from the dropdown, “Test” the connection, and select Ok.

(localdb)\projects2016

Connecting to an instance in SQL Server Management Studio

To connect to an instance of LocalDB using SSMS, use the following line (altered for your instance name) in the connection name:

(localdb)\projects2016

Connection string for ApplicationDbContext

The connection string to use for LocalDB can be altered in the web.config file by changing the instance name to one of your instance names that you want to work with. In the following, replace “projects2016” with whatever is your instance name.

  <connectionStrings>
    <add 
        name="DefaultConnection" 
        connectionString="Data Source=(localdb)\projects2016;AttachDbFilename=|DataDirectory|\my_database.mdf;Initial Catalog=my_database;Integrated Security=True" 
        providerName="System.Data.SqlClient" />
  </connectionStrings>