Hello!

So, there are many different product SKUs of SQL Server available, and generally when developing it’s probably best to use SQL Server Development Edition, even when it used to cost you money to get a product key. The disadvantage of using it though is that it is a large install. The team behind SSDT recognised this and so LocalDB was created, with the intention of using it as a very lightweight version of the database engine to develop against. It is available as it’s own download, and is also packaged as part of SSDT.

However, there are some disadvantages to it: it is in fact a stripped-down version of SQL Express, which lacks many features found even in Standard, let alone Enterprise. And so this made LocalDB fall into the “a good idea but probably not that useful” category of products.

Until recently.

It’s not exactly breaking news when I talk about how a lot of the features available solely in Enterprise have now been made available in the other, less expensive versions of SQL Server, providing you are on SQL Server 2016 SP1. But what makes this interesting now is that LocalDB also benefits from this by virtue of being “Diet” SQL Express.

So Why Is This Important?

One of the big challenges of automating SQL Server database deployments, and there are a lot of big challenges, is running tests early enough in the process. For SSDT-based solutions, the process probably runs like so:

• Build
• Package Build Artefacts
• Deploy to Testing Environment
• Test

But really, you want some level of testing to run before you deploy anywhere: sayyou’re using Nuget packages to package up a DACPAC and a publish.xml, there are Nuget packages with 0% testing on them, making them potentially junk. But now with LocalDB supporting so many more features than it did previously it is possible to:

• Build
• Create LocalDB Instance and Deploy
• Test
• If Tests Are Green, Package
• Deploy to Test Environment

Using something like T-SQLT or SQL Server Database Unit Testing means it is possible to mock objects and data. So at the very least you have the ability to run unit tests before you deploy to a test instance.

This may not sound like a big deal but I think it is and it reduces the number of failed deployments to a test instance, which chances are is being used by multiple teams/individuals. This can slow the rate of development down a lot.

With all this in mind, I’ve written a DSC-like LocalDB script. It will download and install LocalDB to a server.The idea is that this script could be used in a build process (say TeamCity) to download and install LocalDB. Once it’s installed it is possible to use the sqllocaldb utility to start a new instance, then use SQLPackage or DacFX to deploy a database.

View the code on Gist.