I am looking to setup SQL 2008 R2 clustering on Windows Server 2008 R2. Can someone give me some options available for installing SQL Server clustering or best practices? I thought SQL had clustering built in, but after doing research, it looks like you first have to install Windows clustering and then install SQL on top of that.
It's pretty easy if you want a vanilla setup.
Microsoft has detailed instructions available here:
There are two basic types of clustering for SQL Server; single-instance and multi-instance.
Single-instance is just a plain old failover cluster. Only one of the SQL cluster nodes is active and the other one just waits for a failure. Once a failure occurs the passive node will assume running the service from the active node to keep the databases up and running with minimal downtime.
The second basic type is a multi-instance cluster. In this setup both nodes are actively running SQL Server services. When one of the servers fails over in this setup the other node should take on the services that failed. This can be dangerous if you don't take into account how well the server will run once it's responsible for running all of the services on the cluster.
Both of these cluster types can be expanded beyond a simple two-node setup but that's the basics of it. There is also a difference in licensing between the two. In a single-instance cluster you only need one SQL Server license as the second node is not actively running. Once the second node takes over you have a 30 day time limit that it can run SQL Server before it must be licensed as well. In a multi-instance cluster both nodes must be fully licensed. The licensing terms may have changed for R2 so I would double-check this.
Note: SQL Server clustering does not support load balancing. The closest thing you can get to load balancing is a multi-instance cluster but that is only separating what node takes care of what resources. It does not actively load balance your resources.