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.)
Best Answer
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.