Best Database - Mysql / MS Access / MS SQL

[anurag]

Hello

I tried to search on net but did'nt get the answer.

Which database is good for medium size Database.

1. MYSQL
2. MSSQL ( Microsoft) 2000/2003
3. MS Access - i think it will hang if more Records

Pl. suggest which database to use and advantage and disadvantages.

Thanks
Anurag

 

[neroux]

It depends primarily on your platform. If you are using Unix you will have to go with MySQL as MS SQL and Access are Windows only (alternatively you could also use PostgreSQL or Firebird or any other database which runs on Unix).

If you run Windows you have the full range of choice. However for a recommendation one has to know the overall purpose of the DB as well as the expected size of data.

 

[Matthew.]

*nix wise, i would use MySQL. True it is a 'dummed down' version of true databases such as Access (i.e. no referential integrity etc) but it does what most people want.

I wouldn't use anything other than MySQL or PostgreSQL, but MySQL over the latter.

 

[enlytend]

Access doesn't scale well for multiple, concurrent users ... I wouldn't advise it for a web application.

I'm partial to MySQL and Postgres myself, but then I'm not a big Micro$oft fan in general.

As neroux ponted out, if you're on *nix MSSQL and Access aren't among your options ...

 

[lee101]

I would say MySQL, assuming it is web based, although I have never used MsSQL before and have only used an Access database with the Access software, admittedly it has a nice UI for managing, but it seems a bit bloated is alls you want to do is store data. And I would say MySQL is easier to work with, i.e. create a PHP backend to access it.

Lee

 

[neroux]

An additional comment as I saw some rather specific suggestions. I think its difficult to give a good advice without knowing the exact background details and requirements. It could range from Access up until to a clustered Oracle setup.

 

[anurag]

helllo All,

Thanks for your kind help.

I am using Windows platform and have choice of using ASP/ PHP/.net for frontend(application).

I searched on net and as suggested by enlytend, if we try to create more than 5 connections using MS-Access, system will hang.

I think Oracle and Mainframe ( IMS / DB2) are more for large databases and maintenance and licence is very expensive.

So as suggsted options are only between MYSQL ans SQL Server. My issue is how many maximum users can use MYSQL or SQLSERVER at a time.

For example which have around 1000 or 2000 users accessing the database, is it ok to use MYSQL or SQLSERVER. Or system will not work. ( I think i say medium size database) and how many maximum records in one table can store without major problem ( I know MS-Access will not open if try to store more than 5000 records in one table).

Hope I am able to give more or less requirement.

Again thanks for help.

Anurag

 

[neroux]

Are these 1000 to 2000 simultaneous users? In this case it wouldnt be medium sized anymore and you might even want to look into a clustered solution. Otherwise the size of the data will be more important.

What do you want to store? What is the expected size? Can you give a rough outline of your requirements?

 

[anurag]

neroux, Thanks for your kind supprot.

yes. 1000 to 2000 simultaneous users.
what you mean by clustered solution.

basically it is blog site and 1000 to 2000 users will be online at a time. I am not sure about the size of database. I would say may be aruound 1GB. But aruond 50000 or more records in one table.

so 2000 simultaneous users will access, modify or update the tables.

So initially less user but when when user size will grow , I don't want to be in trouble if users get error because of Database issue.

I know moving to new database (mysql --> sql server --> oracle) and again testing is a big pain

I think now yougot some rough idea, still need more input.

Are these 1000 to 2000 simultaneous users? In this case it wouldnt be medium sized anymore and you might even want to look into a clustered solution. Otherwise the size of the data will be more important.

What do you want to store? What is the expected size? Can you give a rough outline of your requirements?

 

[neroux]

With 1000 to 2000 simultaneous users Access is definitely out as it wouldnt be able to handle such an amount of transactions (after all its a desktop database and if used for a web application it should be only used for small setups). With such a high user load you might really want to look into a clustered solution (see here for more information about clusters), not only for load balancing but also for availability.

For such an installation (and with proper hardware) MS SQL and Oracle would definitely not disappoint you but I'd say MySQL would be able to handle it as well (especially in a cluster environment). It will probably basically come down to your particular experience with a database system and how much you can spend.

 

[anurag]

neroux, I agree with you. do you have any idea around how many maximum users at a time MS SQL and MYSQL can handle.

My idea is to use clustered enviornment for application too. Any idea about any good clustered software. I think if initial requirement and analyis is good , my life will be easier.

One more point : does it make any difference that I use ASP or PHP. I want to ask if I use php instead of asp so performance will be good. Or you suggest any other language ( like JAVA or ????)

Basically I am open for all the options

 

[plong0]

on the topic of ASP vs PHP vs JSP (Java)...
ASP is MS technology, runs on MS servers
PHP is Open Source technology, installed as a module to any server
JSP is free (don't think it's fully open sourced yet, Sun just open sourced Java recently) and runs on the Apache Tomcat server (or other compatible ones)... it can run standalone or can be integrated to Apache and other servers

The traditional Open Source server is a LAMP setup ... Linux, Apache, MySQL, PHP ... so us Open Source guys (or M$ haters in general) like that one

JSP isn't as popular, but is a really nice setup for Java developers... You basically have all the power of a Java application running on the server, but web-enabled. Probably isn't as well suited for heavy traffic sites since Java isn't exactly optimized yet, being OO and interpreted (not compiled) and all...

Python might be another one to look at... I know Google uses it for a lot of their stuff. I haven't worked with it yet, but it's next on my list of languages to learn. and it's gaining popularity on the desktop app side insanely fast, so there's another bonus for taking that route.

 

[anurag]

plong0 , thansk for help.

I have experience in ASP and PHP but not much in JSP or Python .

I think i will use ASP or PHP. but not sure which one is good. As i have windows Setup so free to use either of them.

Advantage or disadvantage of ASP or PHP.

 

[neroux]

neroux, I agree with you. do you have any idea around how many maximum users at a time MS SQL and MYSQL can handle.

I cant give you absolute numbers on this as this will heavily depend on the available hardware and the configuration. One question I forgot to ask so far is whether these 1000 to 2000 users will be peak number or the typical one. In the former case you could set out even for a smaller system and let your users maybe experience a little lag in the high time, in the latter your system definitely should be able to handle it.

My idea is to use clustered enviornment for application too. Any idea about any good clustered software. I think if initial requirement and analyis is good , my life will be easier.

There isnt any particular software, but your overall setup (your application, external parts, database, ....) application would need to take into account the clustered environment. You will find more information about SQL Server at http://www.microsoft.com/sql and for the MySQL Cluster at http://www.mysql.com/products/database/cluster/.

One more point : does it make any difference that I use ASP or PHP. I want to ask if I use php instead of asp so performance will be good. Or you suggest any other language ( like JAVA or ????)

Basically I am open for all the options

The language doesnt really matter, choose the one you have most experience in.

 

[anurag]

Thanks neroux for your kind reply.

Basically I am developing new application and expecting 1000 users (the typical one). I have plan for Dedicated servers ( Dual core with 1-2 GB RAM and other Required hardware).

One option is to keep 2 servers ( one for Database and other for application) , all this I am taking about posibilities.

But first thing is development and main issue is Database. As suggested by you, any prgramming language will do.

I am looking for long term so atleast system will be in good shape for atleast next 2 yrs.

 

[vincenzo]

Interesting project, I'm considering something similar.

Can you clarify you projections in regards to users please, as this point is reallyt important IMHO.?

The expectation "2000 simultaneous users will access, modify or update the tables."

Is this your estimate of:
1a. 'Total registered users' from site launch/very soon after launch .
or
1b. 'Total registered users' within 2 years.

If '1b', then you won't have much to worry about in regards to clustering.

Let's not forget that registered users rarely update their blogs more than once per day, and even then, not all users will do so at the same time.
Even if you take an estimate based on all updates within a peak day time in same time zone let's say a 1 hour 'lunch time':

2,000 / 1 hour = 33 per minute

Any decent server setup will handle that.

If your expectations are '1a' then same thing appliess, except you need to provide an estimate of expected registered users over next 2 years.

The next point to consider is probably more important.

How many visitors are you expecting to view these blogs?
To put the server requirements into context, a very popular single user blog can bring a decent server to it's knees.

Interesting topic, and may be helpful if anyone running a popular blog site starts to contribute their experiences.

Best wishes,

- Vince

 

[anurag]

Vince

Thanks for sharing information.

I think proper planning to needed. I have plan to launch the blog in next 4-5 months ( once coding and testing is done) and assuming at one time around 1000 users will access the same topic.

It is possible that even if user access the site ( not create or update operation in one particular table) but we have valid reason that server may be down if 1000 users try to access same table at same time. We need to use field locking, indexing and view to optimize the database.

hope you understand my point.

I think it will worse if 100 users are creating or updating data in the above table.

Reading your point 1a or 1b, I say it will be mix of both, if i am lucky , i will think 1000 users will be in next 1 yr.

Once site is up, i don't want to say that " site is down for Maintencnce", if database is not responding becasue of excess load ( i hate sites which dispaly this message)

 

[plong0]

plong0 , thansk for help.

I have experience in ASP and PHP but not much in JSP or Python .

I think i will use ASP or PHP. but not sure which one is good. As i have windows Setup so free to use either of them.

Advantage or disadvantage of ASP or PHP.

I would recommend PHP then. But I have a strong bias against M$, and am all for Open Source.

Another advantage is that you will likely find a lot more community support for PHP than ASP. So if you need tutorials or need help with coding, you're better off with PHP. As an example, look at the user base here and on other freelancing sites... most people are PHP coders.

 

[neroux]

I would recommend PHP then. But I have a strong bias against M$, and am all for Open Source.

Another advantage is that you will likely find a lot more community support for PHP than ASP. So if you need tutorials or need help with coding, you're better off with PHP. As an example, look at the user base here and on other freelancing sites... most people are PHP coders.

In context with plain ASP I would give the same suggestion. With ASP.NET definitely not. Considering the vast amount of tools and libraries for .NET I would even assume the opposite. Also you will find many PHP developers but many of them will be also amateurs. The percentage of them among .NET developers is much smaller.

 

[anurag]

plong0 and neroux thanks for your help.

I too think the same. ASP and MYSQL is not a good option so i think i will go for PHP and MYSQL
Thanks for your help.

Only issus is that mysql will be able to handle head load or not.

 

[vincenzo]

By the way, have you considered using ready-made applications for multiple blog hosting?

WPMU (WordPress MultiUser)
Bloghoster.net
BlogSystem (by netartmedia.net)

All use PHP and MySQL and although last 2 oabove are commercial scripts, they do give you full source code.

Best wishes,

- Vince

 

[neroux]

I too think the same. ASP and MYSQL is not a good option so i think i will go for PHP and MYSQL
Thanks for your help.

Only issus is that mysql will be able to handle head load or not.

Do you mean ASP and MS SQL or actually MySQL?

In any case I wouldnt second it however. The only issue I'd see with ASP is that it is not anylonger officially supported by Microsoft. ASP.NET is now the way to go.

 

[anurag]

yes Vince. I have seen . Personally I like wordpress. But still i think WordPress MultiUser is not good if compare with WordPress Single user.

Other 2 products are good but still i am evaluating the good and bad about this. As i may need to modify the product in future so need the modify rights for source code.

By the way, have you considered using ready-made applications for multiple blog hosting?

WPMU (WordPress MultiUser)
Bloghoster.net
BlogSystem (by netartmedia.net)

All use PHP and MySQL and although last 2 oabove are commercial scripts, they do give you full source code.

Best wishes,

- Vince

I mean MySQL

I know ASP is no more supproted by microsoft but persnoally I don't like ASP.NET . I a mthinking to go for PHP.

I am confused in PHP or ASP.

Do you mean ASP and MS SQL or actually MySQL?

In any case I wouldnt second it however. The only issue I'd see with ASP is that it is not anylonger officially supported by Microsoft. ASP.NET is now the way to go.

 

[neroux]

I mean MySQL

ASP will work with MySQL just as well as PHP. There is no difference.

I know ASP is no more supproted by microsoft but persnoally I don't like ASP.NET . I a mthinking to go for PHP.
Why dont you like ASP.NET? But if you are more familiar with PHP then you should go with it.

I am confused in PHP or ASP.

Confused how?

 

[anurag]

I think i will go for PHP. No more confussion as PHP is better than ASp in Performance, security and support.

I know ASP is no more supproted by microsoft but persnoally I don't like ASP.NET . I am thinking to go for PHP.
Why dont you like ASP.NET? But if you are more familiar with PHP then you should go with it.


Confused how?