38

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;
AMIC MING
  • 6,306
  • 6
  • 46
  • 62
Stan
  • 25,744
  • 53
  • 164
  • 242
  • 3
    Why? Why? Why? Use a proper instance of SQL Server Express and make all of your users connect to the same, common instance of your database. In the current mode, won't each connection fire up its own copy of the database, and it will get discarded once it's done? Never mind that the security context won't work correctly here AFAIK, and that you will likely be violating EULA. Also why on earth are you using MultipleActiveResultSets? Is that on purpose? – Aaron Bertrand Nov 26 '12 at 19:04
  • 7
    No, LocalDB is not meant for production. – Remus Rusanu Nov 26 '12 at 19:04
  • @AaronBertrand because I had `There is already an open DataReader associated with this Command which must be closed first` error. – Stan Nov 26 '12 at 19:28
  • @Hossein This behavior can be modified http://stackoverflow.com/questions/14153509/how-to-prevent-sql-server-localdb-auto-shutdown – Shenron Oct 21 '16 at 05:48
  • 4
    @RemusRusanu. Can you show some data from Microsoft on why LocalDB cannot be used in production? It would be helpfull – Murugan Mar 07 '17 at 11:40

3 Answers3

48

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.

PtrJsn
  • 19
  • 5
Krzysztof Kozielczyk
  • 5,887
  • 37
  • 28
  • 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. – Kumar Vikramjeet Aug 25 '15 at 18:12
  • LocalDB is absolutely not supported in production. – Hossein Shahdoost Oct 27 '15 at 08:25
  • What makes you say so @HosseinShahdoost? Did Microsoft reverse its production support? A link might be useful if so. – Krzysztof Kozielczyk Oct 27 '15 at 17:06
  • 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", https://msdn.microsoft.com/en-us/library/hh510202.aspx Also this Msdn blog has a lot of useful stuff to read. http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx – Hossein Shahdoost Oct 28 '15 at 06:04
  • 5
    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. – Krzysztof Kozielczyk Oct 30 '15 at 12:09
  • 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. – Hossein Shahdoost Jan 25 '16 at 14:05
  • 3
    LocalDB is the .net alternative for SqlLite ( no permissions required), so i think the same statements is appropriate for LocalDB : http://stackoverflow.com/questions/913067/sqlite-as-a-production-database-for-a-low-traffic-site considering the use-case. You can use it in production, but consider it only for low traffic stuff – NicoJuicy May 10 '16 at 12:30
  • @KrzysztofKozielczyk i use localdb in desktop application. instance was shut down automatically. could you resolve this problem? – Amir Azizkhani Sep 13 '19 at 17:40
  • 2
    @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. – Richard II Oct 30 '19 at 18:52
18

"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). I have successfully tested this fix for both versions. Minimum versions are:

Richard II
  • 853
  • 9
  • 31
  • Could you briefly describe the obstacles you run into? – gpro Jul 16 '19 at 14:18
  • 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: https://stackoverflow.com/a/50491641/1633949. You also need to impersonate that account when you initially setup instance security. – Richard II Jul 16 '19 at 22:04
  • There were other obstacles as well, let me know if the ones above don't dissuade you, and I'll note the others too. – Richard II Jul 16 '19 at 22:05
7

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.

Hossein Shahdoost
  • 1,692
  • 18
  • 32