Hello!

Today I’m going to write a little bit about localdb, partly because I don’t think it is used enough and partly because the other day I was accused of having “a thing” about localdb. I’m not sure I have “a thing” about any particular piece of technology, apart from maybe anything made by Nintendo. But because I do use it a lot I thought it worth sharing some thoughts on this great tool. But first I wanted to know if many people had heard of localdb, let alone use it. So using the incredibly unscientific method of A Twitter Poll, I asked if people had heard of localdb. Now not everyone who came across this poll would be a tech person, but even so most people had replied that they had heard of it.

Creating an Instance Is Very Fast

The other day we were having a debate in the office about testing, namely the setting up of testing and what you should assume is there and what needs to be set up prior to each, or all tests. To give this a bit more context, I’ve written a module that uses CsvDataReader to load some data into a staging table then execute a stored procedure to merge the data. The list of stagingtables, procs and csv files are stored in a json file; the idea being that you pass the json file to a function in the module that then loads the data into the database. When writing tests its pretty obvious that I definitely need:

• a csv files
• a json file
• a staging table
• a proc to do the merge
• a target table

Now I could do with a database, but equally I could use tempdb. But either of these things means I need an instance. Whether or not a test suite should concern itself with creating an instance, or just how much setup is required is a topic of discussion for another time. But because I wanted to be sure that there was an instance running locally I knew I could use I decided to use localdb because it is so fast to create an instance. Using Measure-Command these were some timings when running sqllocaldb.exe -

• create instance - 183 Milliseconds
• stop instance - 66 milliseconds
• start instance - 34 milliseconds
• delete instance - 61 milliseconds.

So the option of spinning up a new instance of localdb, creating some objects in tempdb, running my tests and then stopping and deleting the instance is a trivial matter.

LocalDB is Used by SSDT

Now you probably already know this, but when you install SSDT for Visual Studio you also install localdb. Currently for VS 2017 it is called ' ProjectsV13'. Connect to this instance using Server Explorer in Visual Studio, or to (localdb)\ProjectsV13 in SSMS and you’ll be able to see all the databases you have compiled in SSDT. And from Kevin Cunnane himself, the reason for using localdb like this was to avoid any conflict from anything installed on the local instance of localdb.

Installing LocalDb is Fast

I set up a VM in Azure with some of the smallest specs as parsimoniously as possible: 1 core, 1GB RAM and using standard HDD. As part of PoshSSDTBuildDeploy I wrote a function to install LocalDB. OK maybe on reflection maybe I do have a thing for LocalDB… But at any rate, let’s see who long that takes to run on this basic box: to download it took all of 50 seconds.

That’s not bad going, but getting down to brass tacks the install took all of 35 seconds. Amazing!

And shameless shill time: if you want you can check if localdb is installed by re-running the function from PoshSSDTBuildDeploy every time (and if localdb is not installed then instal it), every build. To take the hassle of running a full local instance to run a dacpac deployment and some tSQLt tests every build, and being able to install the necessary infrastructure is a huge win: who needs Chef when you can do DSC in the build!

LocalDB Instances Can Be Created To Match a Major Release

Let’s say you want to deploy a specific version of a database against a major release. This would be a nontrivial matter if we were to rely on full-fat versions of SQL Server. But with Localdb you can specify a major release version and it will create an instance with the features of that specific version. This does means you need a version of localdb per major version, but still far less troublesome to maintain than multiple version of SQL Server locally.

LocalDB Is Missing Some DMVs

Open up the master and msdb databases and all the system tables and views do look present and correct. However this doesn’t mean that the functionality exists. Given how small an install is of Localdb it stands to reason that some functionality had to go. This is only really problematic if your app or your tests use these missing features. I need to gather the list of these as I had them “somewhere”.

You Can Deploy An Azure V12 Database to LocalDB

A big win for localdb is that you can deploy databases whose target platform is set to Azure. Having an offline developer experience for anything in Azure is great: I know there’s nothing for ADF, nor ARM Template validation. Functions can also be run locally. Erm, I’ve gone on a bit of a digression here. What I’m getting at here us that if you want to do some offline development, or you’d prefer not to have to wait for infrastructure to deploy before you can deploy your database, then LocalDB is a big win. That is not to mention the fact that LocalDB is free whilst Azure SQL will still cost. OK fine, basic Azure SQLDB is pennies, but it’s also as slow as a Sturdy Shuckle, whereas I bet most SQL Developers have got a dev machine with some very respectable specs to it.

In Conclusion

I like LocalDB.