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.
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
