Difference in Clustering architecture

A very common question that we get from our clients is the differentiation between the clustering architectures of Oracle vs SQL Server. Many shops are familiar with only one and when they implement the other RDBMS, the behavior is very different and to some it comes as a surprise

SQL Server supports shared nothing architecture i.e. at any given point of time, among the nodes of a cluster only one of the nodes will have ownership of the databases and hence access to the shared resources i.e. clustering in the case of SQL Server is not for load balancing – it is only for failover and high availability unlike Oracle-RAC which deploys a shared-everything architecture i.e. multiple nodes access the shared resources at the same time and hence provide load-balancing, failover and high availability.

Active-Active clustering in the case of SQL Server means that you have 2 sets of Active/Passive clusters i.e. say you have 2 machines(nodes) and the external storage is on the SAN, then you have this configuration:

Machine 1 Machine2

Active Node 1 Passive Node 1

Passive Node 2 Active Node 2

|———————————————-|

| STORAGE |

|———————————————-|

So, an OLTP application can be put on say Cluster1 and the node on machine one shares all the shared resources. Another application like say JD Edwards is also being run and this is on node 2 and the node on Machine2 owns the shared resources and is the active node while the Node on Machine 1 becomes passive node.

Having such a configuration ensures that no machine is sitting idle and that the hardware is getting utilized properly. Proper care needs to be taken though when sizing the hardware such that in the event of a failover of one of the nodes, the other machine can host both the nodes and thus processor and memory needs to be sized accordingly.

It does not provide horizontal scalability like RAC does (RAC – Real Application Clusters in Oracle has a very different architecture and we are writing a whitepaper on that which will soon be put on our site)- and the reason is that it is a shared nothing architecture. Horizontal scalability can be achieved through different means though like DPVs implementation & a proper failover scheme but requires changes to the application as well.

I hope this helps explain this difference a bit…

Failover Clustering in SQL 2005

Installing SQL Server 2005 Service Pack 1 in a Clustered Environment – Things to Look Out For

Recently applied Service Pack 1 to a clustered 64-bit SQL Server 2005 Enterprise environment, and discovered a couple of issues for which there is no official documentation readily available. Both of these issues are easy to address, but if one is unaware of their existence it can lead to a rather problematic and time-consuming upgrade.

Issue 1: SQL Browser Service

Make sure to manually stop the SQL Browser service. The SP1 installation does not stop this process during the upgrade, and setup will present an error that it cannot continue because sqlbrowser.exe is still active.

Issue 2: Do Not Use SQL Server Authentication

In a clustered environment, where neither server in the SQL cluster physically possesses the hard drives containing the SQL data (they are obviously located on the shared SAN), you must use Windows authentication rather than SQL Server authentication to perform the SP1 upgrade. This issue is not readily apparent. SP1 setup will allow you to proceed after specifying SQL Server authentication and sa password, and will continue through the upgrade process. The only indication that SP1 is not applying properly is a “Failure” status will appear for the Database Services product in the Installation Progress window. If you experience this during your SP1 upgrade, simply cancel out of the setup process and restart it.

Additional Item of Note – Installing Over Remote Desktop Connections:

During the initial installation and configuration of the SQL Server 2005 cluster the following error was encountered:

“Setup failed to start on the remote machine. Check the task scheduler event log on the remote machine”.

The task scheduler event log did not provide any helpful details. The installation was being run on the first node in the cluster over a Remote Desktop session. However, there was also Remote Desktop Connection session active on the second node during that time. This is a show-stopper for the installation – you cannot have any Remote Desktop Sessions active on any of the other nodes in the SQL Cluster. In the event this occurs, you must cancel the installation, end the Remote Desktop Connection, and re-initialize the installation.

Best resource for SQL 2005 clustering:

This is the best resource available for failover clustering in SQL Server 2005 – an excellent whitepaper by Microsoft:

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en