SQL Server for Beginners: Part V – Using SQL Server 2012 LocalDB

In a previous chapter, I described the installation process for SQL Server 2012 Express which included all the bells and whistles of the advanced services package. While it’s great to have all the tools at your disposal to learn from, some people might not want such a large installation on a particular machine or the administration that goes with it but might still want the basic database capabilities of SQL Server on their desktop. In this case, SQL Server 2012 offers a new edition called LocalDB.

LocalDB is a minimized version of SQL Server Express specifically for developers who still need all the programming features including the ability to create stored procedures and other objects within an instance of SQL Server. It has a few restrictions that the average beginner probably won’t be bothered by and you can connect to it with SQL Server Management Studio (SSMS) or other tools to create and manage databases. You can even install the AdventureWorks sample database to work with through an instance of LocalDB.

In this article, I’ll describe the process of installing both LocalDB and SSMS in order to create a small desktop database environment to work with as a developer or a beginner in database design.

While LocalDB is included as part of the SQL Server Express Advanced Services package, it and SQL Server Management Studio also have their own installation packages if you want to install them separately. LocalDB is actually included in the installation of SSMS but also has its own installation file. To download either or both of these packages, simply visit the Microsoft download page and select the SqlLocalDB.MSI install and the SQLManagementStudio_ENU.exe packages that match your system type. There are packages for both 32-bit and 64-bit systems so you will need to download the right ones.

Downloading and Installing LocalDB Only

The LocalDB installation can be done on its own by double-clicking on the
SQLLocalDB.MSI file. It’s very quick but the program’s presence on your machine
will be stripped down; there are no menu options or icons. The program is run
from a command prompt which I’ll go into more detail about later.

To install LocalDB:

  1. Click Next on the title screen as shown above.
  2. Accept the license terms and click Next.
  3. On the next screen, click Install to begin.
  4. Once the installation is complete, click Finish on the completion screen.
Installing SQL Server Management Studio

SQL Server Management Studio provides the interface that you need in order to easily create and manage databases with SQL Server Express and LocalDB. It’s installation is a bit more involved than LocalDB but is still no more difficult than installing any other software. To start, double-click on the SQLManagementStudio_ENU.exe file that you downloaded from Microsoft.

Click on the first choice on the Installation screen indicating a new installation or addition of features and follow the prompts from there.

  1. Accept the license terms on the next page and click Next.
  2. The Product Updates screen might indicate that there are updates available, be sure to include these as part of the installation and click Next.
  3. Depending on the updates installed, the installation program might notify you at some point that your computer needs to restart before it can complete the install. After you restart the machine, if the installation program doesn’t start again on its own, just re-run the installation program and follow the prompts.

The option screen should appear as shown above with the Management Tools and SDK selected. If you have already installed LocalDB, the option will be checked and disabled. Otherwise, you can select it to install the LocalDB if you wish. Click Next and then Next again on the Error Reporting screen. After this, the installation should finish on its own and you can click Close when it’s done.

Once the installation is finished, SQL Server Management Studio should show up under the Microsoft SQL Server 2012 group on your Windows Programs menu.

(Article continues after ad)

Marion Technical College in Ocala, Florida offers a variety of vocational and career programs, including Database Development and Programming. Find out more on our site at http://www.mariontc.edu.


Setting Up LocalDB

LocalDB has a default SQL Server instance named v11.0 and can also support other named instances. These instances are stored within a subdirectory of your Windows Users folder. The path will look something like this:

C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances

The default instance might not be created as part of the main installation which means that you’ll need to manually start it within the SQLLocalDB utility. The main LocalDB utility is an executable file named SQLLocalDB.exe. You’ll probably find it at the following location on your computer:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLLocalDB.exe

In order to use the program, you will need to run the EXE file with the appropriate options from within a command window. The easiest way is to navigate to the above folder within Windows and then hold down the SHIFT key while right-clicking within the window. Select the Open command window here option from the context menu and a command window will appear with that directory already selected.

A command window can be opened to a specific directory by holding the SHIFT key
while right-clicking inside the directory window.

If this menu option is not available in your version of Windows, you can open a command window by clicking the Start button and selecting the Command Window option from the Programs –> Accessories group or by clicking the Start button, selecting the Run menu option and typing cmd in the command box. Once the command window is open, type in the following command to navigate to the proper directory.

cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn

For a full list of the commands that you can use in SQLLocalDB, click here. The commands in the screenshot below will get you started. The first start command will start the default instance, creating it if necessary. For named instances like the one shown in the second and third commands below, it is necessary to explicitly name, create and start the instance. Once you create an instance of SQL Server with the utility, you can browse to the Instances subdirectory under your Windows Users directory and see the files that it has created.

With the SQLLocalDB utility, you can create and start instances of SQL Server LocalDB.

Accessing LocalDB with SQL Server Management Studio

As with SQL Server Express, it’s much easier to work with your databases in a graphical environment. Once you’ve installed both LocalDB and SSMS and ensured there are instances of SQL Server to work with, you can access them through SSMS by correctly setting the Server Name field in the connection box. See the screenshot below for an example. “(localdb)\” followed by the instance name will enable SSMS to connect to the LocalDB instance. LocalDB will use Windows Authentication so that your Windows user account is your authentication for the database.

If you have not started the LocalDB instance through the SQLLocalDB utility, then SSMS might take a little longer to connect because it has to start the instance first.

Installing the AdventureWorks Database

Installing the AdventureWorks sample database from Microsoft works much the same way in LocalDB as it does in normal SQL Server Express. See my earlier article for the full details on downloading and installing this database. Since you will not have full-text indexing installed as part of LocalDB, you will need to make the changes to the script described in that article.

Creating New Databases – File Activation Errors

There is a single issue that exists when creating new databases with SSMS and LocalDB. When trying to create a database in the instance folder under the Users directory, the program consistently throws a file activation error. This appears to be a permissions issue of some kind involving the account that SSMS uses to create a database. The easiest workaround is to simply create the database elsewhere.

Under the Path setting, you can select any directory that you want in which to store the database files. You will need to be sure to change the setting for both the database and log file.

For more information:

SQL Server 2012 Express LocalDB – Main MSDN article

SQLLocalDB Utility – MSDN reference page

What is LocalDB – Additional specifications and installation information from SQL Coffee

 

(This article is updated from an article series previously published by Comeau Software Solutions.)