SQLLocalDB - A temporary Instance available always

What is LocalDB:

A lot of times we find ourselves in a need to get a quick local instance to test some scenarios out. Installing a new instance might take a lot of time and we might not always have space for it.LocalDb is a cut down version of SQL Server that was specifically designed to be light-weight and easy to start and stop quickly. This means a compromise so there are some restrictions for it, but it really is a great choice for most developers because you can quickly create, use and destroy instances that are specific to one particular user. LocalDb has some additional benefits: The utility is shipped with the standard installation of SQL server. It shares the same binaries for each instance so you do not have to use up lots of disk space for each instance that you need. This means that it is really simple to start one up if you find that you need a new instance in the heat of a development session.

How to Create a LocalDB Instance:

Creating a new LocalDb is simple: You just type the following at the command shell:

sqllocaldb create “instance name”

This will create a new instance that you can use called “instance name”, on my machine it takes about 4 seconds to create a new instance and then about another 2 seconds to start the instance. Installing a new instance of any of the other types of SQL Server is measured in minutes to hours.

If you have multiple versions of LocalDb installed, you can create any of those versions really easily, just by executing this code in a command shell:

Sqllocaldb versions

You should get something like this depending on the versions of SQL servers installed.

Let’s say you need a SQL 2014 instance you can use a command like this –

sqllocaldb create “instance name” 12.0.

You would get a new SQL Server 2014 instance you could connect to.

Connecting to a LocalDB Instance:

Connecting to a LocalDB instance is a little bit different than the locally installed instance. If you are connecting to a local named instance you would use the machine name followed with the instance name separated with back slash. For a LocalDB Instance you will use (local)\InstanceName representation to get connected.

Checking the instances currently configured:

Currently configured (started or stopped) instances can be checked by using below command –

sqllocaldb info

You can extend this command to get details regarding a specific instance as below –

sqllocaldb info “instance name”

This command will give you the following details regarding the instance mentioned –

1.    Instance Name

2.    Version

3.    Instance Owner

4.    Instance state (Started/Stopped)

5.    The last start time

If it is started, it also gives the path of the named pipe it is running on. If you have a client that does not understand the (localdb)\instancename then perhaps you can connect over named pipes to the instance.

LocalDb is great for developing because it is so fast to create and start instances, if you often work on different projects you can stop and start instances and have lots of different instances available without the overhead of having to maintain them and store them on disk.

Benefits of LocalDb for development:

• Fast development, it is simple to create new instances for development and testing.
• Shares binaries between all instances of the same version so you do not take up lots of disk space or have to maintain many different versions

Cosiderations of LocalDb for development:

• Does not include some fundamental features such as SQL Agent
• Does not support FILESTREAM
• Cannot be a merge replication subscriber,
• Only allows local queues for Service Broker.
• Always runs under the users security context