Is it normal to use LocalDb in production?

Question

I know that using LocalDb is very good and easy for developement, I wonder if it's good idea to use it in production when I host websites on IIS server?

I'm asking because I wonder if it won't have any kind of speed issues in production.

This is my connection string that I want to use in production

Server=(LocalDB)\\v11.0;Integrated Security=SSPI;MultipleActiveResultSets=true;
AttachDBFilename=|DataDirectory|ProjectDB.mdf;

Solution 1

LocalDB is absolutely supported in production. From the performance point of view it is identical to SQL Server Express, as they share the same database engine.

Now, for a site running in full IIS I would recommend using service-based instance of SQL Server Express. Since IIS is running as a service it is best to have a service-hosted database as well. That means you should not be using User Instance=true or AttachDbFileName=... in your connection string.

If you want to learn more, I recommend this post on using LocalDB from full IIS, part 1 and its follow up, part 2. They go into more details.

Solution 2

"Normal" - hard to say, that's a relative term.

Permitted - yes. Here is a quote from the Microsoft SQL Server Express blog where LocalDB was introduced:

"...if the simplicity (and limitations) of LocalDB fit the needs of the target application environment, developers can continue using it in production, as LocalDB makes a pretty good embedded database too."

---

UPDATE (March 2019)

After using LocalDB in dozens of production sites for two solid years now, I'll confirm that it works. However, if you need a free multi-user ("shared" in LocalDB terminology) DB, use SQL Server Express instead. LocalDB is not a good multi-user solution. There are MANY obstacles to overcome. I don't have time now, but if you would like details on those obstacles and respective workarounds, leave a comment, and I'll document them here or in another question.

---

UPDATE (April 2021)

Showstopper -- the shared (i.e., multi-user) instance feature is broken in SQL Server LocalDB 2017 and 2019. Having one of those versions installed will even prevent accessing a shared instance created in an earlier version. The problem is documented here. The only known "workaround" is to uninstall the broken LocalDB versions, and use LocalDB 2016 or earlier.

---

UPDATE (September 2021)

There is now a fix for the Showstopper issue mentioned in the preceding update (UPDATE April 2021). Caveat: I have not personally tested this fix.

Solution 3

I don't think so,

Even though LocalDb and SQLExpress might be the same on performance, LocalDb's Process shuts down itself if it doesn't have any request for a while. And usually the next request takes too long to response (or even worse, it times out).

I would recommend you install your own instance of SQLEXPRESS.

Comments

[Kumar Vikramjeet]

not sure how to make it work in Server 2012, the file applicationHost.config doesn't show asp.net 4.5 add name entry after installing asp.net.

[Hossein Shahdoost]

LocalDB is absolutely not supported in production.

[Krzysztof Kozielczyk]

What makes you say so @HosseinShahdoost? Did Microsoft reverse its production support? A link might be useful if so.

[Hossein Shahdoost]

This is the first sentence of Microsoft's Documentations "Microsoft SQL Server 2016 Express LocalDB is an execution mode of SQL Server Express targeted to program developers", msdn.microsoft.com/en-us/library/hh510202.aspx Also this Msdn blog has a lot of useful stuff to read. blogs.msdn.com/b/jerrynixon/archive/2012/02/26/…

[Krzysztof Kozielczyk]

I don't see how this is a statement that LocalDB is "absolutely not supported in production". It was created to make developers' life easier, which include the embedded database scenarios (but not in-process). Using it with IIS is a stretch, which I've pointed out in my answer.

[Hossein Shahdoost]

I don't see how this is a statement that LocalDb is "absolutely supported in production" as well. Not to run the main app's db. By taking a good look at the interface you can see that It was created to help developers run their integration and system tests easier.

[NicoJuicy]

LocalDB is the .net alternative for SqlLite ( no permissions required), so i think the same statements is appropriate for LocalDB : stackoverflow.com/questions/913067/… considering the use-case. You can use it in production, but consider it only for low traffic stuff

[gpro]

Could you briefly describe the obstacles you run into?

[Richard II]

If you only need single-user access, you should be fine. Isssues arise when you want the DB to be multi-user, because the process hosting the instance is shut down when the instance creator logs off, and can only be re-started by the creator. Thus you need an "always on" user as the creator. If you can create a dedicated service account, do that. It wasn't an option for me. I chose "Local System" but that presents other issues, some of which are documented here: stackoverflow.com/a/50491641/1633949. You also need to impersonate that account when you initially setup instance security.

[Richard II]

There were other obstacles as well, let me know if the ones above don't dissuade you, and I'll note the others too.

[amir azizkhani]

@KrzysztofKozielczyk i use localdb in desktop application. instance was shut down automatically. could you resolve this problem?

[Richard II]

@amir110, by default, LocalDB instances shut down after 5 minutes of no activity. The instance will start again automatically upon the next connection by its owner/creator to one of its databases. Conversely, it will not start automatically if the user connecting is not the owner/creator (in the case of shared instances.) If this comment doesn't help you solve your problem, post a new question with more detail, and include a link to the new question in a comment here, so we will be notified.