SQL Server Cluster provides both automatic and manual failover
capability for SQL Server services to another node in case the active node is
down. The active node can be down due to an operating system or a hardware
failure in which case the automatic failover to an available node on the same
cluster can happen and the users will start using the application through the
failover node. SQL Server services can be manually moved to a different node
at times when a planned maintenance like operating system upgrade or patch
maintenance is required on the active node.
When you decide you want to cluster SQL Server, you have a choice of configuring what is called Active/Active or an Active/Passive cluster:
*-An Active/Active SQL Server cluster means that SQL Server is running on both nodes of a two-way cluster. Each copy of SQL Server acts independently, and users see two different SQL Servers. If one of the SQL Servers in the cluster should fail, then the failed instance of SQL Server will failover to the remaining server. This means that then both instances of SQL Server will be running on one physical server, instead of two.
As you can imagine, if two instances have to run on one physical server, performance can be affected, especially if the server’s have not been sized appropriately.
*-An Active/Passive SQL Server cluster refers to a SQL Server cluster where only one instance of SQL Server is running on one of the physical servers in the cluster, and the other physical server does nothing, other that waiting to takeover should the primary node should fail.
From a performance perspective, this is the better solution. On the other hand, this option makes less productive use of your physical hardware, which means this solution is more expensive.
Personally, I prefer an Active/Passive configuration as it is easier to set up and administer, and overall it will provide better performance. Assuming you have the budget, this is what I recommend.
*-Two- or Four-Node Clustering?
SQL Server can be clustered using two nodes (using Windows Advanced Server), or it can be clustered using more than two nodes (using Windows Datacenter). Since I don’t personally have any experience is three or four node clustering, I won’t be discussing it here. But for the most part, what I say about two-node clustering also applies to three- or four-node clustering.
*-What is Log Shipping
Essentially, log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. But this is not all. The key feature of log shipping is that is will automatically backup transaction logs throughout the day (for whatever interval you specify) and automatically restore them on the standby server. This in effect keeps the two SQL Servers in “synch”. Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, its not really that easy, but it comes close if you put enough effort into your log shipping setup.