confusion about SQL Server Express and localdb

sql-server sql-server-express embedded-database localdb
Costa picture Costa · Jul 9, 2013 · Viewed 25.9k times · Source

I need to deploy a WCF service with a database on client machines. I am confused about SQL Server Express. I need to verify all of the following.

When attaching database files in the App_Data folder, do I still need to install SQL Server Express engine (Windows service) on client machines?

There is a flavor called SQL Server Express LocalDb. That one does not need an engine (Windows service)? But I think it need a prerequisite installation of LocalDb.

Localdb is introduced with SQL Server 2012. There is NO localdb for SQL Server 2008. Correct? I could not find 2008 version on MS site.

Answer

Ryan Weir picture Ryan Weir · Jul 9, 2013

Do I still need to install SQL Server Express engine (A win Service) on client machines?

Yes, they need it installed for your application to query the database. Can't really get around it.

There is a flavor called SQL Express localDb. That one does not need an engine (win service)? but I think it need a perquisite installation of localdb.

Yes, you'd still need to install LocalDB on the client machine. It's pretty hassle-free to do, but I think it's overkill unless you really need the full features of a SQL Server instance for your webservice.

Instead of LocalDB I'd strongly recommend using SQL Server Compact Edition - it performs the role you're thinking of (your WCF Service can simply connect directly to the database file on your client machines without installing SQL Server, and is lightweight), but doesn't need to be installed (it's simply included as a DLL with your WCF application):

Unlike other editions of Microsoft SQL Server, SQL CE runs in-process with the application which is hosting it.

In Microsoft's own words on the differences between LocalDB and SQLCE:

LocalDB and SQL Server Compact?

Small and simple database, lightweight installation, connecting to a database file -- this will sound familiar to any developer using SQL Server Compact. The similarities are not accidental, as our goal for LocalDB was to be as easy to use as SQL Server Compact (while being as powerful and compatible with full SQL Server as SQL Express).

There are significant differences between LocalDB and SQL Server Compact:

Execution mode: SQL Server Compact is an in-proc DLL, while LocalDB runs as a separate process.

SQL Server CE is kind of like an updated version of MS Access, the file goes with the application which can "just connect" to the database file without involving any database server installation, and its syntax/features are very close to SQL Server standard.

Related questions

How can I determine installed SQL Server instances and their versions?

I'm trying to determine what instances of sql server/sql express I have installed (either manually or programmatically) but all of the examples are telling me to run a SQL query to determine this which assumes I'm already connected to …

Read More
SQL Server after update trigger

I have a problem with this trigger. I would like it to update the requested information only to the row in question (the one I just updated) and not the entire table. CREATE TRIGGER [dbo].[after_update] ON [dbo].[MYTABLE] …

Read More
SQL Server Express CREATE DATABASE permission denied in database 'master'

After I change the option as UserInstance="False", then the error starts to happen. Because I want to use full-text search, the option change is required. BUT, it stopped to work. Is there any way to make it work again? …

Read More