Using SQLServer Express edition instead of Compact edition as local database cache?

Question

By default, when you create a local database cache using the wizard provided by Visual Studio 2008, it will create a SQL Server Compact edition database for you, and provide you synchronization capability between the cache and remote SQL Server using Sync Framework (1.0). This allow us to provide the offline capability in our client server based application.

My question is, instead of using SQL Server Compact edition, is it possible to use SQL Server EXPRESS edition so that we can use stored procedure? All are clients are beefy laptop running on Core 2 Duo, so processing power is not an issue at all. To our user, offline is most valuable feature, though. Plus, it will take us some major rewrite to convert all store proc code to C# middle tier code using LINQ. Finally and most importantly, our boss wants the business logic to be in store procs. SQLServer CE just won't cut it.

Answer 1

Yeah, you can do that. You can have the installer install SQL Server Express onto their computers.

Only thing I'd warn though is that SQL Server can take up some extra RAM (200 - 500 megs) and it's not always going to be obvious to your users why their computer is consuming so much RAM all the time. If they all have plenty of RAM this isn't an issue but anyone with computers with less than 2 gigs might notice this.

You would be able to write stored procedures as well into SQL Server Express. I've used SQL Server Express for some products and it's perfect for most databases that I've needed to build since they never reach the limitations.

Answer 2

is it possible to use SQL Server EXPRESS edition so that we can use stored procedure?

Yes.

You can simplify installation and deployment by using Click Once.

If needed, you can limit the amount of memory used by SQL Express through issuing appropriate configuration commands.

Since your clients use laptops, you might consider providing an option to stop and start SQL Express from within your app, rather than having it auto-start as a service.

P.S. If you're using SPs, you may not need LINQ. ADO.NET can result in much higher-performance code, particularly if you can leverage features like command batching, async queries, multiple result sets, etc, that aren't possible with LINQ.