Comparing SQL Server Clustering & Oracle RAC

Very good findings on what high-availability and fail-over options does SQL Server provide. In short SQL Server has fail-over but no high-availability as compared to Oracle RAC.

1. SQL Server 2008 Peer-to-Peer Replication

• Multiple nodes running their own instances
• Each node has its own copy of data
• Every node is a publisher and subscriber at the same time
• Not scalable because of complex architecture
• Complex to modify schema
• Conflicts may arise if two nodes update the same row
• In case of conflict, the topology remains inconsistent until the conflict is resolved
• Conflict is resolved manually using the method described in
  http://technet.microsoft.com/en-us/library/bb934199.aspx

2. SQL Server 2008 Mirroring

• One primary server and more than one mirror instances
• Periodic Log Shipping between primary and secondary servers
• Failover process is manual
• A separate ‘witness’ server can be deployed to automate the fail over
• Secondary servers  do not participate in any transaction and just wait for the failover
• Equivalent to Oracle standby database technology

3. SQL Server 2008 Failover Clustering

• Two servers running on a shared storage
• All data and logs reside on the SAN and is shared
• One server performs all transactions and the other waits for the failover
• Microsoft Cluster Server takes care of the fail over
• Both instances have separate instance names and one cluster instance name
• Clients connect to the cluster IP address and cluster instance name
• Failover is transparent but a delay (in minutes) is required to mount the database on the failover instance and start it
• There is an application blackout during fail over process
• Reference (http://msdn.microsoft.com/en-us/library/aa479364.aspx)

4. SQL Server 2008 Active/Active Failover Clustering

• Two instances running on a shared storage
• Two different SQL Server databases setup on both servers
• Active/Active Clustering is effectively two different failover clusters
• Each node in the cluster is running one primary instance and one secondary instance of the other node
• Both clusters run a synchronized copy of the database
• Replication is setup between both clusters to keep them synchronized
• Clients see two different databases available to connect to
• In case of failure, one server runs both database instances which may cause performance overhead
• Write cost may increase because of replication and database synchronization
• Application blackout will only be for the clients connected to the failed instance
• Peer-to-Peer replication has conflicts (See No. 1)

5. SQL Server 2008 Federated Database

• Multiple instances running in a network connected to each other
• Each instance carry part of the database
• Complete table is formed always using VIEWS and distributed SQL
• Each instance has a VIEW of the table using UNION ALL between all instances called DPV
• Complex to scale up and manage
• Complex to modify the schema because of multiple databases
• May have HOT-NODE syndrome when one node carry most used data

6. Oracle 11g RAC

• Multiple Nodes running on a shared storage
• All nodes are participating
• Nodes are connected to each other using inter-network
• All nodes servicing the single database
• Scalable because of single database
• Entire cluster fails if SAN fails
• Higher performance inter-connect required for cache fusion as nodes increase
• Virtual IP Address is used to connect to all servers
• In case of failure of one node, clients will connect to other nodes on the same IP address on subsequent requests
• 30-60 seconds of delay required for failover
• Application blackout will only be for the clients connected to the failed instance