Best database to use for lots of connections

[markhkram]

I have an application that uses a file system (text files), and want to start using a database to retrieve information. I have an increasing number of clients who will be accessing this information constantly (will be about 60,000+ computers eventually). What would be the best database to use (preferrably free). Each client will "Check-In" every 5 minutes or so to see if there are any changes.

I've considered the following:
-MSDE
-MySQL
-SQL Server

I know that sql server would be the best out of the three, but if I can get the other 2 to work, that would be ideal. Do MSDE and MySQL allow for database replication, and would they handle this type of load?

Thanks

 

[gmmastros]

I would recommend SQL Express for now. MSDE is the free version of SQL 2000. There are 2 versions of SQL Express. Bith are free, but one is for SQL2005 and the other for SQL2008.

If it turns out that SQL Express cannot handle the load, then you could always upgrade to full SQL Server. SQL Express will operate just as quickly as the paid versions of SQL Server. There are quite a few limitations with the free version, like replication and mirroring. But, while your load is still small-ish, you may be able to use the free version. Then, when your load increases, upgrade to a full version of SQL that supports replication. To upgrade, you simply install the full version, detach the database from the express instance, and attach it to the full version. It really is easy to upgrade.

There are MANY things you can do to increase your throughput. To begin with, you MUST make sure that each query is as fast as possible. This usually means, writing the query so that it effectively uses indexes on the tables. This usually has more effect on the performance than hardware. 2nd would be to use better hardware. There's RAID drives, and SAN's. Hardware can easily get pricey, but in my experience, if you have a well tuned, properly normalized database, you can support MANY users without having to resort to super-pricey hardware.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

On one hand, SQL Server is tightly integrated with .Net as they are both Microsoft development products. I'm unsure of any connection limitations with SQL Express. However, I would not discount MySQL as it has a proven reputation for thousands of connections as it powers many high-volume websites. It's also free, which is great. It has replication, although I am not sure if that is only with the paid versions. You should probably ask those types questions in the MySQL forum if you want to look at MySQL.

 

[markhkram]

Thanks!

I was thinking about MySQL since I have used it a lot more than SQL and MSDE. I'll probably just stick with it... I appreciate your input.

 

[Bluejay07]

My company has experienced access limitations when using SQL Express.

It seems like the express version does limit the number of concurrent users accessing the database. I don't know exactly what the limitation is, however; we have noticed problems after several handful of people accessed the database at the same time.

We switched the company from SQL 2005 express to SQL 2005 and there were no longer any connection issues.