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.