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.
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:
- Package Build Artefacts
- Deploy to Testing Environment
- Create LocalDB Instance and Deploy
- If Tests Are Green, Package
- Deploy to Test Environment
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.