Problem
In a previous tip, I walked through installation and some caveats with the first version of SqlLocalDb, which shipped with SQL Server 2012. Well, it’s been several major releases, and some of the aspects have changed, so I thought I would provide a refresh.
The purpose of SqlLocalDb has remained constant: To provide developers with an easy way to develop with SQL Server locally, on Windows, without the overhead, security, and maintenance of a full-time, proper instance. But for anyone who has used SQL Server and is new to SqlLocalDb, a few of the details are unintuitive.
Solution
The first step is making sure you’re on a supported operating system (Windows 8 / Server 2012 or above), and then download the SQL Server Express Edition installer here. I am using Windows 10 in these examples; if you are using Windows 8 or Windows Server 2012, make sure to consult KB #2681562 : Installing SQL Server on Windows Server 2012 or Windows 8. If you’re using Linux, sorry, but I think you’re out of luck.
Step 1: Install Microsoft SQL Server Express Localdb
To get just the SqlLocalDb MSI (45 MB) vs. the whole enchilada (700+ MB), choose the “Download Media” option to start downloading:
Pick your language, choose the LocalDB option, and pick a location to download the MSI:
Then you’ll get a very big dialog to indicate success. Choose the Open Folder option:
The folder will open, and you will see SqlLocalDB.msi. Launch this executable to start the wizard:
You’ll have to accept a license agreement and then on the next screen click Install:
At some point you will probably be prompted by UAC controls:
Then it will finish:
Alternatively, if you already have a valid SQL Server 2017 install media, you can install SqlLocalDb from that installer, and avoid downloading the media above. Run Setup.exe and from the Installation Center choose “New SQL Server stand-alone installation or add features to an existing SQL Server 2017 instance.”
Next, you’ll be offered to include any important updates (you should check this box unless you are on a very slow Internet connection – but don’t worry, we’re going to patch this installation with the latest Cumulative Update anyway):
Next, you’ll choose the type of installation to perform; you want “a new installation of SQL Server 2017,” even though that choice may not be the most intuitive:
You’ll have to accept the license terms in a new dialog, and then you’ll be asked for a product key. Here, just select the free Express Edition (other editions won’t offer a LocalDB installation):
Then on the Feature Selection screen, make sure you un-check the Database Engine Services option, which is selected by default (unless you also want to install a full-on SQL Server 2017 instance).
Then scroll down and select the LocalDB option (I also selected Client Tools Connectivity).
Click Next, Install, and then you should have this:
Step 2: Patch Microsoft SQL Server 2017
Before you get started using SqlLocalDb, you should patch SQL Server 2017 to the latest Cumulative Update. The reason is that there was initially a critical bug that prevented the creation of database files due to a missing slash in the file path.
This problem was fixed in CU #6 (see KB #4096875), but at the time of writing, the latest Cumulative Update available was CU #9. You should typically use the latest CU available; you can always get the latest CU here.
At the beginning of the install it will not indicate anything about SqlLocalDb, but just check all the boxes you can and proceed. At the end you will see confirmation that SqlLocalDb has been patched (in this case I also updated a SQL Server 2017 instance from CU #8 to CU #9):
Step 3: Install client tools and/or SQLCMD and/or PowerShell
If you don’t already have SSMS, or another way to connect to the SQL Server database, you’re not going to get very far. Rather than guide through the full installation I’ll just point you to the locations to get the most recent versions:
- Latest version of Management Studio (17.8.1 at time of writing):
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
- Latest version of SQLCMD (Command Line Utilities 14.0 at time of writing):
https://www.microsoft.com/en-us/download/details.aspx?id=53591
- Installing Windows PowerShell:
https://docs.microsoft.com/en-us/powershell/scripting/setup/installing-windows-powershell
Step 4: Create an localdb instance via SQLLocalDB Command Line
At the command line, you can interact using the SqlLocalDb utility to configure the instance of localdb. Start with getting information about the installation:
C:\> SqlLocalDb info
Result:
MSSQLLocalDB
This used to return the localdb version number (in the original article, and with the 2012 release, this returned v11.0.
Next, you can create an instance with the following command:
C:\> SqlLocalDb create "MyInstance"
Result:
LocalDB instance "MyInstance" created with version 14.0.3030.27.
Check the info:
C:\> SqlLocalDb info "MyInstance"
Result:
Name: MyInstance
Version: 14.0.3030.27
Shared name:
Owner: PEDRO\aaronbertrand
Auto-create: No
State: Running
Last start time: 7/20/18 10:44:51 AM
Instance pipe name: np:\\.\pipe\LOCALDB#9EBB1CD2\tsql\query
The Instance pipe name may come in handy later, though I’ve found that a lot of the connectivity issues in earlier versions of this feature have gone away. Also, in older versions you had to explicitly start the instance, but it now starts automatically.
If you want to stop and drop the instance, use:
C:\> SqlLocalDb stop "MyInstance"
C:\> SqlLocalDb delete "MyInstance"
But don’t do that just yet. Evidence that this all works so far:
Connection String for SQLCMD
Locate SQLCMD, making sure to use the newest version on your machine (your environment path may list an older version first). Look for the highest version in the Binn folders under C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\[version]\Tools\. You can connect to this instance using Windows authentication with the following code at the command line:
sqlcmd -S "(localdb)\MyInstance"
Then we’re greeted with a line number prompt and can enter sql code on-demand. So something like:
SELECT @@VERSION;
GO
CREATE DATABASE blat;
GO
USE blat;
GO
CREATE TABLE dbo.splunge(Mort int);
GO
INSERT dbo.Splune(Mort) VALUES(1);
SELECT * FROM dbo.splunge;
GO
Yields:
Connect using Microsoft SQL Server Management Studio
Like with SQLCMD, you can connect using (localdb)\MyInstance from SSMS as well (in older versions you needed the pipe name I mentioned above):
When you open Object Explorer, you’ll see the database and table we created, and you can interact with the instance just like any other SQL Server instance (with obvious exceptions, for example there is no SQL Server Agent node under Management):
Connection String using PowerShell
Modern versions of PowerShell are also able to connect to LocalDB instances using the simple instance name format. With my default installation, though, I found I still needed to manually load SMO before it would connect and interact.
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$s = "(localdb)\MyInstance";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;Proof it works:
Conclusion
I hope this gives you a head start into playing with SqlLocalDb for local development. It can be a really useful way to build out a proof of concept or test a query or feature without installing a full-blown SQL Server instance.
Next Steps
- Download and install SQL Server 2017 Express LocalDB.
- Use the standalone engine for local development or proof of concept work.
- See these tips and other resources (some Microsoft links are older, but still relevant):
