I was curious what the path from development in LocalDB to a production instance would look like
...
Does LocalDB improve/streamline deployment in such circumstances?
Not really that much difference, to be honest. While User Instances
is no longer a factor, it is still very easy for a developer to create a bunch of SqlLocalDb instances and lose track of which one holds the "true" version of the truth.
What LocalDb does is eliminate the need to set up a full service-based instance of SQL Server (Express or otherwise), and reduces the complexity of the security model. It is still up to the developer or the team to implement sound development practices with regard to source control and promotion. As expressed in the comments, there are several ways to promote code from a local instance to production (hopefully through some kind of QA/test systems first):
- backup / restore
- database projects via VS/SSDT
- 3rd party comparison tools like SQL Compare
- manual placement of schema / scripts / data in source control
- manual generation of scripts at deployment time
- import/export data wizards (caveat: I have not tried the wizard against SqlLocalDb)
(I've intentionally left off detach / attach because backup / restore is much safer. With detach if something happens to the .mdf file during or after detaching, you now have zero copies of your database. If a backup goes wrong, you still have the source.)
Whether any of these are more or less prone to the same kinds of problems as today, depends more on team discipline and established procedures than whether they use Express or LocalDb for local development. IMHO.
The nice thing LocalDb provides over user instances is that if you connect to a single instance of LocalDb you don't end up with the specific case where every time you use AttachDbFileName
you get a new copy of the database. The most problematic part there is you change a table in one instance, then get an error from your application because it's connected to a copy that doesn't have your table change. As you've pointed out, this has led to a never-ending stream of confusion and similar questions on SO. In fact SqlLocalDb still supports AttachDbFileName
, but I think it's going to be very uncommonly used, if ever.
With LocalDb it is less likely that you run into these problems, but they still exist. It's a different tool with some advantages but it can still be used inappropriately.
In my case, I had Visual Studio 2010 Enterprise on my system when I initiated the setup for SQL Server 2012 Standard. After the SQL 2012 got underway, it stopped and prompted for the VS 2010 setup.msi. At that point, I ejected the SQL Server disc and inserted the VS 2010 setup disc. Then I used the dialog presented by the SQL Server 2012 setup program to browse to the root of the VS 2010 DVD, selected the msi there, and opened it. The SQL 2012 setup continued and when I saw that it was again installing SQL components (rather than VS 2010 components), I ejected the VS 2010 disc (which caused the SQL 2012 setup program to display an error that there was no disc in the drive), placed the SQL 2012 setup disc back into the drive, then clicked the Try Again button. At that point, SQL Server 2012 resumed and after some time it completed successfully.
Best Answer
LocalDB is an optional feature of SQL Server Express edition. It may be installed by:
1) Selecting the LocalDB feature during installation of SQL Server Express
2) Selecting the .NET desktop development feature during installation of VS 2017. According to this thread, SSDT does not automatically install LocalDB.