Given the extant definition for SQL Server 2012's LocalDB as

SqlLocalDB is a local, low-overhead database engine of SQL Server 2012 (and future versions) that allows developers to focus on development rather than instance configuration or security.

I was curious what the path from development in LocalDB to a production instance would look like, especially in light of the issues with user instances (ca. SQL 2005) and trying to promote one of them to production and devs would lose track of which instance they were actually trying to promote. A quick search on StackOverflow betrays such issues in various forms. Does LocalDB improve/streamline deployment in such circumstances?

  • 3
    LocalDB underpins the SSDT development model. Several choices for deployments including dacpac/SqlPackage.exe and source/target compare to generate scripts in a similar vain Redgate/Apex et al. Will revisit with an answer later if nobody beats me to it. – Mark Storey-Smith Dec 13 '12 at 17:47

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.