SQL Clustering
SQL Server 2000
As far as performance tuning, where can I find statistics or could you offer your opinion in the following scenario.
Which offers better performance, running One server in Active mode and another in inactive mode, each with four processors
or
Running both servers in active mode, each with two processors.
Please offer an opinion if you can and a place to look for metrics.
Thank you.
Software/Hardware used:
Software/Hardware used:
ASKED:
June 6, 2005 3:33 PM
UPDATED:
June 8, 2005 3:25 AM
Answer Wiki:
Yuu have to remember a couple of things. First you have to have enough memory in both boxes to run all the instances of SQL on one of the servers in the cluster since SQL does not unallocate memory. Second is to have enough processing power. With those two things in mind I am currently looking at 2 4-way servers with about 12GB of memory. So you are talking more expensive hardware. I a goingto be doing active-active because of the cost of having one server jsut sitting there. You can install different instances of SQL on different servers and then move them as you need to.
To see all answers submitted to the Answer Wiki: View Answer History.
Yuu have to remember a couple of things. First you have to have enough memory in both boxes to run all the instances of SQL on one of the servers in the cluster since SQL does not unallocate memory. Second is to have enough processing power. With those two things in mind I am currently looking at 2 4-way servers with about 12GB of memory. So you are talking more expensive hardware. I a goingto be doing active-active because of the cost of having one server jsut sitting there. You can install different instances of SQL on different servers and then move them as you need to.
Hi,
The results depend on the database used –
In case of Oracle – it has a shared everything architecture. This allows you to split the load across all the CPUs (with servers in active-active mode) so you get the advantage of running queries in parallel and the gains can be considerable. Further, from the developer’s viewpoint separate code for handling failover does not need to be written in the front-end, its handled at the backend.
In most other databases, each system has to handle the full load by itself.
Very roughly, 2×2-CPU systems running Oracle can give you throughput roughly equal to 2×4-CPU active-passive systems. The implications also carry onto RAM requirements.
But this advantage is offset by sensible splitting of your database. If you can split your application to use 2 independent instances, then you can run both the instances back-to-back in an active-passive mode, i.e., Instance A on server A uses Server B as standby and vice versa. Now in case of server Afailure, the full ‘A’ instance fails over to the other server (‘B’) which will now be running 2 instances (A & B) You will stand to loose open transactions at the time of failure, but that can be handled in the application
Conclusion, the approaches to using active-active and active-passive are different. Each approach has its own implications on the full stack (h/w, s/w, appn dev). Both can deliver a certain performance level and as such any evaluation must be made on bottomline costs over a 3 year period to make sense.
I hope that helps
Bye