Technically, this is not a problem I need to solve. I'm developing a project where an application connects directly to an MS Access database. The customer suggested to migrate the database to their SQL Server instance. The application would then use a different connection string to connect straight to the SQL Server database instead of MS-Access. A simple test-run proved that this would work, with two users connected to the same database.
I don't know which version of SQL Server the customer is really using but I assume they know what they're doing. Right now, I'm adding some additional code changes and I can test the application on SQL Server Express 2005 with up to 5 users. But the customer will have over 500 users who will all use the same application at the same time, thus there will be 500 direct connections to the database.
I can't convince the customer to pay some extra for a better client/server model of this project. It would also require some additional development time. And I'm unfamiliar with problems that could arise with this many users on a single SQL Server database. So I feel as if I'm the captain of the Titanic and I've just spotted an iceberg...
So, will it sink? What are the risks of this setup which this customer is overlooking? Or is there no risk worth mentioning? (The customer is experienced with SQL Server so it's mostly me who is having doubts.)
SQL Server 2005 will work just fine, as long as the user is using a version licensed for at least 500 users. You would probably want support for more than 500 because of dropped connections and the like, but I would recommend SQL server over MS Access any day.
I just wanted to add that SQL server is made to have thousands of concurrent connections, so I wouldn't worry if I were you.
Joshua is correct that that SQL Server will handle large numbers of connections without a problem. A more important question is whether your database schema and application code will be able to handle it.
More users trying to access and modify the same data at the same time will likely lead to a lot more request blocking from all the table/row locking in the database. The performance of the app may slow to a crawl with that sort of usage. Without seeing your database schema, it's hard to tell. Although assuming you just did a simple conversion from Access to MS-SQL, it's likely you're not taking advantage of things a real RDBMS is good at like constraints, foreign keys, and indexing.
Another thing you should watch out for are issues that arise because of the multiple concurrent users. Designing an app for a single user lets you take shortcuts with things like database transactions that need to be atomic. You run into the same sort of issues in multi-threaded programming. If you've got a series of database manipulations that need to all occur together, what happens when another user starts the same process while the first user isn't done yet? Will the processing of the second affect the first? If so, you'll need to account for that.
Ultimately, time will tell. But you may be in for more work than you anticipated.
as a very experienced SQL (and Access) user, i agree. the old MSDE2000 had a connection limit I think but the newer SQL Express isn't governed at all. The Express version will be able to handle the number of connections but the Express edition is limited to only one CPU, which constrains things a bit.
If you end up needing more power, youll have to get a multi-CPU edition of SQL , which you have to pay for.