Problem
Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the “User Instances” feature (which has been deprecated) leads to a lot of confusion – developers connect to one instance of the database through their tools, and another instance of the database through their program, and don’t understand why updates to one aren’t reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I’ve seen, the two seem to go hand in hand.
Solution
SQL Server 2012 introduces a new feature, SQL Express LocalDB. The purpose of this new feature is to provide developers with a local environment that is much easier to install and manage. Instead of installing a service and configuring security, they just start up an instance of this new LocalDB runtime as needed. In addition, the download for the SQL Express LocalDB runtime is only about 33 MB (or 27 MB, if you are still on x86), compared to the 100s of MBs required to download a full edition of SQL Server Express.
Also, check out this related tip: Getting Started with SQL Server 2017 Express LocalDB.
Before you get started, you’ll want to make sure that your operating system is patched to the latest service pack and current according to Windows Update. Supported operating systems are as follows:
- Windows 7
- Windows Server 2008 R2
- Windows Server 2008 Service Pack 2
- Windows Vista Service Pack 2
It will also work on Windows 8 if you’re using any of the pre-release versions, but I have not tested this on any of the Server Core variants of Windows Server, so you’re on your own there. For further information on system requirements, please see Hardware and Software Requirements for Installing SQL Server 2012.
You’ll want to make sure that you’ve installed .NET Framework 4.0 and, equally as importantly, the .NET Framework 4.0.2 update (KB #2544514).
Once your system is up to date, you can download the SqlLocalDb installer from:
http://www.microsoft.com/en-us/download/details.aspx?id=29062
- If your system is 64-bit, you want x64/SqlLocalDB.MSI (33.0 MB)
- If your system is 32-bit, you want x86/SqlLocalDB.MSI (27.7 MB)
Setup is rather trivial:
Once installed, you can interact with SqlLocalDb using the command line. The following will tell you the version of SqlLocalDb:
C:\> SqlLocalDb infoResult:
v11.0
If you want to create an instance:
C:\> SqlLocalDb create "MyInstance"Result:
LocalDB instance "MyInstance" created with version 11.0.
To start the instance:
C:\> SqlLocalDb start "MyInstance"Result:
LocalDB instance "MyInstance" started.
You can also create an instance and start it in one command using the -s argument:
C:\> SqlLocalDb create "MyInstance" -sTo stop and delete an instance, you can issue two commands:
C:\> SqlLocalDb stop "MyInstance" C:\> SqlLocalDb delete "MyInstance"
If you try to just delete the instance without first stopping it, you will get this error:
Delete of LocalDB instance "MyInstance" failed because of the following error: Requested operation on LocalDB instance cannot be performed because specified instance is currently in use. Stop the instance and try again.
To check on the status and other details about an instance, you can run:
C:\> SqlLocalDb info "MyInstance"Result:
Name: MyInstance Version: 11.0.2100.60 Shared name: Owner: METEORA\AaronBertrand Auto-create: No State: Running Last start time: 4/29/2012 6:17:24 PM Instance pipe name: np:\\.\pipe\LOCALDB#ABB78D50\tsql\query
Now that an instance is created and started, you’ll probably want to do other things like create databases and run queries. Unfortunately SqlLocalDb on its own only provides an interface to the engine; it does not provide a means to interact with databases. However there are several other ways to connect to and interact with SqlLocalDb instances.
sqlcmd
In order to use sqlcmd, you must install either SQL Server 2012 Management Studio Express or the client tools from a regular SQL Server 2012 edition (though I caution against using Evaluation Edition here, since the client tools will expire after 180 days). You can install just the SQL Server 2012 Command Line Utilities, but I will install Management Studio Express since it’s free, supports all of the functionality you should need to manage LocalDB instances, and for most tasks is preferable to sqlcmd anyway. You can download Management Studio Express from this page:
http://www.microsoft.com/en-us/download/details.aspx?id=29062
As with SqlLocalDb, you’ll want to pick the file that is appropriate for you, depending on whether you are running on x86 or x64. When the Installation Center opens, you’ll want to pick the option “New SQL Server stand-alone installation or add features to an existing installation” – even though neither describes what you’re really doing.
On the Product Updates screen, you can uncheck the box to prevent it from checking the web for updates (or to prevent it from timing out in the event you have slow or no connectivity). At the time of writing no updates were found anyway.
While it should be checked by default, make sure that on the Feature Selection screen, “Management Tools – Basic” is selected.
Once installed, you can connect to the local instance using sqlcmd. Be sure to use the most recent version of sqlcmd on your system; if you have multiple versions (e.g. from previous versions of SQL Server or Visual Studio), calling sqlcmd will pick up the first one in your PATH environment variable, which is almost certainly going to be the earlier version. You can create a shortcut to cmd setting the start location to:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\
So now that sqlcmd is installed, and assuming you started an instance called “MyInstance” per above, you can connect using sqlcmd this way:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\> sqlcmd -S (localdb)\MyInstance 1> SELECT @@VERSION; 2> GO
Result:
So now you can create a database, create a table, etc.
1> CREATE DATABASE foo; 2> GO 1> USE foo; 2> GO Changed database context to 'foo'. 1> CREATE TABLE dbo.bar(ID INT); 2> GO 1> exit
Management Studio / Management Studio Express
We can connect to this instance using Management Studio Express in a similar way. You can launch ssms.exe from the cmd line, and when prompted for a connection, use the server name “(localdb)\MyInstance”:
Here is the database / table we created through sqlcmd (we could of course continue adding/editing objects from Management Studio):
One thing you’ll want to make note of is the location of databases in a SqlLocalDb instance. As described in this blog post, databases are by default created in your user profile directory. So if your profile is on C:\ and that drive is short on space, or if you want to create your databases on other drives for whatever reason, you’ll need to use explicit locations in your CREATE DATABASE statement, e.g. to put the data file on D:\ you can say:
CREATE DATABASE foo ON (name = 'foo_data', filename = 'D:\dev\foo_data.mdf');
It would be nice if you could set the default data / file paths for the SqlLocalDb instance so that you didn’t have to specify the locations every time, but this screen is greyed out:
SQL Server Data Tools
In addition to using sqlcmd and Management Studio Express, you can download SQL Server Data Tools (SSDT), which also installs the Visual Studio 2010 Shell if you don’t already have some edition of Visual Studio 2010 installed. You can download SSDT from the following site:
http://msdn.microsoft.com/en-us/data/hh297027
Eventually this will launch the Web Platform Installer:
Once you’ve installed SSDT, you can launch the program, create a new SQL Server Database Project, and use the new (localdb) instance it creates:
But you can also use “Add Server…” to connect to and work with your existing instance:
Visual Studio
To use SqlLocalDb with Visual Studio proper, see the following blog post, where Roel van Lisdonk walks you through using both Visual Studio 2010 and the newer VS11 beta to connect to SqlLocalDb:
http://www.roelvanlisdonk.nl/?p=2607
PowerShell
It took me a little finagling to get PowerShell to connect to my LocalDB instance. Even with SQL Server 2012 client tools and the .NET 4.0.2 update installed, it seems that the version of SMO that PowerShell uses an older version of SMO that is not LocalDB-aware. Granted, this was Windows 7 without any explicit updates to PowerShell. While you should be able to connect using this syntax:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sn = "(localdb)\MyInstance";
$srv = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;Various attempts at server names (such as (localdb)\.\MyInstance and .\MyInstance yielded generic “Failed to connect to server” errors. It turns out I had to connect using the named pipe syntax, which was necessary for other applications prior to the 4.0.2 update:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$sn = "np:\\.\pipe\LOCALDB#ABB78D50\tsql\query";
$srv = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $sn;
$srv.ConnectionContext.LoginSecure = $true;
$srv.Databases | Select Name;To determine that little hex code associated with the named pipe for the selected instance, you can use the SqlLocalDb info command. As you can see in the following screen shot, I just copied the output from the “Instance pipe name” line:
Note that this hex code will change every time you start the instance. Hopefully this issue will be fixed soon so that PowerShell can connect to SqlLocalDb instances just like the other applications can.
ADO.NET
In order to connect via ADO.NET, there is only a minor change in the way you specify your connection string. As per the above examples, you just use (localdb) in place of “.” or “machine name”:
Data Source=(LocalDB)\MyInstance;Initial Catalog=foo;Integrated Security=True;
This requires the .NET Framework 4.0.2 update, however, otherwise you’ll have the same issues I disclosed about PowerShell, and you’ll need to use the LOCALDB#HEX-PIPE connection format.
Conclusion
Hopefully this gives you a jump start in using SQL Server 2012 Express LocalDB for local development. There are other aspects I haven’t touched on, that I may treat in future tips – for example, sharing instances with other users, and the fact that LocalDB is named as such for a reason: it does not accept remote connections.
Next Steps
- Download and install SQL Server 2012 Express LocalDB.
- Use this standalone engine for new local development or prototype work.
- Check out a new version of this tip Getting Started with SQL Server 2017 Express LocalDB
- Review the following tips and other resources:
