I have web application for which I need to come up with a scalability plan. The application right now is running on 2 computers, the front end is running Tomcat with JSP (Java Server Pages). The back end is MS SQL 2000
The database is running fine now, but the file size and CPU utilization is growing and I need to figure out a way to distribute this database over several machines.
I looked at MSCE and setting up a cluster, but I think that the solution will NOT work in an intensive enterprise environment. (Am I wrong?). For one thing, there is a hard limit of 16 nodes in a cluster. Also, it requires a shared RAID or FibreChannel Array. These solutions are very expensive. Also, it's not easy to add additional capacity if you reach the maximum of the RAID array. For example, if you have a FibreChannel Array with 8 Terrabytes of capacity, if your DB uses up all 8 terrabytes, you have to buy a whole new enclosure.
One of the major objectives I have is to minimize cost and support unrestricted database growth.
Here is what I would like to achieve. If any of you know of a solution or something that YOU have USED which might give the functionality I require .. I would greatly appreciate your feedback.
1) Run single SQL Instance on regular PCs, which host 1 or More Tables (Table level partitioning) of a very large database.
2) Distributed JOINs on Tables residing on different physical PCs
3) Multiple Machines hosting same table (Fault Tolerance)
4) Single Virtual IP for database to Application Front End.
I looked at C-JDBC which is an open source middleware that can do some of the things above. I liked it, but it doesn't seem to support Distributed JOINs which means that our application - which requires quite a few tables JOINs - won't be able to achieve Table level partitioning.
Can anyone recommend any clustering middleware which you can recommend?
Have you had a go at tuning some of the SQL statments to make sure they are using the appropriate indexes? Or using the Index Advisor? I have found that a query that table scans can bog down a server immensely, if it is called often enough.
I have not used federated database servers, because I have not had the need for that degree of complexity. It looks nice in the TPC benchmarks, but requires a great deal of discipline among the database development staff. Linked servers can lead to a host of performance problems on their own: