SQL Server Clustering - what is the difference between Active/Passive and Active/Active Clustering, which is good for better performance?

First part of question is a pure newbie FAQ to know about, and the simple answer would be it is one of the Clustering type you can adopt within your SQL Server environment.

To cover on newbie aspect both Active/Passive & Active/Active configuration is available within SQL Server, both operating system & SQL engine work together to provide availability in case of an application failure, hardware failure, or OS error. This is purely for hardware redundancy through a configuration in which vital, shared resources are automatically transferred from a failing computer to an equally configured server. Active/Active clustering simply means having two separate instances running in the cluster—one (or more) per machine. It refers to a two-node cluster, with each node running SQL Server instances but each with a different set of databases. This setup is conceptually the same as having two Active/Passive nodes, except that one node must be a named instance.

The goal of clustering is to provide increased availability to clients by having a hot standby system with an automatic failover mechanism. SQL Server clustering is not a load-balancing or scale-out technology. To cover the second part of the question about performance then it is the application code causing the issue or any of the hardware on the server. So what best you can do to provide such an availability along with performance from Hardware in specific. As you may be aware clustering services use a private communication channel on each node to keep in sync with each other, to allow communication and act appropriately even if the public network is offline. In this case having mutliple instances is a common too and in this if one of the host is used for more than one virtual server, performance for clients is typically degraded. The main criteria for mutliple instance is to high availability but not to go with high performance. Though multiple instances can run on a single node, they would be sharing CPU and RAM-and that could spell poor performance. As and when it is required ensure to upgrade the hardware too as the new hardware generally performs much better than old, with disk capacity and speed increasing at an ever-growing rate.

A common assumption by the users that Clustering is for load balancing too, no it is not and you should have such a load balance facility using your application's physical design, here is how you can achieve. As per the usage of the application your database will also grow and the corresponding tables too, so in this case you need to think about fragmentation such as performing table scans with a simple query methods. So in this case you can go for TABLE PARTITIONING or PARTITIONED VIEWS, within SQL Server 2005 this has been lot better as compared to previous verisons. Also having the member tables are on their own filegroups, you may get better disk performance if the files in those filegroups are on separate physical drives. Within SQL Server 2005 it can be achievable as long as all of the data is in the same database, you can use table partitioning, which is far easier to implement.

The following articles will take you through the performance tuning resolution in terms of your query design & methods: 

http://technet.microsoft.com/en-us/magazine/fd434c60-c8d4-45fe-978d-bea34bc166e0

http://technet.microsoft.com/en-us/magazine/8f805ae0-bb7f-4338-8586-0450ab39bfd7

http://technet.microsoft.com/en-us/magazine/9d32cebe-93a9-439a-aeae-6965ab3380de

http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx