Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed

I haven't had much chance to use SQL Server Express Edition of late. So, when I hit an issue an on an environment using SQL Server Express Edition last night, I thought to use the opportunity to read around. I must say that Microsoft progressed appreciably the whole portable database story and it has come a long way since the days of MSDE.

First let me explain the  issue I was looking at, in case it might help some troubled soul. So, on an environment using SQL Server Express 2008, I started getting the following error

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

Not knowing what caused it, I tried to Google the error and got many posts suggested to delete the local database file copied to the folder "C:\Users\MyUserName\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS"

Did that and restarted but the error persisted. Then I stumbled upon the following post http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/60889070-5c3c-4823-ae9f-1ed6e32b2ce8/ indicating that the user instance feature of SQL Server 2008 has been deprecated. That was a good pointer. Changed my connection string from 

"Data Source=.\SQLExpress;Initial Catalog=mydbfilepath;Integrated Security=SSPI;MultipleActiveResultSets=true

To

"Data Source=.\SQLExpress;Initial Catalog=mydbname;Integrated Security=SSPI;MultipleActiveResultSets=true"

And that worked.

Coming back to the original topic, there are three flavors of portable databases available for SQL Server for small scale usage and for developers.

SOL Server Express

SQL Server express is the freely available and downloadable version of SQL Server that can be used for small scale applications. The change here is the User Instances have been deprecated from SQL Server Express 2008 onward.

SQL Server Compact

SQL Server compact is a very lightweight version of SQL Server that can connect to a database file. It is In-process DLL, which means it is an ideal candidate to meet all your IMDB (In Memory  Database) needs.

SQL Server Local DB

SQL Server local DB is designed specifically for developers and differs from SQL Server in that it min version of SQL Express with less pre-requisites.

This post compares the three flavors rather well.