Multi-user access through a data-access layer/remoting server

Hi guys,

I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.

I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:

- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.

Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines

Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE

- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server

Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE

Seems quite doable to me, but I may be wrong..please let me know either ways

Thanks,

CP

Hi Jeff,

Thanks for the response.

I'm not sure if the link you suggested is what I'm trying to do since I'm not using ASP.Net at all.

SSCE is a single-user database.

So I'm trying to find out if its feasible to have the Remoting Server (RS) as this Single-User. This RS would contain all the data-access remoting objects which would use ADO.NET to talk to SQL CE DB. The Client users would then connect to this RS through tcp or http and the RS would handle the connections to the SQL CE and pass the data back to the Clients. (And better yet, a layer of .NET Serviced Components between the RS and DB could provide all the services such as JIT, Object Pooling etc.) As far as the SQL CE is concerned, all the calls are coming from the same app (RS) from the same machine.

If you are going to the trouble of implementing what you suggest, then why not use SQL Server Express and have full upgradeability to SQL Server

I've already got an hosted architecture which uses SQL Standard and the other layers I'm talking about..however all the CRUD operations are coded in Stored Procs and Views. Now, I want to scale down this model to a web-downloadable app with zero support assitance for server set-up etc. for a max of 10-20 Concurrent users. I was thinking about SQL Express, but the biggest red flag in that are A) the inability to hide the database design (completeley unacceptable because of the security concerns and proprietary nature of database design) and B) size of the download plus the complex installation and maintenance.

So my ONLY option to get close to the flexiblity of an Access DB is SQL CE. But as you mentioned, there are huge costs to pay such as getting rid of the stored procs, views, and limiting the data types, no check constraints However, the pros seem to outweigh the cons for now. Please let me know if you have some other links, resources or suggestions.

Regards,

CP