Each SQL Server 2008 High Availability option addresses different risks. As we know, no "one" solution fits all. We need to review carefully the business requirements. Basically, we need to consider a few questions like:
- Is Automatic Failover required?
- What is the data granular level for the High Availability?
- How much data loss or downtime is accepted?
- Is the standby server needed to provide read access?
- What are the budget constraints?
It is also very important to know what options are available in the different SQL Server editions before planning for the actual implementation. The below table shows differences between Enterprise and Standard High Availability features.
|Clustering||16-node failover clustering||2-node failover clustering|
|Database Mirroring||Full Support||Partial Support (Single Threaded, Safety Full Only)|
|Log Shipping||Full Support||Full Support|
|Replication||Full Support||Oracle Publishing not supported.|
- Failover Clustering is an ideal selection if there is no budget constraint.
- Database Mirroring is alternative choice with minimal down time, because of automatic failover.
- Log Shipping is a good selection if we want to have multiple secondary servers.
- Replication is a good selection when there is the need to duplicate some of the data, send the data to multiple servers or for disconnected architecture support.
- Multiple options can be used, if there is a need to further protect from failures.
- This is not a complete list of all pros and cons for each option, but I hope this gives you a starting point on which technology to consider for your environment.