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!
live it!
reddit!
technorati!
yahoo!
