1

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

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
1
  • Thanks. This confirms what I've hoped for. The customer is right. :-) Am just hoping that they have the right license to support at least all their users.(But that's their problem, really.)
    – Wim ten Brink
    Jan 5, 2010 at 16:26
2

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.

Improve this answer
1
  • Right now, the system works in a kind of document model, where the user "opens" a document, which is a collection of related data. A record is written to mark the specific document as open. (With time limit.) When the user "saves" the document again, the existing data is replaced by the new data and it's marked as closed again. Since every user will mostly manage their own documents, it's unlikely that two users will access the same data.
    – Wim ten Brink
    Jan 5, 2010 at 20:59
1

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.

Improve this answer
1
  • I'm not paying for any SQL Server. Am using the Developer edition that's part of Delphi. The customer is a large, financial organisation who can easily cough up for a very powerful SQL Server setup. Plus a small collection of experienced DBA's.
    – Wim ten Brink
    Jan 5, 2010 at 21:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .