SQL Server 2012 features: Weighing the benefits, limitations
A comprehensive collection of articles, videos and more, hand-picked by our editors
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.
2. In the License Agreement screen, accept the license agreement and click Next.
3. In the Ready to Install the Program screen, click the Install button to start installation of Microsoft SQL Server 2012 Express LocalDB.
4. Once the installation is successfully completed, click Finish.
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.
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
NAME = N'DemoLocalDB',
FILENAME = N'D:\LocalDB\DemoLocalDB.MDF',
SIZE = 3072KB,
FILEGROWTH = 1024KB
NAME = N'DemoLocalDB_log',
FILENAME = N'D:\LocalDB\DemoLocalDB_Log.LDF',
SIZE = 1024KB,
FILEGROWTH = 10%
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [DemoLocalDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
Once the database is successfully created, you will see the database in Object Explorer as shown here:
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.