Using SQL Server Express as a real server

[ModusPonens]

Can I set up SQL Server Express on a Windows Server and have multiple users access it just like the full SQL Server? If so, what are the limitations?

[bdi]

No, you can only access SQL Server Express from the local machine. However, you could create a small server app that acts as your data access layer. You could then have your clients connect to it, and it could pass on requests to the server. Depending how many users you are planning on having, this might or might not be a good setup.

[xardoz]

Interesting...tell us more, how about a complete app

[bdi]

Not real well schooled on creating the complete app. I'd go something like this though as far as design:

Listener - This class is what holds your port open and listens for requests. I would guess you would want some sort of simple authentication to know that it is your app talking to it, and not a hacker (yes, even on a local network, although you could do simpler authentication if you know the environment is safe).

ConnectionManager - This is a detail about building a server I'm not sure about. I would imagine you need something to manage the connected clients and track them.

InputQueue - This would just be a FIFO queue for queueing up the messages from the listener. Not a lot going on here.

CommandInterpreter - This would get the messages from the input Queue and Interpret them into the sql commands.

Parser - This would have different functions for processing the actual SQL commands and running them against the server. I would make it take stored procedures rather than just running straight SQL. That way, even if someone hacks in, they can only run the stored procedures you have setup.

OutputQueue - The Parser could dump the results into the output Queue. From there the connectionmanager could watch for the data it's waiting on and send it back.

With .NET you can fairly easily turn your dataset into XML and stream it back to your clients.

Of course, all that said, it might be easier to just build an ASP.NET application. I was messing around with it and took the majority of the backend to my app and was accessing it. Just had to change the forms, and a few other minor issues that were coming up.

btw... that was just brainstorming and trying to think through the process, so if anyone wants to add to it or list a better way for something or even some catchphrases, feel free.

[ModusPonens]

It's a shame I can't use it as a server, but it makes sense (commercially) that it wouldn't be allowed.

Good idea about have an agent in between the sql server and the program.