Essential Guide

SQL Server 2012 features: Weighing the benefits, limitations

A comprehensive collection of articles, videos and more, hand-picked by our editors

Introduction to SQL Server 2012 Express LocalDB

SQL Server 2012 Express LocalDB is a good way for developers to be able to play around with SQL Server 2012. Learn how to install and get started with it.

Microsoft SQL Server 2012 Express LocalDB is a lightweight version of SQL Server 2012 targeted for database developers.

Microsoft's goal was to get developers an executable mode of SQL Server Express without much hassle. Developers can more quickly install and use a LocalDB instance when compared to other editions of SQL Server. Remember, though, SQL Server 2012 Express LocalDB has all the limitations of SQL Server 2012 Express Edition.

Installing SQL Server 2012 Express LocalDB

There are some requirements for installing LocalDB. First, you must have Microsoft .NET Framework 4 and Microsoft .NET Framework 4.0.2 Runtime Update (KB2544514). Next, you must have one of the following operating systems: Windows Vista Service Pack 2, Windows 7, Windows Server 2008 Service Pack 2, or Windows Server 2008 R2.

Downloading LocalDB is easy enough. The x86 version for 32-bit operating systems is about 28 MB, while the x64 version for 64-bit OSes is about 33 MB.

Using the SqlLocalDB.exe utility, a database developer can create and manage an instance of SQL Server 2012 Express LocalDB. The SqlLocalDB utility supports different sets of arguments to create, drop, start, stop, share and unshare instances of LocalDB. To learn more about other arguments and syntax that SqlLocalDB supports, see SqlLocalDB Utility.

The next step is to actually install SQL Server 2012 Express LocalDB. Here's a step-by-step guide:

1. Double-click the SqlLocaLDB.MSI file to open Welcome to the Installation Wizard for SQL Server 2012 Express LocalDB as shown in the figure below. Click Next to continue with the installation.

SQL Server LocalDB 1

2. In the License Agreement screen, accept the license agreement and click Next.

SQL Server LocalDB 2

3. In the Ready to Install the Program screen, click the Install button to start installation of Microsoft SQL Server 2012 Express LocalDB.

SQL Server LocalDB 3

SQL Server LocalDB 4

4. Once the installation is successfully completed, click Finish.

SQL Server LocalDB 5

5. Once you have successfully installed SQL Server 2012 Express LocalDB, the next step will be to connect to the LocalDB instance.

Connecting and creating in SQL Server 2012 Express LocalDB

One can connect to the LocalDB instance using these several methods:

1. You can download SQL Server 2012 Management Studio Express, which is free and can connect to LocalDB.

2. Using SQLCMD, you can connect to LocalDB. SQLCMD can be downloaded from SQL Server 2012 Command Line Utilities from the Microsoft SQL Server 2012 Feature Pack.

3. You can connect to LocalDB using SQL Server Data Tools.

When using SQL Server Management Studio to connect, follow these steps:

In Connect to Server dialog box, specify the Server Name as (localdb)\v11.0, choose the Authentication as Windows Authentication, and click Connect to establish a connection with SQL Server 2012 Express LocalDB.

SQL Server LocalDB 6

Once you connect to LocalDB, you can create a new database for your project. Execute the script below in a New Query window of SQL Server Management Studio to create a Demo Database named DemoLocalDB.

CREATE DATABASE [DemoLocalDB]
CONTAINMENT = NONE
ON PRIMARY
(
    NAME = N'DemoLocalDB',
    FILENAME = N'D:\LocalDB\DemoLocalDB.MDF',
    SIZE = 3072KB,
    FILEGROWTH = 1024KB
)
LOG ON
(
    NAME = N'DemoLocalDB_log',
    FILENAME = N'D:\LocalDB\DemoLocalDB_Log.LDF',
    SIZE = 1024KB,
    FILEGROWTH = 10%
)
GO

USE [DemoLocalDB]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [DemoLocalDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

Once the database is successfully created, you will see the database in Object Explorer as shown here:

SQL Server LocalDB 7

By default, a LocalDB database file such as .MDF, .NDF and .LDF are stored in the following location: “C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0\”. Hence, you need to reserve enough space for database growth on the local drive. Either that or create the database by specifying the location of data and log file as mentioned in the above TSQL script.

This was first published in October 2012

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

SQL Server 2012 features: Weighing the benefits, limitations

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close