SQL Server clustering vs. Oracle RAC

 

I did some research on SQL Server clustering a while ago to find out what high-availability and fail-over options does SQL Server provide. Below are my findings. 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

 

Share this post : digg it! Facebook it! live it! reddit! technorati! yahoo!

Advertisements

Oracle Parameter File: Order of lookup

 

Oracle has two type of parameter files for initial parameter configurations: PFILE and SPFILE. The parameter file and the shared parameter file. They are used to specify parameter values at the database startup. The SPFILE is used in a RAC environment and PFILE is used in a stand alone database environment.

The other day I had a problem starting up my RAC and later on it was discovered that the problem was because of these startup files. The recommended way of using an SPFILE in a RAC environment is to specify the (shared) location of your SPFILE in the PFILE. All nodes have their own PFILEs which point to the same SPFILE which is located on a shared storage. I had set it up all right but it was not working. And this was because Oracle searches the configuration files in the specified directory in the following order:

  • spfileSID.ora
  • spfile.ora
  • initSID.ora
  • init.ora

So I had my initSID.ora setup but was not being used because I already had one spfileSID.ora in the same location which always got preference.

 

 

Share this post : digg it! Facebook it! live it! reddit! technorati! yahoo!