1
I have knowledge of Oracle and SQL server databases. I have no prior experience with MySQL.
We are developing a web portal to be used by customers all over USA. We want the data to be partitioned by state, year (for archiving) at the minimum. Database schema may be fairly simple with less than 2 dozen tables, but we expect a lot of records per user every year. We want the user to write records to appropriate partition after logging in to the system. We have to aggregate transaction data for each year and keep summary for 5 to 7 years for regulatory reasons.
We are looking at solutions for implementing this. Many web development outsourcing companies have contacted us to develop this using PHP/MySQL. We got impressed in MySQL as facebook and google use tens of (thousands of) MySQL databases.
But, some US developers have promised to develop the same in MS SQL/ASP.net as MySQL is not robust and scalable in their opinion.
I just read that MySQL has 2GB/4GB file system limit depending on operating system. I am used to very large databases in Oracle/MS SQL. And most webhosting companies say that we can have unlimited 1 GB MySQL databases in their hosting plans.
1) If MySQL databases have a size limitation, how do we switch between databases, when a database runs out of space?
2) What will happen when a user tries to add data to a full table or database?
3) How easy will it be to handle backups, do replication for disaster recover, and manage a large number of databases?
For us scalability and reliability of data as well as hosting expenses are deciding factors. MS SQL database hosting is atleast 10 times more costly than MySQL.
Please advise if it is easy to design a solution with MySQL. If so, is there a reference article or book on how to handle data overflow in tables/databases, switching user connections between databases/tables depending on partition etc.
I request some one who has worked on similar scenarios to give advice from their implementations.
